If no cells to clear, dont start macro

[happydonut]

Hi all,

I have a Macro that clear contents in some cells in different sheets. It looks something like this:

Sub sbClearCellsOnlyData()
ActiveSheet.Unprotect
If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub
Worksheets("R").Range("C2:D2, A6:C6").SpecialCells(xlCellTypeConstants).ClearContents
Worksheets("SA").Range("A2:A18, A22:B22").SpecialCells(xlCellTypeConstants).ClearContents
Worksheets("T").Range("A3:CE325").SpecialCells(xlCellTypeConstants).ClearContents
ActiveSheet.Protect
End Sub

2 problems/requests
- I want that if there is nothing to clear from these cells, to add a MsgBox saying "Nothing to clear".
- Also, now I get an error if there is nothing to clear and the sheet protection disables.

Cheers.

Edit: Also, if cells mentioned in the range above are partially populated, I still get the same error message which I want to avoid.

 

[hrayani]

try this

Sub sbClearCellsOnlyData()

Dim ws As Worksheet

Set WSArray = Worksheets(Array("R", "SA", "T"))

For Each ws In WSArray
ws.Unprotect
 Next

On Error Resume Next

If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub
Worksheets("R").Range("C2:D2, A6:C6").SpecialCells(xlCellTypeConstants).ClearContents
Worksheets("SA").Range("A2:A18, A22:B22").SpecialCells(xlCellTypeConstants).ClearContents
Worksheets("T").Range("A3:CE325").SpecialCells(xlCellTypeConstants).ClearContents

For Each ws In WSArray
ws.Unprotect
 Next

End Sub

I am not sure that one should use on error resume next.... but as of now its doing the trick

 

[Joe4]

I would recommend putting this line at the top of your code:

If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub

before you do any Unprotecting.

In your original code, you are unprotecting, then asking the question, and if they click "No", you are exiting Sub without turning the protection back on.
And there really is no need to go through the unprotecting before asking if they are sure.

 

[happydonut]

try this

Sub sbClearCellsOnlyData()

Dim ws As Worksheet

Set WSArray = Worksheets(Array("R", "SA", "T"))

For Each ws In WSArray
ws.Unprotect
Next

On Error Resume Next

If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub
Worksheets("R").Range("C2:D2, A6:C6").SpecialCells(xlCellTypeConstants).ClearContents
Worksheets("SA").Range("A2:A18, A22:B22").SpecialCells(xlCellTypeConstants).ClearContents
Worksheets("T").Range("A3:CE325").SpecialCells(xlCellTypeConstants).ClearContents

For Each ws In WSArray
ws.Unprotect
Next

End Sub

I am not sure that one should use on error resume next.... but as of now its doing the trick

That seems to work almost as expected (sometimes changes Sheet however). But yeah, maybe something else that on error resume next. What else can I try instead of that?

 

[happydonut]

I would recommend putting this line at the top of your code:

If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub

before you do any Unprotecting.

In your original code, you are unprotecting, then asking the question, and if they click "No", you are exiting Sub without turning the protection back on.
And there really is no need to go through the unprotecting before asking if they are sure.

Thanks, I'll adjust that

 

[hrayani]

also don't forget to protect the sheet at the end of the code.

I forgot to change that Unprotect to protect

That seems to work almost as expected (sometimes changes Sheet however). But yeah, maybe something else that on error resume next. What else can I try instead of that?

Let me think of it... will get back to you if I find any solution

 

[Fluff]

How about

Sub sbClearCellsOnlyData()
   Dim Ary As Variant
   Dim i As Long

   Ary = Array("R", "C2:D2, A6:C6", "SA", "A2:A18,A22:B22", "T", "A3:CE325")
   If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub
   For i = 0 To UBound(Ary) Step 2
      On Error GoTo NoData
      If Evaluate("isref('" & Ary(i) & "'!A1)") Then
         With Sheets(Ary(i))
            .Unprotect
            .Range(Ary(i + 1)).SpecialCells(xlConstants).ClearContents
            .Protect
         End With
      Else
         MsgBox "Sheet " & Ary(i) & " not found"
      End If
   Next i
Exit Sub
NoData:
   MsgBox "No data on sheet " & Ary(i)
   Resume Next
End Sub

 

[hrayani]

Try this:

Sub sbClearCellsOnlyData()

If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub

Dim ws As Worksheet

Set WSArray = Worksheets(Array("R", "SA", "T"))

For Each ws In WSArray
ws.Unprotect
Next

If WorksheetFunction.CountA(Worksheets("R").Range("C2:D2, A6:C6")) <> 0 Then Worksheets("R").Range("C2:D2, A6:C6").SpecialCells(xlCellTypeConstants).ClearContents
If WorksheetFunction.CountA(Worksheets("SA").Range("A2:A18, A22:B22")) <> 0 Then Worksheets("SA").Range("A2:A18, A22:B22").SpecialCells(xlCellTypeConstants).ClearContents
If WorksheetFunction.CountA(Worksheets("T").Range("A3:CE325")) <> 0 Then Worksheets("T").Range("A3:CE325").SpecialCells(xlCellTypeConstants).ClearContents

For Each ws In WSArray
ws.Protect
Next

End Sub

 

[Fluff]

@happydonut
Is there any possibility that you could ever have a formula in the cells to be cleared, or will they always be hard values?

 

[happydonut]

Hey guys, thanks for replies.
I think it's working now, (not sure if best solution), but will try it out few times.

I just added

 On Error Resume Next

as mentioned without wsarray. Because I have the command button in sheet R, so wont need it somewhere else.