Textbox details should be removed if combobox selection is overwritten

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,731
Office Version
  1. 2007
Platform
  1. Windows
Morning,
I have a userform which has a combobo & textboxes.
The combobox hold customers names in the drop down.
Once a customer has been selected the textboxes are preffiled with address,tel number, post code etc.

One of the customers is Barbara & in thic case i am typing a new name the the combobox field of which is Barney.
The code picks up Barbara and i am able to continue to type Barney.
My problem is that now i see the details in the textboxes for Barbara.
I was expecting that as i overtyped Barbara the address, post code etc would have removed itself but this is not the case.

The code in use is shown below, do you see how i can fix this or where my mistkae is please

Rich (BB code):
    Private Sub CommandButton1_Click()
    Dim Lastrow        As Long, i As Long
    Dim wsGIncome      As Worksheet
    Dim arr(1 To 5)    As Variant
    Dim Prompt         As String
   
    Set wsGIncome = ThisWorkbook.Worksheets("G INCOME")
   
    For i = 1 To UBound(arr)
        arr(i) = Choose(i, ComboBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value)
       
        If Len(arr(i)) = 0 Then
            MsgBox "YOU MUST COMPLETE ALL THE FIELDS", vbCritical, "USERFORM FIELDS EMPTY MESSAGE"
            Exit Sub
        End If
    Next i
   
    Application.ScreenUpdating = False
   
    With wsGIncome
        Lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row + 1
       
       With .Cells(Lastrow, 14).Resize(, UBound(arr))
            .Value = arr
            .Font.Name = "Calibri"
            .Font.Size = 11
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Borders.Weight = xlThin
            .Interior.ColorIndex = 6
           
            .Cells(1, 1).HorizontalAlignment = xlLeft
            Application.ErrorCheckingOptions.BackgroundChecking = False
       End With
           .Range("N4").Select
       
    End With
   
    Unload Me
    Application.ScreenUpdating = True
   
    With ActiveSheet.Sort
        With .SortFields
            .Clear
            .Add Key:=Range("N3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End With
        .SetRange Range("N3").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"
   
End Sub
   
   Private Sub ComboBox1_Click()
   Me.TextBox2.Value = Sheets("G INCOME").Range("V" & Me.ComboBox1.ListIndex + 4).Value
   Me.TextBox3.Value = Sheets("G INCOME").Range("W" & Me.ComboBox1.ListIndex + 4).Value
   Me.TextBox5.Value = Sheets("G INCOME").Range("X" & Me.ComboBox1.ListIndex + 4).Value
   End Sub
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
OLD:

VBA Code:
Private Sub ComboBox1_Click()
   Me.TextBox2.Value = Sheets("G INCOME").Range("V" & Me.ComboBox1.ListIndex + 4).Value
   Me.TextBox3.Value = Sheets("G INCOME").Range("W" & Me.ComboBox1.ListIndex + 4).Value
   Me.TextBox5.Value = Sheets("G INCOME").Range("X" & Me.ComboBox1.ListIndex + 4).Value
   End Sub

NEW:

VBA Code:
Private Sub ComboBox1_Change()
   If ComboBox1.MatchFound = False Then Me.TextBox2.Value="":Me.TextBox3.Value="":Me.TextBox5.Value="":Exit Sub
   Me.TextBox2.Value = Sheets("G INCOME").Range("V" & Me.ComboBox1.ListIndex + 4).Value
   Me.TextBox3.Value = Sheets("G INCOME").Range("W" & Me.ComboBox1.ListIndex + 4).Value
   Me.TextBox5.Value = Sheets("G INCOME").Range("X" & Me.ComboBox1.ListIndex + 4).Value
End Sub
 
Upvote 0
Hi,
The same happens.
See atached screenshots.

I start to type BAR & the textboxes are prefiled with BARBARA details BUT i continue to type BARNEY and details still shown
 

Attachments

  • EaseUS_2023_07_ 1_12_00_34.jpg
    EaseUS_2023_07_ 1_12_00_34.jpg
    23.4 KB · Views: 10
  • EaseUS_2023_07_ 1_12_00_47.jpg
    EaseUS_2023_07_ 1_12_00_47.jpg
    21.6 KB · Views: 17
Upvote 0
Replace Private Sub ComboBox1_Click() with this:
VBA Code:
Private Sub ComboBox1_Change()

If Me.ComboBox1.ListIndex > -1 Then
   Me.TextBox2.Value = Sheets("G INCOME").Range("V" & Me.ComboBox1.ListIndex + 4).Value
   Me.TextBox3.Value = Sheets("G INCOME").Range("W" & Me.ComboBox1.ListIndex + 4).Value
   Me.TextBox5.Value = Sheets("G INCOME").Range("X" & Me.ComboBox1.ListIndex + 4).Value
Else
   Me.TextBox2.Value = ""
   Me.TextBox3.Value = ""
   Me.TextBox5.Value = ""
End If

End Sub
 
Upvote 0
Solution
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0
the solution i provided works for me. I use it all the time. not sure why it does not work for you
Actually I think your code should work too, so I don't know why the OP said it didn't work.
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top