Clear Form but Keep Formulas

NFournier

New Member
Joined
Feb 7, 2018
Messages
6
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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() = ""
 
Upvote 0
In that case range B39:B45 doesn't contain any constants, they are either blank, contain formulae, or have logical values (TRUE, FALSE)
 
Last edited:
Upvote 0
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
 
Upvote 0
If the formula is being input to that cell via a macro, then why not just clear the entire range?
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top