Path problem in assigning macro

[helmekki]

Hi there

I have a workbook file located in D:\honey.
I assigned my macro to an icon on the tool bar that i creat.
the macro works fine, but when i change the location of the workbook, then a msg appears telling that cannot find the macro in this path.
i chachked, and the macro is still in the same workbook that i moved to a different folder...........i realised that the problem was resulted from changing the location of the WB to a different location.

So whenever i change the workbook location, i do need to assign the macro to the tool bar icon........

Is there a way that makes the code recognise the new path of the workbook and using this new path, insted of assigning the macro to the icon each time of changing the location?

[Leith Ross]

Hello Helmekki,

Is there a reason you can't include your macro in the workbook?

Leith Ross

[helmekki]

The moved workbook allready contains the macro.....but the when i call the macro by pressing the icon on the toolbar, a msg appears telling me that cannot find the macro in this path.

This means that the macro keept its previous path, and cannot recognise the new path (new location of the WB). i.e. when i assigned the macro to the icon in toolbar, the WB was in D:/hony folder, but when i change the WB to a different folder, so the path changed, which means the icon needs another assigning when the WB is in the new location

IS there a way to make the macro recognise the new path and update its path OR
maybe there is a routine that reads the file location and updates the paths of the icon on the toolbar?

your help means a lot to me

[Leith Ross]

Hello Helmekki,

Sorry about the delay in answer you back. You will need to add some code to your VBA module "ThisWorkbook". You will need to change my example names to what you are using in your project code.
_________________________________________________________________

Private Sub Workbook_Open()

Dim Location As String

With ThisWorkbook
Location = .Path &"\" & .Name
End With

CommandBar("MyToolBar").Controls(1).OnAction = Location

End Sub
_________________________________________________________________


This should fix your problem,
Leith Ross

[helmekki]

Thank u 4 reply

when excution reachs this line:

Please Login or Register  to view this content.

Then a msg appears telling : object variable or with block variable not set

Any idea why
your help is heghly appreciated

[helmekki]

any idea ?

thank u

[helmekki]

Thank u all

I got the answer

your help ment a lot to me