Hi,

I've got a worksheet protected but I'd like my users to be able to add or
delete rows. I thought I could add a shortcut menu - but it's not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I doing wrong? Or
is there a better way to accomplish this?


Art

Art,

Your code looks good to me. Try changing
.OnAction = "mInsertRows"
' to
.OnAction = "'" & ThisWorkbook.Name & "'!mInsertRows"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Art" wrote in message
...
Hi,

I've got a worksheet protected but I'd like my users to be able
to add or
delete rows. I thought I could add a shortcut menu - but it's
not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I
doing wrong? Or
is there a better way to accomplish this?


Art

Chip,

Thanks -- I found something else. I had the mInsertRows in the Sheet
module. I moved it to a regular module and it started to work.

Thanks for the help.

Art

"Chip Pearson" wrote:

Art,

Your code looks good to me. Try changing
.OnAction = "mInsertRows"
' to
.OnAction = "'" & ThisWorkbook.Name & "'!mInsertRows"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Art" wrote in message
...
Hi,

I've got a worksheet protected but I'd like my users to be able
to add or
delete rows. I thought I could add a shortcut menu - but it's
not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I
doing wrong? Or
is there a better way to accomplish this?


Art

Good afternoon, Chip!

I stumbled onto this whilst looking for a way to disable all ShortCut menus
(any advice gratefully appreciated!)

I tried to modify the example to the following, but it doesn't want to work.
I get "Invalid procedure Call or Argument on the SET line.

Any ideas?

Thanks & have a good weekend

Pete

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Su
-----------------------------------------------------------------------------------------------
"Chip Pearson" wrote:

Art,

Your code looks good to me. Try changing
.OnAction = "mInsertRows"
' to
.OnAction = "'" & ThisWorkbook.Name & "'!mInsertRows"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Art" wrote in message
...
Hi,

I've got a worksheet protected but I'd like my users to be able
to add or
delete rows. I thought I could add a shortcut menu - but it's
not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I
doing wrong? Or
is there a better way to accomplish this?


Art

The code ran fine for me. However, if you run it twice. you may want to put
in

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
On Error Resume Next
Application.CommandBars("Edit Menu").Delete
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in message
...
Good afternoon, Chip!

I stumbled onto this whilst looking for a way to disable all ShortCut
menus
(any advice gratefully appreciated!)

I tried to modify the example to the following, but it doesn't want to
work.
I get "Invalid procedure Call or Argument on the SET line.

Any ideas?

Thanks & have a good weekend

Pete

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub
--------------------------------------------------------------------------
---------------------
"Chip Pearson" wrote:

Art,

Your code looks good to me. Try changing
.OnAction = "mInsertRows"
' to
.OnAction = "'" & ThisWorkbook.Name & "'!mInsertRows"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Art" wrote in message
...
Hi,

I've got a worksheet protected but I'd like my users to be able
to add or
delete rows. I thought I could add a shortcut menu - but it's
not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I
doing wrong? Or
is there a better way to accomplish this?


Art

Tom,

It just goes in a normal module?

Regards

Pete



"Tom Ogilvy" wrote:

The code ran fine for me. However, if you run it twice. you may want to put
in

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
On Error Resume Next
Application.CommandBars("Edit Menu").Delete
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in message
...
Good afternoon, Chip!

I stumbled onto this whilst looking for a way to disable all ShortCut
menus
(any advice gratefully appreciated!)

I tried to modify the example to the following, but it doesn't want to
work.
I get "Invalid procedure Call or Argument on the SET line.

Any ideas?

Thanks & have a good weekend

Pete

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub
--------------------------------------------------------------------------
---------------------
"Chip Pearson" wrote:

Art,

Your code looks good to me. Try changing
.OnAction = "mInsertRows"
' to
.OnAction = "'" & ThisWorkbook.Name & "'!mInsertRows"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Art" wrote in message
...
Hi,

I've got a worksheet protected but I'd like my users to be able
to add or
delete rows. I thought I could add a shortcut menu - but it's
not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I
doing wrong? Or
is there a better way to accomplish this?


Art

Yes, as written it would go in a normal module. But it isn't going to run
by itself. You would have to trigger it when you want to create the
toolbar.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
The code ran fine for me. However, if you run it twice. you may want to
put
in

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
On Error Resume Next
Application.CommandBars("Edit Menu").Delete
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in message
...
Good afternoon, Chip!

