Populate other text boxes using combo box and if not found then to enter new data in combo box

[terhab]

Hello Everyone on this Forum. Greetings to you all.

How Can I use the available text value of the dynamic named range in the combo box to get the other text boxes populated?
If the text is not available in the combo box then how can I enter the new text in the combo box, upon submitting shall get included in the combo box for my next use?

Please help.
Thanks and regards

 

[Worf]

Hello

Are the controls on a worksheet or inside a UserForm?

 

[terhab]

Inside a Userform.
Thanks.

 

[Worf]

See if this example is useful:

• The text box will be populated with the combo box current value. How many text boxes do you want to fill and with what?
• The combo box is populated from a range.
• If the user manually fills the combo box with a new value, it will be added to both the range and the control list.
• Tell me what needs to be changed on the code.

Dim rng As Range, lr%


Private Sub ComboBox1_AfterUpdate()


TextBox1.Value = ComboBox1.Value
If rng.Find(ComboBox1.Value, LookIn:=xlValues) Is Nothing Then
    Set rng = rng.Resize(rng.Rows.Count + 1)
    rng.Cells(rng.Rows.Count, 1).Value = ComboBox1.Value
    FillCombo
End If


End Sub


Private Sub UserForm_Initialize()


lr = Sheets("Sheet1").Range("d" & Rows.Count).End(xlUp).Row
Set rng = Sheets("Sheet1").Range("d20:d" & lr)
FillCombo


End Sub


Sub FillCombo()
With Me.ComboBox1
    .Clear
    .List = Application.Transpose(rng)
End With
End Sub

 

[terhab]

Dear Worf,
Thank you for your reply and regret to enter late.I tried your code but got an error. I will let you know the details very soon. Thanks once again.

 

[terhab]

