Home |
Search |
Today's Posts |
#1
|
|||
|
|||
select worksheet and run macro
Hi,
I am trying to run some code when a worksheet is selected. I'm doing this using a macro currently (code below), but this requires that i use a quick menu button and i would like to make it a form button and assign a macro to it (so i can distribute the file without everyone having to create a menu button). I would like to replace (or modify) the code below to select any sheet other than the ones named "a" and "b". Can someone help modify this code please ? Many thanks, Code:
Sub ImportAlarms() Dim thisSheet As Worksheet Dim targetSheet As Worksheet On Error GoTo failed Set thisSheet = Application.ActiveSheet Set targetSheet = Sheets(TARGET_SHEET) If (Not (thisSheet Is Nothing) And Not (targetSheet Is Nothing) And thisSheet.Name < targetSheet.Name) Then If (thisSheet.Cells(1, 1) = "Profile Alarms") Then Call importSheet(thisSheet) Else Call MsgBox("Please select an Alarm sheet") End If Else Call MsgBox("Please select an Alarm sheet") End If Exit Sub failed: Call MsgBox("Please select an Alarm sheet") End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select worksheet and run macro
How about in each sheet that's in the workbooks have a Worksheet_Activate() event to call your code passing in the sheet name?
So in each worksheet: Option Explicit Private Sub Worksheet_Activate() Call ImportAlarms(Me.Name) End Sub Then just amend your code a little to have the sheet name passed into it as a parameter? Like this: Sub ImportAlarms(strSheetName As String) Dim thisSheet As Worksheet Dim targetSheet As Worksheet On Error GoTo failed Set thisSheet = Application.ActiveSheet Set targetSheet = Sheets(strSheetName) If (Not (thisSheet Is Nothing) And Not (targetSheet Is Nothing) And thisSheet.Name < targetSheet.Name) Then If (thisSheet.Cells(1, 1) = "Profile Alarms") Then Call importSheet(thisSheet) Else Call MsgBox("Please select an Alarm sheet") End If Else Call MsgBox("Please select an Alarm sheet") End If Exit Sub failed: Call MsgBox("Please select an Alarm sheet") End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select worksheet and run macro
On each worksheet you will need to add an event handler for the sheet activate event, then pass the sheet name into the code you have written
So on each worksheet module add this: Private Sub Worksheet_Activate() Call ImportAlarms(Me.Name) End Sub Then I have amended your code to incorporate the sheet name parameter: Sub ImportAlarms(strSheetName As String) Dim thisSheet As Worksheet Dim targetSheet As Worksheet On Error GoTo failed Set thisSheet = Application.ActiveSheet Set targetSheet = Sheets(strSheetName) If (Not (thisSheet Is Nothing) And Not (targetSheet Is Nothing) And thisSheet.Name < targetSheet.Name) Then If (thisSheet.Cells(1, 1) = "Profile Alarms") Then Call importSheet(thisSheet) Else Call MsgBox("Please select an Alarm sheet") End If Else Call MsgBox("Please select an Alarm sheet") End If Exit Sub failed: Call MsgBox("Please select an Alarm sheet") End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
select worksheet and run macro
Scott Spence explained :
On each worksheet you will need to add an event handler for the sheet activate event, then pass the sheet name into the code you have written So on each worksheet module add this: Private Sub Worksheet_Activate() Call ImportAlarms(Me.Name) End Sub Nope! Bad idea..! I'd use the Workbook_SheetActivate event under ThisWorkbook so the code for all sheets is in one place, making it easier to manage and maintain. Additionally, I suggest storing the 'target' sheet names (alarm sheets only) in a delimited string constant at the top of the module and have the code run on each target sheet via a For..Each loop. In thisWorkbook: Option Explicit Private Sub Workbook_SheetActivate(ByVal Sh As Object) Call ImportAlarms(Sh) End Sub In a standard module: Option Explicit Const msTARGET_SHEETS As String = "Sheet1,Sheet2,Sheet4" Sub ImportAlarms(Wks As Worksheet) Dim s As Variant, WksSource As Worksheet Set WksSource = Sheets("UseActualName") '//edit to suit 'Skip any sheets not in the list If InStr(1, msTARGET_SHEETS, Wks.Name) 0 Then For Each s In Split(msTARGET_SHEETS, ",") With Sheets(s): Call importSheet(WksSource): End With Next 's End If End Sub Sub importSheet(Wks As Worksheet) ' Whatever you do here could just as easily be done in the caller. ' Seems like you're making things more complex than need be, IMO! With Wks '//do whatever it is you think you can't do in the caller... End With 'Wks End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
select worksheet and run macro
Whys that a bad idea?
It is good to have it in the Workbook_SheetActivate event yes so it is all in one place and easier to manage you're right. It was a suggestion, in your example you are adding hard coded sheet names! What if someone changes the sheet name? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
select worksheet and run macro
Scott Spence explained on 1/31/2012 :
Whys that a bad idea? It is good to have it in the Workbook_SheetActivate event yes so it is all in one place and easier to manage you're right. It was a suggestion, in your example you are adding hard coded sheet names! What if someone changes the sheet name? Store the sheet codenames instead! Then it doesn't matter what users do with the tab names. Change this line... If InStr(1, msTARGET_SHEETS, Wks.Name) 0 Then to.. If InStr(1, msTARGET_SHEETS, Wks.CodeName) 0 Then And change this line... With Sheets(s): Call importSheet(WksSource): End With to.. With Sheets(Get_SheetTabName(ActiveWorkbook, s)) Call importSheet(WksSource) End With ...which uses the following function... Function Get_SheetTabName(Wkb As Workbook, CodeName As String) As String Dim Wks As Worksheet For Each Wks In Wkb.Worksheets If Wks.CodeName = CodeName Then _ Get_SheetTabName = Wks.name: Exit Function Next End Function -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
select worksheet and run macro
On Jan 31, 11:06*am, GS wrote:
Scott Spence explained on 1/31/2012 : Whys that a bad idea? It is good to have it in the Workbook_SheetActivate event yes so it is all in one place and easier to manage you're right. It was a suggestion, in your example you are adding hard coded sheet names! What if someone changes the sheet name? Store the sheet codenames instead! Then it doesn't matter what users do with the tab names. Change this line... * If InStr(1, msTARGET_SHEETS, Wks.Name) 0 Then to.. * If InStr(1, msTARGET_SHEETS, Wks.CodeName) 0 Then And change this line... * With Sheets(s): Call importSheet(WksSource): End With to.. * With Sheets(Get_SheetTabName(ActiveWorkbook, s)) * * Call importSheet(WksSource) * End With ..which uses the following function... Function Get_SheetTabName(Wkb As Workbook, CodeName As String) As String * Dim Wks As Worksheet * For Each Wks In Wkb.Worksheets * * If Wks.CodeName = CodeName Then _ * * * Get_SheetTabName = Wks.name: Exit Function * Next End Function -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc You can put all, or restricted, sheet(s) macro in the THISWORKBOOK module |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
select worksheet and run macro
Don Guillett has brought this to us :
On Jan 31, 11:06*am, GS wrote: Scott Spence explained on 1/31/2012 : Whys that a bad idea? It is good to have it in the Workbook_SheetActivate event yes so it is all in one place and easier to manage you're right. It was a suggestion, in your example you are adding hard coded sheet names! What if someone changes the sheet name? Store the sheet codenames instead! Then it doesn't matter what users do with the tab names. Change this line... * If InStr(1, msTARGET_SHEETS, Wks.Name) 0 Then to.. * If InStr(1, msTARGET_SHEETS, Wks.CodeName) 0 Then And change this line... * With Sheets(s): Call importSheet(WksSource): End With to.. * With Sheets(Get_SheetTabName(ActiveWorkbook, s)) * * Call importSheet(WksSource) * End With ..which uses the following function... Function Get_SheetTabName(Wkb As Workbook, CodeName As String) As String * Dim Wks As Worksheet * For Each Wks In Wkb.Worksheets * * If Wks.CodeName = CodeName Then _ * * * Get_SheetTabName = Wks.name: Exit Function * Next End Function -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc You can put all, or restricted, sheet(s) macro in the THISWORKBOOK module That's exactly what I suggested, Don. Makes for easier project management and maintenance! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select Worksheet to print Macro | Excel Worksheet Functions | |||
why does this macro select the entire worksheet when run? | Excel Discussion (Misc queries) | |||
need a macro select all cells in a worksheet? | Excel Discussion (Misc queries) | |||
select worksheet to run macro | Excel Discussion (Misc queries) | |||
Help Please! Macro Error on Select Worksheet | Excel Programming |