Run Time Error

D

Dave

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
 
J

Jim Thomlinson

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
 
J

john

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
 
P

p45cal

..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").ClearContent
 
J

john

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: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
 
D

Dave

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: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
--
jb








- Show quoted text -
Click to expand...

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
 
J

john

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: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



Hope helpful
 
D

Dave

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: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

Hope helpful

--
jb











- Show quoted text -
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Deactivate macros but reactivate again once another macro is run 2
400 Error 3
Help with error 53 8
run-time error 1004 - VBA Gurus Please Help 12
run multiple macros from drop down list 12
Run-Time Error '1004' 13
Run Time Error 1004: Please help 3
Run Time Error 1004: Please Help 3

Top