Combined Sub Worksheet_Change(ByVal Target As Range) code not firing

Question

Ok, I have two sets of code, the second set of the code below to hide and unhide cells based on another selection works perfectly. I also need to then protect the form as a whole and only unprotect cells once another has data in them (this remains unlocked).

However the code to unlock, appears not to unlock any of the cells - so in effect it isn't doing what I need it to. I am a relative novice here and get my code and idea's from these forums, and other internet sites, and over time have amended them to suit my own needs.

Drawn a blank on this one through and need some help if possible.

here is my code - the first part is the bit which doesn't unlock the cell range I have entered.

Private Sub Worksheet_Change(ByVal Target As Range)

   ' section one unlocks the range of cells A6:D115 when D5 changes from empty  to not empty, and then allows a user to enter data into the range of cells in not empty case condition below

If Target.Address(False, False) = "D5" Then
Select Case Target.Value
        Case Is = "": Range("A6:D115").Locked = True
    Case Is <> "": Range("C6:D6,A16:D16,C19:D22,D25:D25,D41:D57,B58:D58,C63:D63,C65:D73,C75:D78,C80:D84,D88:D88,A93:D98,D101:D103,B113:B114,D113:D113").Locked = False
    End Select
End If

' section two hides and unhides a series of cells based on the D25 selection

If Target.Address(False, False) = "D25" Then
Select Case Target.Value
    Case "Select as appropriate": Range("40:85").EntireRow.Hidden = False
    Case "USA - Breen Road": Range("40:85").EntireRow.Hidden = False
         Range("45:45,47:47,53:57,77:78").EntireRow.Hidden = True
    Case "USA - Conroe": Range("40:85").EntireRow.Hidden = False
         Range("40:52,77:78,80:80,85:85").EntireRow.Hidden = True
    Case "USA - Lafayette": Range("40:85").EntireRow.Hidden = False
         Range("43:43,45:47,49:49,53:57,61:83").EntireRow.Hidden = True
    Case "Europe - Aberdeen": Range("40:85").EntireRow.Hidden = False
         Range("40:49,53:57,77:78,80:80").EntireRow.Hidden = True
    Case "Europe - Gateshead": Range("40:85").EntireRow.Hidden = False
         Range("53:57").EntireRow.Hidden = True
    Case "Middle East - Dubai": Range("40:85").EntireRow.Hidden = False
         Range("43:43,46:47,50:57").EntireRow.Hidden = True
    Case "Middle East - Saudi Arabia": Range("40:85").EntireRow.Hidden = False
         Range("43:43,45:47,50:53").EntireRow.Hidden = True
    Case "Middle East - All": Range("40:85").EntireRow.Hidden = False
         Range("43:43,46:47,50:57").EntireRow.Hidden = True
    Case "Far East - Singapore - Loyang": Range("40:85").EntireRow.Hidden = False
         Range("41:41,44:57,77:78,80:80").EntireRow.Hidden = True
    Case "Far East - Singapore - Tuas": Range("40:85").EntireRow.Hidden = False
         Range("40:49,53:57,77:78,80:82").EntireRow.Hidden = True
    Case "Far East - Singapore - All": Range("40:85").EntireRow.Hidden = False
         Range("41:41,44:49,53:57,77:78,80:80").EntireRow.Hidden = True
    Case "Far East - Perth - Australia": Range("40:85").EntireRow.Hidden = False
         Range("41:57,63:63,67:67,72:72,74:83").EntireRow.Hidden = True
    End Select
End If

End Sub

Hopefully someone with a bit more knowledge than I have can help.

Is the event routine triggered at all? Put a command like `Debug.Print target.address` as first command line or set a break point on your `if` statement. — FunThomas, May 07 '18 at 14:14
Nope nothing at all is triggering - question is - how do I fix that? I tried MsgBox(Target.Address), and the right cell is being shown, but I guess the condition isn't right then. So if I changed the target address to another reference cell which changes value based on the empty or Not empty status of this first cell - would that work? — RobertW081171, May 07 '18 at 14:22
Usually people use range intersect for deciding if a certain cell is changed, and thus certain code needs to be triggered. — Luuklag, May 07 '18 at 14:24
OK thanks, but I managed to get it to trigger now, but what happens now is I get the following error. Run-Time error '1004: Unable to set the Locked property of the Range class Any idea's — RobertW081171, May 07 '18 at 14:28

Luuklag · Answer 1 · 2018-05-07T14:33:09.560

0

You can use the following code to check if the changed cell is D5:

If not Intersect(Target, Range("D5")) Is Nothing Then

It uses a double denial, so it might read strange, but is ought to work.

This should replace: If Target.Address(False, False) = "D5" Then in your code.

edited May 07 '18 at 14:33

answered May 07 '18 at 14:27

Luuklag

3,897
11
38
57

Forgive my ignorance, but where would I put this code or rather what in my code would I replace the above with? Is this in effect a complete change of code, I use intersect rather than my class code? – RobertW081171 May 07 '18 at 14:31
Luuklag - I get a type mismatch now, when I use this code. My next line of code after the intersect is still Case Is = "":Range..... However when I take the Case Is="" line of code out, the code goes through to the second line of code and I get the same runtime error code where the cell locked status = false cannot be completed – RobertW081171 May 07 '18 at 14:36
Are there multiple worksheets in your file, then you should explicitly reference them. – Luuklag May 07 '18 at 14:40
I removed the second sheet and with the Intersect code combined with the Case is code get the runtime error again, doesn't seen to like the Range address I have in for the Locked=False property – RobertW081171 May 07 '18 at 14:48
On what line exactly do you get a type mismatch? – Luuklag May 07 '18 at 14:49
The second one - the code errors when it reaches the following: : Range("C6:D6,A16:D16,C19:D22,D25:D25,D41:D57,B58:D58,C63:D63,C65:D73,C75:D78,C80:D84,D88:D88,A93:D98,D101:D103,B113:B114,D113:D113").Locked = False – RobertW081171 May 07 '18 at 14:53
I'm not sure the `Range()` allows for non-continous data. Try using only C6:D6 – Luuklag May 07 '18 at 14:57
Nope doesn't like that either, I have a password set for protect/unprotect - would this have to be removed for the basic code above to work? – RobertW081171 May 07 '18 at 15:09
Not by a computer now, but why not give it a try when you first disable password protection. – Luuklag May 07 '18 at 20:10
Unprotecting the worksheet would negate the whole point of the code. when I do that and then delete the content of the target cell, I get a type mismatch – RobertW081171 May 08 '18 at 13:25
when I protect the sheet but without a password, the run time error 1004 unable to set locked property of the Range class - which then refers to the second range of cells to unlock, this still comes up if I make this range a single cell or multiple cell ranges. – RobertW081171 May 08 '18 at 13:32
I think you should try the suggestions from the first answer here: https://stackoverflow.com/questions/9184818/error-1004-when-setting-range-locked?noredirect=1&lq=1 – Luuklag May 08 '18 at 13:37

Combined Sub Worksheet_Change(ByVal Target As Range) code not firing

1 Answers1