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.

 

[dmt32]

Hi,
if you only want to clear cell constants in a specific sheet try this change to your code

Sub ClearUnlockedCells()
On Error Resume Next
    Sheets("Sheet1").UsedRange.Cells.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End Sub

Change the sheet name shown in RED as required.

Updated code uses the Range.SpecialCells method to clear constants (numbers & text) only from the range & should leave any cells containing formulas intact.

Dave

 

[Simplemountain]

Thank you for the response Dave!

There are many cells on my sheet that contain numbers and text that I would like to also keep the same. I tried your code above to see if I could work it in but it is not clearing anything. Do I need to define a range?

I was hoping there might be an easy way to use the code I had but limit the range to only the one sheet.

 

[dmt32]

hi,
should work ok - did you change the sheet name to correct sheet? also, is your sheet protected?

Dave

 

[Simplemountain]

I have been working on adding this line:

Set wks = "sheet name"

 

[Simplemountain]

yes, sheet is protected. I did change the name. Will the protection block the code?

 

[dmt32]

I have been working on adding this line:

Set wks = "sheet name"

that's not in my code - use code as published & change the sheet name where shown in red.

Dave

 

[dmt32]

yes, sheet is protected. I did change the name. Will the protection block the code?

code will likely error but as there is an On error trap won't display the error.

You can modify code to allow for sheet protection

Sub ClearUnlockedCells()
On Error Resume Next
    With Sheets("Sheet1")
        .Unprotect Password:=""
        .UsedRange.Cells.SpecialCells(xlCellTypeConstants).ClearContents
        .Protect Password:=""
    End With
On Error GoTo 0
End Sub

add password as required

Dave

 

[Simplemountain]

Sorry, I was referring to adjusting the original code.

I pasted your code in as follows:
Sub ClearUnlockedCells()
On Error Resume Next
Sheets("Quote Work Up Sheet").UsedRange.Cells.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End Sub

There seems to be some issues with it being saved in compatibility mode at some point.

 

[Simplemountain]

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?