Save workbook with a name mentioned in a cell
 

1. Take your macro and insure it is in a standard module
2. Remove the Private from its header
3. Close the VBA Window
4. From the Excel Window
At the top of Excel is the menu bar with stuff like:
File Edit View Insert........
Just to the right of File is a tiny Excel icon
Right-click the icon and select View Code
Paste in the following:

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

5. Close the VBA Window (again)

At this point, whenever a Save is invoked (either from menu or button)
Workbook_BeforeSave will get invoked. This event macro will call your macro
which will actually perform the Save.

--
Gary''s Student - gsnu200737


"Danny" wrote:

Hi Gary,

I'm not that familiar with Macro's is there an easy way to have this
explained, it is not clear to me of what you exactly mean?

Thanks

"Gary''s Student" wrote:

Hi Danny:

Re-package your macro into the workbook event form:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Danny's code
Cancel = True
End Sub

This goes in the ThisWorkbook code area, not in a standard module.
--
Gary''s Student - gsnu200737


"Danny" wrote:

Hi,

I have tried to use explaination from the community, but till know this did
not work.

Can someone advise me on the following?

I want to save a workbook with a refference in a cell, at the moment i use:

Private Sub Workbook_Save()
Dim rng As Range

Set rng = ActiveSheet.Range("M2")

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

End Sub

This is working if i run it manually, but what do i need to do to activate
this when i click on the save buton?

Thanks