Reference One Sheet on clear data Sub

[Simplemountain]

Hello,

I have a sheet I've been working on for some time that is a work sheet for pricing quotes.

I lock all the cells on the sheet except the ones needed to input data to keep all of my formulas safe. I added a clear Sheet button using code I got from this forum so that users could easily clear the sheet without affecting locked cells. Here is the code:


Sub ClearUnlockedCells()

Dim wks As Worksheet

For Each wks In ThisWorkbook.Field
On Error Resume Next
wks.UsedRange.Value = vbNullString
Err.Clear: On Error GoTo -1: On Error GoTo 0
Next wks

Set wks = Nothing

End Sub

This button works perfectly. I've realized now that I want to be able to allow users to change the value of certain cells for hourly rates. In order to keep my formulas intact, I created a second sheet in the Workbook where the rates can be entered and then set the applicable cells in the first sheet equal to them.

Now however, when I hit the clear sheet button, it clears the second sheet as well. Can someone advise on how I can change the code above to only clear the active sheet or some other solution? I've been experimenting but am missing something simple.

 

[Simplemountain]

Previously, I had used the lock feature to exclude all of the cells I wanted to keep intact and only clear the cells I had allowed a user access to edit.

 

[Simplemountain]

Previously I had used the lock feature to exclude any cells I wanted to keep intact and only clear the cells I had allowed users to edit.

 

[dmt32]

Thank you Dave,

That last code works perfectly and as expected.

Unfortunately, it also clears several cells that I have Text and numbers in that I would like to keep as well.

Is there some way to exclude or apply this code only to specific ranges?

Instead of using UsedRange, pass the range(s) you want to clear.

example

.Range("A1:D20,F1:F20, K1:K20").Cells.SpecialCells(xlCellTypeConstants).ClearContents

Dave

 

[Simplemountain]

Thank you Dave. This worked perfectly and I understand the steps in this macro much more to be able to tweek it in the future.

Here is the complete code for anyone looking.

Sub ClearUnlockedCells()
On Error Resume Next
With Sheets("Sheet Name")
.Unprotect Password:="Password"
.Range("D9:D12, A18:AC55, D59, T59 example range").Cells.SpecialCells(xlCellTypeConstants).ClearContents
.Protect Password:="Password"
End With
On Error GoTo 0
End Sub