PDA

View Full Version : [SOLVED] Command Bars With Custom Face IDs



Paleo
02-03-2005, 07:25 AM
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:banghead: 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?:help

Richie(UK)
02-03-2005, 07:32 AM
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
02-03-2005, 07:51 AM
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
02-03-2005, 08:51 AM
?? 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
02-03-2005, 08:58 AM
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
02-03-2005, 09:06 AM
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
02-03-2005, 09:07 AM
Thought it may be something like that!!! Have a look here >> KB (http://www.vbaexpress.com/kb/getarticle.php?kb_id=262) :thumb

Paleo
02-03-2005, 09:09 AM
Hi John,

thanks I will try to adapt it.

mvidas
02-03-2005, 10:07 AM
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
02-03-2005, 12:22 PM
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
02-03-2005, 12:32 PM
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
02-03-2005, 12:39 PM
Hi Matt,

same here. Result:



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

mvidas
02-03-2005, 12:51 PM
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
02-03-2005, 12:55 PM
Hi Matt,

nothing has changed, stills the same.

mvidas
02-03-2005, 02:34 PM
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)
02-03-2005, 03:49 PM
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
02-04-2005, 08:05 AM
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?:think:

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

Richie(UK)
02-04-2005, 08:33 AM
... 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
02-23-2005, 04:38 PM
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
02-23-2005, 06:04 PM
Carlos, is the problem with the creation code? Or are you just talking about the FaceId's?

Paleo
02-23-2005, 06:11 PM
Hi Zack,

the first problem was that when I deploy my workbook its attribute macro still pointing to my computers directory structure. In example: the attributed macro at my computer was C:\Temp\Carlos.xls!Control, but at the deploied computer should be Q:\Casa\Minha.xls!Control. So it doesnt work and I have to set it up mannually whats no good.

Then I was told the command bar should be created by code. Well the creation is fine but then I loose my custom images for the buttons and I didnt know how to keep using them.

So I think there might be two viable solutions for this:
1) to correct the buttons macro address or
2) to programmatically insert the custom images at the buttons.

The problem is that I just dont know how to do either...

Zack Barresse
02-25-2005, 09:37 AM
Re 1: As Richie has said, it's best to use the feature of creating command (bars) on the fly with the workbook_open type of event. If you would like the buttons reassigned (command bar deleted & created) run the code at any time. So you would either need to run the code when you changed locations or close it and open it back up to get the code to trigger.

As for the images, do you have some images you are trying to use already?

Richie: I didn't see any images in your file either. Did I miss something?

mdmackillop
02-25-2005, 10:37 AM
For a different approach: The following code should create the settings when the workbook is opened, Can you can you use the GetSettings function in your code and use this as a path to your macros?
MD


Private Sub Workbook_Open()
Test = GetSetting(appname:="MyTB", section:="Startup", _
key:="FilePath")
If Test <> "" Then Exit Sub
SaveSetting appname:="MyTB", section:="Startup", _
key:="FilePath", setting:=ActiveWorkbook.Path
End Sub

Richie(UK)
02-25-2005, 12:12 PM
Doh! Obviously had a bit of a Homer moment and zipped the wrong example! Sorry guys.

I've attached the right one this time. ;)

Paleo
02-25-2005, 12:19 PM
Hi guys,

Zack: yes my problem was the pictures.
Malcolm: Yes I could but I would still have the pictures problem
Richie: Yes, now you got it. Exactly what I needed. Problem Solved. Thanks!