Run Time Error
Two reasons maybe:
1 - your checkboxes are not on the sheet named "sheet1"
and or
2 - your checkboxes are from the forms toolbar
I have added some additional code to cover checkboxes from forms toolbar but
do check code is refering to correct worksheet.
Sub ClearFields()
Dim bx As msforms.CheckBox
For Each obj In Sheets("Sheet1").OLEObjects
If TypeOf obj.Object Is msforms.CheckBox Then
Set bx = obj.Object
bx.Value = False
End If
Next
For Each chkbx In Sheets("Sheet1").CheckBoxes
chkbx.Value = xlOff
Next
With Sheets("Sheet1")
..Range("H6:M9,H10:H13,I11:M11,H15:M20,O6:Q8,O11:Q 11,O15:Q15,J10,J13,O12,O16").ClearContents
End With
Sheets("Waiver Fact Sheet").Select
ActiveWindow.SmallScroll Down:=-6
Sheets("Partial Rel Waivers Order Ltr").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Index sheet").Select
End Sub
Hope helpful
--
jb
"Dave" wrote:
On Aug 11, 1:24 pm, john wrote:
Found a moment to shorten your clearcontents - think what i have done gives
same result:
Sub ClearFields()
Dim bx As msforms.CheckBox
For Each obj In Sheets("Sheet1").OLEObjects
If TypeOf obj.Object Is msforms.CheckBox Then
Set bx = obj.Object
bx.Value = False
End If
Next
With Sheets("Sheet1")
.Range("H6:M9,H10:H13,I11:M11,H15:M20,O6:Q8,O11:Q1 1,O15:Q15,J10,J13,O12,O16Â*").ClearContents
End With
Sheets("Waiver Fact Sheet").Select
ActiveWindow.SmallScroll Down:=-6
Sheets("Partial Rel Waivers Order Ltr").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Index sheet").Select
End Sub
--
jb
"Dave" wrote:
Hi I have the following macro to clear up a spreadsheet, when the code
hits the blocj to clear up the check boxes I am getiing a runtime
error 438. Can you please glance at the code and see what I have done
wrong thanks in advance.
Hi I have the following macro to clear up a spreadsheet, when the code
hits the blocj to clear up the check boxes I am getiing a runtime
error 438. Can you please glance at the code and see what I have done
wrong thanks in advance.
Sub ClearFields()
Range("H6:M6").Select
Selection.ClearContents
Range("H7:M7").Select
Selection.ClearContents
Range("H8:M8").Select
Selection.ClearContents
Range("H9:M9").Select
Selection.ClearContents
Range("H10").Select
Selection.ClearContents
Range("J10").Select
Selection.ClearContents
Range("O6").Select
Selection.ClearContents
Range("P6").Select
Selection.ClearContents
Range("Q6").Select
Selection.ClearContents
Range("Q7").Select
Selection.ClearContents
Range("P7").Select
Selection.ClearContents
Range("O7").Select
Selection.ClearContents
Range("O8").Select
Selection.ClearContents
Range("P8").Select
Selection.ClearContents
Range("Q8").Select
Selection.ClearContents
Range("O11").Select
Selection.ClearContents
Range("O12").Select
Selection.ClearContents
Range("P11").Select
Selection.ClearContents
Range("Q11").Select
Selection.ClearContents
Range("O15").Select
Selection.ClearContents
Range("O16").Select
Selection.ClearContents
Range("P15").Select
Selection.ClearContents
Range("Q15").Select
Selection.ClearContents
Range("H11:M11").Select
Selection.ClearContents
Range("H12").Select
Selection.ClearContents
Range("H13").Select
Selection.ClearContents
Range("J13").Select
Selection.ClearContents
Range("H15").Select
Selection.ClearContents
Range("H16").Select
Selection.ClearContents
Range("H17").Select
Selection.ClearContents
Range("H18").Select
Selection.ClearContents
Range("H19").Select
Selection.ClearContents
Range("H20:M20").Select
Selection.ClearContents
Range("I15").Select
Selection.ClearContents
Range("I16").Select
Selection.ClearContents
Range("I17").Select
Selection.ClearContents
Range("I18").Select
Selection.ClearContents
Range("I19").Select
Selection.ClearContents
Range("J15").Select
Selection.ClearContents
Range("J16").Select
Selection.ClearContents
Range("J17").Select
Selection.ClearContents
Range("J18").Select
Selection.ClearContents
Range("J19").Select
Selection.ClearContents
Range("K15").Select
Selection.ClearContents
Range("K16").Select
Selection.ClearContents
Range("K17").Select
Selection.ClearContents
Range("K18").Select
Selection.ClearContents
Range("K19").Select
Selection.ClearContents
Range("L15").Select
Selection.ClearContents
Range("L16").Select
Selection.ClearContents
Range("L17").Select
Selection.ClearContents
Range("L18").Select
Selection.ClearContents
Range("L19").Select
Selection.ClearContents
Range("M15").Select
Selection.ClearContents
Range("M16").Select
Selection.ClearContents
Range("M17").Select
Selection.ClearContents
Range("M18").Select
Selection.ClearContents
Range("M19").Select
Selection.ClearContents
Sheets("Sheet1").CheckBox72.Value = False
Sheets("Sheet1").CheckBox11.Value = False
Sheets("Sheet1").CheckBox112.Value = False
Sheets("Sheet1").CheckBox111.Value = False
Sheets("Sheet1").CheckBox79.Value = False
Sheets("Sheet1").CheckBox80.Value = False
Sheets("Sheet1").CheckBox51.Value = False
Sheets("Sheet1").CheckBox84.Value = False
Sheets("Sheet1").CheckBox55.Value = False
Sheets("Sheet1").CheckBox85.Value = False
Sheets("Sheet1").CheckBox56.Value = False
Sheets("Sheet1").CheckBox86.Value = False
Sheets("Sheet1").CheckBox57.Value = False
Sheets("Sheet1").CheckBox87.Value = False
Sheets("Sheet1").CheckBox58.Value = False
Sheets("Sheet1").CheckBox88.Value = False
Sheets("Sheet1").CheckBox59.Value = False
Sheets("Sheet1").CheckBox89.Value = False
Sheets("Sheet1").CheckBox60.Value = False
Sheets("Sheet1").CheckBox90.Value = False
Sheets("Sheet1").CheckBox61.Value = False
Sheets("Sheet1").CheckBox91.Value = False
Sheets("Sheet1").CheckBox62.Value = False
Sheets("Sheet1").CheckBox92.Value = False
Sheets("Sheet1").CheckBox173.Value = False
Sheets("Sheet1").CheckBox174.Value = False
Sheets("Sheet1").CheckBox63.Value = False
Sheets("Sheet1").CheckBox95.Value = False
Sheets("Sheet1").CheckBox64.Value = False
Sheets("Sheet1").CheckBox96.Value = False
Sheets("Sheet1").CheckBox66.Value = False
Sheets("Sheet1").CheckBox97.Value = False
Sheets("Sheet1").CheckBox67.Value = False
Sheets("Sheet1").CheckBox98.Value = False
Sheets("Sheet1").CheckBox212.Value = False
Sheets("Sheet1").CheckBox213.Value = False
Sheets("Sheet1").CheckBox69.Value = False
Sheets("Sheet1").CheckBox100.Value = False
Sheets("Sheet1").CheckBox70.Value = False
Sheets("Sheet1").CheckBox101.Value = False
Sheets("Sheet1").CheckBox102.Value = False
Sheets("Sheet1").CheckBox103.Value = False
Sheets("Waiver Fact Sheet").Select
ActiveWindow.SmallScroll Down:=-6
Sheets("Partial Rel Waivers Order Ltr").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Index sheet").Select
End Sub- Hide quoted text -
- Show quoted text -
Thanks for all your help I am using the code below now but its still
not unhecking the check boxes: Any advice?
Sub ClearFields()
Dim bx As msforms.CheckBox
For Each obj In Sheets("Sheet1").OLEObjects
If TypeOf obj.Object Is msforms.CheckBox Then
Set bx = obj.Object
bx.Value = False
End If
Next
With Sheets("Sheet1")
..Range
("H6:M9,H10:H13,I11:M11,H15:M20,O6:Q8,O11:Q11,O15: Q15,J10,J13,O12,O16Â*").ClearContents
End With
Sheets("Waiver Fact Sheet").Select
ActiveWindow.SmallScroll Down:=-6
Sheets("Partial Rel Waivers Order Ltr").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Index sheet").Select
End Sub
|