ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Limit Macro to Intended worksheet. (https://www.excelbanter.com/excel-worksheet-functions/133024-limit-macro-intended-worksheet.html)

Quin

Limit Macro to Intended worksheet.
 
Is there a way to limit a macro to work only on ONE work sheet? When I
record a macro I only see an option to store in a WORKBOOK but not a work
sheet.

The problem I have is my macro will distroy my worksheet if I hit the
shortcut key while the wrong worksheet tab is selected. With multiple macros
and multiple worksheets it is only a matter of time before I hit the shortcut
key and get unintended results!

Quin

JE McGimpsey

Limit Macro to Intended worksheet.
 
one way:

Public Sub MyMacro
If ActiveSheet.Name < "Safe" Then Exit Sub
'<rest of your macro here
End Sub

In article ,
Quin wrote:

Is there a way to limit a macro to work only on ONE work sheet? When I
record a macro I only see an option to store in a WORKBOOK but not a work
sheet.

The problem I have is my macro will distroy my worksheet if I hit the
shortcut key while the wrong worksheet tab is selected. With multiple macros
and multiple worksheets it is only a matter of time before I hit the shortcut
key and get unintended results!

Quin


Gord Dibben

Limit Macro to Intended worksheet.
 
Make your macro sheet-specific.

Sub MyMacro()
Sheets("Sheet1").Activate
run the code
End Sub

Or.........................

With Sheets("Sheet1")
run the code
End With

Or.....................

Use worksheet_activate code in the sheet module

Private Sub Worksheet_Activate()
MyMacro
End Sub

Note: this last one will run the macro everytime the sheet is selected. May
not be what you need.


Gord Dibben MS Excel MVP

On Thu, 1 Mar 2007 18:01:10 -0800, Quin wrote:

Is there a way to limit a macro to work only on ONE work sheet? When I
record a macro I only see an option to store in a WORKBOOK but not a work
sheet.

The problem I have is my macro will distroy my worksheet if I hit the
shortcut key while the wrong worksheet tab is selected. With multiple macros
and multiple worksheets it is only a matter of time before I hit the shortcut
key and get unintended results!

Quin




All times are GMT +1. The time now is 11:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com