Adding a control to the Ribbonx

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,152
I've searched Ron de Bruin's site, but did not find anything that would fit.

Right now I have a button on the ribbon which when pressed runs a macro which stores a True on a hidden worksheet thereby not allowing that button to run the macro again.

Is there such a thing as a button you can put on the ribbon which when pressed would stay depressed therefore not allowing the macro to run again and not just in the current session, rather, for any time you reopen Excel.

The best I could find would be a toggle button, but not sure this will work as in would allow the button to be clicked again.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi Jef

In the first line of the macro test the value of the cell in the hidden sheet.
then run the other code if it is not True

You can give the user info with a msgbox if you want why the button only can be used ones if it is True
 
Upvote 0
Hi Ron,

Thanks for the reply.

The more I thought about it, I realized I was asking for a method which could be easily obtained through multiple tabs; therefore, after the initial setup, the next tab just won't have any buttons to run the setup macros again.

I created two tabs by employing your "GetVisible" method from your site.

Code:
 < tab id="MyCustomTab1" label="First" insertAfterMso="TabHome" 
getVisible="GetVisible" tag="MySetupTab" >
Code:
 < tab id="MyCustomTab2" label="Second" insertAfterMso="TabHome" 
getVisible="GetVisible" tag="MyAfterSetupTab" >

Code:
Private Sub Workbook_Open()
    Call WhichTab
End Sub

Code:
Sub WhichTab()
If Sheet1.Range("A1").Value = True Then
    Call RefreshRibbon(Tag:="MySetupTab")
    Else: Call RefreshRibbon(Tag:="MyAfterSetupTab")
End If
End Sub

The tabs work as expexted, but I get the msgbox below.

Code:
Sub RefreshRibbon(Tag As String)
    MyTag = Tag
    If Rib Is Nothing Then
        MsgBox "Error, Save/Restart your workbook"
    Else
        Rib.Invalidate
    End If
End Sub

Could you help me understand where I might be going wrong with this method?
 
Upvote 0
Hi Jeff

Send me your test workbook then i will look at it tomorrow.
You can find my mail address on my site
 
Upvote 0
Hi Jeff

Strange one, looks like that the tag MySetupTab is used in Excel ?

Rename this tag like "MySetupTabtest" and it will work correct
 
Upvote 0
I have my program as an Add-in - so I write the setting into sheet1 belonging to the Add-in - in your case the Button.

Then when you close Excel (if that is what determines a new session) - I use code below (to save my settings) - that way I can carry my settings forward to next time Excel is opened. It sounds like you can do the same - If I understood you correctly.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save 'This saves the sheet1 content in my XLAM file
End Sub

I guess you have some other way to reactivate the Button.
 
Upvote 0
There is something else going on, I see different behavior
I see if I have time to spend more time to look for the problem
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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