Thread: Run Time Error
View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
Dave[_11_] Dave[_11_] is offline
external usenet poster
  
Posts: 41
Default 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

Reply With QuoteReply With Quote