ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Run Time Error (https://www.excelbanter.com/excel-programming/432280-run-time-error.html)

Dave[_11_]
Run Time Error
 
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

Jim Thomlinson
Run Time Error
 
One of your check boxes no longer exists as a guess. You can also clean up
that code a bunch...

Sub ClearFields()

Range("H6:M6").ClearContents
Range("H7:M7").ClearContents
Range("H8:M8").ClearContents
Range("H9:M9").ClearContents
Range("H10").ClearContents
Range("J10").ClearContents
Range("O6").ClearContents
Range("P6").ClearContents
Range("Q6").ClearContents
Range("Q7").ClearContents
Range("P7").ClearContents
Range("O7").ClearContents
Range("O8").ClearContents
Range("P8").ClearContents
Range("Q8").ClearContents
Range("O11").ClearContents
Range("O12").ClearContents
Range("P11").ClearContents
Range("Q11").ClearContents
Range("O15").ClearContents
Range("O16").ClearContents
Range("P15").ClearContents
Range("Q15").ClearContents
Range("H11:M11").ClearContents
Range("H12").ClearContents
Range("H13").ClearContents
Range("J13").ClearContents
Range("H15").ClearContents
Range("H16").ClearContents
Range("H17").ClearContents
Range("H18").ClearContents
Range("H19").ClearContents
Range("H20:M20").ClearContents
Range("I15").ClearContents
Range("I16").ClearContents
Range("I17").ClearContents
Range("I18").ClearContents
Range("I19").ClearContents
Range("J15").ClearContents
Range("J16").ClearContents
Range("J17").ClearContents
Range("J18").ClearContents
Range("J19").ClearContents
Range("K15").ClearContents
Range("K16").ClearContents
Range("K17").ClearContents
Range("K18").ClearContents
Range("K19").ClearContents
Range("L15").ClearContents
Range("L16").ClearContents
Range("L17").ClearContents
Range("L18").ClearContents
Range("L19").ClearContents
Range("M15").ClearContents
Range("M16").ClearContents
Range("M17").ClearContents
Range("M18").ClearContents
Range("M19").ClearContents
With Sheets("Sheet1")
.CheckBox72.Value = False
.CheckBox11.Value = False
.CheckBox112.Value = False
.CheckBox111.Value = False
.CheckBox79.Value = False
.CheckBox80.Value = False
.CheckBox51.Value = False
.CheckBox84.Value = False
.CheckBox55.Value = False
.CheckBox85.Value = False
.CheckBox56.Value = False
.CheckBox86.Value = False
.CheckBox57.Value = False
.CheckBox87.Value = False
.CheckBox58.Value = False
.CheckBox88.Value = False
.CheckBox59.Value = False
.CheckBox89.Value = False
.CheckBox60.Value = False
.CheckBox90.Value = False
.CheckBox61.Value = False
.CheckBox91.Value = False
.CheckBox62.Value = False
.CheckBox92.Value = False
.CheckBox173.Value = False
.CheckBox174.Value = False
.CheckBox63.Value = False
.CheckBox95.Value = False
.CheckBox64.Value = False
.CheckBox96.Value = False
.CheckBox66.Value = False
.CheckBox97.Value = False
.CheckBox67.Value = False
.CheckBox98.Value = False
.CheckBox212.Value = False
.CheckBox213.Value = False
.CheckBox69.Value = False
.CheckBox100.Value = False
.CheckBox70.Value = False
.CheckBox101.Value = False
.CheckBox102.Value = False
.CheckBox103.Value = False
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
--
HTH...

Jim Thomlinson


"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

John
Run Time Error
 
I assume your checkboxes are from the controls toolbox? if so just cycle
through all checkboxes on worksheet, this may work for you:

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:M6").ClearContents
.Range("H7:M7").ClearContents
.Range("H8:M8").ClearContents
.Range("H9:M9").ClearContents
.Range("H10").ClearContents
.Range("J10").ClearContents
.Range("O6").ClearContents
.Range("P6").ClearContents
.Range("Q6").ClearContents
.Range("Q7").ClearContents
.Range("P7").ClearContents
.Range("O7").ClearContents
.Range("O8").ClearContents
.Range("P8").ClearContents
.Range("Q8").ClearContents
.Range("O11").ClearContents
.Range("O12").ClearContents
.Range("P11").ClearContents
.Range("Q11").ClearContents
.Range("O15").ClearContents
.Range("O16").ClearContents
.Range("P15").ClearContents
.Range("Q15").ClearContents
.Range("H11:M11").ClearContents
.Range("H12").ClearContents
.Range("H13").ClearContents
.Range("J13").ClearContents
.Range("H15").ClearContents
.Range("H16").ClearContents
.Range("H17").ClearContents
.Range("H18").ClearContents
.Range("H19").ClearContents
.Range("H20:M20").ClearContents
.Range("I15").ClearContents
.Range("I16").ClearContents
.Range("I17").ClearContents
.Range("I18").ClearContents
.Range("I19").ClearContents
.Range("J15").ClearContents
.Range("J16").ClearContents
.Range("J17").ClearContents
.Range("J18").ClearContents
.Range("J19").ClearContents
.Range("K15").ClearContents
.Range("K16").ClearContents
.Range("K17").ClearContents
.Range("K18").ClearContents
.Range("K19").ClearContents
.Range("L15").ClearContents
.Range("L16").ClearContents
.Range("L17").ClearContents
.Range("L18").ClearContents
.Range("L19").ClearContents
.Range("M15").ClearContents
.Range("M16").ClearContents
.Range("M17").ClearContents
.Range("M18").ClearContents
.Range("M19").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

p45cal[_59_]
Run Time Error
 

...and all those clear contents lines can be reduced to one:
Range("H6:M9,O6:Q8,H10:H13,I11:M11,J10,J13,O11:Q11 ,O12,O15:Q15,O16,H15:M20").ClearContents


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=124339

John
Run Time Error
 
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: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
--
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

Dave[_11_]
Run Time Error
 
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

John
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

Dave[_11_]
Run Time Error
 
On Aug 12, 2:03*am, john wrote:
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: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

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*").Cl*earContents

* * 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- Hide quoted text -

- Show quoted text -

Thank you so much that last code did the job, thanks again for all
your help, thanks group!!


All times are GMT +1. The time now is 12:26 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com