Delete or rename a submenu

[bpeirson]

I have an add-in which when it loads will open a specific workbook and read information from it. This information is then used to name a control button. What is happening is that a custom menu has a sub menu with controls which include specific information thus:
[tt]
Custom menu -->
Sub menu -->
Available hours = 323
Next start date = 12/03/06
[/tt]
At the click of a button I would like the information to update. This means I have to either delete and reinstall the controls with new information or rename them with the new information.

I used the macro recorder to generate code to delete the controls but when I run the VBA it doesn't work.

Any answers?

Or maybe an alternate solution for displaying this info without having a workbook open. I've found that most people can't function in Excel with multiple workbooks open, especially if the open workbook is not maximized.

 

[GVF]

You have a couple of things to consider. When you open the workbook, you must save the information that will be used for the new control caption in a declared Public or Global variable. It will then be available to the code that runs when you push the button. Alternatively, you can have code in the button macro that will retrieve the information (from the workbook) that you want to use for the new caption.

Your custom menubar must be loaded.
You can update the Caption, OnAction, icon, enable etc. of a control on the fly.
The macro recorder doesn't work when customizing menubars.

TheNewCommandName = Activecell.Value
With Commandbars("My Custom Bar").Controls(1).Controls(2)
.Caption = TheNewCommandName
End With

The above snippet will change the caption of the first menu, second command of the custom bar called "My Custom Bar". Whatever code ran before will still run, just the caption that the user sees will change.

With Commandbars("My Custom Bar").Controls(1).Controls(2)
.OnAction = "ADifferentSub"
.FaceID = 38
End With

The above code leaves the caption alone but changes the macro that runs and also changes the icon.

You could also put the code that changes the control into the macro that opens the workbook. That might allow you to eliminate the button you are using to change the control.

Greg