I stumbled onto this whilst looking for a way to disable all ShortCut
menus
(any advice gratefully appreciated!)

I tried to modify the example to the following, but it doesn't want to
work.
I get "Invalid procedure Call or Argument on the SET line.

Any ideas?

Thanks & have a good weekend

Pete

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub

--------------------------------------------------------------------------
---------------------
"Chip Pearson" wrote:

Art,

Your code looks good to me. Try changing
.OnAction = "mInsertRows"
' to
.OnAction = "'" & ThisWorkbook.Name & "'!mInsertRows"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Art" wrote in message
...
Hi,

I've got a worksheet protected but I'd like my users to be able
to add or
delete rows. I thought I could add a shortcut menu - but it's
not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I
doing wrong? Or
is there a better way to accomplish this?


Art

Hi, Tom,

Not quite sure what i was doing wrong, but it's OK now.
Don't suppose you have any ideas on how to disable the shortcut
menus(specifically the worksheet one), do you? Would I have to remove all the
commands one at a time, then do a reset later on?
I want to stop users deleting rows by any means other than those which I
provide in my system.

Thanks again

Pete




"Tom Ogilvy" wrote:

Yes, as written it would go in a normal module. But it isn't going to run
by itself. You would have to trigger it when you want to create the
toolbar.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
The code ran fine for me. However, if you run it twice. you may want to
put
in

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
On Error Resume Next
Application.CommandBars("Edit Menu").Delete
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in message
...
Good afternoon, Chip!

I stumbled onto this whilst looking for a way to disable all ShortCut
menus
(any advice gratefully appreciated!)

I tried to modify the example to the following, but it doesn't want to
work.
I get "Invalid procedure Call or Argument on the SET line.

Any ideas?

Thanks & have a good weekend

Pete

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub

--------------------------------------------------------------------------
---------------------
"Chip Pearson" wrote:

Art,

Your code looks good to me. Try changing
.OnAction = "mInsertRows"
' to
.OnAction = "'" & ThisWorkbook.Name & "'!mInsertRows"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Art" wrote in message
...
Hi,

I've got a worksheet protected but I'd like my users to be able
to add or
delete rows. I thought I could add a shortcut menu - but it's
not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I
doing wrong? Or
is there a better way to accomplish this?


Art

commandbars("Cell").Enabled = True

for the Sheet Tab (if that is of interest) use "Ply"

--
Regards,
Tom Ogilvy



"Peter Rooney" wrote in message
...
Hi, Tom,

Not quite sure what i was doing wrong, but it's OK now.
Don't suppose you have any ideas on how to disable the shortcut
menus(specifically the worksheet one), do you? Would I have to remove all
the
commands one at a time, then do a reset later on?
I want to stop users deleting rows by any means other than those which I
provide in my system.

Thanks again

Pete




"Tom Ogilvy" wrote:

Yes, as written it would go in a normal module. But it isn't going to
run
by itself. You would have to trigger it when you want to create the
toolbar.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
The code ran fine for me. However, if you run it twice. you may want
to
put
in

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
On Error Resume Next
Application.CommandBars("Edit Menu").Delete
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in
message
...
Good afternoon, Chip!

I stumbled onto this whilst looking for a way to disable all
ShortCut
menus
(any advice gratefully appreciated!)

I tried to modify the example to the following, but it doesn't want
to
work.
I get "Invalid procedure Call or Argument on the SET line.

Any ideas?

Thanks & have a good weekend

Pete

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub


--------------------------------------------------------------------------
---------------------
"Chip Pearson" wrote:

Art,

Your code looks good to me. Try changing
.OnAction = "mInsertRows"
' to
.OnAction = "'" & ThisWorkbook.Name & "'!mInsertRows"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Art" wrote in message
...
Hi,

I've got a worksheet protected but I'd like my users to be able
to add or
delete rows. I thought I could add a shortcut menu - but it's
not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I
doing wrong? Or
is there a better way to accomplish this?


Art

There's always a simpler way, isn't there? :-)

Thanks for this, Tom!

Regards

Pete



"Tom Ogilvy" wrote:

commandbars("Cell").Enabled = True

for the Sheet Tab (if that is of interest) use "Ply"

--
Regards,
Tom Ogilvy



