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
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
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
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.