Unlock Cells and Clear Contents

[ashani]

Hi
I'm trying to set the macro up to unlock multiple ranges and clear contents on those ranges from Sheet1,Sheet2 & Sheet3 and also to set the message box to say "xyz" - please can someone guide me.

Many thanks,

 

[Michael M]

What ranges do you want cleared ?
Have you tried using the macro recorder to achieve this ?

 

[ashani]

Hi Michael,

I have tried that but it's not working so I want to clear for example : Sheet1 (A4:B5, C10:D20), Sheet2 (A7:Q27) and so on.

Many thanks

 

[Michael M]

Try

Sub MM1()
With Sheets("Sheet1")
.Range("A4:B5", "C10:D20").ClearContents
End With
With Sheets("Sheet2")
.Range("A7:Q27").ClearContents
End With
MsgBox "All cells cleared ! "
End Sub

 

[ashani]

Thanks so much Michael
How about unlocking those cells in the range so the cells are set to auto lock after data entry and I want to clear contents as well as unlock those cells for future entries.

 

[Michael M]

Option Explicit

Sub MM1()
With Sheets("Sheet1")
.Unprotect
    With Range("A4:B5", "C10:D20")
        .Locked = False
        .ClearContents
        .Locked = True
    End With
.Protect
End With
Sheets("Sheet2").Select
With Sheets("Sheet2")
.Unprotect
    With Range("A7:Q27")
        .Locked = False
        .ClearContents
        .Locked = True
    End With
.Protect
End With
End Sub

 

[ashani]

Thanks Michael
I'm getting error message of Compile Error, Wrong number of arguments or invalid property assignment

The range is highlighted and this is what I have put

With Range("B8:C13", "E8:G13", "F25:G29", "F31:G35")

 

[Michael M]

Sub MM1()
Sheets("Sheet1").Select
With ActiveSheet
.Unprotect
     With Range("B8:C13, E8:G13, F25:G29, F31:G35")
        .Locked = False
        .ClearContents
        .Locked = True
    End With
.Protect
End With
Sheets("Sheet2").Select
With ActiveSheet
.Unprotect
    With Range("A7:Q27")
        .Locked = False
        .ClearContents
        .Locked = True
    End With
.Protect
End With
End Sub

 

[ashani]

You're amazing Michael - thank you every so much. I owe you big time

 

[ashani]

Hi Michael
Sorry to bother you again - quick question :

In my spreadsheet there are 2 macros.

1) to create pdf and save - call “create pdf”
2) this one to clear contents and unlocked

Is it possible to have an option that if the first macro hasn’t been run fully (I.e created pdf) then the message appears “you need to save file” before it clears the cell ?

Thank you once again