none
Excel 2010 – VBA Userforms – Vlookup via Textbox, display result in another textbox RRS feed

  • Question

  • Hi,

    Thank you in advance for your time.

    I have a multi-page userform and I am having problems getting the result I am after.

    I am trying to get the user to enter a four digit (numeric code) into a textbox and display the contents of that code in another textbox when the user clicks in another textbox.

    I have 2 textboxes.

    One textbox txt_SAC_No is for the user to enter a four digit numeric code, which should return the address in another textbox txt_Site_Address.

    The lookup is located in a dynamic named range as SiteAddress which encompasses 20 columns. The first column has the code and the second column holds the actual address which is what I am after.

    Included in the named range, I have set up a code of 0000 (zeros) and instead of having the address in it, I have a message in there to tell the user to manually type the address (overwriting my text message). The code, 0000, means that there is no pre-defined address and it's a once off entry.

    I do not want the address to be updated in the source of the Vlookup as it is a ‘once only’ entry and unlikely to ever be used again.

    I have searched on the web and attempted to adapt various codes, but I have been unable to get anything to work.

    I have some sample code I have tried, but my attempt to adapt it to my needs has not been successful.

    Worksheet where data is transferred is code named: ws_Incident_Details (which is actually sheet named “Incident Details” – but I’d like to use the ‘coded’ sheet name in case someone changes the name in the tab of the sheet

    Private Sub SAC_No_AfterUpdate()
        'lookup value based on SacNo
            With Me
                .txt_Site_Address = Application.WorksheetFunction.VLookup(CLng(Me.txt_SAC_No), wsAlarmResponseList.Range("SiteAddress"), 2, 0)
             End With
    End Sub
    

    I'd really appreciate any help :) I have tried other avenues for help, but it doesn't seem like anyone wants the challenge :(

    Thanking you in advance .... it will indeed be appreciated :)

    Kind regards,

    ShyButterfly


    Hope you have a terrific day, theShyButterfly

    Tuesday, March 31, 2015 5:30 AM

Answers

  • One textbox txt_SAC_No is for the user to enter a four digit numeric code, which should return the address in another textbox txt_Site_Address.

    The lookup is located in a dynamic named range as SiteAddress which encompasses 20 columns. The first column has the code and the second column holds the actual address which is what I am after.

    Included in the named range, I have set up a code of 0000 (zeros) and instead of having the address in it, I have a message in there to tell the user to manually type the address (overwriting my text message).

    Okay, that is simple.

    Forget about dynamic named ranges and VLOOKUP, such things are useful in XLSX files without macros, but they are useless in VBA.

    Remove the dynamic range from the name manager, select all the cells that belongs to this data and name the cells as "SiteAddress" directly.

    Then use a code like below.

    Andreas.

    Sub Test()
      Dim R As Range
      Set R = Range("SiteAddress").Columns(1).Find(txt_SAC_No.Value, _
        LookIn:=xlValues, LookAt:=xlWhole)
      If R Is Nothing Then
        txt_Site_Address = "(not found)"
      ElseIf R.Value = "0000" Then
        txt_Site_Address = "(manually type the address)"
      Else
        txt_Site_Address = R.Offset(, 1).Value
      End If
    End Sub

    Tuesday, March 31, 2015 7:15 AM
  • This is my sample form link ... if you open the form and enter a code in there ... nothing happens. 

    This question is related to your other thread:
    https://social.msdn.microsoft.com/Forums/de-DE/2ca67afc-b437-43d2-a41f-4091a5b525ef/vba-userforms-getting-errors-when-item-from-combo-box-is-not-selected?forum=excel

    I'll answer your question there.

    Andreas.

    • Marked as answer by L.HlModerator Tuesday, April 7, 2015 5:43 AM
    Tuesday, March 31, 2015 1:59 PM
  • Hi Andreas :)

    The above code works like a dream :) Thank you ...

    I succeeded on this ocassion on being smart, and was successful in adapting your code to auto-complete textboxes based on the SAC # details.

    They ALL work purrrrfectly .... thank you SO much .....

    Here's the code that worked (this is based on the Name Range only capturing the 'Column Heading' ... so I created a Named Range for each of the Column Headings (highlighted in bold ... I hope :) )

     
    Private Sub txt_SAC_No_Change()
                                Dim R As Range
                                Set R = Range("SAC").EntireColumn.Find(txt_SAC_No.Value, _
                                  LookIn:=xlValues, LookAt:=xlWhole)
                                If R Is Nothing Then
                                  txt_Site_Address = "(not found)"
                                ElseIf R.Value = "0000" Then
                                  txt_Site_Address = "(manually type the address)"
                                Else
                                  txt_Site_Address = Range("SiteAddress").Cells(R.Row).Value
                                End If
    
        'Manned / UnManned Site:
                Set R = Range("SAC").EntireColumn.Find(txt_SAC_No.Value, _
                   LookIn:=xlValues, LookAt:=xlWhole)
                If R Is Nothing Then
                   txt_Manned_UnManned_Auto = "N/A"
                ElseIf R.Value = "0000" Then
                   txt_Manned_UnManned_Auto = "N/A"
                Else
                   txt_Manned_UnManned_Auto = Range("MannedUnManned").Cells(R.Row).Value
                End If
      
        'txt_Business_Owner:
                Set R = Range("SAC").EntireColumn.Find(txt_SAC_No.Value, _
                   LookIn:=xlValues, LookAt:=xlWhole)
                If R Is Nothing Then
                   txt_Business_Owner = "N/A"
                ElseIf R.Value = "0000" Then
                   txt_Business_Owner = "N/A"
                Else
                   txt_Business_Owner = Range("Business").Cells(R.Row).Value
                End If
    
    
        'Site Type:
                Set R = Range("SAC").EntireColumn.Find(txt_SAC_No.Value, _
                   LookIn:=xlValues, LookAt:=xlWhole)
                If R Is Nothing Then
                   txt_Site_Type = "N/A"
                ElseIf R.Value = "0000" Then
                   txt_Site_Type = "N/A"
                Else
                   txt_Site_Type = Range("SiteType").Cells(R.Row).Value
                End If
                              
                              
              End Sub
    
    

    With immense gratitude :)

    The ShyButterfly


    Hope you have a terrific day, theShyButterfly

    Monday, April 13, 2015 5:46 AM

