| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
| Urgent!!! Run-time error '-2147024770 (8007007e)' Automation error | Excel Programming | |||
| Error handling error # 1004 Run-time error | Excel Programming | |||
| run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
| Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
![Dave[_11_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
