Issue Check Box validation, once check box must be ticked in range

**franksonata** · 02-24-2012, 06:18 AM

Hi, I have a sheet with 26 active x checkboxes, I am wanting to incorporate some validation so one of the checkboxes must be ticked before an action can be completed. So far I have had some great help from this forum to send me on my way, and need this part to complete my sheet. In this case it is something very simple I am missing!

The code below checks which checkboxes are checked and depening on which will email to a particular email address. I have tried introducing a case whereby if none of the checkboxes are ticked a message will be displayed. I have been looking at this for so long now I just cant get my else clause working.

'Validation to choose which email route, validate checkboxes in range LA

T1 = True
T2 = True
T1 = False
T2 = False


            For Each OleObj In Worksheets("SAP Access Request Form").OLEObjects
                If TypeName(OleObj.Object) = "CheckBox" Then

            Select Case OleObj.Name
            
            Case "CheckBox1" To "CheckBox10", "CheckBox22" To "CheckBox26"
            T1 = T1 Or OleObj.Object.Value
                           
            Case "CheckBox11" To "CheckBox21"
            T2 = T2 Or OleObj.Object.Value
            
            
            End Select
                   
                End If
            
                 Next OleObj
        
            If (Not T1) And (Not T2) Then MsgBox ("tick one")
            
            Cancel = True
        
        Exit Sub
        Else
        
            If T1 = True Then AuthEmailAddress = "@email.co.uk"
            If T2 = True Then AuthEmailAddress = "wmaill@.com"
            If T1 = True Then Approver = " me,"
            If T2 = True Then Approver = " you,"

End If

**smuzoen** · 02-24-2012, 07:42 AM

Take the msgbox off the If statement line

If (Not t1) And (Not t2) Then
  MsgBox ("tick one")
            
            Cancel = True
        
        Exit Sub
        Else
'etc etc

Avoid using If then DO SOMETHING
instead

If then
DO SOMETHING
Else
Do Some other job
End If

For example you last section of code

If T1 = True Then
AuthEmailAddress = "@email.co.uk"
Approver = " me,"
End If
If T2 = True Then
AuthEmailAddress = "wmaill@.com"
Approver = " you,"
End If

Avoid completing an If Then on one line - it is not good programming practice (IMO)