Dependent Data Validation List & ByVal Sub

[crburke92]

Hey all,

I have a Data Validation list on one of my sheets in Cell B1 that drives a ByVal Sub:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B1").Value = "All" Then
Range("ALL").EntireColumn.Hidden = False
    ElseIf Range("B1") = "HV Breakers" Then
        Range("ALL").EntireColumn.Hidden = True
        Range("HVBREAKERS").EntireColumn.Hidden = False
    ElseIf Range("B1") = "HV Breaker W/ Timing" Then
        Range("ALL").EntireColumn.Hidden = True
        Range("HVBREAKERWTIMING").EntireColumn.Hidden = False
    ElseIf Range("B1") = "LV Breakers" Then
        Range("ALL").EntireColumn.Hidden = True
        Range("LVBREAKER").EntireColumn.Hidden = False
        End If
End Sub

This was working fine until I hit the character limit for my list (Just a portion of code, actually much longer). When I changed my list formula to =SheetList!$A$1:$A$24 to allow for a longer list, my sheet stopped updating. Any fix for this?

 

[crburke92]

Ok, the code is in the right place, did you add the word Stop, as it's not there now?

Yes sorry I could have clarified that. I had stop there, it does not open the window the then pan through using F8. I took it out trying something else which is when I took the snip. Like I said too. I initially just had the equipment names listed in the data validation list and It was working fine. I then switched it to the list reference, didnt work, then went back to having them listed in the data validation list as initially done and it no longer worked. Nothing else had changed.

 

[Fluff]

Would you be willing to upload the file to a share? It doesn't need any data, just the code & data validation.

 

[crburke92]

Would you be willing to upload the file to a share? It doesn't need any data, just the code & data validation.

Yep that's no issue. I'm still in building phase so all information is generic and made up. How do I upload the entire file as opposed to just a photo

 

[Fluff]

You can't upload it here, but if you upload to a site such as OneDrive, GoogleDrive, DropBox mark for sharing & then post the link you are give to the thread.

 

[crburke92]

You can't upload it here, but if you upload to a site such as OneDrive, GoogleDrive, DropBox mark for sharing & then post the link you are give to the thread.

Excel Sheet

Shared with Dropbox

www.dropbox.com

You're the best Fluff.

 

[Fluff]

Thanks for that, unfortunately it's working for me.
You do have macros enabled don't you?

 

[crburke92]

Thanks for that, unfortunately it's working for me.
You do have macros enabled don't you?

Yes, 100% have macros enabled. Like I said, it was working this morning and just randomly stopped. My module 1-3 still work as well. That’s so weird it’s working for you…

 

[Fluff]

Try running the reset code again & then change the drop down.

 

[crburke92]

Try running the reset code again & then change the drop down.

Yeah I’ll try a few things on my end, see if my buddy can run it on his laptop. Thanks for checking it out and not judging how clunky I’m sure my coding is haha. It’s good to know it at least works somewhere though..

 

[crburke92]

Try running the reset code again & then change the drop down.

Hey Fluff,

Just wanted to let you know I found the issue. While macros were enabled, 'Enable Excel 4.0 macros when vba macros are enabled' was not checked. Checked it off and its working now.... Not sure why it's been working for days then stopped on a dime but that did the trick... haha. Thanks for your help today Fluff, you really are the best on here!