Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Clear Check Box
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default Clear Check Box
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 489
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default Clear Check Box
Hi Ryan,

I wrote another code and i'm trying to use some of yours that you redone for
me as mine is not very efficient.

The code I done is below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strPrompt As String
Dim intbuttons As Integer
Dim strTitle As String
If Target.Address = Range("L2:M3").Address Then
strPrompt = "Do you want Put Staff into OT Order?"
intbuttons = vbYesNo + vbInformation
strTitle = "Galashiels Staff Overtime Rota © M Neil "
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("L5:M6").Address Then
strPrompt = "Do you want to Reset the OT Sheet to Zero?"
intbuttons = vbYesNo + vbInformation
strTitle = "Galashiels Staff Overtime Rota © M Neil "
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
ActiveSheet.Shapes("Check Box 1").Select
With Selection
.Value = xlOff
End With
ActiveSheet.Shapes("Check Box 2").Select
With Selection
.Value = xlOff
End With
ActiveSheet.Shapes("Check Box 3").Select
With Selection
.Value = xlOff
End With
ActiveSheet.Shapes("Check Box 4").Select
With Selection
.Value = xlOff
End With
ActiveSheet.Shapes("Check Box 5").Select
With Selection
.Value = xlOff
End With
ActiveSheet.Shapes("Check Box 6").Select
With Selection
.Value = xlOff
End With
ActiveSheet.Shapes("Check Box 7").Select
With Selection
.Value = xlOff
End With
ActiveSheet.Shapes("Check Box 8").Select
With Selection
.Value = xlOff
End With
ActiveSheet.Shapes("Check Box 9").Select
With Selection
.Value = xlOff
End With
ActiveSheet.Shapes("Check Box 10").Select
With Selection
.Value = xlOff
End With
ActiveSheet.Shapes("Check Box 11").Select
With Selection
.Value = xlOff
End With
ActiveSheet.Shapes("Check Box 12").Select
With Selection
.Value = xlOff
End With
Range("A7:B16,F7:G16,A24:B33,F24:G33").Select
Range("F24").Activate
Range("A7:B16,F7:G16,A24:B33,F24:G33,F41:G50,A41:B 50").Select
Range("A41").Activate
Selection.ClearContents
Range("O7:P16").Select
Selection.Copy
Range("A7:B7").Select
ActiveSheet.Paste
Range("Q7:R16").Select
Application.CutCopyMode = False
Selection.Copy
Range("F7:G7").Select
ActiveSheet.Paste
Range("O18:P27").Select
Application.CutCopyMode = False
Selection.Copy
Range("A24:B24").Select
ActiveSheet.Paste
Range("Q18:R27").Select
Application.CutCopyMode = False
Selection.Copy
Range("F24:G24").Select
ActiveSheet.Paste
Range("O29:P38").Select
Application.CutCopyMode = False
Selection.Copy
Range("A41:B41").Select
ActiveSheet.Paste
Range("Q29:R38").Select
Application.CutCopyMode = False
Selection.Copy
Range("F41:G41").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
MsgBox "You Must Now Save the File and click Yes 2 Times",
vbInformation, "Galashiels Staff Overtime Rota © M Neil "
End If
End If
End Sub

As you can see I had another range of cells to click to reset the data on
the sheet and copy some info from another part of the sheet, the code you
done for me was clearing the check boxes on sorting the names into order and
not by selecting the other cells.

Can you have a look at my code and see where it can be more efficient, I
know it could be more efficient but i'm only learning at the moment with VBA
and big learning curve.

Many thanks

Mark
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Clear check boxes? Dave Peterson Excel Programming 0 December 1st 09 08:12 PM
Clear check boxes? Geoff[_5_] Excel Programming 0 December 1st 09 08:09 PM
Clear check box macro Kyla Dockery Excel Programming 8 August 5th 09 03:14 PM
Clear check box problem dow Excel Programming 1 November 11th 07 02:05 PM
Clear All Check Boxes Steve C Excel Programming 5 March 15th 07 09:38 PM


All times are GMT +1. The time now is 10:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement