Why "object variable or with block variable not set" error?

  • Thread starter Thread starter SSjmg2477
  • Start date Start date
S

SSjmg2477

Any idea why the buttons will not be created when opening the exce
sheet? The deletes work fine, but I am getting an object variable o
with block variable not set error on the first With block. Also if
attach the same code to a custom button and run the macro that way th
buttons do get created. They only are not created when opening th
excel sheet, which is what I am trying to accomplish. Any thoughts?

Sub Workbook_Open()

Application.CommandBars("Assembly Test").Controls(5).Delete
Application.CommandBars("Assembly Test").Controls(4).Delete
Application.CommandBars("Assembly Test").Controls(3).Delete
Application.CommandBars("Assembly Test").Controls(2).Delete
Application.CommandBars("Assembly Test").Controls(1).Delete

With CommandBars("Assembly Test").Controls.Add
.Caption = "Add Test Condition"
.Style = msoButtonIconAndCaption
.OnAction = "Sheet1.CommandButton1_Click"
.BeginGroup = True
.FaceId = 38
End With

With CommandBars("Assembly Test").Controls.Add
.Caption = "Add Main Row for Sub-Test Conditions"
.Style = msoButtonIconAndCaption
.OnAction = "Sheet1.CommandButton2_Click"
.BeginGroup = True
.FaceId = 38
End With

With CommandBars("Assembly Test").Controls.Add
.Caption = "Add Sub-Test Condition"
.Style = msoButtonIconAndCaption
.OnAction = "Sheet1.CommandButton3_Click"
.BeginGroup = True
.FaceId = 38
End With

With CommandBars("Assembly Test").Controls.Add
.Caption = "Add Page Divider Row"
.Style = msoButtonIconAndCaption
.OnAction = "Sheet1.CommandButton4_Click"
.BeginGroup = True
.FaceId = 112
End With

With CommandBars("Assembly Test").Controls.Add
.Caption = "Delete"
.Style = msoButtonIconAndCaption
.OnAction = "Sheet1.CommandButton5_Click"
.BeginGroup = True
.FaceId = 67
End With

End Su
 
In workbook event programming, references to CommandBars NEED the
"Application." in front of it.
So change With CommandBars("Assembly Test")... to With
Application.CommandBars("Assembly Test")...
Bob Umlas
Excel MVP
 
Back
Top