Attaching menus creating problems 1

[Dan15]

Here is the situation: A custom menu bar is created on one computer, then the excel template is put on the server. When the template is put on a different computer, the menu items on the custom menu are not working. This is because they are looking at the original template for the code to execute the macros, even though the macros are copied in the template. I would rather not go to each menu item and re-address the macro location, what is the way to go about fixing this?

Thanks-

-Dan

 

[RobBroekhuis]

In other words, it found two menu items that it wanted to change, and apparently your filename/path string is 61 characters long. And, you say, after this ran, the menu items still didn't work? I don't think I can do much more without actually seeing the workbook in action.
Rob

 

[RobBroekhuis]

I reviewed your workbook, and found that the issue was that each control on your toolbar was a popup populated with a number of other controls, so that the FixIt macro needed to go one level deeper. The following revised macro works:

Sub FixCustomToolbar(strToolBar As String)
Dim objControl As CommandBarControl, oC As CommandBarControl, p As Integer
For Each objControl In Application.CommandBars(strToolBar).Controls
If TypeName(objControl) = "CommandBarPopup" Then
For Each oC In objControl.Controls
p = InStr(oC.OnAction, "!&quot
If p > 0 Then oC.OnAction = Mid(oC.OnAction, p + 1)
Next oC
Else
p = InStr(objControl.OnAction, "!&quot
If p > 0 Then objControl.OnAction = Mid(objControl.OnAction, p + 1)
End If
Next objControl
End Sub

Please let me know if you have any problems getting it to function like it should.

Rob

 

[Dan15]

Same response - I can run it from the editor. I run it, then it returns a prompt asking for me to choose a macro from all the available macros. The macro is run, then I am returned to the editor screen.

Question - does this have anything to do with using command buttons instead of custom menu items?

 

[RobBroekhuis]

Dan,
I made the modification in my previous post to your workbook, and found that, after saving, closing and reopening the file, the menu items from your custom toolbar were operable. There is no reason why you should be prompted to choose a macro.
When you insert the modified code, save, and close, then reopen:
- do you get the prompt you mention when you reopen?
- what happens when you choose one of your custom menu items?

Again, this worked for me, so I'm sure we can get it to work for you...
Rob

 

[Dan15]

Ok, I have the following:

Private Sub Workbook_Open()
FixCustomToolbar "Bidding004"
'FixCustomControlBar "Bidding004"
End Sub

in ThisWorkbook, with Workbook and Open selected.

I also have:

Sub FixCustomToolbar(strToolBar As String)
Dim objControl As CommandBarControl, oC As CommandBarControl, p As Integer
For Each objControl In Application.CommandBars(strToolBar).Controls
If TypeName(objControl) = "CommandBarPopup" Then
For Each oC In objControl.Controls
p = InStr(oC.OnAction, "!&quot
If p > 0 Then oC.OnAction = Mid(oC.OnAction, p + 1)
Next oC
Else
p = InStr(objControl.OnAction, "!&quot
If p > 0 Then objControl.OnAction = Mid(objControl.OnAction, p + 1)
End If
Next objControl
End Sub

located in Module 1

When I open and close the file, nothing happens.

 

[RobBroekhuis]

What do you mean by "nothing happens"? Are the menu items still not operable? You shouldn't SEE anything happen, but the controls should be fixed.
Rob

 

[Dan15]

I mean that the buttons are not changed. They still are not operable. this is now the output in the Immediate Window:

61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
0
0
0
0
0
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61

 

[RobBroekhuis]

I just now realized there's yet another level of menus in your structure. Could you first confirm that the second-level menu items work (e.g. Storm - Overflow drain). If they do, then the additional fix to get the next level taken care of is straightforward.
Rob

 

[Dan15]

You are correct Rob- the second level items work.

 

[Dan15]

Actually, the menus go as deep as 4 or 5 levels.

 

[RobBroekhuis]

Okay, I rewrote the code recursively so that you can have as many levels as you'd like. Try this:

Sub FixControl(objControl As CommandBarControl)
Dim oC As CommandBarControl, p As Integer
If TypeName(objControl) = "CommandBarPopup" Then
For Each oC In objControl.Controls
FixControl oC 'recursive call
Next oC
Else
p = InStr(objControl.OnAction, "!&quot
If p > 0 Then objControl.OnAction = Mid(objControl.OnAction, p + 1)
End If
End Sub

Sub FixCustomToolbar(strToolBar As String)
Dim objControl As CommandBarControl
For Each objControl In Application.CommandBars(strToolBar).Controls
FixControl objControl
Next objControl
End Sub
Rob

 

[Dan15]

Rob-

You are a life saver. This works perfectly now, there is no way I would have figured this out. Thank you for all of your help-

-Dan

 

[RobBroekhuis]

You're welcome. I'm going to keep the code for myself, too
Rob

 

[Dan15]

Hey Rob, if you have any time- I have another problem I'm looking at with that spreadsheet, maybe you know a quick solution - I would like to create a button that does the following: for each line item entered, the font color of specific cells on that same row is changed to white. This way I can print out that spreadsheet without showing all of the prices. I was thinking that this could be accomplished with creating a column where if a line item is added, a 1 is put in the cell of that column. Then when you activate this "white-out" button, it reads that column, and where there is a 1, the corresponding cells in that row are colored white.

Thanks again-

-Dan

 

[RobBroekhuis]

That would be fairly straightforward. In the code triggered by your button, you could do

for each cell in range("Z&quot.specialcells(xlcellconstants)
if cell=1 then range(cells(cell.row,1),cells(cell.row,5)).font.color=vbwhite
next cell

this example assumes you put the 1 manually into column Z, and colums A:E are the ones you want hidden. You can adjust as needed.
Rob

 

[RobBroekhuis]

Oops - that should be xlCellTypeConstants.
Rob

 

[Dan15]

Ok, I'm getting an error in the first line:

for each cell in range("Z&quot.specialcells(xlcellconstants)

I am trying to reference column K in place of Z, but I do not know the syntax.

Along those lines, do you know a good reference for VB code? I get tripped up with the actual coding, just on the basic level of reference cells, columns, etc.

 

[RobBroekhuis]

Sorry, that should be

columns("K&quot.specialcells(xlCellTypeConstants)

Any book on programming in Excel should help you on your way. I started with "Excel 2000 Programming for Dummies", which is basic but useful. I've also read (but unfortunately don't own) John Walkenbach's more comprehensive book on the topic. I'd go to Amazon and read some of the reviews to see if any of the more recent books come more highly recommended.
Rob

 

[Dan15]

cell coloring works fine.
Thanks again-

-Dan

 

[Dan15]

The following line of code worked great until XP was installed. Now it is coming up as an error

For Each objControl In Application.CommandBars(strToolBar).Controls

What do I need to change here to make this work?

Thanks-

-Dan