CommandBarButton State (msoButtonUp / msoButtonDown)

Greg Truby

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,025
OK - I'm startin' to feel a bit loopy from too much of this :oops: I have looked through all 9 threads returned from a search on terms button, state, msoButtonDown and from what I can see, what I want to do should be possible. But it just ain't workin' for me.

The problem: I have my own little versions of the Formatting and Standard toolbars that I've doctored up to suit my needs. On my "CustomStandard" bar, I've a regular custom button that toggles the VB toolbar's visibility back and forth. I stumbled across the .State property and thought "hey, that looks like a way I can make some of my buttons act like the Bold, Italic buttons, etc.". i.e. the show down/up per the environment. But I cannot get it to even return a state of "msoButtonDown" even when it's running the assigned macro, much less get it to stick in a state of "msoButtonDown". I'm running XL2002. Am I doing something wrong here?

Here's the code I've tried (I pulled the lines that returned the .State property out, but it always returns msoButtonUp. The beginGroup bit was to make sure I was hitting the correct button - that worked - I've got the right button.)

<font face=Courier New><SPAN style="color:#007F00">'------------------------------------------------------------------------------</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> ToggleVBToolbar()
    <SPAN style="color:#00007F">Dim</SPAN> cbButton <SPAN style="color:#00007F">As</SPAN> CommandBarButton
    
    <SPAN style="color:#00007F">Set</SPAN> cbButton = CommandBars("CustomStandard").Controls("Visual Basic Toolbar")
    Application.CommandBars("Visual Basic").Visible = <SPAN style="color:#00007F">Not</SPAN> Application.CommandBars("Visual Basic").Visible
    
    <SPAN style="color:#00007F">If</SPAN> Application.CommandBars("Visual Basic").Visible <SPAN style="color:#00007F">Then</SPAN>
        cbButton.State = msoButtonDown
        <SPAN style="color:#007F00">'cbButton.BeginGroup = True</SPAN>
        MsgBox "down"
    <SPAN style="color:#00007F">Else</SPAN>
        cbButton.State = msoButtonUp
        <SPAN style="color:#007F00">'cbButton.BeginGroup = False</SPAN>
        MsgBox "up"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Thanks for any help y'all can offer.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Well, I think I may know what the problem is (I *think*).

I just created a custom toolbar with two controls, and assigned to the first one, the "TheAction" sub.

Now, uncomment/comment the lines that are there to see what I mean... I *think* that you can only change the State of the control that is running... sort of like a UDF called from a cell, it can only return a value to THAT cell, not to a cell somewhere else.

Here's the code:

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> TheAction()
    <SPAN style="color:#00007F">Dim</SPAN> Btn <SPAN style="color:#00007F">As</SPAN> Office.CommandBarButton
    
    <SPAN style="color:#007F00">'This one works fine</SPAN>
    <SPAN style="color:#007F00">'Set Btn = Application.CommandBars.ActionControl</SPAN>
    
    <SPAN style="color:#007F00">'This one failed (The calling control is  #1)</SPAN>
    <SPAN style="color:#007F00">'Set Btn = Application.CommandBars("Custom 1").Controls(2)</SPAN>
    
    <SPAN style="color:#007F00">'This one worked ok too</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> Btn = Application.CommandBars("Custom 1").Controls(1)
    
    Btn.State = IIf(Btn.State = msoButtonUp, msoButtonDown, msoButtonUp)
    MsgBox "I'm " & IIf(Btn.State = msoButtonDown, "Pressed", "Not pressed")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 
Upvote 0
Nope, wait, on a second test, it seems to work fine on all 3 cases IF the second control has some macro assigned to it. If not, then I get an error -2147467259, "Method 'State' of object '_CommandBarButton' failed"
 
Upvote 0
Juan Pablo,

¡Muchisimas Gracias! Your confirmation that I wasn't barking mad helped a lot. I duplicated what you did, and no luck. Could not get it to work. Every time, the check returned "not pressed". Even went over to a co-worker's machine and tried it and it continued to fail.

But your first question held the key. I was using the "Custom Macro" button (the one that comes with the little smiley face). So I tried starting with the Bold button instead - one that I knew would hold in a down position and voilá, it worked! So I went back and stuck a Bold on my original toolbar, copied over my Image, assigned the same macro as I had on the original toggle VB toolbar button and problem solved. It "sticks" down/up when I want it to.

Thank you again for the assistance. Still don't see why it returned a "not pressed" even while executing. :unsure: I would have thought that even if the button could not "stick" down, at the very least it would consider itself "pressed" until the assigned macro had finished executing. But while that riddle remains unanswered the problem is solved! :biggrin:
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
451
AndyEd
A
S
  • Question
Referencing class variable(s) from another class
Replies
0
Views
575
simurq
S
E
  • Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
2K
profray
P
N
  • Question
toggle button with Tri - State, in two states hide colums and one not...How?
2
Replies
14
Views
4K
Nikko
N
N
  • Question
Trouble copying with multipleRange
Replies
8
Views
585
nmgmarques
N

Forum statistics

Threads
1,215,460
Messages
6,124,949
Members
449,198
Latest member
MhammadishaqKhan

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top