Newbie: Problem changing VB in file backup macro

[zoso]

I wonder if you are able to help me please?

I am trying to automatically back up a file called Running Log.xls and was kindly given the code below to input in Immediate. The problem that I have though, is that when I save any other Excel files I open, the Running Log file opens up as well and the macro below is run.

Would you be kind enough to amend the data below or tell me what I should do so that the macro only runs when I close the Running Log file?

Many thanks!


Sub SaveWorkbookBackup()
Dim awb As Workbook, BackupFileName As String, i As Integer, OK As Boolean
If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
Set awb = ActiveWorkbook
If awb.Path = "" Then
Application.Dialogs(xlDialogSaveAs).Show
Else
BackupFileName = awb.FullName
i = 0
While InStr(i + 1, BackupFileName, ".") > 0
i = InStr(i + 1, BackupFileName, ".")
Wend
If i > 0 Then BackupFileName = Left(BackupFileName, i - 1)
BackupFileName = BackupFileName & ".bak"
OK = False
On Error GoTo NotAbleToSave
With awb
Application.StatusBar = "Saving this workbook..."
.Save
Application.StatusBar = "Saving this workbook backup..."
.SaveCopyAs BackupFileName
OK = True
End With
End If
NotAbleToSave:
Set awb = Nothing
Application.StatusBar = False
If Not OK Then
MsgBox "Backup Copy Not Saved!", vbExclamation, ThisWorkbook.Name
End If
End Sub

 

[zoso]

Smitty

You ROCK! I had no-eyed deer of this function

THANK YOU SOOOO MUCH FOR YOUR INPUT, YOU'VE MADE MY DAY!!!!

 

[Smitty]

Happy New Year!

 

[zoso]

Hmmm....

I guess I spoke too soon....

Although your suggestion works perfectly, removing the macro hasn't prevented my problem recurring!!!

Now when I open any other workbook, I get the dialog box 'the macro "Running Log.xls!SaveWorkbookBackup cannot be found'.

I've checked and after deleting the SaveWorkbookBackup module I now only have 1 module (10) containing the simple macros I need.

Heyulp!

 

[zoso]

Looks like I found the source of the error (sort of...)

All I did was remove the Save icon, close Excel, then re-open Excel and put the icon back - no idea how it worked but it did - any suggestions why would satisfy my curiousity!!!

Thanks Jon and the others for giving your time on this!

 

[Smitty]

Now when I open any other workbook, I get the dialog box 'the macro "Running Log.xls!SaveWorkbookBackup cannot be found'.

I've checked and after deleting the SaveWorkbookBackup module I now only have 1 module (10) containing the simple macros I need.

It would seem that you're calling SaveWorkbookBackup from your other workbooks. Even if you deleted it, any code referring to it will still look for it, hence the cannot be found message.

I am assuming that your "Save" icon was/is a Forms button to which that macro was assigned. When you deleted the button, you also deleted its association with the macro, which would make sense as to why it works for you now.

To keep things tidy, you can also rename your modules. Open VBA Properties (F4) and select the module in question. To the right of (Name), highlight "Module10" and rename it. It helps you maintain some semblance of order.

Hope that helps,

Smitty

 

[zoso]

Smitty

You're absolutely right - that's the only way I know to access my macros, thanks once again.

And thanks also for your suggestion which I'm doing right now!

Best wishes for 2004 to you!