Thread: Link text Box to Combobox in Userform and autoupdate database

Hi,

I am pretty new to VBA programming. I am new to programming of any kind all together. However, I am required to prepare an excel userform in which a user can select a value from the drop down combobox and the text box is linked to the corresponding column. The combobox gets its value from a database in the same sheet. Further if the value is not present, user can type the value in both the boxes. I have tried to do it myself, seeking help from various forums but m not able to do it.

Please Help me with this.

P.S. would it be possible to add the value in the data base automatically, if it is typed by the user.

Private Sub cmdclose_Click()
Unload Me
End Sub

        Private Sub cmdAdd_Click()
        Dim lRow As Long
        Dim lmeter As Long
        Dim ws As Worksheet
        Set ws = Worksheets("sheet1")
        lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        lmeter = Me.cmboMeter.ListIndex
        
        If Trim(Me.cmboMeter.Value) = "" Then
        Me.cmboMeter.SetFocus
        MsgBox "please eneter a value"
        End If
        Exit Sub
        
        If Trim(Me.cmboMeter.Value) = "other.." Then
        Me.txtRef_1.SetFocus
        MsgBox "please enter a reference number"
        Exit Sub
        End If
        
         With ws
         .Cells(lRow, 1).Value = Me.cmboMeter.Value
         .Cells(lRow, 2).Value = Me.txtRef_1.Value
         .Cells(lRow, 3).Value = Me.units.Value
         End With
         Me.cmboMeter.Value = ""
         Me.txtRef_1.Value = ""
         Me.units.Value = ""
         Me.cmboMeter.SetFocus
        End Sub





Private Sub UserForm_Initialize()
Dim cmeter As Range
Dim ws As Worksheet
Set ws = Worksheets("sheet4")
Me.MultiPage1.Value = 0


For Each cmeter In ws.Range("meter1")
With Me.cmboMeter
.AddItem cmeter.Value
.List(.ListCount - 1, 1) = cmeter.Offset(0, 1).Value
End With
Next cmeter


End Sub

Private Sub txtRef_1_AfterUpdate()

Me![txtRef_1] = Me!cmboMeter.Column(1)
End Sub

Thanks.

hi abhiD,
as an option try this and see if it helps you!

Private Sub cmboMeter_Change()
Dim myName As String, myRange As Range
   
   myName = Me.cmboMeter.Text
   Set myRange = ThisWorkbook.Sheets("sheet4").Range("a:a")
   Set found = myRange.Find(myName, LookIn:=xlValues)
   If Not found Is Nothing Then
      Me.txtRef_1 = found.Offset(, 1)
      Me.units = found.Offset(, 2)
   Else
      Me.txtRef_1 = ""
      Me.units = ""
   End If
End Sub

Private Sub cmdAdd_Click()
Application.ScreenUpdating = False
     
'switch off screen updating to speed up code and prevent screen flickering
  
    
    Dim NextRw As Long
    With Sheets("Sheet4")
        'find next empty row using Column A
        NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
Dim Wks As Worksheet
    
        Set Wks = ActiveSheet

        'input the textbox entries
        .Cells(NextRw, 1).Value = Me.cmboMeter.Text
        .Cells(NextRw, 2).Value = Me.txtRef_1.Value
        .Cells(NextRw, 3).Value = Me.units.Value
        End With
        
         Me.cmboMeter.Value = ""
         Me.txtRef_1.Value = ""
         Me.units.Value = ""
         Me.cmboMeter.SetFocus
         Application.ScreenUpdating = True

        End Sub

Private Sub UserForm_Initialize()
Dim rList As Range
        With Sheets("Sheet4")
        Set rList = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
     Me.cmboMeter.List = rList.Value
   
End Sub

hi abhiD,
thank you for the rep! hope it helped you!
If your problem is solved, please say so clearly, and mark your thread as Solved:
Click the Edit button on your first post in the thread,
Click Go Advanced, select [SOLVED] from the Prefix dropdown, then
click Save Changes.

abhid, did you try to add something (what you did not find in yr combobox) and then click ok button?!
I tested it with "123", which is not listed, and clicked ok, "123" and the "aaa", "bbb" (for both textboxes) are sent to yr sheet!
or...did I miss something?

@john

Ya.. i checked it again, its working, i was trying to be overtly smart by using a check box..!:P
thanks for the help man..!

and i am not able to edit the first post hence cannot make it solved. I dunno why is that happening. Will mail the forum regulators regarding this..!
thanks newes.

you're welcome!