Adding a control to the Ribbonx

[jeffreybrown]

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.

 

[jeffreybrown]

Any thoughts?

 

[Ron de Bruin]

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

 

[jeffreybrown]

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.

 < tab id="MyCustomTab1" label="First" insertAfterMso="TabHome" 
getVisible="GetVisible" tag="MySetupTab" >

 < tab id="MyCustomTab2" label="Second" insertAfterMso="TabHome" 
getVisible="GetVisible" tag="MyAfterSetupTab" >

Private Sub Workbook_Open()
    Call WhichTab
End Sub

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.

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?

 

[Ron de Bruin]

Hi Jeff

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

 

[jeffreybrown]

Hi Ron,

Thanks again for your time...It's on the way.

 

[Ron de Bruin]

Hi Jeff

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

Rename this tag like "MySetupTabtest" and it will work correct

 

[Rasm]

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.

 

[Ron de Bruin]

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