PDA

View Full Version : Solved: Resetting to Default Values



ajrob
08-30-2006, 11:33 PM
Alright, this is probably going to look ugly ... but it works (sorta). I'm trying to set cells across multiple worksheets to default values. Further, I'd like to launch the procedure from yet another worksheet from a button called "Reset".

Well, the routine Sub Reset_Form works, but when I try to launch it - I get an error message, "Select method of range class failed"

Any suggestions? Is there a way to simplify the code?

Thanks.


Sub Reset_Form()
' Gray input cells and Yellow drop-downs
Worksheets("Main Form").Range("inp_PAE").Select
Selection.ClearContents
Worksheets("Main Form").Range("inp_SalesRep").Select
Selection.ClearContents
Worksheets("Main Form").Range("inp_CustCont1").Select
Selection.ClearContents
Worksheets("Main Form").Range("inp_CustCont2").Select
Selection.ClearContents
Worksheets("Main Form").Range("inp_CustCont3").Select
Selection.ClearContents
Worksheets("Main Form").Range("inp_CustCont4").Select
Selection.ClearContents
Worksheets("Main Form").Range("inp_Date").Value = Date
Worksheets("Main Form").Range("inp_SER").Select
Selection.ClearContents
Worksheets("Main Form").Range("inp_Inquiry").Select
Selection.ClearContents
Worksheets("Main Form").Range("inp_SalesOrdr").Select
Selection.ClearContents
Worksheets("Main Form").Range("inp_Market").Value = "O&G"
Worksheets("Main Form").Range("inp_Equip").Value = "New"
Worksheets("Main Form").Range("inp_ProjName").Select
Selection.ClearContents
Worksheets("Main Form").Range("inp_ProjSubName").Select
Selection.ClearContents
Worksheets("Main Form").Range("inp_CustName").Select
Selection.ClearContents
Worksheets("Main Form").Range("inp_SiteDesc").Select
Selection.ClearContents
Worksheets("Main Form").Range("inp_Country").Value = "USA"
Worksheets("Main Form").Range("inp_StatProv").Select
Selection.ClearContents
Worksheets("Main Form").Range("inp_City").Select
Selection.ClearContents
Worksheets("Main Form").Range("sel_RurRec").Select
Selection.ClearContents
Worksheets("Main Form").Range("sel_RurAg1").Select
Selection.ClearContents
Worksheets("Main Form").Range("sel_RurAg2").Select
Selection.ClearContents
Worksheets("Main Form").Range("sel_SubRes").Select
Selection.ClearContents
Worksheets("Main Form").Range("sel_UrComm").Select
Selection.ClearContents
Worksheets("Main Form").Range("sel_UrInd").Select
Selection.ClearContents
Worksheets("Main Form").Range("sel_SeaCoast").Select
Selection.ClearContents
Worksheets("Main Form").Range("sel_DryLake").Select
Selection.ClearContents
Worksheets("Main Form").Range("sel_Desert").Select
Selection.ClearContents

' Check boxes
Worksheets("Main Form").Range("sel_Units").Value = 1
Worksheets("Lists").Range("inp_Location").Value = False
Worksheets("Main Form").Range("chk_Conv").Value = False
Worksheets("Main Form").Range("chk_SoLo").Value = False
Worksheets("Main Form").Range("chk_SoLoTpz").Value = False

End Sub

Bob Phillips
08-31-2006, 01:19 AM
Try this tidied up version and post back if you still get the problem


Sub Reset_Form()
' Gray input cells and Yellow drop-downs
With Worksheets("Main Form")
.Range("inp_PAE").ClearContents
.Range("inp_SalesRep").ClearContents
.Range("inp_CustCont1").ClearContents
.Range("inp_CustCont2").ClearContents
.Range("inp_CustCont3").ClearContents
.Range("inp_CustCont4").ClearContents
.Range("inp_Date").Value = Date
.Range("inp_SER").ClearContents
.Range("inp_Inquiry").ClearContents
.Range("inp_SalesOrdr").ClearContents
.Range("inp_Market").Value = "O&G"
.Range("inp_Equip").Value = "New"
.Range("inp_ProjName").ClearContents
.Range("inp_ProjSubName").ClearContents
.Range("inp_CustName").ClearContents
.Range("inp_SiteDesc").ClearContents
.Range("inp_Country").Value = "USA"
.Range("inp_StatProv").ClearContents
.Range("inp_City").ClearContents
.Range("sel_RurRec").ClearContents
.Range("sel_RurAg1").ClearContents
.Range("sel_RurAg2").ClearContents
.Range("sel_SubRes").ClearContents
.Range("sel_UrComm").ClearContents
.Range("sel_UrInd").ClearContents
.Range("sel_SeaCoast").ClearContents
.Range("sel_DryLake").ClearContents
.Range("sel_Desert").ClearContents

' Check boxes
.Range("sel_Units").Value = 1
End With
With Worksheets ("Lists")
.Range("inp_Location").Value = False
.Range("chk_Conv").Value = False
.Range("chk_SoLo").Value = False
.Range("chk_SoLoTpz").Value = False
End With
End Sub

ajrob
08-31-2006, 05:57 PM
Thanks for the tip. I ran the macro and experienced a "Cannot change part of a merged cell" error. I did a little investigating and solved. For reference to anybody else who might run into this issue:

I had a merged cell (let's say A1:A2) named "ProjName". If you want to clear the contents of a merged cell area, you need to add a MergeArea command as follows:
.Range("inp_PAE").MergeArea.ClearContents

Cheers