Error 91 Object Not Set: Can I initialise variables via code? [Excel 03]

[Dynamo Nath]

I'm attempting to write some code that creates a toolbar with some buttons on it that carries out some simple instructions; this is just a test before I try and incorporate it into the real spreadsheet.

The first sub creates the toolbar and its commandbarbuttons and their properties. The second sub checks which sheet is active and whether or not the macros attached to the commandbarbuttons can be run or not. If it can be run it calls the code below.

Everything works fine until an error occurs, either deliberately by stopping the code or via an error. I get error 91 - Object variable not set. I think this is caused by the variables being reset/not initialised once the error has been cleared. The button on the spreadsheet used to start the first macro running doesn't initialise the variables; the only way I can do it is by manually running the first macro from within the code editor.

Is it possible to re-initialise the variables via code?

The .State is the where the error occurs.

Thanks in advance.

Public Sub button_command()
'* Uses the tag property of the commandbar button for the Select Case
'* to execute the relevant code
      
    Select Case Application.CommandBars.ActionControl.Tag
    Case "button1"
        With mycbb
            Select Case .State
                Case "0"
                    MsgBox "hello"
                    .State = -1 'msoButtonDown
                Case "-1"
                    MsgBox "wibble"
                    .State = 0 'msoButtonUp
            End Select
        End With
    Case "button2"
        Call button_msg
    End Select
    
End Sub

 

[Andrew Poulsom]

What is mycbb supposed to be? You haven't assigned anything to it.

 

[Dynamo Nath]

Ah sorry. Forget to say that it is a commandbarbutton that I have named at the module level, hence why it doesn't appear in that sub. The other button (case 2) is called mycbb2.

 

[RoryA]

You could add a test to see if mycbb is nothing - if so, rerun the initialise routine. This seems like a very odd way of doing things though - can you not simply use Actioncontrol directly, or refer to the control you need by name?

 

[Dynamo Nath]

Hi Rory,

I'll try testing to see if mycbb is nothing. How do I rerun the initialise routine without recreating the toolbar again?

Sorry, which bit is odd? I've been trying to follow the process through from a book (Pro Excel Development) and by using the help to make things run a bit better/simpler to understand. It was supposed to be an exercise in using a table driven approach to creating the toolbar and associated controls but that book, whilst good, isn't the easiest to follow at times.

Also, I've not used actioncontrol before so not completely sure what it does.

 

[RoryA]

Actioncontrol is a reference to the toolbar button you clicked to run the code.

 

[Dynamo Nath]

I added in a test that runs after the error occurs/I reset the project and it returns the object is "nothing". Presumably at this point I could get it to re-initialise the objects but not sure how. Would that be a call back to the procedure that created the commandbar and the controls?

 

[Norie]

Where and how exactly dud you declare cbb?

Also, where does it get a value?

 

[Dynamo Nath]

Norie,

mycbb was declared at module level as office.commandbarbutton. The following code then creates the toolbar and buttons.

Public Sub make_toolbar()
'* Creates the toolbar with two buttons.
'* Uses .tag to enable the Select Case in the follow on routines

'    Dim mybar As CommandBar
    Dim command As CommandBar
    
    myButtonStatus = Worksheets(1).Range("msoButtonStatus")
    
    Set mybar = Application.CommandBars.Add
    
' checks for existing toolbar of same name and removes it
    For Each command In Application.CommandBars
        If command.Name = "my bar" Then
            command.Delete
        End If
    Next command
    
    With mybar
        .Visible = True
        .Position = msoBarFloating
        .Name = "my bar"
    End With
    
' adds the buttons to the "my bar" toolbar
    Set mycbb = Application.CommandBars("my bar").Controls.Add(Type:=msoControlButton)
    Set mycbb2 = Application.CommandBars("my bar").Controls.Add(Type:=msoControlButton)

    With mycbb
        .Style = msoButtonIconAndCaption
        .FaceId = 20
        .Tag = "button1"
        .Caption = "test"
        .Width = 100
        .State = myButtonStatus
        .OnAction = "worksheet_test"
    End With
    
    With mycbb2
        .Style = msoButtonIconAndCaption
        .FaceId = 25
        .Tag = "button2"
        .Caption = "wibble"
        .Width = 100
        .State = myButtonStatus
        .OnAction = "worksheet_test"
    End With
       
End Sub

 

[Norie]

Sorry, but I still don't see a declaration of cbb or cbb2.

Are you holding back some code?