Macro in WB assigned to toolbar, after "save as" points to differn

[Activoz_Interpreting]

Let me begin by saying I'm not a programmer (any more), I'm a Spanish
interpreter at a Court. I have done all of the following using the "macro
record" and "customize toolbar" features from the Excel menus - I do not do
any VB programming.

I have created an Excel 2003 spreadsheet, running in Windows XP, that
contains a simple macro, stored in the workbook itself, to do some find and
replace commands. Let's call the worksheet "Master_file.xls". Let's call the
macro "My_macro" The macro is in the spreadsheet.

I created a custom toolbar (let's call it "smiley_face"), attached to this
spreadsheet, and assigned the macro to the toolbar. The name of the assigned
macro appears as My_macro. It does not indicate any excel file name.

I made the spreadsheet read-only - I want to be sure my end-users at other
Courts can’t mess it up.

Here’s the problem:

My users paste data into the spreadsheet, press the smiley face button,
(which runs My_macro) and then "save as". It's read-only so they rename it as
"Saved_File".

The next time my user opens "Master_File", pastes data, and then presses the
"smiley face" button to run the macro, Excel opens "Saved_File" to get the
macro.
The name of the macro assigned to the toolbar now says 'Saved_File'!My_macro

When the user tries to do a save as "Saved_File", excel says it can't save,
since that file is already open (since it opened it to get the macro). They
have to close “Saved_File†first, then "save as" again, and they are confused
about what happened. Sometimes they wind up not saving anything.

I’m perplexed about why the macro address pointer assigned to the toolbar
changes to the file name last saved. I tried hard-coding the name into the
original worksheet, but that doesn’t seem to make any difference. I would
like the macro to always run from “Master_Fileâ€, rather than from the file
last saved. Can I do this without coding, or by just editing the existing
macro code in the VB editor? Or what are my options?

Here’s the macro code:

Sub Replace_Chars()
'
' Replace_Chars Macro
' Macro recorded 8/31/2007 by Maintenance
'
Cells.Replace What:="@", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="~*F-", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End Sub

Thanks a lot!
Activoz_Interpreting

 

[Greg Wilson]

What you describe is a common problem and also caused me huge pain until I
got wise. Solution: Add the button programmatically on workbook open and make
it Temporary. Also, have code that hides in when the wb is deactivated and
unhides it when the wb is reactivated. This way, whenever the wb is opened,
it will be there. Same with any copies.

It will also not be available in the case the user has multiple workbooks
open and switches to another wb (hides). Most importantly, if the user does a
"Save As" then it won't matter since the button is destroyed when Excel is
closed and recreated when the original or any copy is opened. The code also
prevents multiple instances of the button being created in the event the user
opens two versions simultaneously (original and copy).

Suggested code follows. Paste it to the ThisWorkbook class module:

Private Sub Workbook_Open()
With Application.CommandBars(1)
On Error Resume Next
.Controls("Replace Text").Delete
On Error GoTo 0
With .Controls.Add(Temporary:=True)
.FaceId = 313
.Caption = "Replace Text"
.OnAction = "My_macro"
.Style = msoButtonIconAndCaption
End With
End With
End Sub

Private Sub Workbook_Activate()
On Error Resume Next
With Application.CommandBars(1)
.Controls("Replace Text").Visible = True
End With
On Error GoTo 0
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
With Application.CommandBars(1)
.Controls("Replace Text").Visible = False
End With
On Error GoTo 0
End Sub

Regards,
Greg