Hello Everyone on this Forum.
it looks like I've to rephrase my query as I found after 5 days that my above one is confusing even to me.
ok. Here it goes.( I am new to VBA and please have patience as this seems a layman's understanding)
i have a user form with ComboBox1 for "Company"entries and various text boxes for addresses viz "tel no", "fax no", "Po box", "mail I'd" etc.
For the ComboBox1 have assigned the the row source( in the properties table) to a dynamic named range called "Company".
This dynamic range named "Company" was created in excel.
I have a command button called "Submit"
Under the command button click ,the information in the comboBox1 and various text boxes do get transferred to a sheet finding the last empty row.
So far so good and now comes the tricky part.
when I again enter as a new entry for previous entered company in the comboBox1, I find there are already duplicates in the drop down list.
first of all I want the duplicates not to be seen,only in the drop down list ( but remain in the transferred sheet)
Secondly since the previous entered company has all the rest of information such as "tel no", "fax no", "Po box", "mail I'd" etc. I do not want to enter the same information again but get the respective text boxes populated so that when I finish with the whole user form and press the "Submit" button, the information get transferred to the sheet.
please note that every new use of user form has some new details every time. Such as date, inquiry number, project details , region etc.

 

[Worf]

Hello</SPAN>
The following example uses this structure:</SPAN>

• Columns H through K are for Company name, Phone, Fax and PO Box data respectively. First row will be for headers.</SPAN>
• Column M will contain a list of unique Company names.</SPAN>
• The command button transfers data to columns H-K</SPAN>
• Upon updating the combo box, the text boxes will be populated with data from H-K.</SPAN>
• Please test it and tell me what needs to be changed.

</SPAN>

Dim Company As Range, lrh%, ws As Worksheet
Private Sub CommandButton1_Click()
' transfer data from UF to sheet
lrh = ws.Range("h" & Rows.Count).End(xlUp).Row + 1
ws.Range("h" & lrh).Value = Me.ComboBox1.Value
ws.Range("i" & lrh).Value = Me.TextBox1.Value
ws.Range("j" & lrh).Value = Me.TextBox2.Value
ws.Range("k" & lrh).Value = Me.TextBox3.Value
End Sub
Private Sub UserForm_Initialize()
Set ws = Sheets("Sheet1")
Range("m:m").ClearContents
lrh = ws.Range("h" & Rows.Count).End(xlUp).Row
ws.Range("H1:H" & lrh).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("M1"), Unique:=True
Set Company = ws.Range("m2:m" & ws.Range("m" & Rows.Count).End(xlUp).Row)
FillCombo
End Sub
Sub FillCombo()
With Me.ComboBox1
    .Clear
    .List = Application.Transpose(Company)
End With
End Sub
Private Sub ComboBox1_AfterUpdate()
Dim c As Range
lrh = ws.Range("h" & Rows.Count).End(xlUp).Row
Set c = ws.Range("h2:h" & lrh).Find(Me.ComboBox1.Value, LookIn:=xlValues)
If Not c Is Nothing Then
    Me.TextBox1.Value = c.Offset(, 1).Value     ' Phone #
    Me.TextBox2.Value = c.Offset(, 2).Value     ' Fax #
    Me.TextBox3.Value = c.Offset(, 3).Value     ' PO Box
Else
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
End If
        
End Sub

 

[terhab]

Dear Worf,
Hello
Deeply regret for delayed reply as I was out of the country for a few days.
Thank you so much. Your code worked like magic.
I had to make a small change. Well! not exactly change but to add a "update combobox command button" to ,transfer(update) the company to M column or retrieve the companies from the M column to the combobox, which is the unique company names. as per the code I think this is done by initializing the userform. So, I tried to insert the "call userform_initialize" in the CommandButton1_Click but had a problem.
So I inserted the "call userform_initialize" under "update combobox command button".

Thank you very much for the code.
This forum is great.
The addition is in the following

Dim Company As Range, lrh%, ws As Worksheet
Private Sub cmndok_Click()
Set ws = Sheets("Sheet8")
' transfer data from UF to sheet
lrh = ws.Range("h" & Rows.Count).End(xlUp).Row + 1
ws.Range("h" & lrh).Value = Me.ComboBox1.Value
ws.Range("i" & lrh).Value = Me.TextBoxTel.Value
ws.Range("j" & lrh).Value = Me.TextBoxFax.Value
ws.Range("k" & lrh).Value = Me.TextBoxPOBox.Value
End Sub

Private Sub usfrprojectdetails_Initialize()
Set ws = Sheets("Sheet8")
Range("m:m").ClearContents
lrh = ws.Range("h" & Rows.Count).End(xlUp).Row
ws.Range("H1:H" & lrh).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("M1"), Unique:=True
Set Company = ws.Range("m2:m" & ws.Range("m" & Rows.Count).End(xlUp).Row)
FillCombo
End Sub

Sub FillCombo()
With Me.ComboBox1
    .clear
    .List = Application.Transpose(Company)
End With
End Sub

Private Sub ComboBox1_AfterUpdate()
Set ws = Sheets("Sheet8")
Dim c As Range
lrh = ws.Range("h" & Rows.Count).End(xlUp).Row
Set c = ws.Range("h2:h" & lrh).Find(Me.ComboBox1.Value, LookIn:=xlValues)
If Not c Is Nothing Then
    Me.TextBoxTel.Value = c.Offset(, 1).Value     ' Phone #
    Me.TextBoxFax.Value = c.Offset(, 2).Value     ' Fax #
    Me.TextBoxPOBox.Value = c.Offset(, 3).Value     ' PO Box
Else
    Me.TextBoxTel.Value = ""
    Me.TextBoxFax.Value = ""
    Me.TextBoxPOBox.Value = ""
End If
End Sub
  'THIS IS THE OTHER ADDITIONAL COMMAND BUTTON
Private Sub updatecombobox_Click()   
Call usfrprojectdetails_Initialize
End Sub

WHAT I WANTED WAS IS IN THE SECOND LINE OF THE CODE BUT IT DOES NOT WORK AS THERE IS A PROBLEM IN " .List = Application.Transpose(Company)" WHICH IS IN SUBFILL COMBOBOX.

Private Sub cmndok_Click()
Call usfrprojectdetails_Initialize
Set ws = Sheets("Sheet8")
' transfer data from UF to sheet
lrh = ws.Range("h" & Rows.Count).End(xlUp).Row + 1
ws.Range("h" & lrh).Value = Me.ComboBox1.Value
ws.Range("i" & lrh).Value = Me.TextBoxTel.Value
ws.Range("j" & lrh).Value = Me.TextBoxFax.Value
ws.Range("k" & lrh).Value = Me.TextBoxPOBox.Value
End Sub

However, My main problem is solved and I can go further in my VBA design for the quotation and workings. Thank you once again. You've been a great trouble shooter.

 

[Worf]

Hello
You are welcome. Please test this new version:

Dim Company As Range, lrh%, ws As Worksheet


Private Sub cmndok_Click()
    ' transfer data from UF to sheet
    lrh = ws.Range("h" & Rows.Count).End(xlUp).Row + 1
    ws.Range("h" & lrh).Value = Me.ComboBox1.Value
    ws.Range("i" & lrh).Value = Me.TextBoxTel.Value
    ws.Range("j" & lrh).Value = Me.TextBoxFax.Value
    ws.Range("k" & lrh).Value = Me.TextBoxPOBox.Value
End Sub


Private Sub usfrprojectdetails_Initialize() ' not event driven
    Range("m:m").ClearContents
    lrh = ws.Range("h" & Rows.Count).End(xlUp).Row
    ws.Range("H1:H" & lrh).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=ws.Range("M1"), Unique:=True
    Set Company = ws.Range("m2:m" & ws.Range("m" & Rows.Count).End(xlUp).Row)
    FillCombo
End Sub


Private Sub UserForm_Initialize()   ' event driven
    Set ws = Sheets("Sheet8")
    ws.Range("h1").Value = "Company Name" ' make sure there is a header
    usfrprojectdetails_Initialize
End Sub


Sub FillCombo()
    With Me.ComboBox1
        .Clear
        .List = Application.Transpose(Company)
    End With
End Sub


Private Sub ComboBox1_AfterUpdate()
    Dim c As Range
    lrh = ws.Range("h" & Rows.Count).End(xlUp).Row
    Set c = ws.Range("h2:h" & lrh).Find(Me.ComboBox1.Value, LookIn:=xlValues)
    If Not c Is Nothing Then
        Me.TextBoxTel.Value = c.Offset(, 1).Value     ' Phone #
        Me.TextBoxFax.Value = c.Offset(, 2).Value     ' Fax #
        Me.TextBoxPOBox.Value = c.Offset(, 3).Value     ' PO Box
    Else
        Me.TextBoxTel.Value = ""
        Me.TextBoxFax.Value = ""
        Me.TextBoxPOBox.Value = ""
    End If
End Sub
  'THIS IS THE OTHER ADDITIONAL COMMAND BUTTON
Private Sub updatecombobox_Click()
    usfrprojectdetails_Initialize
End Sub