select worksheet and run macro

[Hammah]

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 require
that i use a quick menu button and i would like to make it a form butto
and assign a macro to it (so i can distribute the file without everyon
having to create a menu button).

I would like to replace (or modify) the code below to select any shee
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 Su

 

[Scott Spence]

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

 

[Scott Spence]

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

 

[GS]

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

 

[Scott Spence]

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?

 

[GS]

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

 

[Don Guillett]

Scott Spence explained on 1/31/2012 :





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

 

[GS]

Don Guillett has brought this to us :

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!