PDA

View Full Version : Solved: Lock unprotected cells, if a cell is changed - VBA



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

SamT
06-19-2013, 09:17 AM
I only looked at the "Target" part of your code...
Sub Test()
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
"Target" is an undeclared and un-Set variable and is = "Null." "Target" is not an Object and Null has no Address.

BTW, If Target Is Range("D6") Then:
'OR
If Target.Address = "D6" Then:

Jaspal
06-19-2013, 11:07 AM
Thank you SamT. What does your statement mean "Target is an undeclared and unset set variable and is =null?" I will try the code that you suggested!
Thank you once again.

SamT
06-19-2013, 01:47 PM
What does your statement mean "Target is an undeclared and unset set variable and is =null?"
In the code you posted, you have not declared "Target, ie, there is no
Dim Target as Range
In the code you posted you have not assigned a value to "Target", ie, there is no
Set Target = Range("some Range")

that's not "My code." It is your code placed in VBA tags using the Green VBA button at the top of the post Editor window.

Jaspal
06-19-2013, 02:14 PM
Thank you for explaining SamT.
Could you also help me with the following.

Allow end users to delete multiple rows within a range only - VBA
My Macro underneath allows users to delete a row in a selected range only,if a row outside the selection is chosen it comes with an error mesasage.
I also want to allow end users to to be able to delete multiple intermittent/together rows as well only within that selection, so the error message will still stay if they select outside the range.

If I update my code from Actice Cell to Selection.EntireRow.Delete, I can still select a row outside the range in another section and delet deletes it?

Thank you in advance.

' Delete Macro
Call UnprotectSheet(ActiveSheet.Name)
Dim msg
msg = MsgBox("Are you sure you want to delete Rows?", vbYesNo)
If msg = vbNo Then Exit Sub
Dim r, s As Integer, activeRow As Integer
Firstrow = Selection.Row
Lastrow = Selection.Rows.Count + Firstrow - 1
'activeRow = ActiveCell.Row
r = Range("A6").Value
s = Range("A8").Value
If Firstrow >= r And Lastrow <= s Then
'If activeRow >= r And activeRow <= s Then
Selection.EntireRow.Delete
Else
MsgBox ("You cannot delete this row, you can only delete the rows between " & r & " and " & s)
End If
'Range(r & ":" & s).Select
'Selection.Locked = False
Call ProtectSheet(ActiveSheet.Name)
End Sub

Jaspal
06-20-2013, 01:49 AM
Hello SamT,

I changed my code the "Target" piece as you suggested as under, but it still does not work for me. My aim is that if a cell value is changed based on two drop downs in my sheet the range is locked again. The change you suggested does not do the trick.

If Target Is Range("D6") Then:
'OR
If Target.Address = "D6" Then:

Thank you.

SamT
06-20-2013, 05:31 AM
Post the entire macro, including the "Sub MacroName(Macro arguments)"