VBA UserForm: Reference Cell Value and Populate Other Cells Based on the Referenced Cell's Value

TJPotter88

New Member
Joined
Oct 8, 2014
Messages
1
Hi,

I am somewhat of a novice user when it comes to VBAs and UserForms. The UserForm I am using currently is setup with 1 combobox, 3 text fields, as well as submit and close command buttons.

The combobox references values D10:D252, and then based on the value selected, is supposed to populate 3 text fields to the right of the selected combobox value (referenced cell).

The problem I am having is that regardless of the value I select (D10:D252) from the combobox, E10, F10 and H10 are the only cells that populate. I guess I need to add a loop or something to find the specified cell value and then populate the corresponding cells.

Listed below is the code I am using (I know there are some errors), as well as the names assigned to the combobox and text fields. Any help would be greatly appreciated. Thank you in advance.

Combobox: cboPrecinct
Text Field 1: txtnoID
Text Field 2: txtOther
Text Field 3: txtNotReg

-------------------------------------
Private Sub cboPrecinct_Change()
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

-------------------------------------

Private Sub cmdSubmit_Click()
Dim ws As Worksheet
Dim Addto As Range

Set ws = Sheet2
Set Addto = ws.Range("D10, D252").End(xlUp).Offset(1, 0)

With ws
cboPrecinct.Value = Sheet2.Range("D10, D252").Value
Addto_Offset(0, 1).Value = txtNoID.Value
Addto_Offset(0, 2).Value = txtOther.Value
Addto_Offset(0, 4).Value = txtNotReg.Value
End With

cboPrecinct.Value = ""
txtNoID.Value = ""
txtOther.Value = ""
txtNotReg.Value = ""
MsgBox "Your results have been submitted. Thank you for your hard work. Click Ok to close this form."
Unload Me
End Sub

Private Sub cmdView_Click()
Unload Me
Sheet2.Select
Sheet2.Range("A1").Select
End Sub

-------------------------------------

Private Sub Frame1_Click()
End Sub

Private Sub Frame2_Click()
End Sub

Private Sub Label4_Click()
End Sub

Private Sub UserForm_Click()
End Sub

Private Sub UserForm_Initialize()
Me.cboPrecinct.SetFocus
End Sub
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Hello & welcome
Change the cmdSubmit_Click() code to this
Code:
Private Sub cmdSubmit_Click()
Dim ws As Worksheet
Dim Addto As Long

Set ws = Sheet2

Addto = cboPrecinct.ListIndex + 10
With ws
    .Range("E" & Addto) = txtNoID.Value
    .Range("F" & Addto) = txtOther.Value
    .Range("G" & Addto) = txtNotReg.Value
End With

cboPrecinct.Value = ""
txtNoID.Value = ""
txtOther.Value = ""
txtNotReg.Value = ""
MsgBox "Your results have been submitted. Thank you for your hard work. Click Ok to close this form."
Unload Me
End Sub
You haven't supplied code to how the combobox is populated, but this is what I used
Code:
Private Sub UserForm_Initialize()

    Dim ValU As Variant

    For Each ValU In Sheet2.Range("D10:D252")
        cboPrecinct.AddItem ValU
    Next ValU

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,612
Messages
5,523,896
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top