"Peter Rooney" wrote in message
...
Hi, Tom,

Not quite sure what i was doing wrong, but it's OK now.
Don't suppose you have any ideas on how to disable the shortcut
menus(specifically the worksheet one), do you? Would I have to remove all
the
commands one at a time, then do a reset later on?
I want to stop users deleting rows by any means other than those which I
provide in my system.

Thanks again

Pete




"Tom Ogilvy" wrote:

Yes, as written it would go in a normal module. But it isn't going to
run
by itself. You would have to trigger it when you want to create the
toolbar.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
The code ran fine for me. However, if you run it twice. you may want
to
put
in

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
On Error Resume Next
Application.CommandBars("Edit Menu").Delete
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in
message
...
Good afternoon, Chip!

I stumbled onto this whilst looking for a way to disable all
ShortCut
menus
(any advice gratefully appreciated!)

I tried to modify the example to the following, but it doesn't want
to
work.
I get "Invalid procedure Call or Argument on the SET line.

Any ideas?

Thanks & have a good weekend

Pete

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub


--------------------------------------------------------------------------
---------------------
"Chip Pearson" wrote:

Art,

Your code looks good to me. Try changing
.OnAction = "mInsertRows"
' to
.OnAction = "'" & ThisWorkbook.Name & "'!mInsertRows"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Art" wrote in message
...
Hi,

I've got a worksheet protected but I'd like my users to be able
to add or
delete rows. I thought I could add a shortcut menu - but it's
not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I
doing wrong? Or
is there a better way to accomplish this?


Art

Tom,

Why's it called "Ply" (or is it just the way it is?)

Regards

Pete



"Tom Ogilvy" wrote:

commandbars("Cell").Enabled = True

for the Sheet Tab (if that is of interest) use "Ply"

--
Regards,
Tom Ogilvy



"Peter Rooney" wrote in message
...
Hi, Tom,

Not quite sure what i was doing wrong, but it's OK now.
Don't suppose you have any ideas on how to disable the shortcut
menus(specifically the worksheet one), do you? Would I have to remove all
the
commands one at a time, then do a reset later on?
I want to stop users deleting rows by any means other than those which I
provide in my system.

Thanks again

Pete




"Tom Ogilvy" wrote:

Yes, as written it would go in a normal module. But it isn't going to
run
by itself. You would have to trigger it when you want to create the
toolbar.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
The code ran fine for me. However, if you run it twice. you may want
to
put
in

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
On Error Resume Next
Application.CommandBars("Edit Menu").Delete
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in
message
...
Good afternoon, Chip!

I stumbled onto this whilst looking for a way to disable all
ShortCut
menus
(any advice gratefully appreciated!)

I tried to modify the example to the following, but it doesn't want
to
work.
I get "Invalid procedure Call or Argument on the SET line.

Any ideas?

Thanks & have a good weekend

Pete

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub


--------------------------------------------------------------------------
---------------------
"Chip Pearson" wrote:

Art,

Your code looks good to me. Try changing
.OnAction = "mInsertRows"
' to
.OnAction = "'" & ThisWorkbook.Name & "'!mInsertRows"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Art" wrote in message
...
Hi,

I've got a worksheet protected but I'd like my users to be able
to add or
delete rows. I thought I could add a shortcut menu - but it's
not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I
doing wrong? Or
is there a better way to accomplish this?


Art

Ply can be used to mean layer (like in plywood).

Each sheet in a workbook can kind of look like a different layer.

Why the developers choose "Ply" instead of something like "SheetTab" is anyone's
guess.

Peter Rooney wrote:

Tom,

Why's it called "Ply" (or is it just the way it is?)

Regards

Pete

"Tom Ogilvy" wrote:

commandbars("Cell").Enabled = True

for the Sheet Tab (if that is of interest) use "Ply"

--
Regards,
Tom Ogilvy



"Peter Rooney" wrote in message
...
Hi, Tom,

Not quite sure what i was doing wrong, but it's OK now.
Don't suppose you have any ideas on how to disable the shortcut
menus(specifically the worksheet one), do you? Would I have to remove all
the
commands one at a time, then do a reset later on?
I want to stop users deleting rows by any means other than those which I
provide in my system.

Thanks again

Pete




"Tom Ogilvy" wrote:

