Loop Structure for User Form with Check Boxes

[ronnie76]

I have a User Form with 8 Check Boxes where user can check one or all of the Check Boxes. There is a different command for each Check Box and I need the code to test if each or all of the Check Boxes are checked and then run the code assigned to each check box. I have written the code with If statements below for reference. Any help would be greatly appreciated. Thanks.


Private Sub OKButton_Click()
Application.ScreenUpdating = False
Application.EnableEvents = False
If CheckBox40 = True Then
ActiveCell.Offset(0, 62).Range("A1:X1").Select
Selection.Replace What:="40°,", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
If CheckBox70 = True Then
ActiveCell.Offset(0, 62).Range("A1:X1").Select
Selection.Replace What:="70°,", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
If CheckBox80 = True Then
ActiveCell.Offset(0, 62).Range("A1:X1").Select
Selection.Replace What:="80°,", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
If CheckBox100 = True Then
ActiveCell.Offset(0, 62).Range("A1:X1").Select
Selection.Replace What:="100°,", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
If CheckBoxIndirect = True Then
ActiveCell.Offset(0, 62).Range("A1:X1").Select
Selection.Replace What:="Indirect,", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
If CheckBoxDirect = True Then
ActiveCell.Offset(0, 62).Range("A1:X1").Select
Selection.Replace What:="Direct,", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
If CheckBoxUV = True Then
ActiveCell.Offset(0, 62).Range("A1:X1").Select
Selection.Replace What:="UV,", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
If CheckBoxFreezer = True Then
ActiveCell.Offset(0, 62).Range("A1:X1").Select
Selection.Replace What:="Freezer", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Else
End If
MsgBox "Are You Sure You Want to Cancel These Conditions"
Columns("N:GS").Select
Selection.EntireColumn.Hidden = True
Range("B2000").End(xlUp).Offset(1, 0).Select
ConditionsCancel.Hide
Application.ScreenUpdating = False
Application.EnableEvents = False
End Sub

 

[Norie]

Perhaps this?

arrCaps = Array("40", "70", "80", "100", "Indirect", "Direct", "UV", "Freezer")

For I = LBound(arrCaps) To UBound(arrCaps)
    Set ctl = Me.Controls("Checkbox" & arrCaps(I))
    If ctl.Value = True Then
        If IsNumeric(arrCaps(I)) Then
            strRpl = arrCaps(I) & "°"
        Else
            strRpl = arrCaps(I)
        End If
        ActiveCell.Offset(0, 62).Range("A1:X1").Select
        Selection.Replace What:=strRpl, Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End If
Next I

Note this is untested.

Oh, and the code you've posted won't even compile and it's hard to see what it's meant to do.

 

[ronnie76]

Sorry about the confusion

Maybe this can clarify. I've modified the form a little. So the user inputs a number into a text box (ShelfTestNumber) the code finds that number then selects that cell. The code then, depending on which check boxes are checked offsets 62 cells to the right and replaces all of the "40°" or "70°" etc. text with a blank "". As it is now it works perfectly if only one of the check boxes are checked. I need it to check to see if any or all of the Check Boxes are marked then replace the corresponding text. The code you posted worked but for only the first text box selected. Is there a way to Loop through the code until it has tested all of the check boxes. Thanks again for all of the help.

Private Sub CancelSelected_Click()

Dim ts As Range
myText = ConditionsCancel.ShelfTestNumber.Text
Set ts = ActiveSheet.Cells.Find(What:=myText, LookIn:=xlValues)
If Not ts Is Nothing Then
ts.Select
End If
Set ts = Nothing


If CheckBox40 = True Then
ActiveCell.Offset(0, 62).Range("A1:X1").Select
Selection.Replace What:="40°,", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If CheckBox70 = True Then
ActiveCell.Offset(0, 62).Range("A1:X1").Select
Selection.Replace What:="70°,", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If CheckBox80 = True Then
ActiveCell.Offset(0, 62).Range("A1:X1").Select
Selection.Replace What:="80°,", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
End Sub

 

[Norie]

Did you try the code I posted?

 

[ronnie76]

YEs I did try the code

Yes the code worked I just had to put the Cells.Find line below your code then it worked perfectly. Thank you so much for all of your help. It is greatly appreciated.

 

[Norie]

What Cells.Find line?

I can't see that anywhere in the original code.

Ah, just spotted it in the 2nd code posted.

By the way there should be no need for all that selecting and using Selection.