Userform textbox value will be save in cell

albert211994

New Member
Joined
Feb 7, 2017
Messages
10
I want to create a form which will used as encoding flatform. All data that will be encoded in the form will be save in the worksheet. In my worksheet column A, cells are already populated with data. In my form, there will be a combo box, two text box and a label. The data that will be entered in combo box is the data in column be. I want the macro to copy the values entered in both text box besides the value that match what you entered in combo box. For example, in column A I have Store1(A2),Store2(A3) and Store3(A4). If I select in my combo box Store2 and entered value in the two textbox, the data will be save in B3 for textbox1 and C3 for textboxt2.

I hope someone can help and you can understand what i want to do with my macro.

Thanks a lot in advance.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Ok here is my solution. Make one form with a combo box, two text boxes and a command button (add additional for exit if required).
Use the below code and adjust as per requirement (the names).
Code:
Private Sub cmdA_Click()
Dim i As Integer, j As Integer, rCell As Range, lastrow As Integer, rngStores As Range
i = txtA.Text
j = txtB.Text


With ThisWorkbook.Sheets("stores")
    lastrow = .Range("A65536").End(xlUp).Row
    Set rngStores = .Range("A1:A" & lastrow)
End With




For Each rCell In rngStores
    If rCell.Value = cboStore.Value Then
        rCell.Offset(0, 1) = i
        rCell.Offset(0, 2) = j
    Else
    End If
Next


End Sub


Private Sub cmdEnd_Click()
End
End Sub


Private Sub UserForm_Initialize()
Dim rCell As Range
Dim rngStores As Range
Dim lastrow As Integer


ThisWorkbook.Sheets("stores").Activate


With ThisWorkbook.Sheets("stores")
    lastrow = .Range("A65536").End(xlUp).Row
    Set rngStores = .Range("A1:A" & lastrow)
End With




'Populate combo
    For Each rCell In rngStores
            cboStore.AddItem rCell.Value
    Next rCell
    cboStore.ListIndex = 0


End Sub
 
Upvote 0
Thank you! It worked! Just want to add another text box where when what you selected in the combo box a value will be stored in the textbox. For example if I selected Store1 the value in D2 will appear in textbox3. I hope you can also help with this.
 
Upvote 0
Ok that's super simple tweak. Just add another textbox named "txtC" in your form and modify the following portion of code as given below.
Code:
For Each rCell In rngStores
    If rCell.Value = cboStore.Value Then
        rCell.Offset(0, 1) = i
        rCell.Offset(0, 2) = j
        txtC.Text = rCell.Offset(0, 3)
    Else
    End If
Next
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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