Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Data Validation - 'Go To' source cell (2003 SP2)

    Dear Loungers,

    1. This must be possible but I can't find it...
    I have a column that has data validation applied and it refers to a list, having populated a cell is there any way to "go to" the referenced list entry

    2. The data validation list referred to above does not complain on invalid entries. It is set up to do so but simply doesn't, any ideas why?

    Thank you

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Data Validation - 'Go To' source cell (2003 SP2)

    Hi,
    When you say "go to", do you mean you actually want to select that cell?
    Re 2, I suspect your problem is the 'Ignore Blanks' option - uncheck that and see if it helps.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Data Validation - 'Go To' source cell (2003 SP

    Rory,

    Quite right about ignore blanks, thank you - it's not very intuitive is it?

    About the other thing:

    What I want to do is for example this

    1. I have a column with data validation based on a list defined elsewhere for example OrgType
    2. I have selected a cell in that column that holds the value "company"
    3. I want to "go to" the entry in the OrgType list with the value of "company"

    I was thinking that right click might have something - I mean it's a little like wanting a hyperlink - or that Go To would have something but I don't think it does

    thank you for your help

    liz

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Data Validation - 'Go To' source cell (2003 SP

    Liz,
    That's actually a bug, hence the fact it is not intuitive!
    You could use code like this in the DoubleClick event of the worksheet:

    <pre>Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim varData, strSource As String, rngData As Range
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    varData = Target.Value
    strSource = Mid(Target.Validation.Formula1, 2)
    Set rngData = ActiveWorkbook.Names(strSource).RefersToRange.Find (varData)
    rngData.Parent.Activate
    rngData.Select
    Cancel = True
    End Sub
    </pre>


    Note: this assumes you have the data validation in A1 and only monitors that cell but you can adjust that as necessary
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Data Validation - 'Go To' source cell (2003 SP

    Rory,

    This looks like the sort of thing... except I may be doing it wrong as I can't quite make it work. I was using only one cell to test it (in my case D3) but double click does nothing. Could I have implemented or executed it wrongly?

    Then having made it work can I change it to monitor either a number of ranges - I have a number of columns on each sheet that use data validation and some others that retrive using VLOOKUP. Or can I refer to the whole worksheet and will there be performance implications?

    Thanks liz

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Validation - 'Go To' source cell (2003 SP

    Have you placed the code in the worksheet module? You open this module by right-clicking the worksheet tab and selecting View Code from the popup menu.

    You can replace Range("A1") in the code with a reference to a range that has similar validation. You shouldn't include cells that don't have validation - that would cause an error.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation - 'Go To' source cell (2003 SP

    Did you put Rory's code in the event module module behind the sheet? That code can not go in a normal module. If that is not where you put it, the right click on the sheet tab with the cell/cells that have the validation. In the popup menu, click on "View Code" and put the code in the module that appears.

    To make the code work on more than one cell, you must change the line of code below:

    <code>
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    </code>

    change the Range("A1") in that line with the range or Union of ranges where you want the code to work.
    Legare Coleman

  8. #8
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Data Validation - 'Go To' source cell (2003 SP

    Hans,

    Found the problem... the code was in the right place but I was still in design mode and had closed the toolbar so didn't notice! All works beatifully now. Manyt thanks to all of you,

    liz

Posting Permissions

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