I want to be able to populate text boxes on a user form based on the selection made in the combo box. The code I have works with one exception. If the first item in the combo box is chosen, all the text boxes populate with the information from column D (so all the text boxes have the same information in them). All the other combo box selections fill the appropriate text boxes correctly. I'm not sure where I've gone wrong. Any help would be appreciated. Thanks.
Option Explicit
Dim strFind
Dim rSearch As Range 'range to search
Dim c As Variant
Private Sub ComboBox1_Change()
Set rSearch = Sheets("Copiers Master").Range("A2:A94")
strFind = Me.ComboBox1.Value
With rSearch
Set c = .Find(strFind, LookIn:=xlValues, LookAt:= _
xlWhole, MatchCase:=True)
If Not c Is Nothing Then 'found it
DepartmentTextBox.Value = c.Offset(0, 1).Value
VendorTextBox.Value = c.Offset(0, 2).Value
ModelTextBox.Value = c.Offset(0, 3).Value
LocationTextBox.Value = c.Offset(0, 7).Value
RenewalAmountTextBox.Value = c.Offset(0, 10).Value
Account1TextBox.Value = c.Offset(0, 11).Value
Account1PmtsTextBox.Value = c.Offset(0, 12).Value
Account2TextBox.Value = c.Offset(0, 13).Value
Account2PmtsTextBox.Value = c.Offset(0, 14).Value
Account3TextBox.Value = c.Offset(0, 15).Value
Account3PmtsTextBox.Value = c.Offset(0, 16).Value
ContactPhoneTextBox.Value = c.Offset(0, 19).Value
ContactNameTextBox.Value = c.Offset(0, 20).Value
ContactEmailTextBox.Value = c.Offset(0, 21).Value
End If
End With
End Sub
Private Sub DepartmentTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Save_Changes
End Sub
Private Sub VendorTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Save_Changes
End Sub
Private Sub ModelTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Save_Changes
End Sub
Private Sub LocationTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Save_Changes
End Sub
Private Sub RenewalAmountTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Save_Changes
End Sub
Private Sub Account1TextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Save_Changes
End Sub
Private Sub Account1PmtsTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Save_Changes
End Sub
Private Sub Account2TextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Save_Changes
End Sub
Private Sub Account2PmtsTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Save_Changes
End Sub
Private Sub Account3TextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Save_Changes
End Sub
Private Sub Account3PmtsTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Save_Changes
End Sub
Private Sub ContactPhoneTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Save_Changes
End Sub
Private Sub ContactNameTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Save_Changes
End Sub
Private Sub ContactEmailTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Save_Changes
End Sub
Private Sub Save_Changes()
Set rSearch = Sheets("Copiers Master").Range("A2:A94")
strFind = Me.ComboBox1.Value
With rSearch
Set c = .Find(strFind, LookIn:=xlValues, LookAt:= _
xlWhole, MatchCase:=True)
If Not c Is Nothing Then 'found it
c.Offset(0, 1).Value = DepartmentTextBox.Value
c.Offset(0, 2).Value = VendorTextBox.Value
c.Offset(0, 3).Value = ModelTextBox.Value
c.Offset(0, 7).Value = LocationTextBox.Value
c.Offset(0, 10).Value = RenewalAmountTextBox.Value
c.Offset(0, 11).Value = Account1TextBox.Value
c.Offset(0, 12).Value = Account1PmtsTextBox.Value
c.Offset(0, 13).Value = Account2TextBox.Value
c.Offset(0, 14).Value = Account2PmtsTextBox.Value
c.Offset(0, 15).Value = Account3TextBox.Value
c.Offset(0, 16).Value = Account3PmtsTextBox.Value
c.Offset(0, 19).Value = ContactPhoneTextBox.Value
c.Offset(0, 20).Value = ContactNameTextBox.Value
c.Offset(0, 21).Value = ContactEmailTextBox.Value
End If
End With
End Sub
Bookmarks