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:
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?
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
it's strange as
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
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
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
Code:
Sheets("Sheet1").Range(Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1))
Code:
Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)
Look forward to your views on this