ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,731
- Office Version
- 2007
- Platform
- 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
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