Yes, as written it would go in a normal module. But it isn't going to
run
by itself. You would have to trigger it when you want to create the
toolbar.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
The code ran fine for me. However, if you run it twice. you may want
to
put
in

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
On Error Resume Next
Application.CommandBars("Edit Menu").Delete
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in
message
...
Good afternoon, Chip!

I stumbled onto this whilst looking for a way to disable all
ShortCut
menus
(any advice gratefully appreciated!)

I tried to modify the example to the following, but it doesn't want
to
work.
I get "Invalid procedure Call or Argument on the SET line.

Any ideas?

Thanks & have a good weekend

Pete

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub


--------------------------------------------------------------------------
---------------------
"Chip Pearson" wrote:

Art,

Your code looks good to me. Try changing
.OnAction = "mInsertRows"
' to
.OnAction = "'" & ThisWorkbook.Name & "'!mInsertRows"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Art" wrote in message
...
Hi,

I've got a worksheet protected but I'd like my users to be able
to add or
delete rows. I thought I could add a shortcut menu - but it's
not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I
doing wrong? Or
is there a better way to accomplish this?


Art













--

Dave Peterson

I was thinking of plywood "sheets" maybe a little bit easier
to remember but not as correct as your explanation.


"Dave Peterson" wrote in message ...
Ply can be used to mean layer (like in plywood).

Each sheet in a workbook can kind of look like a different layer.

Why the developers choose "Ply" instead of something like "SheetTab" is anyone's
guess.

Peter Rooney wrote:

Tom,

Why's it called "Ply" (or is it just the way it is?)

Regards

Pete

"Tom Ogilvy" wrote:

commandbars("Cell").Enabled = True

for the Sheet Tab (if that is of interest) use "Ply"

--
Regards,
Tom Ogilvy



"Peter Rooney" wrote in message
...
Hi, Tom,

Not quite sure what i was doing wrong, but it's OK now.
Don't suppose you have any ideas on how to disable the shortcut
menus(specifically the worksheet one), do you? Would I have to remove all
the
commands one at a time, then do a reset later on?
I want to stop users deleting rows by any means other than those which I
provide in my system.

Thanks again

Pete




"Tom Ogilvy" wrote:

Yes, as written it would go in a normal module. But it isn't going to
run
by itself. You would have to trigger it when you want to create the
toolbar.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
The code ran fine for me. However, if you run it twice. you may want
to
put
in

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
On Error Resume Next
Application.CommandBars("Edit Menu").Delete
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in
message
...
Good afternoon, Chip!

I stumbled onto this whilst looking for a way to disable all
ShortCut
menus
(any advice gratefully appreciated!)

I tried to modify the example to the following, but it doesn't want
to
work.
I get "Invalid procedure Call or Argument on the SET line.

Any ideas?

Thanks & have a good weekend

Pete

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub


--------------------------------------------------------------------------
---------------------
"Chip Pearson" wrote:

Art,

Your code looks good to me. Try changing
.OnAction = "mInsertRows"
' to
.OnAction = "'" & ThisWorkbook.Name & "'!mInsertRows"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Art" wrote in message
...
Hi,

I've got a worksheet protected but I'd like my users to be able
to add or
delete rows. I thought I could add a shortcut menu - but it's
not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I
doing wrong? Or
is there a better way to accomplish this?


Art













--

Dave Peterson

Dave,

Thanks for this! I'm sure I would have guessed it in 10,000 years or so! :-)

Regards

Pete



"Dave Peterson" wrote:

Ply can be used to mean layer (like in plywood).

Each sheet in a workbook can kind of look like a different layer.

Why the developers choose "Ply" instead of something like "SheetTab" is anyone's
guess.

Peter Rooney wrote:

Tom,

Why's it called "Ply" (or is it just the way it is?)

Regards

Pete

"Tom Ogilvy" wrote:

commandbars("Cell").Enabled = True

for the Sheet Tab (if that is of interest) use "Ply"

--
Regards,
Tom Ogilvy



"Peter Rooney" wrote in message
...
Hi, Tom,

Not quite sure what i was doing wrong, but it's OK now.
Don't suppose you have any ideas on how to disable the shortcut
menus(specifically the worksheet one), do you? Would I have to remove all
the
commands one at a time, then do a reset later on?
I want to stop users deleting rows by any means other than those which I
provide in my system.

