CODEWITHSUNDEEP
vbaexcelworksheet
ilarson007
ilarson007

Reputation: 157

Trying to Disable Command Bar Controls on Worksheet Open VBA

I am trying to disable a bunch of controls when the sheet opens (and then re-enable them when the sheet closes), but when opening the sheet (when the code runs), I get the error "Object variable or With block variable not set. Here is my code:

Option Explicit

Private Sub Workbook_Open()

OptionsDisable

End Sub

Private Sub Workbook_Close()

OptionsEnable

End Sub

Sub OptionsDisable()

Dim myControls As CommandBarControls
Dim ctl As CommandBarControl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=21)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=3181)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=292)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=3125)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=855)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=1576)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=293)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=541)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=3183)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=294)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=542)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=886)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=887)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=883)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=884)
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl

End Sub

Sub OptionsEnable()

Dim myControls As CommandBarControls
Dim ctl As CommandBarControl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=21)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=3181)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=292)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=3125)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=855)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=1576)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=293)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=541)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=3183)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=294)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=542)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=886)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=887)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=883)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=884)
    For Each ctl In myControls
        ctl.Enabled = True
    Next ctl

End Sub

Upvotes: 0

Views: 2665

Answers (1)

user1759942
user1759942

Reputation: 1350

First off, at workbook open the commandbars are not populated. stick your code in a macro and run it after everything is populated. second, there is no control with id 3181 (just fyi) I looped through all of the id's and there is no 3181, this is the second one in the list. so, you'll need to add some error handling in there. like this

If Not myControle Is Nothing Then

'do the loop code

End If

because when a specific control is not found then myControl gets set to Nothing

edit

because the command bars are not populated on workbook open, put the code in the "SheetActivate" method of the workbook, then in the workbook open put

Sheets(2).Activate
Sheets(1).Activate

this will force the sheetactivate event to fire. its there twice because if the workbook opens on the sheet that you put first, (in my example, sheet2) then the activate does not fire. so hense, activate sheet 2, then sheet 1 (incase sheet2 was already open when the workbook opened)

Upvotes: 1

Related Questions