Populate a combobox & textbox in a userform with data from a dynamic table

[JackyJ]

I have two userforms. These userforms are accessed from 2 command buttons on sheet 2. The first userform (named 'Add Suburb') adds information to a dynamic table on Sheet 1. This table has 3 columns named ‘City’, ‘Suburb’ & ‘Klm from GPO’. The second userform (named 'Edit Suburb') edits or deletes information from the same dynamic table. My problem is that I don’t know how to populate the 1 combobox and 2 text boxes with data from the table in the second userform ('Edit Suburb'). I want to be able to select the suburb, that needs editing or deleting, using a combobox. The 2 text boxes named ‘City’ & ‘Klm from GPO’ would then fill automatically from the other 2 columns in the table. I will then be able to update the information as required.
Any help would be greatly appreciated.


Updated: The original post had the Sheet numbers back to front.

[Tinbendr]

I'm confused. Does the data go to the green table or the blue table?

Is the data (in either table) unique?

You say the buttons are on Sheet 1, but they are on sheet2. You say you want the data to go to sheet two, but the code under frmAdd points to sheet1.

[mikerickson]

In the Edit Suburb form: is this what you want?
The user chooses a suburb from the combobox.
The text box fills with the (current) city that matches the suburb.
The other textbox fills with the (current) Klm.
Do you want to be able to edit the City or only the Klm?

[JackyJ]

My sincere apologies. I got the sheet numbers mixed up when writing the request.
The userforms will be updating the blue Table in Sheet 1. This sheet will be hidden when in use.

The green table in Sheet 2, using data validation, will access the data in the Ranges in Sheet 1 based on the information entered into the blue table in Sheet 1.

In the Edit Suburb form: is this what you want?
The user chooses a suburb from the combobox.
The text box fills with the (current) city that matches the suburb.
The other textbox fills with the (current) Klm.
Do you want to be able to edit the City or only the Klm?

This is correct and I wish to edit both the City and the Klm of the suburb that is selected with the combobox.

[mikerickson]

Try putting this in the Edit Userform's code module.
It uses the (new) named range CityData which
RefersTo: =Sheet1!$C$1:INDEX(Sheet1!$A:$A,MATCH("zzz",Sheet1!$A:$A),1)

[VBA]Private Sub cboSub_Change()
On Error GoTo Halt
With ThisWorkbook.Names("CityData").RefersToRange
With Rows(Application.Match(cboSub.Value, Columns(2), 0))
txtCity = CStr(.Range("A1").Value)
txtKlm = Val(CStr(.Range("C1").Value))
End With
End With
Halt:
On Error GoTo 0
End Sub

Private Sub cmdAdd_Click()
On Error GoTo Halt
With ThisWorkbook.Names("CityData").RefersToRange
With Rows(Application.Match(cboSub.Value, Columns(2), 0))
.Range("A1").Value = txtCity.Text
.Range("C1").Value = Val(txtKlm.Text)
End With
End With
On Error GoTo 0
Call FillWithSuburbs
Halt:
On Error GoTo 0
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Call FillWithSuburbs
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the 'Close Form' button!"
End If
End Sub

Sub FillWithSuburbs()
Dim oneCell As Range, newName As String, i As Long
Dim myColl As New Collection
For Each oneCell In ThisWorkbook.Names("CityData").RefersToRange.Columns(2).Offset(1, 0).Cells
newName = CStr(oneCell.Value)
If newName <> vbNullString Then
For i = 1 To myColl.Count
If LCase(newName) < LCase(myColl(i)) Then
On Error Resume Next
myColl.Add Item:=newName, key:=LCase(newName), before:=i
On Error GoTo 0
Exit For
End If
Next i
On Error Resume Next
myColl.Add Item:=newName, key:=LCase(newName)
On Error GoTo 0
End If
Next oneCell
txtCity.Text = vbNullString
txtKlm.Text = vbNullString
With cboSub
.Clear
For i = 1 To myColl.Count
.AddItem myColl(i)
Next i
End With
End Sub[/VBA]

[JackyJ]

Thank you so much mikerickson.
I’m not sure if I’ve done something wrong here, but after selecting the suburb from the combobox (which now references your new ‘CityData’), the ‘City’ and ‘Klm’ text boxes read data from the Green table on sheet 2 instead of the Blue table on Sheet 1. Is this correct?
I also think I’ve made a mistake regarding the ‘City’ text box. I’m thinking it should have been a combobox because it needs to access data from the range ‘CitiesCol’ in Sheet 1. Would this be correct? Is it possible to populate a ‘City’ combobox when selecting the ‘Suburb’, and still have the ‘City’ combobox access the range ‘CitiesCol’ in Sheet 1 whilst editing.
I hope I’m not overstepping the mark here. I’m a novice when it comes to excel.
You help has been invaluable. Thanks again.

[mikerickson]

The file attached to post#5 draws it data (and edits) the data in the named range "CityData" which is on Sheet1.

The frmEdit takes the user chosen Suburb and allows the user to change the city and Klm that that suburb is associated with.

Since each suburb is associated with one city, a combobox control might not be appropriate.

[JackyJ]

Yes, I see what you mean mikerickson in regards to the city being unique to a suburb. I’m still having trouble with the edit Suburb function though. I think I may have not explained myself adequately. My limited knowledge with this program would not be helping with the terminology.
After entering the vba code, I found that when I select a suburb for editing, the only time the ‘City’ & ‘Klm’ text boxes are populated is when the suburb in question is also in the green table. If the suburb is not in the green table then these text boxes are not populated. If I delete all information in the green table then the ‘City’ & ‘Klm’ text boxes will not be populated when any suburb is selected in the combobox.
The green table should not figure in any adding or editing of information in the blue table. The blue table is completely independent of the green table.
My apologies if I have misled with any unclear explanations. Thanks again for your help.