Set variable based on form command button clicked-CodePudding

What I am trying to do is run the same code no matter which command button is clicked on a form but set a variable depending on the button clicked. So, they click “rota 1” command button on the form, it sets the variable in the code as “rota 1” then it searches the sheet for “rota 1” and returns the values next to that. If the click “Rota 2” command button it runs the same code but with “Rota 2” set as the variable. Here is the code I have.

Private Sub Manager_week_1_Click()
   'find manager week 1
   Dim SMon, EMon, STue, ETue, SWed, EWed, SThu, EThu, SFri, EFri, SSat, ESat, SSun, ESun As Range
   'copy manager week 1
   With Sheets("User Interface").Range("A1:Z100")
   Set SMon = .Find("Manager week 1", LookIn:=xlValues).Offset(0, 1)
   Set EMon = .Find("Manager week 1", LookIn:=xlValues).Offset(0, 2)
   Set STue = .Find("Manager week 1", LookIn:=xlValues).Offset(0, 3)

...

End Sub

where it says "manager week 1" I would like that to be a variable based on which command button in the form they click. Thanks in advance

CodePudding user response：

The basic idea would be to move the code in the Click event to a method and then call that method from the Click event passing a parameter:

Option Explicit

Private Sub Manager_week_1_Click()
   FindManager "Manager week 1"
End Sub

Private Sub Manager_week_2_Click()
   FindManager "Manager week 2"
End Sub

Private Sub FindManager(ByVal Manager As String)
   'find manager week 1
   Dim SMon, EMon, STue, ETue, SWed, EWed, SThu, EThu, SFri, EFri, SSat, ESat, SSun, ESun As Range
   'copy manager week 1
   With Sheets("User Interface").Range("A1:Z100")
   Set SMon = .Find(Manager, LookIn:=xlValues).Offset(0, 1)
   Set EMon = .Find(Manager, LookIn:=xlValues).Offset(0, 2)
   Set STue = .Find(Manager, LookIn:=xlValues).Offset(0, 3)
   
   '...
   
End Sub

CodePudding user response：

I think your just looking to call a subroutine:

Private Sub Manager_week_1_Click()
    SearchSheet "Manager week 1"
End Sub

Private Sub Manager_week_2_Click()
    SearchSheet "Manager week 2"
End Sub

Private Sub Manager_week_3_Click()
    SearchSheet "Manager week 3"
End Sub

Private Sub SearchSheet(lookFor As String)
    '// the variable lookFor contains the passed string
    
    ...
    Set SMon = .Find(lookFor, LookIn:=xlValues).Offset(0, 1)
    ...
End Sub

Also note that after a declaration like Dim a, b, c as X only c is of type X - instead: Dim a as X, b as X, c as x