Hi, I need help with a excel file. I know very little about VBA. I have excel pro 2007 beta loaded.
I would like to lock or protect all the sheets the workbook using vba. And still be able to add data to unprotected cells on one any worksheets. I need vba macro to do this so all the sheets are protected and run the macros but still be able to edit cells that are unprotected.
The code below does not protect all the worksheets. Only the 1st 2 out of 3. The two that are protected are where the macros run from. The third is not.
If you need any answers to my questions. I can e-mail it so it can be reviewed.
My code is below.
Sincerely, Tom Robert
Public K(7)
Sub Pick6()
ActiveSheet.Unprotect
Range("DataField").Sort Key1:=Range("DataField")
SetColour
ActiveSheet.Protect
End Sub
Sub SetColour()
ActiveSheet.Unprotect
Dim ColourRange As Range
Dim i As Integer
Set ColourRange = Range("Kolor")
Erase K
For i = 1 To 7
K(i) = ColourRange.Item(i).Interior.ColorIndex
ActiveSheet.Protect
Next
ActiveSheet.Unprotect
Dim oCell As Range
'// Set Colour
For Each oCell In Sheet3.Range("D3:D11")
Select Case oCell.Text
Case "A"
oCell.Interior.ColorIndex = K(1)
Case "B"
oCell.Interior.ColorIndex = K(2)
Case "C"
oCell.Interior.ColorIndex = K(3)
Case "D"
oCell.Interior.ColorIndex = K(4)
Case "E"
oCell.Interior.ColorIndex = K(5)
Case "F"
oCell.Interior.ColorIndex = K(6)
Case "G"
oCell.Interior.ColorIndex = K(7)
ActiveSheet.Protect
End Select
Next
ActiveSheet.Protect
End Sub
protect,unprotect question help needed
Kryor
Hey,
Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim intsheet As Integer
For intsheet = 1 To Worksheets.Count
With Worksheets(intsheet)
.Protect Password:="YourPassword"
End With
Next intsheet
Application.ScreenUpdating = True
End Sub
Cathrine