PDA

View Full Version : ClearContents of a range of cells in on sheets bar 5



Juenapa
03-07-2011, 01:42 AM
This works:
Hi, I have made a bit of code to ClearContents of a range of cells. At the moment in clears every sheet expect one of my choosing. But I need to choose 5 sheets instead of one.

Can any one help.

Thanks.

Option Explicit

Sub ClearA1toJ92()

Dim ws As Worksheet
Dim TheName As String

TheName = "Sheet2"
For Each ws In ActiveWorkbook.Worksheets
If Not ws.Name = TheName Then
ws.Range("A1:J9").ClearContents
End If
Next ws

End Sub

GTO
03-07-2011, 02:35 AM
I think you meant except one of my choosing, and now would like to skip 5 specific sheets. You can add "And Not" tests to your If, but you might want to try Select Case.

Option Explicit

Sub exam()
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
Select Case wks.Name
Case "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7"
Case Else
wks.Range("A1:J9").ClearContents
End Select
Next
End Sub

Hope that helps,

Mark

Juenapa
03-07-2011, 04:08 AM
That worked great, thanks.

Is there a way of adapting it to create a macro to do another task for a range on all sheets except a few again?

Unlocking cell protection: VBA code:

Selection.Locked = False
Selection.FormulaHidden = False

Thanks a lot.

Jason

GTO
03-07-2011, 04:18 AM
Hi Jason,

I am afraid I am not understanding the question.

Mark

Juenapa
03-07-2011, 04:35 AM
I'm dealing protected sheets and locked cells. I have a macro to protect and unprotect the sheets. I need a macro to unlock cells on all sheets in a range like before. To unlock a cell, - right click a cell, format cells, protection tab, untick "locked".

The vba code when recording a new macro is:
Selection.Locked = False
Selection.FormulaHidden = False


Thanks

Jason

Rob342
03-07-2011, 05:00 AM
Hi Mark / Jason

Another way if you want


Option Explicit
Private Sub ClearSheets()
Dim ws As Worksheet
Dim TheName As String
TheName = "Sheet2"

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> TheName Then
ws.Range("A1:J9").ClearContents
End If
Next ws

End Sub



I use this method to unlock sheets

Private Sub PassWord()
Dim jason As String
Dim ws As Worksheets
Dim Inpass As String

Const PWD As String = "jason"
Inpass = Application.InputBox("Please Enter The Password To Proceed. Enter To Cancel.")
If Inpass = "" Or Inpass <> PWD Then
Exit Sub
End If

'Call another routine or input code to reset locked cells
End Sub

Juenapa
03-07-2011, 05:17 AM
I use this to change between un/protected sheets, but I also have a security macro that automatically locks cells after data is entered. I need a way of just unlocking a range of cells on nearly every sheet, unlocking cells not protecting sheet.

Public Sub aProtectAllSheets()

Dim objSheet As Worksheet

'Protecting all worksheets with password
For Each objSheet In Worksheets
If objSheet.ProtectContents = False Then objSheet.Protect "abc"
Next objSheet

End Sub

GTO
03-07-2011, 05:17 AM
Maybe...

Sub exam()
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
Select Case wks.Name
Case "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7"
Case Else
wks.Unprotect "Your Password"
wks.Range("A1:B20").Locked = False
wks.Protect "Your Password"
End Select
Next
End Sub

GTO
03-07-2011, 05:59 AM
Cross-posted: http://www.mrexcel.com/forum/showthread.php?t=533549

Jason, you may wish to read: Here (http://www.excelguru.ca/node/7)

Juenapa
03-08-2011, 02:08 AM
Just changed it slightly and it works fine.

Thanks

Sub AA_Unlock_Cells()
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
Select Case wks.Name
Case "Home", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday"
Case Else
wks.Range("b2:f6").Locked = False

End Select
Next
End Sub