Hi All,

I'm just a noob to VBA so i need some help which may be obvious.

I need to run a macro when save / saveas is used within WorkbookX. got
that part worked out,

but it tries to run in other workbooks that are also open, which I
don't want it to, because it is referring to sheets that only exist in
WorkbookX. Here's the code from WorkbookX

Sub Workbook_Open()

Application.CommandBars("Standard").Controls("&Sav e").OnAction =
"MySave"

Application.CommandBars("Worksheet Menu
Bar").Controls("File").Controls("Save").OnAction = "MySave"

Application.CommandBars("Worksheet Menu
Bar").Controls("File").Controls("Save As...").OnAction = "MySaveAs"

End Sub

Sub Workbook_BeforeClose(Cancel As Boolean)

Application.CommandBars("Standard").Controls("&Sav e").OnAction = ""

Application.CommandBars("Worksheet Menu
Bar").Controls("File").Controls("Save").OnAction = ""

Application.CommandBars("Worksheet Menu
Bar").Controls("File").Controls("Save As...").OnAction = ""

End Sub


So how do I limit this working to just WorkbookX - while still allowing
the user to change WorkbookX's name using SaveAs?

thanks

Paul

Why not just add your macro to the save event in that workbook

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'your macro
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul S" wrote in message
oups.com...
Hi All,

I'm just a noob to VBA so i need some help which may be obvious.

I need to run a macro when save / saveas is used within WorkbookX. got
that part worked out,

but it tries to run in other workbooks that are also open, which I
don't want it to, because it is referring to sheets that only exist in
WorkbookX. Here's the code from WorkbookX

Sub Workbook_Open()

Application.CommandBars("Standard").Controls("&Sav e").OnAction =
"MySave"

Application.CommandBars("Worksheet Menu
Bar").Controls("File").Controls("Save").OnAction = "MySave"

Application.CommandBars("Worksheet Menu
Bar").Controls("File").Controls("Save As...").OnAction = "MySaveAs"

End Sub

Sub Workbook_BeforeClose(Cancel As Boolean)

Application.CommandBars("Standard").Controls("&Sav e").OnAction = ""

Application.CommandBars("Worksheet Menu
Bar").Controls("File").Controls("Save").OnAction = ""

Application.CommandBars("Worksheet Menu
Bar").Controls("File").Controls("Save As...").OnAction = ""

End Sub


So how do I limit this working to just WorkbookX - while still allowing
the user to change WorkbookX's name using SaveAs?

thanks

Paul

Thanks Bob,

I should have posted my macro as well, it also contains a save, so I'm
not sure if this way will work, all my sheets except one need to be
hidden in any saved file (one way to secure it a little better)

Here is my macro

Sub MySave()

Application.ScreenUpdating = False

ReturnSheet = ActiveSheet.Name
ReturnAddress = ActiveCell.Address

'hide all the sheets except one
For i = 2 To Sheets.Count
Sheets(i).Visible = xlVeryHidden
Next i

ActiveWorkbook.Save

'unhide all the sheets again
Show_all

Sheets(ReturnSheet).Select
Range(ReturnAddress).Select

End Sub

Paul,

You could put all that code in the BeforeClose event of your one workbook,
remove it from elsewhere.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul S" wrote in message
ups.com...
Thanks Bob,

I should have posted my macro as well, it also contains a save, so I'm
not sure if this way will work, all my sheets except one need to be
hidden in any saved file (one way to secure it a little better)

Here is my macro

Sub MySave()

Application.ScreenUpdating = False

ReturnSheet = ActiveSheet.Name
ReturnAddress = ActiveCell.Address

'hide all the sheets except one
For i = 2 To Sheets.Count
Sheets(i).Visible = xlVeryHidden
Next i

ActiveWorkbook.Save

'unhide all the sheets again
Show_all

Sheets(ReturnSheet).Select
Range(ReturnAddress).Select

End Sub

Ok thanks Bob,

have done that mostly into beforesave,

but it is still spitting out errors when another workbook B is open and
I try to Quit Excel (using red cross or FileExit) from Workbook B
window when my workboook A is also open.

If I use the small black cross - or FileClose on each workbook, no
troubles at all.

It seems that it is trying to look for sheets in Workbook B that only
exist in Workbook A, and subsequently can't find the ranges I am
looking for.

any ideas on making the macros in the beforesave event, only apply to
Workbook A, so that when quitting Excel from Workbook B, it doesn't
interfere with the macros in Workbook A

Hope this makes sense

cheers
Paul

or check somehow that if it is running in Workbook A, run the macro,
and if in another workbook, skip the macro and just do a normal
save/saveas

cheers

Paul