Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup Text and Display in Form (Excel 2003)

    Hi

    I have create a Form, I want to look up the correspondence values in column B, C, D in sheet 1 and display these values in the matching textboxes of the form which are; Account Name & Standing Instruction details, after I input the account no and the currency into the form. I have attached the form and data.

    How to modify this code to do what I want :

    Sub FindText()
    Dim wks As Worksheet
    Dim rngDataRange As Range
    Dim rng As Range
    Dim strFindText As String

    Set wks = ThisWorkbook.Worksheets("Sheet1")
    Set rngDataRange = wks.Range("A:Z")
    strFindText = " "

    For Each rng In rngDataRange
    If rng.Text = strFindText Then
    ....."what need to be change here to display the values in the ComboBox"
    End If
    Next rng
    End Sub

    Thanks in advance.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Lookup Text and Display in Form (Excel 2003)

    I think you are trying to emulate a database in Excel. It might be better to use a database program such as Access for this kind of problem.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Text and Display in Form (Excel 2003)

    Hi Hans

    Unfortunately, my office does not use Access. Morover, This sheet contain about 1000 entries, hence the idea to create the Form.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Lookup Text and Display in Form (Excel 2003)

    Perhaps it's time that your office starts using Access (or another database system).

  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: Lookup Text and Display in Form (Excel 2003)

    It would be much easier if you changed your data structure to show all the details on all the rows rather than having blank data - is that possible?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Text and Display in Form (Excel 2003)

    Hi Rory,

    The blank row actually refers to the account no above, and yes its possible, I just need to copy down.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Text and Display in Form (Excel 2003)

    Hi Hans,

    Generally every business will have a normal Office appl. but due to some policies, my organisation doesn't allow that.

    Thanks for the suggestion.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Text and Display in Form (Excel 2003)

    Hi

    I have change the codes However, there are some which encounter some problem. The Form don't show what I want when I click the Exit button.
    Private Sub ExitButton_Click()
    'exits the system'
    Dim response As String
    response = MsgBox("Are you sure you wish to exit?", VbMsgBoxStyle.vbYesNo, "check selection")
    If response = vbYes Then
    Me.Unload

    End Sub

    The Vlookup for Currency produce error 13 : type mismatch after I type in a currency code, such as USD
    code :
    Private Sub txtCurr_AfterUpdate()
    With txtCurr
    TxtSI.Text = Application.VLookup(.Text, Worksheets("SI").Range("A"), 4, False)

    End With

    End Sub


    I am loss as to where did I go wrong. Would appreciate your help.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Lookup Text and Display in Form (Excel 2003)

    1) response should be declared as an Integer, not as a String

    2) The instruction to unload the form is

    Unload Me

    instead of

    Me.Unload

    3) You're missing an End If below the line that unloads the form.

    4) Your code refers to several non-existent controls: btnClear, txtCode1, txtCode2, txtName, txtAddress, txtPostCode, txtTown.

    5) VLookup always searches for the search value in the first column of the lookup range.

    <img src=/w3timages/blueline.gif width=33% height=2>

    I wouldn't use a form at all, I would use either AutoFilter or Advanced Filter.

  10. #10
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Text and Display in Form (Excel 2003)

    Hi Hans,

    Thanks. I have managed to complete the codes for the form. However, I noticed that if a user input a wrong ac,
    it won't show an error message, such as " Invaild Account". Can you help with the codes.

    Thanks
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Lookup Text and Display in Form (Excel 2003)

    Change the text boxes for account and currency to combo boxes, and fill them in the Initialize event with the available values.

    If you use AutoFilter instead of a userform, you get dropdown lists automatically, without any programming.

  12. #12
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Text and Display in Form (Excel 2003)

    Hi Hans,

    I know about the Auto and advance filter functions, but I need something where it is easy for the users and I can't assume that
    all will know how to use this. Moreover, this workbook / sheet will be protected and a read only file.

    Coming back to this, I am at a loss how to use the ComboBox and the Initialize event. I have read from MS site, VB Help and JW's book, but still confuse how to use it. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> . Appreciate that you shed some light on it as I am still learning VB and its beyond my level currently. Thanks

    Attached the latest file.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Lookup Text and Display in Form (Excel 2003)

    You can turn on AutoFilter, then protect the worksheet and specify that AutoFilter is allowed.
    AutoFilter is extremely easy to use.

  14. #14
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Text and Display in Form (Excel 2003)

    Hi Hans

    Thanks, I will try on the autofilter however, I still would like to know how to do what you have mentioned above changing the txtboxes to comboboxes and Initialize event with the available values.

    thanks in advance
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Lookup Text and Display in Form (Excel 2003)

    See attached version. The code uses a hidden worksheet Sheet2 to store the lists of unique entries.

Page 1 of 2 12 LastLast

Posting Permissions

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