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

 

[asjeff]

Dan -
I've always found Toolbars in Excel a real pain to transfer as you describe for precisely the same reasons you have found. Thesedays I always create the toolbar programmatically from the personal.xls. I have included some code to help you on your way, but if you decide to use it you will have to change a few things to your requirements. Suggest you stick the code in an autoexec module or similar

Hope this helps.....

Asjeff


Dim cbrCommandBar As CommandBar
Dim cbcCBA As CommandBarButton
Dim cbcCBB As CommandBarButton
Dim cbcCBC As CommandBarButton
Dim cbcCBD As CommandBarButton


Sub Main()

'This will create a custom command bar for the program

On Error Resume Next

'delete any previous version
Application.CommandBars("MyToolbar&quot.Delete

'Add main command bar and position it
Set cbrCommandBar = Application.CommandBars.Add
cbrCommandBar.Name = "MyToolbar"
cbrCommandBar.Position = msoBarTop

'add some controls (comboboxes in this case - change to what you require)
With cbrCommandBar.Controls
Set cbcCBA = .Add(msoControlButton)
Set cbcCBB = .Add(msoControlButton)
Set cbcCBC = .Add(msoControlButton)
Set cbcCBD = .Add(msoControlButton)

'Set properties of command buttons
cbcCBA.Width = 100
cbcCBA.Caption = "Your Caption"
cbcCBA.TooltipText = "Your Tool Tip"

cbcCBB.Width = 100
cbcCBB.Caption = "Your Caption"
cbcCBB.TooltipText = "Your Tool Tip"
'etc etc

cbcCBA.OnAction = "ProcedureA"
cbcCBB.OnAction = "ProcedureB"
cbcCBC.OnAction = "ProcedureC"
cbcCBD.OnAction = "ProcedureD"

End With

cbrCommandBar.Visible = True

End Sub



Sub ProcedureA()
msgbox "A"
End Sub
Sub ProcedureB()
msgbox "B"
End Sub
Sub ProcedureC()
msgbox "C"
End Sub
Sub ProcedureD()
msgbox "D"
End Sub

 

[Dan15]

Unfortunately, I am stuck with the menus, there are way too many menu items to create the menus automatically. I agree that it is better to use your method if there are limited menu items. But I have hundreds of menu items in the drop down menus. Is there anywhere I can change where the menu sources the code? (aside from changing each individual menu item)

Thanks-

 

[RobBroekhuis]

Can you pinpoint EXACTLY when the mismatch occurs? I don't quite understand your first post - simply copying the template to a different computer shouldn't change anything about the internal workings of a workbook. Does it?
Rob

 

[asjeff]

And are the menus attached to the individual Workbooks or to your Personal.xls template?

Asjeff

 

[Dan15]

That is the problem - the custom menus are not specific to a workbook, they are also attached to the excel workspace. When that workbook is moved, the menu items still look in the original workspace for the macros, not in the new location. Apparently, this has something to do with the way a custom menu button is created, whether it is "attached" or "detached". Either way, I cannot seem to correct the situation, once I have gotten to this point. Every single menu item on my computer look back to the original computer to run the macro, instead of in it's own macro file.

 

[RobBroekhuis]

I'm sure there is a remedy. Unfortunately (call me dense) I'm still unclear on the exact situation. If you have computer A and computer B, and workbook MyBook.xls, or a MyTemplate.xlt, could you tell us exactly:

- on which computer was the custom menu created?
- where is the custom menu? in excel.xlb? as a different .xlb?
- where are the macros located?
- what do you mean by "when that workbook is moved"?
- on which computer do the errors happen?
- what is the value of the .onaction property of the commandbarcontrol objects that are no longer working?

Rob

 

[Dan15]

OK, I will try to clarify-

Book1.xls was created on computer A. The custom menu was created when Book1.xls was created. There are about 10 drop down menus, with up to 50 menu items. When a menu item is selected, it puts a number in a specific cell, in the active line. Corresponding cells are filled in with the price and description, from vlookup functions with the data table on sheet2, based on that initial number. Once this was created, the menu items work. Book1.xls was then put on the server. Then it was copied onto Computer 2. When opened, none of the menu items worked. I then went to customize, selected any menu item with a right click, and went to assign macro. The menu path for every macro is for book1.xls on computer A. Now, the macros are there in book1.xls on computer B, but the custom menu is still linked to computer A.

Here is the strange part - when a custom menu is created for the first time, with new menu items, there are no problems with copying the file with the attached custom menus to different computers. The problems I described above arise when the custom menu bar is accessed again to add more items, on computer A.

As far as I know, this all has something to do with "attaching" of "detaching" the custom menus at various points of their creation. I have found a lot of material describing the procedure for creating the custom menus. But I have not seen anything to address the problem once it has occured.

Thank you for your help-

-Dan

 

[RobBroekhuis]

I understand better now - thanks. Very specifically, what is the exact value of the onaction property of any one of the menu items that no longer works?
Rob

 

[Dan15]

Here is an example of each menu item macro: (each one assigns a different number - and yes, the Next.Next.next etc should be done with something like ActiveCell.Offset(0,8).Select, but it was created by someone else.)

Sub Gas2()
' Gas2 Macro

ActiveCell.Select
ActiveCell.Next.Next.Next.Next.Next.Next.Next.Next.Next.Next.Next = 238
Qty = Application.InputBox("Please Enter Quantity&quot
ActiveCell.Next.Next.Next.Next.Next.Next.Next.Next = Qty
ActiveCell(2).Select
End Sub

 

[RobBroekhuis]

Try using the following sub:

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

you can run this in the workbook_open event handler, for each of your custom toolbars, e.g.

FixCustomToolbar "Mybar1"
FixCustomToolbar "Mybar2"

I haven't tested this fully, but I think it'll work.
Rob

 

[Dan15]

The code works when I run it from the VB editor. How do I set this up to run when I hit each menu item? Do I need to run this sub first for every menu item?

 

[RobBroekhuis]

You would put the calls to the Fix sub in your workbook_open event handler. If you haven't worked with those before, here's how:

- in the VB Project explorer (top left of screen), double-click on the "ThisWorkbook" item under "MicroSoft Excel Objects"
- the code window for your workbook will open. Select "Workbook" from the left dropdown menu, and "Open" from the right dropdown menu. This creates a new workbook_open event handler sub
- inside your event handler, put one or more calls to FixCustomToolbar (which you can put in any VBA code module - including the ThisWorkbook code module you just edited).

Exactly which calls depends on how you implemented your menu items. If they are new menu structures that are part of the main menu across the top of the screen, then you'd use

FixCustomControlBar "Worksheet Menu Bar"

If you created a new custom toolbar or menu bar, then you would use the name you assigned to it.
Rob

 

[Dan15]

Rob -
I have this sub in the module 1 of workbook "G21"

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

Then I put this sub in "ThisWorkbook" or event handler

Private Sub Workbook_Open()
FixCustomToolbar "Bidding004"
End Sub

"Bidding004" is the name of the custom toolbar.

Sub FixCustomToolbar works when run directly from Module 1.

When I run Sub Workbook_Open, it does not do anything.

What have I done wrong here?

Thanks-

-Dan

 

[RobBroekhuis]

You mean that the sub doesn't run when you open the workbook, or the sub does run but doesn't do what you want it to?
To troubleshoot:
Put a msgbox statement or a stop statement in your workbook_open, just before the fixcustomtoolbar command. this will tell you whether the workbook_open handler runs properly.
If it does, please tell me exactly what is/isn't happening with your toolbar.
Cheers
Rob

 

[Dan15]

I put the stop statement in so the workbook_open handler runs properly.


Sub FixCustomToolbar works when it is run from the editor. I run this sub, then it asks for a specific macro to run, then it executes the macro.

This sub runs without any errors, but nothing happens:

Private Sub Workbook_Open()
FixCustomToolbar "Bidding004"
End Sub

 

[RobBroekhuis]

Can you test, by putting the stop in the FixCustomToolbar sub, if the FixCustomToolbar sub is properly run when your workbook is opened? If it does, I'm really puzzled why it would work properly when executed from the editor, but not when executed during the workbook_open process. Please let me know. If you'd like me to take a look at it, you can mail me a mock-up version of the workbook at broekhrr@apci.com
Rob

 

[Dan15]

Originally you said to use this

FixCustomControlBar "Worksheet Menu Bar"

but it didn't work so I changed it to

FixCustomToolbar "Bidding004"

This is strange - I tried the stop in every line of the FixCustomToolbar sub and it worked, but didn't execute.

To clarify, I started here:

Private Sub Workbook_Open()
FixCustomToolbar "Bidding004"
End Sub

and then put stop in different lines of:

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

End Sub


The result was that the FixCustomToolbar code ran up to the stop command, regardless of where I put it. But, it simply went to the stop command, it does not execute as it does when I run FixCustomToolbar without running Private Sub Workbook_Open(). If I run FixCustomToolbar independly, it operates. But when through Private Sub Workbook_Open(),FixCustomToolbar does not operate correctly, but yet doesn't return an error message.

 

[RobBroekhuis]

Could you add, below the line p=instr...

debug.print p

and note the values that are output to the immediate (debug) window? If they are all 0, or if there are none at all, then that's a clue as to what's going on.
Rob

 

[Dan15]

Here is output:

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