Clear Check Box
Are your checkboxes Forms or ActiveX checkboxes? Since you didn't specify, I
will assume you are using Forms checkboxes. Plus, you said you need to
"uncheck ALL checkboxes in my worksheet", so what I did was wrote a simple
loop that will loop thru all the checkboxes in the worksheet and turn them
off. If you need to uncheck only certain checkboxes let me know. I also
took the liberty of cleaning up your code. It is really unneccessary to
select ranges and write the code as you did. This will run much more
efficiently for you. Let me know if you have any issues with it. Hope this
helps! If so, let me know, click "YES" below.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strPrompt As String
Dim intButtons As Integer
Dim strTitle As String
Dim chk As CheckBox
If Target.Address = Range("K2").Address Then
Application.ScreenUpdating = False
strPrompt = "Do you want Put Staff into OT Order?"
intButtons = vbYesNo + vbInformation
strTitle = "Galashiels Operational Resources © MN"
If MsgBox(strPrompt, intButtons, strTitle) = vbYes Then
Range("A7:D16").Sort Key1:=Range("C7"), Order1:=xlAscending,
Header:=xlNo
Range("F7:I16").Sort Key1:=Range("H7"), Order1:=xlAscending,
Header:=xlNo
Range("A24:D33").Sort Key1:=Range("C24"), Order1:=xlAscending,
Header:=xlNo
Range("F24:I33").Sort Key1:=Range("H24"), Order1:=xlAscending,
Header:=xlNo
Range("A41:D50").Sort Key1:=Range("C41"), Order1:=xlAscending,
Header:=xlNo
Range("F41:I50").Sort Key1:=Range("H41"), Order1:=xlAscending,
Header:=xlNo
strPrompt = "Do you want to Reset the OT List to Zero?"
If MsgBox(strPrompt, intButtons, strTitle) = vbYes Then
Range("G39,H39:I39,H41:I50,H52:I52,B3:D3,B4,B5,C5: D5," & _
"C7:D16,C18:D18,G3:I3,G4,G5,H5:I5,H7:I16,H18:I 18," & _
"B20:D20,B21,B22,C22:D22,C24:D33,C35:D35,G20:I 20," & _
"G21,G22,H22:I22,H24:I33,H35:I35,B37:D37,B38,B 39," & _
"C39:D39,C41:D50,C52:D52,G37:I37,G38").ClearConten ts
End If
End If
' turn forms checkboxes off
For Each chk In ActiveSheet.CheckBoxes
chk.Value = xlOff
Next chk
Application.ScreenUpdating = True
End If
End Sub
--
Cheers,
Ryan
"terilad" wrote:
Hi,
I have the following code on my worksheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strPrompt As String
Dim intbuttons As Integer
Dim strTitle As String
If Target.Address = Range("K2").Address Then
strPrompt = "Do you want Put Staff into OT Order?"
intbuttons = vbYesNo + vbInformation
strTitle = "Galashiels Operational Resources © MN "
If MsgBox(strPrompt, intbuttons, strTitle) = vbYes Then
Range("A7:D16").Select
ActiveWorkbook.Worksheets("Staff OT").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Staff OT").Sort.SortFields.Add
Key:=Range("C7:C16" _
), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Staff OT").Sort
.SetRange Range("A7:D16")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("F7:I16").Select
ActiveWorkbook.Worksheets("Staff OT").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Staff OT").Sort.SortFields.Add
Key:=Range("H7:H16" _
), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Staff OT").Sort
.SetRange Range("F7:I16")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A24:D33").Select
ActiveWorkbook.Worksheets("Staff OT").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Staff OT").Sort.SortFields.Add Key:=Range( _
"C24:C33"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Staff OT").Sort
.SetRange Range("A24:D33")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("F24:I33").Select
ActiveWorkbook.Worksheets("Staff OT").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Staff OT").Sort.SortFields.Add Key:=Range( _
"H24:H33"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Staff OT").Sort
.SetRange Range("F24:I33")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A41:D50").Select
ActiveWorkbook.Worksheets("Staff OT").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Staff OT").Sort.SortFields.Add Key:=Range( _
"C41:C50"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Staff OT").Sort
.SetRange Range("A41:D50")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("F41:I50").Select
ActiveWorkbook.Worksheets("Staff OT").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Staff OT").Sort.SortFields.Add Key:=Range( _
"H41:H50"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Staff OT").Sort
.SetRange Range("F41:I50")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End If
End If
If Target.Address = Range("K4").Address Then
strPrompt = "Do you want to Reset the OT List to Zero?"
intbuttons = vbYesNo + vbInformation
strTitle = "Galashiels Operational Resources © MN "
If MsgBox(strPrompt, intbuttons, strTitle) = vbYes Then
Range("B3:D3,B4,B5,C5:D5,C7:D16,C18:D18,G3:I3,G4,G 5,H5:I5,H7:I16,H18:I18,B20:D20,B21,B22,C22:D22,C24 :D33,C35:D35,G20:I20,G21,G22,H22:I22,H24:I33,H35:I 35").Select
Union(Range("G39,H39:I39,H41:I50,H52:I52,B3:D3,B4, B5,C5:D5,C7:D16,C18:D18,G3:I3,G4,G5,H5:I5,H7:I16,H 18:I18,B20:D20,B21,B22,C22:D22,C24:D33,C35:D35,G20 :I20,G21,G22,H22:I22,H24:I33,H35:I35,B37:D37,B38,B 39,C39:D39"),
Range("C41:D50,C52:D52,G37:I37,G38")).Select
Selection.ClearContents
Range("A1").Select
End If
End If
End Sub
I am needing to uncheck all checkboxes in my worksheet along with the bottom
code when cell K4 is selected, the checkboxes are nemed 1 to 12.
Can anyone help me with this.
Many thanks
Mark
|