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

 

[Dynamo Nath]

Sorry, not on purpose.

Dim myButtonStatus As Integer
Dim mycbb As Office.CommandBarButton
Dim mycbb2 As Office.CommandBarButton
Dim mybar As CommandBar

 

[Norie]

No problem.

If you want these variables to be available to other modules you need to declare them as Public.

By the way, are you using Option Explicit?

If you added that in each module it could help with this sort of thing.

 

[Dynamo Nath]

Do I need to specifically declare them as public? They are only being used within the same module at present so I thought that putting them at the top outside of the first procedure declared them public by default...

Yep, using option explicit.

 

[Norie]

So all the code you've posted is in the same module?

Oops, looks like I made a mistake.

When exactly are you running the subs?

 

[Dynamo Nath]

There's a button on the spreadsheet, purely for want of somewhere better to put it, that calls the make_toolbar procedure. The remaining procedures are called once one of mycbb or mycbb2 are pressed. They first check to make sure that sheet1 is the active sheet, if no then exit sub; if true call button_command.

Everything works as expected until such time as I either reset the manually or an error occurs. It's at this point that my understanding gets hazy

 

[Dynamo Nath]

I've split the make_toolbar macro into two parts; first part creates the toolbar, second part adds the buttons. I added a test for "nothing" to the button_command macro which says that if mycbb/mycbb2 is nothing then call the macro that adds the buttons. This seems to resolve the issue but I'm not really sure why... I'm hazarding a guess that this is the re-initialising that I needed to do?

 

[RoryA]

I'm still not seeing why you need the variables after creating the buttons. It seems to me that Actioncontrol (i.e. whichever button was clicked) is all you are interested in.

 

[Dynamo Nath]

Rory,

To be honest me either, I was following the example given in the help. Although it could also have been because I didn't really understand what the example was showing me and therefore incorrectly applied it to this.

The example in the help shows this method for determining which custom button was pressed.

 

[GTO]

Howdy All,

Tired, so maybe (leaning towards probably) a dumb question, but why are we testing for a string instead of TRUE/FALSE?

Case "0"

Mark

 

[Dynamo Nath]

Hi Mark,

The example I was working from had used strings so I just went with that. No other reason than it helped me to follow through what was happening and what was calling what.