User Form - Combo Box selection populates textboxes based on array from workbook

[Mabes]

Hello all,
I have been tinkering with this code for a bit and cannot seem to get it to work. I have a table that stores a lot of info on each row. We access that based on the unique number in the first column - I have created a user form to make it simpler for my staff to retrieve that info. The only problem is that when I select one of the unique numbers - none of the other boxes populates. The key is that once a selection is made in the TrComboBox I want the text boxes to pull the appropriate data from my table. Can anyone help me out?

Private Sub TrComboBox_AfterUpdate()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("MAIN PROFILE")
wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To wsLR
    If ws.Cells(x, 1) = TrComboBox.Value Then
        Me.SysNoTextBox = ws.Cells(x, 9)
        Me.TrAddressTextBox = ws.Cells(x, 3)
        Me.CityTextBox = ws.Cells(x, 4)
        Me.ProvTextBox = ws.Cells(x, 5)
        Me.ABMIDTextBox = ws.Cells(x, 13)
        Exit Sub
    End If
Next x
End Sub

 

[Luke M]

Welcome to the forum!
Hmm. There's a few objects in there w/o parent objects, and your loop may be taking too long. How about this?

Private Sub TrComboBox_AfterUpdate()
Dim ws As Worksheet
Dim x As Long
Dim fCell As Range
Set ws = ThisWorkbook.Sheets("MAIN PROFILE")

With ws
    'Find the cell which has rec nunmber. Much faster than looping
    Set fCell = .Range("A:A").Find(trcombobox.Value, , xlValues, xlWhole)
   
    'If record doesn't match, do nothing
    If fCell Is Nothing Then Exit Sub
    x = fCell.Row
   
    Me.SysNoTextBox.Value = .Cells(x, 9).Value
    Me.TrAddressTextBox.Value = .Cells(x, 3).Value
    Me.CityTextBox.Value = .Cells(x, 4).Value
    Me.ProvTextBox.Value = .Cells(x, 5).Value
    Me.ABMIDTextBox.Value = .Cells(x, 13).Value

End With
End Sub

 

[Mabes]

Thanks for the quick reply Luke - no dice. I have read elsewhere that numerical values in the combo box could cause some problems. The Range that is being looked up has the cells formatted to a custom "00000" format. But when I select from the combo box the first value (00018) turns up as 18. Since that doesn't precisely match the value in the Range it isn't pulling the data...? Could that be the reason your code isn't working? I tried it with another number that doesn't have zeroes at the beginning and the same thing happened. I suspect that the code is looking for a value with a format that doesn't match where it is looking. Is that a possibility? Thanks again for the help!

 

[Luke M]

It shouldn't be...the code says to take the value of each item in question, so what is being displayed (ie, the format), shouldn't be making a difference. Would it be possible for you to post your workbook (or at least the userform & sheet in question)?

 

[NARAYANK991]

Hi ,

See the attached file ; a combo box always returns a string output. You need to compare after converting it using the VAL function.

Secondly , if your values which you are looking for are within a table , instead of using xlValues in the Find function , you need to use xlFormulas ; xlValues finds only in worksheet cells , not in Excel tables.

Narayan

 

[Luke M]

Thanks for the tip about ComboBoxes Narayan. However, I would disagree on the statement about xlValues and the Find function, at least with my testing. Using XL 2010, this seems to work just fine.

Macro I tested:

Sub test()
Dim fCell As Range
Set fCell = Range("A:A").Find("A", , xlValues, xlWhole)
If fCell Is Nothing Then Exit Sub
MsgBox fCell.Address
End Sub

Worksheet setup:


Result of macro running:


Have I misunderstood, my friend?

 

[NARAYANK991]

Hi Luke ,

Sorry , it's a mistake on my part ; now , when I check it works , but I am sure when I checked it earlier , it showed only the cell address in the worksheet and not the cell address within the table.

So , that conclusion in my earlier post is wrong. Thanks.

Narayan

 

[Mabes]

Here is a screen shot of the form itself and the stuff I am trying to do with it. The sheet itself is too big to upload....

 

[Mabes]

Gents - Luke M's code did in fact work. I am still encountering the problem with the formatting though - Here is the latest version (with many thanks to Luke M!)

Private Sub TrComboBox_AfterUpdate()
Dim Ws As Worksheet
Dim x As Long
Dim fCell As Range
Set Ws = ThisWorkbook.Sheets("MAIN PROFILE")

With Ws
    'Find the cell which has rec nunmber. Much faster than looping
    Set fCell = .Range("A:A").Find(TrComboBox.Value, , xlValues, xlWhole)
   
    'If record doesn't match, do nothing
    If fCell Is Nothing Then Exit Sub
    x = fCell.Row
   
    Me.SysNoTextBox.Value = .Cells(x, 9).Value
    Me.TrAddressTextBox.Value = .Cells(x, 3).Value
    Me.CityTextBox.Value = .Cells(x, 4).Value
    Me.ProvTextBox.Value = .Cells(x, 5).Value
    Me.ABMIDTextBox.Value = .Cells(x, 14).Value
    Me.OnsiteSecTextBox.Value = .Cells(x, 28).Value
    Me.NSSTextBox.Value = .Cells(x, 24).Value
    Me.StaffTextBox.Value = Application.UserName
   
End With
End Sub

The issue is that any the list that the combobox draws from contains some identifiers (4-8 Digit values that are unique to a facility) that start with zero. Those ones, when selected, return no values into the textboxes. I think it is because when selected the zeroes dissapear, and so when compared to the list (where the zeroes are present) the code does not see a match. Any ideas on how to fix that?

 

[Luke M]

Instead of grabbing the Cells(...).Value, try grabbing the Cells(...).Text

The text property will grab what the cell's displaying, rather than it's actual value. That should help with the leading zeroes issue.

 

[Mabes]

Well that didn't work either. The issue is the ComboBox. When I select a value in the list it cuts the zeroes and shrinks it down to the digits behind the zeroes.
How do I adjust the ComboBox properties? maybe ComboBox.Value=Format(Custom"00000")

 

[NARAYANK991]

Hi ,

If you had uploaded your file , you would have got your solution by now.

Narayan

 

[Mabes]

Here is the file.

 

[NARAYANK991]

Hi ,

I am somewhat confused by the file you have uploaded.

You have 2 userforms , one for making new entries , and one for updating earlier entries ; I assume that each of the buttons will link to its own macro. At present I don't see either of these 2 macros.

The UserForm1 has a lot of code , while UserForm2 has none. Which userform has the problem ?

From your initial posting , I assume that when I enter the transit number in the TransitNoTextBox , there should be code which will use this to retrieve information from the database and populate all the other fields in UserForm2. Where is this code ?

Can you clarify ?

Narayan

 

[Mabes]

Narayan,
It's the "New Entry" form. The only thing I am trying to figure out is why I can't get the combobox to populate correctly. The list is correct in the display, but when I select one all of the zeroes dissapear and the code won't pull the rest of the data because the values don't match. Any ideas?

 

[NARAYANK991]

Hi ,

If it is a new entry form , where is the question of the combobox populating correctly ?

Only if it is a retrieve and update , you need to select one from the list of entries , which will then ensure that all the textboxes are populated correctly based on the data which is available in the database.

If you are using the New Form for retrieving data based on the entered ID , that is not correct.

Just as you have code for the New Form , you need to write separate code for the Update Form , which will then do what you want done.

Narayan