Dependent Data Validation List & ByVal Sub

crburke92

Board Regular
Joined
Feb 5, 2019
Messages
71
Hey all,

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

VBA Code:
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?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Changing the way a data validation cell is populated should not affect the macro.
Try putting this in a normal module & run it. Then change B1 & see if the columns get hidden
VBA Code:
Sub Reset()
   Application.EnableEvents = True
End Sub
 
Upvote 0
Changing the way a data validation cell is populated should not affect the macro.
Try putting this in a normal module & run it. Then change B1 & see if the columns get hidden
VBA Code:
Sub Reset()
   Application.EnableEvents = True
End Sub
I didn't assume it would... That didn't do the trick either :(

Not sure if this makes a difference. My ByVal Sub is written into the sheet itself, not a module. Pretty sure thats how the Worksheet_Change has to be done though right?
 
Upvote 0
Ok, add the word Stop as shown
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Stop
If Range("B1").Value = "All" Then
and then change B1. The code window should open with Stop highlighted, you can then step through the code using F8 to see what is happening.
 
Upvote 0
Ok, add the word Stop as shown
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Stop
If Range("B1").Value = "All" Then
and then change B1. The code window should open with Stop highlighted, you can then step through the code using F8 to see what is happening.
I did that, nothing happened. Tried going back to a reduced list in my data validation box that was working, and now it's not updating. Made sure my named ranges didnt somehow change but they're fine. Double checked spelling and its fine (When I initially cleared my list from data validation box I copied and pasted it to SheetList!B1 Just so I wouldnt lose it. If I didn't change my macro at all, what else would cause it to stop working out of nowhere?
 
Upvote 0
Ok, if you right click the tab with the data validation in B1 & select view code, is the code in the window that opens up?
 
Upvote 0
Ok, if you right click the tab with the data validation in B1 & select view code, is the code in the window that opens up?
Yes, Code is under ClientDatabase worksheet, as a worksheet_Change. I've added a few photos so you may have a better idea of what I'm looking at.
Pic1.PNG
Pic2.PNG
Pic3.PNG
 
Upvote 0
Ok, the code is in the right place, did you add the word Stop, as it's not there now?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top