Removing code duplication

[AndyEd]

I have another question regarding reducing duplication in code. I have the following involving a number of toggle buttons. Is there a way to condense it?

Thank you again...

Private Sub tog1_Click()

    Dim i As Integer

    With Me
        If .tog1.Value = True Then
            .tog1.BackColor = vbGreen
            For i = 12 To 14
                Controls("txt" & i).Value = vbNullString
                Controls("txt" & i).Enabled = True
            Next i
        Else
            .tog1.BackColor = vbRed
            For i = 12 To 14
                Controls("txt" & i).Value = "NA"
                Controls("txt" & i).Enabled = False
            Next i
        End If
    End With
           
End Sub

 

[dmt32]

If the code is common to all your togglebuttons just make it a common code & pass your togglebuttons to it as an argument.
if other elements of the the code need different values these can also be passed as arguments

Dave

 

[AndyEd]

Sorry Dave, I'm fairly new to VBA and am I'm learning through examples. How do you achieve that? Or could you point me in the right direction in terms of reading material.

Thanks, Andy

 

[dmt32]

untested but as an example

place code in a STANDARD module

Sub SetControls(ByVal TogButton As MSForms.ToggleButton, ByVal FromNum As Long, ByVal ToNum As Long)
    Dim Form As Object
    Dim i As Long

    Set Form = TogButton.Parent
    
            TogButton.BackColor = IIf(TogButton.Value, vbGreen, vbRed)
            For i = FromNum To ToNum
                With Form.Controls("txt" & i)
                    .Value = IIf(TogButton.Value, vbNullString, "N/A")
                    .Enabled = TogButton.Value
                End With
            Next i
End Sub

code has three parameters

- TogButton (Object) = A ToggleButton Object
- FromNum (Long) = The Start Index Number Of The Control
- ToNum (Long) = The End Index Number Of the Control

Note: FromNum & ToNum assumes that your TextBoxes follow the naming convention "txt" & index number

and to call it from your Togglebuttons

Private Sub ToggleButton1_Click()
    SetControls Me.ToggleButton1, 12, 14
End Sub

Dave

 

[AndyEd]

Thanks Dave, sorry but what's the significance of the 12, 14 in the line?

    SetControls Me.ToggleButton1, 12, 14

Thanks,

Andy

 

[AndyEd]

Don't worry. Just figured it out.

 

[AndyEd]

Dave, what if there is also a ComboBox within the group. I have tried various different ways to show it but it keeps causing an error 'Could not fine the specified object'.

Thanks, Andy

 

[dmt32]

Example code provided was just an idea of an approach you could take in answer to your original question regrading code duplication for togglebuttons.

If you have further issues in developing your project you need to post all the relevant code & for complex projects, place copy of your workbook with dummy data in a file sharing site like dropbox & provide a link to it

Dave

 

[AndyEd]

Ok. Will do. Thanks Dave. Again, very much appreciated.

 

[dmt32]

Had a brief look at your project from download provided in your latest post & can see the problem with SetFocus issue in IsValidDateEntry Function.

Normally, to access another control like a frame or multipage from say a textbox, you refer to the parent(s)

But to get to the correct tab on your multipage this is muddled a little with fact that on page 1 textboxes are placed within a Frame and on page 2, they are not.

So to SetFocus on TextBox in page 1 from page 2 you would for example, do something like this:

Set PageName = DateBox.Parent.Parent
If Not InvalidDate Then InvalidDate = True: Form.MultiPage1.Value = PageName.Index: .SetFocus

But all textboxes on page 2 to its

Set PageName = DateBox.Parent

I can see that you have spent some time creating your Userform but as you are discovering they can be for complex applications, be very difficult to program as you have to write every single line of code yourself which even experienced programmers can find challenging.

Looking at your project it appears you may be trying to develop some form of database system? If so & just my personal view, suggest may want to pause what you are trying to do & look at a Database Management System like Access or similar.

Dave