Run macro in one open workbook only

[Paul S]

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("&Save").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("&Save").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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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("&Save").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("&Save").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
>

[Paul S]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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 S]

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 File>Exit) from Workbook B
window when my workboook A is also open.

If I use the small black cross - or File>Close 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

[Paul S]

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