Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Sep 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validation lists (2003)

    Hi there
    When using a lookup list in Access the information is "Auto-completed" when you start filling in the first few characters. Is it possible to do this in Excel? I have a list with quite a large source, but when typing the first few characters the information does not appear and you basically have to select from the entire list in order to select information. This is quite a lengthy process as people need to select their e.g. names from a list of 100. It would be easier if they start typing their names and the rest is completed. Any ideas?
    Thank you!
    <img src=/S/butterfly.gif border=0 alt=butterfly width=15 height=15>

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Validation lists (2003)

    Excel should do this automatically.
    If you type test into cell A1, enter and type testinto cell A2, it should autocomplete!
    Go to Tools Options. Click the Edit tab, and verify if Enable autocomplete for cell values is active! If not, check the box!
    Regards,
    Rudi

  3. #3
    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: Validation lists (2003)

    Hi,
    Unfortunately data validation does not work the same way as a combobox. Are you using this to populate one cell only or is this validation applied to a whole range of cells?
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Lounger
    Join Date
    Sep 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation lists (2003)

    Hi Rory
    The validation is for a whole range of cells and it is getting the content from another range of cells.
    Thank you!

  5. #5
    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: Validation lists (2003)

    Hi Rhonda,
    This is not actually that easy. I can think of two ways of achieving roughly what you want, neither is perfect. They are both demonstrated in the attached workbook (you will need to enable macros for the second way to work - I recommend as always that you have a look at the code before you enable it!)
    The first way uses an amended name list including initial letters to break up the list of names and a dynamic range name for the validation. You have to enter an initial letter in column A (on the first sheet) then click on the drop down and the list will start from that letter. This does mean that your users could enter B for example as a name and the spreadsheet would accept it - I don't know if that is a problem.
    The second way hijacks the right-click event of the worksheet for column A only. On the second sheet, if you right-click in column A, a small userform appears with a combobox listing the names. You can then select one as normal and it will be entered in the target cell. Column A also has data validation applied.
    Hope that gives you some ideas.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation lists (2003)

    Rory,

    I attempted to adapt the second method you provided to Rhonda to an Excel XP (2002) workbook with a similar need. Unfortunately, my VBA knowledge (being more than novice, but apparently only somewhat more) has enabled me to create a right click which does not display the combobox form, but is very good at locking Excel up so tight, that I must use the task manager to end the Excel session.

    I created a named dynamic range (CaseID) in the 'Cases' worksheet, to use for the "validation list" that the combobox uses (my list will be constantly growing). I then built the form with combobox and button exactly like yours (with different labelling of course) called frmCasePicker and linked the combobox to Cases!CaseID. I then included your Worksheet_BeforeRightClick VBA code in the 'Transactions' worksheet (where it will be used), changing the With statement and Target.Value equasion to look at the appropriate form and combobox name (mine is called ComboBoxCase). I also applied data validation (matching that which you placed on your sample worksheet) to Transactions column A (which is the column where I'm entering the CaseIDs for the transaction rows). However, unlike your sample spreadsheet, adding validation to column A causes dropdown boxes to appear in any selected cell (column A only) in my worksheet.

    Obviously, I've missed something along the way, since your sample works perfectly on my system, but my creation does not. I'd include an example of my workbook, but, unfortunately, it would take me a week to filter out all the privacy information in it. Do you (or anyone else) have any ideas about what I've done wrong in applying this method to my workbook?

    Thanks for the help,

  7. #7
    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: Validation lists (2003)

    Jamie,
    A few quick questions:
    1. What is the formula for your dynamic range?
    2. What is the code in the Right-Click event of the worksheet?
    3. Did you apply data validation as well to the same sheet?
    If you can supply the details for the first two questions, I can probably create a sample sheet using the correct code/names for you to compare to your own example or else try and work out what has gone wrong.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation lists (2003)

    Rory,
    Thanks for the response. To answer your questions:

    <font color=blue>1. What is the formula for your dynamic range?</font color=blue>

    CaseID (Dynamic Named Range):

    =OFFSET(Cases!$A$2,0,0,COUNTA(Cases!$B$2:$B$65536) ,1)

    Since the cells of column A contain a formula which builds the Case ID from the data entered in
    columns B through E, I use column B to determine how many rows currently contain Case IDs.

    <font color=blue>2. What is the code in the Right-Click event of the worksheet?</font color=blue>

    Here's how I modified your code:

    <pre>Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    With frmCasePicker
    .Top = Target.Top - ActiveWindow.Height + Application.Height - Target.Height
    .Left = Target.Left + Target.Width + 20
    .Show
    End With
    Target.Value = frmCasePicker.ComboBoxCase.Text
    Cancel = True
    End If
    End Sub</pre>


    <font color=blue>3. Did you apply data validation as well to the same sheet?</font color=blue>

    At first, I did not have any data validation applied to any columns. However, when I discovered that the right-click was locking up Excel, I added validation to only column A (where I'm trying to enter the Case ID from the list in column a of the Cases worksheet) in the Transactions worksheet. Since the right-click still caused lockups, I removed the validation.

    The reason I'm trying to use this approach is to speed up data entry of the Case ID for transactions where the case already exists (has been entered in the Cases worksheet). This also lets the user enter transactions for new cases in a way that subtly informs them that the case's master data needs to be entered without interfering with the transaction entry process.

    Thanks,

  9. #9
    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: Validation lists (2003)

    Jamie,
    Does the attached work for you? It uses your defined name and I changed the names of the form and combobox.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation lists (2003)

    Rory,

    Your workbook functions perfectly with the changes you made for me. What still has me confused, is why the workbook I created (which for all intents and purposes is set up just like yours) doesn't work. I must be doing something wrong or leaving something out.

    Thank you for your help,

  11. #11
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation lists (2003)

    Rory,

    Due to the importance of this project, I was able to enlist the help of several people and create a test file. We copied the "original" workbook and filled it with bogus data that could be used for testing. Upon completion of the test workbook, we made an inexplicable discovery: the right-click worked flawlessly. We had made only one set of changes to the test workbook as opposed to the original, we modified all the references to the workbook's name in the VBA code (none of them had changed automatically when we copied and saved the workbook) to reflect the name of the test workbook: TPR Case Tracking-Test.xls (the original doesn't have "-Test" in the name).

    But that wasn't the end of the mystery. After making this discovery, I tried the right-click on the original workbook and, lo-and-behold, it worked as well. I have no explanation for the sudden change in functionality, but am happy that it is working. I am attaching the test workbook in a zipped file so that you can see what we are doing.

    I have one more question. When the user has typed enough of the Case ID for the desired (or new ) ID to appear in the combobox, if they click on the enter button, the ID is immediately entered into the appropriate cell in column A. However, if they choose to press the 'Enter' key, instead, they have to press it twice to make the entry complete. It appears that the first press of 'Enter' only changes the focus from the combobox to the cmdbutton. Is there any change that can be made to the VBA code to make the entry work on the first keypress?

    Your method is brilliant and very useful. Thanks for your willingness to share your knowledge and skill with the rest of us. I sure appreciate your helpfullness,

  12. #12
    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: Validation lists (2003)

    Jamie,
    Glad you got it working! You should find that if you change the value of the Default property of the Enter commandbutton to True, it will then work on the first press of the enter key.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation lists (2003)

    Rory,

    Thanks to you and many other on this forum, I learn more every day. Changing the Default property works perfectly. I have several very happy users right now.

Posting Permissions

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