Repetion of code

[mikeymay]

I was wondering if there was a possible solution available for the repetion of code that relates to multiple objects (combobox's, buttons, check box's) on a user form?

I have a form that contains a multipage tab (7 pages/tabs) and on each page there are objects that are duplicated on the other pages but with slightly different names to distinguish which pages they are on e.g. cboxCheckBox1Page1, cboxCheckBox2Page1, cboxCheckBox1Page2, cboxCheckBox2Page2, etc, etc.

Example of code for Checkbox's

Private Sub cboxCopyPriorTueWk1_Click()

If cboxCopyPriorTueWk1 = True Then
    boolPopulateForm = True
    cbCoreStartTueWk1 = cbCoreStartMonWk1
    cbCoreFinishTueWk1 = cbCoreFinishMonWk1
    boolPopulateForm = False
    
    boolTueWk1Correct = True
    
    cboxCopyPriorWedWk1.Enabled = True
    cbCoreStartTueAll.Enabled = False
    cbCoreFinishTueAll.Enabled = False
    
    btnTueAllShift1.Enabled = False
    btnTueAllShift2.Enabled = False
    btnTueAllShift3.Enabled = False
    btnTueAllShift4.Enabled = False
    
    Else
End If

End Sub

Private Sub cboxCopyPriorWedWk1_Click()

If cboxCopyPriorWedWk1 = True Then
    boolPopulateForm = True
    cbCoreStartWedWk1 = cbCoreStartTueWk1
    cbCoreFinishWedWk1 = cbCoreFinishTueWk1
    boolPopulateForm = False
    
    boolWedWk1Correct = True
    
    cboxCopyPriorThuWk1.Enabled = True
    cbCoreStartWedAll.Enabled = False
    cbCoreFinishWedAll.Enabled = False
    
    btnWedAllShift1.Enabled = False
    btnWedAllShift2.Enabled = False
    btnWedAllShift3.Enabled = False
    btnWedAllShift4.Enabled = False
    
    Else
End If

End Sub

Private Sub cboxCopyPriorTueWk2_Click()

If cboxCopyPriorTueWk2 = True Then
    boolPopulateForm = True
    cbCoreStartTueWk2 = cbCoreStartMonWk2
    cbCoreFinishTueWk2 = cbCoreFinishMonWk2
    boolPopulateForm = False
    
    boolTueWk2Correct = True
    
    cboxCopyPriorWedWk2.Enabled = True
    cbCoreStartTueAll.Enabled = False
    cbCoreFinishTueAll.Enabled = False
    
    btnTueAllShift1.Enabled = False
    btnTueAllShift2.Enabled = False
    btnTueAllShift3.Enabled = False
    btnTueAllShift4.Enabled = False
    
    Else
End If

End Sub

Private Sub cboxCopyPriorWedWk2_Click()

If cboxCopyPriorWedWk2 = True Then
    boolPopulateForm = True
    cbCoreStartWedWk2 = cbCoreStartTueWk2
    cbCoreFinishWedWk2 = cbCoreFinishTueWk2
    boolPopulateForm = False
    
    boolWedWk2Correct = True
    
    cboxCopyPriorThuWk2.Enabled = True
    cbCoreStartWedAll.Enabled = False
    cbCoreFinishWedAll.Enabled = False
    
    btnWedAllShift1.Enabled = False
    btnWedAllShift2.Enabled = False
    btnWedAllShift3.Enabled = False
    btnWedAllShift4.Enabled = False
    
    Else
End If

End Sub

Basically, I was hoping there was one piece of code that will cater for each of the 42 checkbox's on the 7 tabs and also the numerous combobox's and buttons that do similar things.


Thanks

 

[Gary McMaster]

You could pass the control to a common procedure to take care of some of it. Below is a greatly oversimplified example using "Select Case". you could use the controls name and/or type or whatever else is available.

The "Tag" property of most, if not all, controls is a string available for exclusive use by you, the user. You could assign a logical pattern of codes to each tag string at design time. It might make your code easier to read and understand if someone else needs to work with it.

Hope it helps.

Gary

Private Sub CommandButton1_Click()

Test UserForm1.CommandButton1

End Sub

Private Sub CommandButton2_Click()

Test UserForm1.CommandButton2

End Sub

Public Sub Test(oControl As Object)

'Change the clicked command button caption to the current time
oControl.Caption = Time

MsgBox oControl.Name

Select Case Left(oControl.Tag, 3)

    Case "Mon"
    
        Select Case Mid(oControl.Tag, 4, 3)
        
            Case "Jan"
                'Code for control tagged "MonJan..."
            Case "Feb"
            
            Case "Mar"
            
        End Select
        
    Case "Tue"
    
    Case "Wed"

End Select

End Sub

 

[bertie]

Do you really need all those controls?

The only thing which changes in you code is the day being processed. So you only need one set of controls. For example:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
   [COLOR=darkblue]With[/COLOR] cboDay
      .AddItem "Mon"
      .AddItem "Tue"
      .AddItem "Wed"
      .AddItem "Thu"
      .AddItem "Fri"
      .AddItem "Sat"
      .AddItem "Sun"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cboDay_Change()
   [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] cboDay.Value
      [COLOR=darkblue]Case[/COLOR] "Mon"
         Process ("Sun")
      [COLOR=darkblue]Case[/COLOR] "Tue"
         Process ("Mon")
      [COLOR=darkblue]Case[/COLOR] "Wed"
         Process ("Tue")
      [COLOR=darkblue]Case[/COLOR] "Thu"
         Process ("Wed")
      [COLOR=darkblue]Case[/COLOR] "Fri"
         Process ("Thu")
      [COLOR=darkblue]Case[/COLOR] "Sat"
         Process ("Fri")
      [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Else[/COLOR]
         Process ("Sat")
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Process([COLOR=darkblue]ByVal[/COLOR] myDay [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR])
 
   [COLOR=green]'your code goes here[/COLOR]
 
   MsgBox "Day being processed is: " & myDay
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Edit: Gary beat me to it.