Controls("insert...") Run-time error '5'

[ste23]

I am trying to use the following code but I am getting 'Run-time error '5': Invalid procedure call or argument' at the line of code which should disable the 'insert'
Could anyone give me any advice. When trying to find help for this issue I have found the line of code numerous times and people don't report any issues.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

    Application.CommandBars("cell").Reset
    
    If Not Application.Intersect(Target, Range("A1:G10")) Is Nothing Then
        With Application
        .CommandBars("cell").Controls("delete...").Enabled = False
        .CommandBars("cell").Controls("insert...").Enabled = False
        End With
End Sub

I'm using Excel 2007 on XP [work] and Vista [home]

Thanks

Ste

 

[TMShucks]

Maybe an alternative:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Application.CommandBars("cell").Reset
If Not Application.Intersect(Target, Range("A1:G10")) Is Nothing Then
    With Application
        .CommandBars("cell").Controls("Delete...").Visible = False
        .CommandBars("cell").Controls("Insert...").Visible = False
    End With
End If
End Sub

 

[ste23]

Thanks, the alternative code achieves what I need.

I'm curious to know why my code doesn't work though. Turning enabled to false on "delete..." works fine and 'greys out' the option. However the "insert..." line fails.

"insert..." seems to be the right reference as the visible property works fine.

Thanks again, I'll stick with this for now.

Ste

 

[vds1]

I'm curious to know why my code doesn't work though.
</QUOTE>

There was no "End If"

 

[TMShucks]

@vds1: no, the missing End If stops the code running altogether. However, the "Enabled=False" works for "Delete..." but not for "Insert...". On the other hand, "Visible=False" works in both cases.

@ste23: sorry, I can't explain it. I tried various things like changing the sequence but it just stopped sooner. Changing "Enabled" to "Visible" worked though so, as you say, looks like it is the right reference.

 

[RoryA]

I think it's a timing issue since the caption appears to be changed as the toolbar is displayed. Using Controls("Insert cells") works for me with Enabled (testing in 2010) but interestingly, using

Controls("Insert").Visible

fails every time. Possibly better to do it by control ID - you should also use a commandbar loop really since there are 2 'cell' commandbars.

 

[TMShucks]

Been experimenting in the immediate window:

?Application.CommandBars("cell").Controls("Insert...").Enabled
False
Application.CommandBars("cell").Controls("Insert...").Enabled=True
?Application.CommandBars("cell").Controls("Insert...").Enabled
True
Application.CommandBars("cell").Controls("Insert...").Enabled=False
?Application.CommandBars("cell").Controls("Insert...").Enabled
False
Application.CommandBars("cell").Controls("Insert...").Enabled=True
?Application.CommandBars("cell").Controls("Insert...").Enabled
True
Application.CommandBars("cell").Controls("Insert...").Enabled=False
?Application.CommandBars("cell").Controls("Insert...").Enabled
False
Application.CommandBars("cell").Controls("Insert...").Visible=True
Application.CommandBars("cell").Controls("Delete...").Visible=True

So, you can have the Control Visible and/or Enabled. Not sure it gets any closer to establishing why it doesn't work for you.


On the back of that testing, I also tried this:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Application.CommandBars("cell").Reset
If Intersect(Target, Range("A1:G10")) Is Nothing Then Exit Sub
On Error Resume Next
Application.CommandBars("cell").Controls("Delete...").Enabled = False
Application.CommandBars("cell").Controls("Insert...").Enabled = False
'Application.CommandBars("cell").Controls("Delete...").Visible = False
'Application.CommandBars("cell").Controls("Insert...").Visible = False
On Error GoTo 0
End Sub

Sadly, it only disables "Delete..."

 

[GTO]

Hi All,

In ol' 2000, if I run:

Option Explicit
    
Sub example1()
Dim CB As CommandBar, ctl As CommandBarControl
Dim lRow As Long, n As Long
    
    For Each CB In Application.CommandBars
        If CB.Type = msoBarTypePopup Then
            With Sheet1
    
                lRow = Application.Max(.Cells(.Rows.Count, 1).End(xlUp).Offset(1).Row, _
                                       .Cells(.Rows.Count, 2).End(xlUp).Offset(1).Row)
    
                .Cells(RowIndex:=lRow, _
                       ColumnIndex:=1 _
                       ).Value = CB.Name
                
                n = 0
                For Each ctl In CB.Controls
                    .Cells(RowIndex:=n + lRow, _
                           ColumnIndex:=2 _
                           ).Value = ctl.ID
                           
                    .Cells(RowIndex:=n + lRow, _
                           ColumnIndex:=3 _
                           ).Value = ctl.Caption
                    n = n + 1
                Next
            End With
        End If
    Next
End Sub

I get 3181 and 292 for '&Insert...' and '&Delete...'

On the other hand, if I plunk this in the Before RightClick:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Application.CommandBars("cell").Reset
    
    
'    If Not Application.Intersect(Target, Range("A1:G10")) Is Nothing Then
'        With Application
'            '.CommandBars("Cell").Controls("C&ells...").Enabled = False
'            .CommandBars("Cell").FindControl(, 295).Enabled = False
'            .CommandBars("Cell").Controls("&Delete...").Enabled = False
'        End With
'    End If
'
'Exit Sub
    
Dim cb As CommandBar
Dim ctl As CommandBarControl
Dim myctl As CommandBarControl
    
    Set cb = Application.CommandBars("Cell")
    
    For Each ctl In cb.Controls
        Debug.Print ctl.ID & vbTab & ctl.Caption
        ctl.Enabled = False
    Next
    
    Exit Sub

I get (try and ignore the crappy T2C):

Excel 2000
	J	K	L	M
15	21	Cu&t
16	19	&Copy
17	22	&Paste
18	755	Paste	&Special...
19	295	C&ells...
20	292	&Delete...
21	3125	Clear	Co&ntents
22	2031	Insert	Co&mment
23	1592	Delete	Co&mment
24	1593	Sh&ow	Comment
25	855	&Format	Cells...
26	1966	Pic&k	From	List...
27	1576	&Hyperlink...
28	30094	&Hyperlink
Access Test

So, at least in 2000, the caption and Id seem to be rather iffy. I tried w/both Id and Caption like this, and seems fine:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Application.CommandBars("cell").Reset
    
    
    If Not Application.Intersect(Target, Range("A1:G10")) Is Nothing Then
        With Application
            '.CommandBars("Cell").Controls("C&ells...").Enabled = False
            .CommandBars("Cell").FindControl(, 295).Enabled = False
            .CommandBars("Cell").Controls("&Delete...").Enabled = False
        End With
    End If

Mark

 

[ste23]

Mark

Thanks for that. The FindControl route has worked. Although when I ran the debug print I didn't get the I.D.295 come up so I would never have got the right one. I had previously found the ID of 3181 but being a VBA novice I didn't know about the FindControl method.

Thanks for your help on that.

Ste

 

[ste23]

@ste23: sorry, I can't explain it. I tried various things like changing the sequence but it just stopped sooner. Changing "Enabled" to "Visible" worked though so, as you say, looks like it is the right reference.

@TMShucks - Thanks for your help. It's odd how it doesn't work but we now have a good workaround thanks to GTO.