Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,564
- Office Version
- 365
- 2016
- Platform
- Windows
I'm not sure why, perhaps a second set of less fatigued eyes, can find the problem ...
I am getting an "Unable to set the locked property of the range class" error with the line in red. The sheet isn't protected, and I use this same code succesfully in other parts of the routine.
I will appreciate any suggestions provided.
I am getting an "Unable to set the locked property of the range class" error with the line in red. The sheet isn't protected, and I use this same code succesfully in other parts of the routine.
Rich (BB code):
Sub reset_diamonds()
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim wshmain As Worksheet
Set wshmain = Worksheets("Main")
With wshmain
.Unprotect
'GROOMING
With .Range("D24, E25") 'date, time
.UnMerge
.Value = "0"
.Locked = False
End With
With .Range("D25, D26") 'qualifier, staff name
.UnMerge
.Value = ""
.Locked = False
End With
With wshmain 'copy and paste shift formula (locked)
Application.CutCopyMode = False
Worksheets("Lists").Range("D31").Copy
.Range("G26").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Range("G26").Locked = True
End With
With .Range("D24:G24, E25:G25, D26:F26")
.MergeCells = True
End With
'PREPARATION1
With .Range("K24, L25") 'date, time
.UnMerge
.Value = "0"
.Locked = False
End With
With Range("K25, K26, K27") 'qualifier, staff, type
.UnMerge
.Value = ""
.Locked = False
End With
With wshmain 'copy and paste shift formula (locked)
Application.CutCopyMode = False
Worksheets("Lists").Range("D31").Copy
.Range("N26").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Range("N26").Locked = True
End With
With .Range("K24:N24, L25:N25, K26:M26, K27:N27")
.MergeCells = True
End With
'SIGNATURE1
With .Range("V24, U25") 'time, staff
.UnMerge
.Value = "0"
.Locked = False
End With
With .Range("U24, U25")
.Value = ""
.Locked = False
End With
With wshmain 'copy and paste shift formula (locked)
Application.CutCopyMode = False
Worksheets("Lists").Range("D31").Copy
.Range("X25").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Range("X25").Locked = True
End With
With .Range("V24:X24, U25:W25")
.MergeCells = True
End With
'LIGHTS1
With .Range("AC24, AC26") 'time on, time off
.Font.Color = RGB(0, 0, 0)
.UnMerge
.Value = "0"
.Locked = False
End With
'With .Range("AE25, AE27")
' .Font.Color = RGB(0, 0, 0)
'End With
With .Range("AB24, AB25, AB26, AB27") 'qualifier on, staff on, qualifier off, staff off
.Font.Color = RGB(0, 0, 0)
.Value = ""
.Locked = False
End With
With wshmain 'copy and paste shift formula (locked)
Application.CutCopyMode = False
Worksheets("Lists").Range("D31").Copy
.Range("AE25,AE27").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Range("AE25,AE27").Locked = True
End With
With wshmain.Range("AC24:AE24, AB25:AD25, AC26:AE26, AB27:AD27")
.MergeCells = True
End With
'CLOSING
With .Range("AI24, AJ25") 'date, time,
.UnMerge
.Value = "0"
.Locked = False
End With
With .Range("AI25, AI26") 'qualifier, staff
.Value = ""
.Locked = False
End With
With wshmain 'copy and paste shift formula (locked)
Application.CutCopyMode = False
Worksheets("Lists").Range("D31").Copy
.Range("AL26").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Range("AL26").Locked = True
End With
With .Range("AI24:AK24, AJ25:AL25, AI26:AK26")
.MergeCells = True
End With
'Setup Detail
With .Range("K29, K30, P33, P29:P32, K31:K33, K34, K35")
.UnMerge
.Value = ""
.Locked = False
End With
With .Range("K29:L29, K30:L30, P33:Q33, K34:U34, K35:U35")
.MergeCells = True
End With
'RELINE 1-4
With .Range("I38, P38, W38, AD38, I39, P39, W39, AD39, I42, P42, W42, AD42")
.UnMerge
.Value = ""
.Locked = False
End With
With .Range("I40, I41, P40, P41, W40, W41, AD40, AD41")
.UnMerge
.Value = "0"
.Locked = False
End With
With wshmain 'copy and paste shift formula (locked)
Application.CutCopyMode = False
Worksheets("Lists").Range("D31").Copy
.Range("K42,R42, Y42, AF42").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Range("K42,R42, Y42, AF42").Locked = True
End With
With .Range("I40:K40, I38:K38, I39:K39, I41:K41, I42:J42, P38:R38, P39:R39, P40:R40, P41:R41, P42:Q42, W38:Y38, W39:Y39, W40:Y40, W41:Y41, W42:X42, AD38:AF38, AD39:AF39, AD40:AF40, AD41:AF41, AD42:AE42")
.MergeCells = True
End With
.Protect
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
I will appreciate any suggestions provided.