Thanks again

Pete




"Tom Ogilvy" wrote:

Yes, as written it would go in a normal module. But it isn't going to
run
by itself. You would have to trigger it when you want to create the
toolbar.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
The code ran fine for me. However, if you run it twice. you may want
to
put
in

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
On Error Resume Next
Application.CommandBars("Edit Menu").Delete
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in
message
...
Good afternoon, Chip!

I stumbled onto this whilst looking for a way to disable all
ShortCut
menus
(any advice gratefully appreciated!)

I tried to modify the example to the following, but it doesn't want
to
work.
I get "Invalid procedure Call or Argument on the SET line.

Any ideas?

Thanks & have a good weekend

Pete

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub


--------------------------------------------------------------------------
---------------------
"Chip Pearson" wrote:

Art,

Your code looks good to me. Try changing
.OnAction = "mInsertRows"
' to
.OnAction = "'" & ThisWorkbook.Name & "'!mInsertRows"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Art" wrote in message
...
Hi,

I've got a worksheet protected but I'd like my users to be able
to add or
delete rows. I thought I could add a shortcut menu - but it's
not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I
doing wrong? Or
is there a better way to accomplish this?


Art













--

Dave Peterson

David,

Somehow I think I'll remember this for the rest of my life.

I think I need to get out more. :-)

Regards

Pete

"David McRitchie" wrote:

I was thinking of plywood "sheets" maybe a little bit easier
to remember but not as correct as your explanation.


"Dave Peterson" wrote in message ...
Ply can be used to mean layer (like in plywood).

Each sheet in a workbook can kind of look like a different layer.

Why the developers choose "Ply" instead of something like "SheetTab" is anyone's
guess.

Peter Rooney wrote:

Tom,

Why's it called "Ply" (or is it just the way it is?)

Regards

Pete

"Tom Ogilvy" wrote:

commandbars("Cell").Enabled = True

for the Sheet Tab (if that is of interest) use "Ply"

--
Regards,
Tom Ogilvy



"Peter Rooney" wrote in message
...
Hi, Tom,

Not quite sure what i was doing wrong, but it's OK now.
Don't suppose you have any ideas on how to disable the shortcut
menus(specifically the worksheet one), do you? Would I have to remove all
the
commands one at a time, then do a reset later on?
I want to stop users deleting rows by any means other than those which I
provide in my system.

Thanks again

Pete




"Tom Ogilvy" wrote:

Yes, as written it would go in a normal module. But it isn't going to
run
by itself. You would have to trigger it when you want to create the
toolbar.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
The code ran fine for me. However, if you run it twice. you may want
to
put
in

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
On Error Resume Next
Application.CommandBars("Edit Menu").Delete
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in
message
...
Good afternoon, Chip!

I stumbled onto this whilst looking for a way to disable all
ShortCut
menus
(any advice gratefully appreciated!)

I tried to modify the example to the following, but it doesn't want
to
work.
I get "Invalid procedure Call or Argument on the SET line.

Any ideas?

Thanks & have a good weekend

Pete

Sub AAAShortCutMenu()
Dim InsDelMenu As CommandBar
Set InsDelMenu = CommandBars.Add(Name:="Edit Menu",
Position:=msoBarPopup, Temporary:=True)
With InsDelMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert Rows"
.OnAction = "InsertRows"
End With
End Sub


--------------------------------------------------------------------------
---------------------
"Chip Pearson" wrote:

Art,

Your code looks good to me. Try changing
.OnAction = "mInsertRows"
' to
.OnAction = "'" & ThisWorkbook.Name & "'!mInsertRows"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Art" wrote in message
...
Hi,

I've got a worksheet protected but I'd like my users to be able
to add or
delete rows. I thought I could add a shortcut menu - but it's
not working.

I'm using the following:

-----------------------------------
Set mInsertAndDeleteMenu = CommandBars.Add( _
Name:="Edit Menu", Position:=msoBarPopup, _
Temporary:=True)

With mInsertAndDeleteMenu.Controls.Add(Type:=msoControl Button)
.Caption = "Insert Rows"
.OnAction = "mInsertRows"
End With
-----------------------------------

The menu appears, but mInsertRows does not run. What am I
doing wrong? Or
is there a better way to accomplish this?


Art













--

Dave Peterson