All replies

  • One textbox txt_SAC_No is for the user to enter a four digit numeric code, which should return the address in another textbox txt_Site_Address.

    The lookup is located in a dynamic named range as SiteAddress which encompasses 20 columns. The first column has the code and the second column holds the actual address which is what I am after.

    Included in the named range, I have set up a code of 0000 (zeros) and instead of having the address in it, I have a message in there to tell the user to manually type the address (overwriting my text message).

    Okay, that is simple.

    Forget about dynamic named ranges and VLOOKUP, such things are useful in XLSX files without macros, but they are useless in VBA.

    Remove the dynamic range from the name manager, select all the cells that belongs to this data and name the cells as "SiteAddress" directly.

    Then use a code like below.

    Andreas.

    Sub Test()
      Dim R As Range
      Set R = Range("SiteAddress").Columns(1).Find(txt_SAC_No.Value, _
        LookIn:=xlValues, LookAt:=xlWhole)
      If R Is Nothing Then
        txt_Site_Address = "(not found)"
      ElseIf R.Value = "0000" Then
        txt_Site_Address = "(manually type the address)"
      Else
        txt_Site_Address = R.Offset(, 1).Value
      End If
    End Sub

    Tuesday, March 31, 2015 7:15 AM
  • Hi Andreas,

    Thank you for picking up my problem :)

    I renamed the dynamic range so it wouldn't be called. I created another normal range (selecting all columns with information required). Column 1 has the code, column 2 has the address details.

    First I copied your code into my 'working' form - noting happened (nada :()

    Then I exported my 'working' form and changed the name and imported it again and removed all irrelevant code and copied your code directly into a test form on the sample form - still no reaction.

    This is my sample form link ... if you open the form and enter a code in there ... nothing happens. 

    Because you are not using Vlookup I am a little confused/unfamiliar with the coding (used to the Vlookup formula in sheets). I intend to adapt your code for a couple of other textboxes to display the values ... so I hope I can get my head around it :)

    On opening my form ... you will find the other problem I have .. that the form will not open on the first page as a default ... (driving me insane)... so I have to make sure the first page is selected before I open the form. :) (the glitches never seem to stop :(

    Please let me know if there is any details you are not clear about ... I've tried to be as clear as possible, but then I know what I am talking about LOL ... doesn't help anyone else though! LOL

    Thank you again for taking the time to resolve my issue

    Sincerely

    ShyButterFly


    Hope you have a terrific day, theShyButterfly

    Tuesday, March 31, 2015 10:39 AM
  • This is my sample form link ... if you open the form and enter a code in there ... nothing happens. 

    This question is related to your other thread:
    https://social.msdn.microsoft.com/Forums/de-DE/2ca67afc-b437-43d2-a41f-4091a5b525ef/vba-userforms-getting-errors-when-item-from-combo-box-is-not-selected?forum=excel

    I'll answer your question there.

    Andreas.

    • Marked as answer by L.HlModerator Tuesday, April 7, 2015 5:43 AM
    Tuesday, March 31, 2015 1:59 PM
  • Hi Andreas :)

    The above code works like a dream :) Thank you ...

    I succeeded on this ocassion on being smart, and was successful in adapting your code to auto-complete textboxes based on the SAC # details.

    They ALL work purrrrfectly .... thank you SO much .....

    Here's the code that worked (this is based on the Name Range only capturing the 'Column Heading' ... so I created a Named Range for each of the Column Headings (highlighted in bold ... I hope :) )

     
    Private Sub txt_SAC_No_Change()
                                Dim R As Range
                                Set R = Range("SAC").EntireColumn.Find(txt_SAC_No.Value, _
                                  LookIn:=xlValues, LookAt:=xlWhole)
                                If R Is Nothing Then
                                  txt_Site_Address = "(not found)"
                                ElseIf R.Value = "0000" Then
                                  txt_Site_Address = "(manually type the address)"
                                Else
                                  txt_Site_Address = Range("SiteAddress").Cells(R.Row).Value
                                End If
    
        'Manned / UnManned Site:
                Set R = Range("SAC").EntireColumn.Find(txt_SAC_No.Value, _
                   LookIn:=xlValues, LookAt:=xlWhole)
                If R Is Nothing Then
                   txt_Manned_UnManned_Auto = "N/A"
                ElseIf R.Value = "0000" Then
                   txt_Manned_UnManned_Auto = "N/A"
                Else
                   txt_Manned_UnManned_Auto = Range("MannedUnManned").Cells(R.Row).Value
                End If
      
        'txt_Business_Owner:
                Set R = Range("SAC").EntireColumn.Find(txt_SAC_No.Value, _
                   LookIn:=xlValues, LookAt:=xlWhole)
                If R Is Nothing Then
                   txt_Business_Owner = "N/A"
                ElseIf R.Value = "0000" Then
                   txt_Business_Owner = "N/A"
                Else
                   txt_Business_Owner = Range("Business").Cells(R.Row).Value
                End If
    
    
        'Site Type:
                Set R = Range("SAC").EntireColumn.Find(txt_SAC_No.Value, _
                   LookIn:=xlValues, LookAt:=xlWhole)
                If R Is Nothing Then
                   txt_Site_Type = "N/A"
                ElseIf R.Value = "0000" Then
                   txt_Site_Type = "N/A"
                Else
                   txt_Site_Type = Range("SiteType").Cells(R.Row).Value
                End If
                              
                              
              End Sub
    
    

    With immense gratitude :)

    The ShyButterfly


    Hope you have a terrific day, theShyButterfly

    Monday, April 13, 2015 5:46 AM