Applying one code block to the sheet that calls it

[Brian Belliveau]

Some of you may recall my various questions re: unhiding columns & rows.

Now that I have a code that works on Worksheets("PayPeriod_01"),
I'd like to have the code respond to each of several sheets. For the
purposes of our 2 week pay period, there would be 26 or so pay periods.

Rather than have to copy the code 25 times, and doing a search / replace to
change the sheet number, is there a way of having the one code block be
called by a copy of the button, but use a variable on the sheet ( 01 , 02,
03, ... 25, 26 ) to ...

1) change the sheet name ( PayPeriod_01 , PayPeriod_02 , PayPeriod_03,
.... )

2) change the range name that determines the rows to open for that sheet
( Empls_Per_01, Empls_Per_02, Empls_Per_03, ...)

3) change the range name that determines the columns to open for that sheet
( Jobs_Per_01, Jobs_Per_02, Jobs_Per_03, ... )

I would guess that each sheet would have a cell with its' number ( 01, 02,
03 ... )

Or should a new button on each sheet define the 3 variables, and then call
the rest of the code?

Thanks for the help.

Brian
=====================================================

My complete code to date ...

Private Sub CommandButton1_Click()

'This part opens 3 rows for each employee, in 1st & 2nd weeks
Dim RowRange As Range
Dim r1 As Integer, r2 As Integer, r3 As Integer, r4 As Integer
Set RowRange = Worksheets("Select_Employees").Range("Empls_Per_01")
For r = 1 To 19
If UCase(RowRange.Cells(r, 1)) = "X" Then
r1 = 48 + (2 * r)
r2 = 48 + (2 * r) + 2
r3 = 298 + (2 * r)
r4 = 298 + (2 * r) + 2
Worksheets("PayPeriod_01").Rows(r1 & ":" & r2).Hidden = False
Worksheets("PayPeriod_01").Rows(r3 & ":" & r4).Hidden = False
End If
Next

'This part opens 2 columns for each job in progress
Dim ColRange As Range
Dim c As Long
Dim c1 As Integer
Set ColRange = Worksheets("Select_Jobs").Range("Jobs_Per_01")
For c = 1 To 100
If UCase(ColRange.Cells(c, 1)) = "X" Then
c1 = 3 + (2 * c)
Worksheets("PayPeriod_01").Columns(c1).Resize(, 2).Hidden = False
End If
Next c
End Sub

 

[Otto Moehrbach]

Brian
One way:
Have a button on each sheet that calls the same main macro and passes
the sheet name to that macro. That macro (or sub macro) can use a Select
Case construct to set all the variables than depend on the sheet name.
Done. HTH Otto

 

[Charles Maxson]

Brian,

Adding on to Otto's post, I get the worksheet very easily from Application
caller property in Excel. NOTE: To use application.caller, you need to use
Form Button types, not OLE Control Button types....

Sub AllButtons_Click()
Dim ws As Worksheet

Set ws = ActiveSheet.Shapes(Application.Caller).Parent

'//To test
MsgBox "I reside on " &
ActiveSheet.Shapes(Application.Caller).Parent.Name

End Sub

 

[Otto Moehrbach]

Thanks Charles. That went into my HowTo file. That's good to know. Otto