Clear Form but Keep Formulas

[NFournier]

Hello,

I've been working on a new form an I'm at the point where I want to create a "Clear Form" button, which I do have working however there are certain cells that have formulas in them that I'd like to keep in there.

Right now the part that is failing is in bold:

Private Sub CommandButton1_Click()
Sheets("Supplier Request Form").Range("C15") = ""
Sheets("Supplier Request Form").Range("B16:B22") = ""
Sheets("Supplier Request Form").Range("G16:G19") = ""
Sheets("Supplier Request Form").Range("B24:B26") = "Select Option"
Sheets("Supplier Request Form").Range("G24:G26") = "Select Option"
Sheets("Supplier Request Form").Range("B28") = "Select Option"
Sheets("Supplier Request Form").Range("G28") = ""
Sheets("Supplier Request Form").Range("B32") = "Select Option"
Sheets("Supplier Request Form").Range("G32") = ""
Sheets("Supplier Request Form").Range("B34") = "Select Currency"
Sheets("Supplier Request Form").Range("G34") = "Select Freight Option"
Sheets("Supplier Request Form").Range("G37") = ""
Sheets("Supplier Request Form").Range("B39:B45").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("Supplier Request Form").Range("G39:G41") = ""
Sheets("Supplier Request Form").Range("G46") = ""
Sheets("Supplier Request Form").Range("B48:B54") = ""
Sheets("Supplier Request Form").Range("B58:B59") = ""
Sheets("Supplier Request Form").Range("C61") = "Select Option"
Sheets("Supplier Request Form").Range("B66:B67") = "Select Option"
Sheets("Supplier Request Form").Range("G66") = "Select Option"
Sheets("Supplier Request Form").Range("B69") = ""
Sheets("Supplier Request Form").Range("E71:H71") = ""
Sheets("Supplier Request Form").Range("E74:H74") = ""

MsgBox "This form has been reset."

End Sub

Thanks for any help you can provide.

 

[mgd_Excel]

Why not avoid clearing the formula cells. ( Good idea to lock these cells from your users)

Otherwise you can use range.formula = "your formula" to reset them each time

Also use range.clearcontents instead of range() = ""

 

[JoeMo]

In what way does the bold line fail?

 

[NFournier]

What I'm getting is "Run-time error'1004':, No cells were found."

 

[Fluff]

In that case range B39:B45 doesn't contain any constants, they are either blank, contain formulae, or have logical values (TRUE, FALSE)

 

[NFournier]

This range contains an IF formula but because this is a form, the same cell can be used for multiple reasons.

The form is used to enter in check or ACH information

If a person selects check, the form asks if the address above is the same, select Yes from a dropdown list and the IF formula will automatically enter the same information as listed previously.

If a person select ACH, instead of check, that same cell can be use to type their banking information in instead

 

[Fluff]

If the formula is being input to that cell via a macro, then why not just clear the entire range?