Hi,

I have a woksheet that I share with users. There is macro to insert row(s)
as needed. Some users INSERTS ROWS MANUALLY that makes the other macros in
the worksheet fail.

Is there a macro that will PREVENT users to Mannually Insert a Row and at
the same time my macro to automatically insert a row will still work?

Thank you.

Protect the worksheet and modify your macro to unprotect the sheet, insert the
rows, protect the sheet.

You may want to provide them with a macro to delete rows, too.

Be aware that lots of things are disabled if the worksheet is protected. You'll
want to test before you distribute.

And worksheet protection is easily broken--so it's not foolproof.

Danny wrote:

Hi,

I have a woksheet that I share with users. There is macro to insert row(s)
as needed. Some users INSERTS ROWS MANUALLY that makes the other macros in
the worksheet fail.

Is there a macro that will PREVENT users to Mannually Insert a Row and at
the same time my macro to automatically insert a row will still work?

Thank you.

--

Dave Peterson

Thanks for your response. I copied this macro from this NG that does not show
the Insert Bar.

However, the user will still be able to use a shortcut key. I can't find a
solution to that. If I protect the sheet, there will be more problems.

I hope that you can help me on preventing the user to use a shortcut key to
insert a row

Sub auto_Open()

'Disable/enable all of the POP-up Command Bars.
Dim CB As CommandBar
For Each CB In CommandBars
If CB.Type = msoBarTypePopup Then CB.Enabled = False
Next CB

'DISABLE/enable "customizing" toolbars.
CommandBars("Toolbar List").Enabled = False

'Disable/enable all Command Bars
For Each CB In Application.CommandBars
CB.Enabled = False
Next CB

'Disable/enable SELECTED Command Bar
Application.CommandBars("Worksheet Menu Bar").Enabled = True

'Disable/enable Wk Sheet Menu Controls

Application.CommandBars("Worksheet menu bar").Controls("Insert").Visible
= False




End Sub

"Dave Peterson" wrote:

Protect the worksheet and modify your macro to unprotect the sheet, insert the
rows, protect the sheet.

You may want to provide them with a macro to delete rows, too.

Be aware that lots of things are disabled if the worksheet is protected. You'll
want to test before you distribute.

And worksheet protection is easily broken--so it's not foolproof.

Danny wrote:

Hi,

I have a woksheet that I share with users. There is macro to insert row(s)
as needed. Some users INSERTS ROWS MANUALLY that makes the other macros in
the worksheet fail.

Is there a macro that will PREVENT users to Mannually Insert a Row and at
the same time my macro to automatically insert a row will still work?

Thank you.

--

Dave Peterson

You can catch the shortcut key with code like:

Option Explicit
Sub turnitoff()
Application.OnKey "^{107}", ""
End Sub
Sub turniton()
Application.OnKey "^{107}"
End Sub


I tried
Option Explicit
Sub turnitoff()
Application.OnKey "^{+}", ""
End Sub

But that didn't work for the + key on the numeric keypad.

But Jim Cone suggested {107} for use with that key--it worked for me on my
standard USA keyboard.


Danny wrote:

Thanks for your response. I copied this macro from this NG that does not show
the Insert Bar.

However, the user will still be able to use a shortcut key. I can't find a
solution to that. If I protect the sheet, there will be more problems.

I hope that you can help me on preventing the user to use a shortcut key to
insert a row

Sub auto_Open()

'Disable/enable all of the POP-up Command Bars.
Dim CB As CommandBar
For Each CB In CommandBars
If CB.Type = msoBarTypePopup Then CB.Enabled = False
Next CB

'DISABLE/enable "customizing" toolbars.
CommandBars("Toolbar List").Enabled = False

'Disable/enable all Command Bars
For Each CB In Application.CommandBars
CB.Enabled = False
Next CB

'Disable/enable SELECTED Command Bar
Application.CommandBars("Worksheet Menu Bar").Enabled = True

'Disable/enable Wk Sheet Menu Controls

Application.CommandBars("Worksheet menu bar").Controls("Insert").Visible
= False


End Sub

"Dave Peterson" wrote:

Protect the worksheet and modify your macro to unprotect the sheet, insert the
rows, protect the sheet.

You may want to provide them with a macro to delete rows, too.

Be aware that lots of things are disabled if the worksheet is protected. You'll
want to test before you distribute.

And worksheet protection is easily broken--so it's not foolproof.

Danny wrote:

Hi,

I have a woksheet that I share with users. There is macro to insert row(s)
as needed. Some users INSERTS ROWS MANUALLY that makes the other macros in
the worksheet fail.

Is there a macro that will PREVENT users to Mannually Insert a Row and at
the same time my macro to automatically insert a row will still work?

Thank you.

--

Dave Peterson


--

Dave Peterson

Thanks a lot Dave. Have a great evening!

"Dave Peterson" wrote:

You can catch the shortcut key with code like:

Option Explicit
Sub turnitoff()
Application.OnKey "^{107}", ""
End Sub
Sub turniton()
Application.OnKey "^{107}"
End Sub


I tried
Option Explicit
Sub turnitoff()
Application.OnKey "^{+}", ""
End Sub

But that didn't work for the + key on the numeric keypad.

But Jim Cone suggested {107} for use with that key--it worked for me on my
standard USA keyboard.


Danny wrote:

Thanks for your response. I copied this macro from this NG that does not show
the Insert Bar.

However, the user will still be able to use a shortcut key. I can't find a
solution to that. If I protect the sheet, there will be more problems.

I hope that you can help me on preventing the user to use a shortcut key to
insert a row

Sub auto_Open()

'Disable/enable all of the POP-up Command Bars.
Dim CB As CommandBar
For Each CB In CommandBars
If CB.Type = msoBarTypePopup Then CB.Enabled = False
Next CB

'DISABLE/enable "customizing" toolbars.
CommandBars("Toolbar List").Enabled = False

'Disable/enable all Command Bars
For Each CB In Application.CommandBars
CB.Enabled = False
Next CB

'Disable/enable SELECTED Command Bar
Application.CommandBars("Worksheet Menu Bar").Enabled = True

'Disable/enable Wk Sheet Menu Controls

Application.CommandBars("Worksheet menu bar").Controls("Insert").Visible
= False


End Sub

"Dave Peterson" wrote:

Protect the worksheet and modify your macro to unprotect the sheet, insert the
rows, protect the sheet.

You may want to provide them with a macro to delete rows, too.

Be aware that lots of things are disabled if the worksheet is protected. You'll
want to test before you distribute.

And worksheet protection is easily broken--so it's not foolproof.

Danny wrote:

Hi,

I have a woksheet that I share with users. There is macro to insert row(s)
as needed. Some users INSERTS ROWS MANUALLY that makes the other macros in
the worksheet fail.

Is there a macro that will PREVENT users to Mannually Insert a Row and at
the same time my macro to automatically insert a row will still work?

Thank you.

--

Dave Peterson


--

Dave Peterson