Results 1 to 4 of 4

Thread: Data Validation list VBA odd behaviour?

  1. #1
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0

    Data Validation list VBA odd behaviour?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  2. #2
    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).

  3. #3
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    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.
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  4. #4
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •