Disable Insert button code works fine, but some times throws Run-Time error '5'

**challasl** · 11-17-2016, 08:59 AM

Hi,
Below is my code in sheet1 . Sometimes, this is working fine( Disabling "Insert" menu option ). But when I close and reopen the Excel/view another sheet and revisit sheet1 , Run-Time error '5' with "Invalid procedure call or argument" error is popping up at Application.CommandBars block. There is no consistency in the error line . Could some one please help me out on how to fix this issue. Thanks in advance.

Public Sub Worksheet_Activate()
With CommandBars("Worksheet Menu Bar")
.Controls("Insert").Controls("Rows").Enabled = False
.Controls("Insert").Controls("Columns").Enabled = False
End With
With Application.CommandBars("Row")
.Controls("Insert").Enabled = False
End With
With Application.CommandBars("Cell")
.Controls("&Insert...").Enabled = False
End With
End Sub

Private Sub Worksheet_Deactivate()
With CommandBars("Worksheet Menu Bar")
.Controls("Insert").Controls("Rows").Enabled = True
.Controls("Insert").Controls("Columns").Enabled = True
End With
With Application.CommandBars("Row")
.Controls("Insert").Enabled = True
End With
With Application.CommandBars("CELL")
.Controls("&Insert...").Enabled = True
End With
End Sub

**cytop** · 11-17-2016, 09:15 AM

The Insert option for the Row Commandbar also uses the '&' accelerator same as the option in the Cell commandbar.

**challasl** · 11-17-2016, 09:24 AM

Thanks for the quick reply. So for Row command bar, should I use Controls("&Insert...").Enabled = <XXX> or just Controls("&Insert").Enabled = <XXX>
Please suggest as I am new to VBA.

**cytop** · 11-17-2016, 10:48 AM

Why don't you simply just try it? It would give you an answer a lot quicker than posting here and waiting for someone to reply.

**challasl** · 11-18-2016, 02:27 AM

Hi, I have tried with both "&Insert" and "&Insert..." . While "&Insert..." was throwing Run-Time '5' error on both my local machine and VM ,
"&Insert" was working on my Local machine Excel, but still throwing same error in my VM. Both the machines have Excel 2007.

This issue seems to be very weird as same code is working fine at times and suddenly RunTime error 5 pops up. Could you please Guide. Thanks.

**cytop** · 11-18-2016, 03:52 AM

Sorry, I can't help with the Virtual Machine issue.

All I can say is the menu name must exactly match the caption displayed including any trailing ellipses, if they are used. The ampersand should be placed before the underlined letter, if any.

**challasl** · 11-18-2016, 05:41 AM

Thanks for your support. I was finally able to fix this issue with below code change. Seems like hardcoding the menu option name was creating the problem..
Set CtrlMenu = Application.CommandBars("Row")
For Each Item In CtrlMenu.Controls
If Item.Caption Like "*Insert*" Then
Item.Enabled = False
Exit For
End If
Next

Set CtrlMenu = Application.CommandBars("cell")
For Each Item In CtrlMenu.Controls
If Item.Caption Like "*Insert*" Then
Item.Enabled = False
Exit For
End If
Next

Disable Insert button code works fine, but some times throws Run-Time error '5'

LinkBack

Thread Tools

Rate This Thread

Display

Disable Insert button code works fine, but some times throws Run-Time error '5'

Re: Disable Insert button code works fine, but some times throws Run-Time error '5'

Re: Disable Insert button code works fine, but some times throws Run-Time error '5'

Re: Disable Insert button code works fine, but some times throws Run-Time error '5'

Re: Disable Insert button code works fine, but some times throws Run-Time error '5'

Re: Disable Insert button code works fine, but some times throws Run-Time error '5'

Re: Disable Insert button code works fine, but some times throws Run-Time error '5'

Thread Information

Users Browsing this Thread

Similar Threads

error on disable/insert row disable code

[SOLVED] VBA Program works some times and some times gives me a Run-time error '1004'?

vba code throws an error first time but runs perfectly the second time. HELP!!

[SOLVED] Code Gives error if I run it from Module but works fine when run it from This Worksheet

Getting this code to work on MAC (ERROR 68) - works fine on Windows

macro works fine once than scrambles everything the second time

Bookmarks

Bookmarks

Posting Permissions