how to get a macro to work

[Guest]

Hi There,

I have received some info through this posting, but it is not working how i
want to.
Hoppefully someone can help me further?

I have a Macro in a Module (Workbook_save)

Sub Workbook_Save()
Dim rng As Range

Set rng = ActiveSheet.Range("M2")

ActiveWorkbook.SaveAs _
Filename:=rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub

And a macro to start up the above in This workbook:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call Workbook_Save
Cancel = True
End Sub

But for some reason it is not saving the file as supose to do, i get the
error:
Compile Error - Expected variable or Proceder, not module

How can tell me what this is and how to fix?

Thanks

 

[Dave Peterson]

Try renaming that module (not the procedure) to something else:

Mod_Workbook_save
???

 

[Guest]

I agree with Dave Peterson, if nothing else, bad form to use reserved words
and possibly confusing the system in this case.

You may also have a problem with the location and logic of things. The
_BeforeSave routine must be in the workbook's code segment. The other
routine should be in a regular code module.

Now the logic issue: _BeforeSave is called before any save is done, so when
you have it call your other routine, _BeforeSave gets called again and sooner
or later the Cancel=True gets hit and the save is cancelled, and the workbook
is never saved.

But if you'll set a flag in your routine to tell _BeforeSave how to act when
your other routine is being used, you can get it done. You'll need a Public
boolean variable to act as the flag. Here's what I came up with:

code for the Workbook's code module:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As _
Boolean, Cancel As Boolean)
If justSayYes Then
Exit Sub ' continue with save normally
End If
Call myBook_Save
Cancel = True
End Sub

code for your other routine in a normal code module, note that the flag is
defined in the declarations section of that module before any sub or function
statement:

Public justSayYes As Boolean

Sub myBook_Save()
Dim rng As Range

Set rng = ActiveSheet.Range("M2")

justSayYes = True

ActiveWorkbook.SaveAs Filename:=rng.Value & _
".xls", FileFormat:=xlWorkbookNormal

End Sub

 

[Guest]

I think I may have had wrong post chosen when I replied before, but no matter
- I realized right after posting that we need to reset that flag after it
gets used, so the _BeforeSave code needs a slight modification:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As _
Boolean, Cancel As Boolean)
If justSayYes Then
justSayYes = False ' reset it
Exit Sub ' continue with save normally
End If
Call myBook_Save
Cancel = True
End Sub

 

[Guest]

Thanks, but i'm still havig the same problem, and get the Compile error -
Expected variable or proceder, not module.

And when i change the module name, it will not give me this error, but does
not save at all.

 

[Dave Peterson]

How do you know it's not saving?

Maybe you could put a
msgbox "Saved as " & activeworkbook.fullname
at the end of whatever the name of that procedure is that does the save.

 

[Guest]

Since you're forcing a SaveAs you don't get a dialog box anymore - it just
does it. You should see the name of your file change in the title bar of
Excel. Try putting a different entry into M2 and then run the routine and
see if the title bar doesn't change.

I'm stymied by the compiler error. I never got that even when I had your
original code set up completely wrong here with the intent of trying to
duplicate the problem.

Here's a workbook with my code in it that appears to work for me (Excel 2003
format). Just click the link and choose SAVE to your drive and give it a
test run. Change the entry in M2 and watch what happens when you click the
Save button or even try to use File | Save As.
http://www.jlathamsite.com/uploads/SaveAsInterrupted.xls

 

[Guest]

Thanks, The link worked i have copied the macro into my worksheet and it is
working now, just one question.

Is it correct that this macro only does it once, after changing the file
name and press save it change the filename, but when i change M2 again it
saves it over the first name.

Thanks a lot for your help

 

[Guest]

It just saves using the filename in M2 - haven't tested, but it should
overwrite file of the same name. Easy test:
make an entry into some cell other than M2, use it. Close the file, reopen
it to verify that the entry in that cell is as you made it. Change that
entry - reuse the function and close the file and open again to see that the
entry has also changed.