If no cells to clear, dont start macro

happydonut

New Member
Joined
Nov 28, 2019
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hi all,

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

VBA Code:
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,036
Office Version
  1. 2016
Platform
  1. Windows
try this

VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
I would recommend putting this line at the top of your code:
VBA 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

New Member
Joined
Nov 28, 2019
Messages
34
Office Version
  1. 365
Platform
  1. Windows
try this

VBA Code:
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

New Member
Joined
Nov 28, 2019
Messages
34
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I would recommend putting this line at the top of your code:
VBA 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

Well-known Member
Joined
Jul 23, 2010
Messages
1,036
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
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

Well-known Member
Joined
Jul 23, 2010
Messages
1,036
Office Version
  1. 2016
Platform
  1. Windows
Try this:

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
@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

New Member
Joined
Nov 28, 2019
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
 On Error Resume Next
as mentioned without wsarray. Because I have the command button in sheet R, so wont need it somewhere else.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

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
Top