Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Get from table. (Excel)

    I have two worksheets one is a table holding peoples info and the other worksheet is a fill in type.

    I have developed a macro which uses cells that are named and use offset, match, index functions.

    the problem is this thing I made is sloppy, and not doing what I want.

    I first envisioned having a drop down combo box. the user could then type the starting letters and the combo box would find a match. I could not get this to work though, so I had to goto a form listbox.



    I am looking for a way to use a (combo box) instead of the list box . Also Right now I think I have done too much work for what I need and though someone else might know a simpilar way.
    Any Ideas?

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get from table. (Excel)

    It looks like you have 1) Table (database) with data
    2) an entry form for new data (green cells)
    3) a combobox ---what do you want to do with this? I appears that you have is set up to
    VIEW exiting records in your db. If so, this function is unrelated to data entry.

    Do you need a macro to transfer the entry-form data to the db? or are you looking
    for a way to use the combobox as a data entry form?

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get from table. (Excel)

    I don't think that you need any code, just Data Validation and VLOOKUP. Does the attached worksheet do what you want?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get from table. (Excel)

    yes, thats sort of the idea I am after. But sometimes the name is not in my DB. so it must be added manually. The data validation box doesnt allow you to type in it, but the combo box does.

    I am looking for a way to quickly fill in customer information. Some customers are repeats so it makes sense to store them in a DB so the next time they show up, its easy to add them.

    But in some cases the customer has not been added to the DB so I will have to just type their info in manually. The DB is updated by a macro which looks in a Contacts folder in Outlook, so if I want to update the database with customer info, they will need to be added to the outlook contact list instead.

    So, the idea I am chasing is to have some kind of automation fillin if a lookup reconizes a match in the DB. if not the I can fill it in manually.

    My current one seems to not be forgiving so I am trying to figure a different routine and thought maybe a comboBox was a good idea to acheive what I am after.

  5. #5
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get from table. (Excel)

    Paul, I am looking for the combo box as being a way to fill in the green cells. The unique key here would be Customers Name so those would be stored in the combo box.
    when I type in the combo box "A", the list in the combo box goes to the first A entry in the list, as I type more chars, the list narrows down to a match. I can then scroll through the list to find a match, If I cant then the name will have to be added manually, and the dataBase wont be updated with the new name.

    Thanks

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get from table. (Excel)

    Ah, in that case, I would keep the Data Validation, but turn off the error message. Then I would move the VLOOKUP to the Worksheet Change event:
    <pre>Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$B$3" Then Exit Sub
    Dim c As Range
    For Each c In Range("B4:B11")
    c = Application.Evaluate( _
    "IF(ISERROR(VLOOKUP($B$3,CustInfo," & c.Row - 1 & ",FALSE))" & _
    ","""",VLOOKUP($B$3,CustInfo," & c.Row - 1 & ",FALSE))")
    Next c
    End Sub
    </pre>



    See attached worksheet for the rewrite. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get from table. (Excel)

    Thanks,
    Is there a way to update CustInfo,

    I ask because I have a macro behind a button.
    When the button is click the macro updates the sheet from a contact list in outlook, so the data in Table changes.
    so the range CustInfo would need to be dynamic making its range selection the same size as the last Customename , growing or subtracting in rows depending on what the update bring in.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Get from table. (Excel)

    If you are using VB to import the range, you could add code like this to set the range based on how many items are in column A:

    <pre>With Worksheets("Table")
    .Range(.Range("J1"), .Range("A65536").End(xlUp)).Name = "CustInfo"
    End With</pre>


    Steve

Posting Permissions

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