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

 

[AndyEd]

I'd probably agree with you, however I know even less about Access than I do Excel.

 

[dmt32]

I'd probably agree with you, however I know even less about Access than I do Excel.

There are a few things you will need to get your head around but making the Forms do what you want is a lot easier and it is a database.

In meantime found a few moments to update the couple of the codes you had issues with

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'.

DELETE Current code & place updated version in a STANDARD module

Sub SetControls(ByVal tog As Object, ByVal FromNum As Long, ByVal ToNum As Long)
 
    Dim Form        As Object, cb As Object
    Dim i           As Long, cbIndex As Long
 
    Set Form = UserFormNewCase
 
    cbIndex = Val(Mid(tog.Name, 4))
 
    Set cb = Form.Controls("cbo" & cbIndex)
 
    If cbIndex > 4 Then
        With cb
            .Value = IIf(tog.Value, vbNullString, "NA")
            .Enabled = tog.Value
        End With
    End If
 
    tog.BackColor = IIf(tog.Value, vbGreen, vbRed)
 
    For i = FromNum To ToNum
        With Form.Controls("txt" & i)
            .Value = IIf(tog.Value, vbNullString, "NA")
            .Enabled = tog.Value
        End With
    Next i
 
End Sub

all togglebutton should now only need one line of code

Private Sub tog5_Click()

    SetControls Me.tog5, 24, 29
   
End Sub

Updated IsValidDateEntry Function

Place code in a STANDARD module

Function IsValidDateEntry(ByVal Form As Object, ParamArray DateTextBoxes() As Variant) As Boolean
    Dim PageName    As Object
    Dim DateBox         As Variant
    Dim InvalidDate     As Boolean
 
    Const RequiredFormat As String = "dd/mm/yy"
 
    For Each DateBox In DateTextBoxes
        With DateBox
         
            Set PageName = DateBox.Parent.Parent
            Debug.Print PageName.Name
            If IsDate(.Value) Then
                .Value = Format(DateValue(.Value), RequiredFormat)
                .BackColor = vbWhite
            Else
                If Len(.Value) > 0 And .Value <> "NA" Then
                    .BackColor = RGB(255, 242, 204)
                    If Not InvalidDate Then InvalidDate = True: Form.MultiPage1.Value = PageName.index: .SetFocus
                Else
                    .BackColor = vbWhite
                End If
            End If
        End With
        Set MPage = Nothing
        Set PageName = Nothing
    Next DateBox
 
    If InvalidDate Then MsgBox "Please enter valid date(s).", vbOKOnly + vbInformation, "Invalid Date entry"
 
    IsValidDateEntry = Not InvalidDate
 
End Function

Both codes are designed to go in their own modules to reduce the amount of code clutter you have in the userform code page.

For this to work you MUST place all the controls in Page 2 of the Multipage in a Frame - this can be hidden from view by removing the caption, setting specialeffect to Flat & Border to None.

Dave

 

[AndyEd]

There are a few things you will need to get your head around but making the Forms do what you want is a lot easier and it is a database.

I get what you're saying. I don't think I'm that far off now though...

Thank you again for your assistance. It is very much appreciated.