Command Bars With Custom Face IDs

[Paleo]

Hi,

I have created a CommandBar and attributed some macros to it, but it saves the file name and path for each macro, then I copy the file to another computer and bang all the buttons from the command bar get an error because they are still pointing to the macros in the other computers directory system. The same happens if I change the file name.

Is there a way to force to button from the command bar to always run only local macros?

[Richie(UK)]

Hi,

How did you create your CommandBar?

These problems often happen when the menus are created 'manually'. The safest option is to create the menu, buttons and assignments using code that you call in the workbook Open event.

[Paleo]

Hi Richie,

yes, I have created t manually because I am using images for the buttons. I just use the Enabled event in the workbook_open to show it.


Any suggestions on how to solve this problem?

[johnske]

?? Dont quite understand what you mean. I assume everything including the macros are all getting copied to the other computer also.

So do you mean that it fails when the code comes to something like Application.Workbooks.Open("C:\Windows\Desktop\Myfolder\MyBook.xls") ??

(because the other computer may have (say) a drive K: instead of C: )

[Paleo]

Hi John,

almost this. At my command bar property attribute macro I have:

C:\Temp\Carlos.xls!Control

But at the new computer it should be

Q:\Casa\Minha.xls!Control

As I have 7 buttons in this workbook and have to move it very freqently its very bad to have to handle with that manually.

[Paleo]

I think maybe the solution could be to use a for..each loop to check every button out of the command bar "Carlos", get its macro path and substitute it by the Application.Path.

What do you think? Would that be possible?

[johnske]

Thought it may be something like that!!! Have a look here >> KB

[Paleo]

Hi John,

thanks I will try to adapt it.

[mvidas]

Hi Carlos,

First off, you could use something like:

Dim CTL As CommandBarControl, ModMac As String
 For Each CTL In Application.CommandBars("Carlos").Controls
  ModMac = Mid$(CTL.OnAction, InStr(1, CTL.OnAction, "!", 1) + 1)
  CTL.OnAction = "'" & ThisWorkbook.Name & "'" & ModMac
 Next CTL

But I think you'd have better luck if you created the commandbar when the workbook is loaded, and removed it when closed. That way you're sure that it is always correct. You could use something like:

Private Sub Workbook_Open()
 On Error Resume Next
 Application.CommandBars("Carlos").Delete
 On Error GoTo 0
 Application.CommandBars.Add "Carlos"
 With Application.CommandBars("Carlos").Controls.Add
  .Caption = "Macro 1"
  .Tag = "Macro 1"
  .OnAction = "'" & ThisWorkbook.Name & "'!ModuleName.MacroName"
 End With
End Sub

Private Sub Workbook_BeforeClose()
 On Error Resume Next
 Application.CommandBars("Carlos").Delete
End Sub

in the ThisWorkbook module.

Just a thought
Matt

[Paleo]

Hi Matt,

it was a good try but didnt work.

What was:

Q:\Casa\Minha.xls!Control

Became:

'Q:\Casa\Minha.xls'!''Minha.xls'Control

Any other idea? I cant use your second suggestion because of the buttons images.

[mvidas]

I just realized a mistake in what I had posted. Change my 4th line to:

CTL.OnAction = "'" & ThisWorkbook.Name & "'!" & ModMac

(I forgot the ! after the second ' )

[Paleo]

Hi Matt,

same here. Result:

Q:\'Minha.xls'!''Minha.xls'Control

[mvidas]

Hmmm, odd that it doesn't contain a ! character already.. maybe try:

Dim CTL As CommandBarControl, ModMac As String
 For Each CTL In Application.CommandBars("Carlos").Controls
  If CTL.OnAction Like "*!*" Then
   ModMac = Mid$(CTL.OnAction, InStr(1, CTL.OnAction, "!", 1) + 1)
  Else
   ModMac = Mid$(CTL.OnAction, InStr(1, CTL.OnAction, ".xls", 1) + 5)
  End If
  CTL.OnAction = "'" & ThisWorkbook.Name & "'!" & ModMac
 Next CTL

[Paleo]

Hi Matt,

nothing has changed, stills the same.

[mvidas]

Hmm, not sure what else it could be without actually being able to try it directly. No way to upload an attachment for this?

[Richie(UK)]

Hi Paleo,

As suggested above by both Matt and myself, the easiest cure for this problem is to have the menu created by code.

I don't see what the problem is with the button images. Do you realise that you can use custom images by copying the picture and using PasteFace? Am I missing your point here?

Example attached.

[Paleo]

Hi Matt and Richie,

I will post the workbook here.
How could I use PasteFace if the image wont be at the users computer? Its intended to be sent by mail to many people and I dont plan to create an instalation procedure, wanted to send only the xls file.
I am thinking about adding the CommandBar with no macros attributed, attribute them by code and delete the attributions before closing the file. What do you think, worth trying?

Thanks for the attached file Richie I will try to implement it with my solution.

[Richie(UK)]

... How could I use PasteFace if the image wont be at the users computer? Its intended to be sent by mail to many people and I dont plan to create an instalation procedure, wanted to send only the xls file.

Hi Paleo,

The image is attached to a worksheet in the workbook that contains the code (see the example in the workbook that I attached )

[Paleo]

Hi Richie,

there is no picture attached to in the workbook you posted. Its simply using an excel built in picture (code 59, smile), What I need is a function to insert buttons using images other than those provided by excel. I am using images created by me. Any suggestions?

[Zack Barresse]

Carlos, is the problem with the creation code? Or are you just talking about the FaceId's?