[Lori713]

I'd like to write a little VB code to hide a certain menu item (for instance, Save As) if certain conditions are met. It would look something like the following:

If myFlag = True Then
[hide/disable/gray out the Save As menu bar item]
End If

Visual Basic Help wasn't, well, helpful. I saw a lot about Command Bars, but no real examples of how to hide (enabled = false) them because I don't know the index nor the name(?) of the Save As menu item.

I tried the following to see if I could get it to tell me if "SaveAs" existed... but it bombs on the third line (some rude message about error 424 and an object being required... but I don't know the object name so... waaaahhh).

Sub HideSaveAs()
foundFlag = False
For Each CommandBarButton In CommandBars
If CommandBarButton.Name = "SaveAs" Then
CommandBarButton.Visible = False
foundFlag = True
End If
Next CommandBarButton
If Not foundFlag Then
MsgBox "The collection does not contain a SaveAsForms command bar."
End If
End Sub


Any ideas?

Thanks!

Lori

[Dynamo2000]

Lori, Just a thought, one strategy you might try is to use the Record Macro feature. Start this feature, do what you want (or at lease enuough of it to find the info you are after) and then stop the recording. Look at the code generated and wa la! Good luck!!!

[Lori713]

Thanks for the suggestion. Actually, I usually do that when I've got something repetitive that I can actually access through recording. Unfortunately, graying out (disabling) a menu bar item isn't one of those instances. Thanks anyways!

[AlbertB]

Interesting one Lori.

I'm no expert on Excel or VB unfortunately, but coincidentally I was looking for an explanation of exactly the same thing. I saw it done in the "Minipops" quiz when I tried to cheat and steal the answers , and was curious as to how they could disable a few of the menu options.

I'll follow this one to see if it can be solved, and if I find anything myself I'll pass it on.

Have you thought of putting a similar post in the Business Applications forum so that the Excel experts can get a look?

[Lori713]

I hadn't thought of cross-posting it to the Excel forum. I know I've seen it done before (a very long time ago), so as far as I know it's possible. Thanks!