Toolbar button to start a macro

[Scannerman]

I've written a Macro that imports data from a .CSV file, formats it and saves the result to a new comma separated .txt file. I would like to start the macro from a button on standard toolbar so I've added my button and assigned my macro to it. Fine so far, it works correctly the first time I use the button.

Trouble is every time I save my new .csv file my toolbar button gets re-assigned to a macro in the new file. Of course because it is a .txt file there's no macro there. The next time I open the original spreadsb=heet and try to run the macro from the toolbar button I get an error. It works fine from the keyboard shortcut I've set up though.

How can I assign my toolbar button to a fixed named .xls file and macro, so that it doesn't get re-assigned every time?

I could attach my code but I don't think that has anything to do with the problem. I think its a problem with the custom toolbar button.


Hope somebody can help.

[Zack Barresse]

Hi Scannerman,


When in Customize mode (Tools --> Customize..) and you right click your toolbar button, go to Assign Macro. Do you see the macro with the workbook name preceeding it? Is that how you attached the macro to it, or did you do it by code? How exactly did you attach the macro to the button?

[Scannerman]

Hi Firefytr,



As you described; click by click: Tools>Customize>Options, Drag a custom button to the toolbar, Right click the button, and Assign the macro.

[Jacob Hilderbrand]

What we can do is have the macro re-save the file to its original name after creating the csv file.


Option Explicit

Sub MyMacro()
Dim FName As String
FName = ThisWorkbook.FullName
'Your code here...
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=FName
Application.DisplayAlerts = True
End Sub

[Scannerman]

Thanks for that. Simple really when you think about it. I was just a bit surprised when my button kept getting re-assigned.

[Jacob Hilderbrand]

You're Welcome

Take Care