Jaspal
06-19-2013, 06:13 AM
Hello there,
I have a code that looks in cell D6 and where it finds the number in the coulmn, adds a row in the relevant section and unlocks columns "K - R & W". Cell D6 is a unquie value which is based on drop down in cells a5 & A6.
I want to add a functionality when either a5 or a6 is changed my unlocked section is locked again. Below is my attempt. I tought if I input if statement on D6 that would work, because VBA does not recognise "Drop down" changing in Excel, but even with Cell D6 I get Object reqwuired error?
Thank you in advance.
Call UnprotectSheet(ActiveSheet.Name)
Dim r As Integer
Range("A:E").EntireColumn.Hidden = False
r = Range("d6").Value
Cells(r, 1).Select
ActiveCell.EntireRow.Insert
Range("B" & r - 1 & ":I" & r - 1).Copy
Range("B" & r).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("K" & r & ":O" & r).Locked = False
Range("W" & r).Locked = False
Application.ScreenUpdating = True
Range("A:E").EntireColumn.Hidden = True
Range("K" & r).Activate
'If Target.Address = Range("d6").Address Then
'Range("K" & r & ":O" & r).Locked = True
'Range("W" & r).Locked = True
'End If
Call ProtectSheet(ActiveSheet.Name)
End Sub
I have a code that looks in cell D6 and where it finds the number in the coulmn, adds a row in the relevant section and unlocks columns "K - R & W". Cell D6 is a unquie value which is based on drop down in cells a5 & A6.
I want to add a functionality when either a5 or a6 is changed my unlocked section is locked again. Below is my attempt. I tought if I input if statement on D6 that would work, because VBA does not recognise "Drop down" changing in Excel, but even with Cell D6 I get Object reqwuired error?
Thank you in advance.
Call UnprotectSheet(ActiveSheet.Name)
Dim r As Integer
Range("A:E").EntireColumn.Hidden = False
r = Range("d6").Value
Cells(r, 1).Select
ActiveCell.EntireRow.Insert
Range("B" & r - 1 & ":I" & r - 1).Copy
Range("B" & r).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("K" & r & ":O" & r).Locked = False
Range("W" & r).Locked = False
Application.ScreenUpdating = True
Range("A:E").EntireColumn.Hidden = True
Range("K" & r).Activate
'If Target.Address = Range("d6").Address Then
'Range("K" & r & ":O" & r).Locked = True
'Range("W" & r).Locked = True
'End If
Call ProtectSheet(ActiveSheet.Name)
End Sub