Data Validation list VBA odd behaviour?

Simon Lloyd

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
Hi all, i'm trying to pick up all the items in a cells data validation list, this first approach works great:
Code: 
Sub DV_Item_List()
Dim MyCell As Range
For Each MyCell In Range(Right(ActiveCell.Validation.Formula1, Len(ActiveCell.Validation.Formula1) - 1))
msg = msg & vbLf & MyCell.Value
Next MyCell
MsgBox msg
End Sub
I use RIGHT to trim the = from the validation formula so i can use it in the range, my trouble begins when i want to use it in the Worksheet_SelectionChange event, using this gives me an error (1004), on checking the value it shows that the = has not been trimmed off??? anyone any ideas?
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyCell As Range
For Each MyCell In Range(Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1))
msg = msg & vbLf & MyCell.Value
Next MyCell
MsgBox msg
End Sub
The data validation is on the activesheet, the named ranges it refers to are on another, just using the first code posting i don't need to qualify the range with the sheet that hosts the named range if i use it within the worksheet code module i do like this
Code: 
Sheets("Sheet1").Range(Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1))
it's strange as
Code: 
Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)
actual gives the named range as a string without the = yet plug that in to RANGE and it reverts to include the =?

Look forward to your views on this :)
 
Not sure I am getting the whole picture Simon, but does this do what you want?

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyCell As Range
Dim msg As String

    For Each MyCell In ActiveWorkbook.Names(Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)).RefersToRange
        msg = msg & vbLf & MyCell.Value
    Next MyCell
    MsgBox msg
End Sub

It does assume that the DV has a named range, and it needs to cater for other cells (non-DV).
 
Hi Bob thanks for the reply, i suppose thats better than qualifying the worksheet, i just found my two posted methods odd why they didn't work the same without qualifying it?. Your solution works just as well (and shorter :)), i'm not interested in cells that don't have DV, just FYI, i'm designing a crewing tool which consists of many cells with DV and of course many dynamic named ranges associated with them, as the tool is going to be point 'n' click and there are many staff to consider i'm populating an area with all the names from the selected DV minus the chosen one so the user has an idea of who is left to deploy in that skill base, some job skills overlap, these cells are adjacent and have DV, when two DV cells are adjacent i'll be populating the "Remaining Crew" area with the people from both DVs.
 
Simon,
If you use Range in a worksheet code module without specifying a worksheet, it refers to a range on that sheet. Since your range is not on the sheet with the DV list, you will get an error.
Incidentally, I would use
Code: 
Mid$(Target.Validation.Formula1, 2)
to get the range.
 
You must log in or register to reply here.
Back
Top