Loop Structure for User Form with Check Boxes

ronnie76

Board Regular
Joined
Jul 26, 2007
Messages
101
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Perhaps this?
Code:
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

Board Regular
Joined
Jul 26, 2007
Messages
101
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Did you try the code I posted?
 

ronnie76

Board Regular
Joined
Jul 26, 2007
Messages
101
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
What Cells.Find line?

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

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

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

Forum statistics

Threads
1,181,102
Messages
5,928,062
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top