User Form, Combo Box, Moving Entries to spreadsheet

keenbutlean

New Member
Joined
May 18, 2011
Messages
1
G'day,

Could someone please assist an amateur in the transfer of data entered on a user form so that it appears on a spreadsheet.

I have created a user form with 4 text boxes labelled as: "Job Number"; "Customer Name"; "Suburb" and "State/Country. The next two entries are combo boxes labelled: "Engineer Name (cboengineer)" and "Engineer Code (cboFSR)".

I have created a worksheet named "LookupLists" with the data for the two combo boxes and I also have a "cmdadd" and a "cmdexit" button on the user form for transfering the entries on the form back to a worksheet named "formdata" and then to close the form. By the way, am I able to combine these two functions in to one button whereby the data is traansferred and the form closed at the same time?

The entries made on the user form from the textboxes transfer to the worksheet. However the entries from the combo boxes do not. Obviously this is because they haven't been told where to go. Could someone please advise how I add this function.

Please find below code for the user form:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("formdata")

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

If Trim(Me.txtjobnumber.Value) = "" Then
Me.txtjobnumber.SetFocus
MsgBox "Please enter Customer Details"
Exit Sub
End If

ws.Cells(iRow, 1).Value = Me.txtjobnumber.Value
ws.Cells(iRow, 2).Value = Me.txtcustomername.Value
ws.Cells(iRow, 3).Value = Me.txtsuburb.Value
ws.Cells(iRow, 4).Value = Me.txtstate.Value

Me.txtjobnumber.Value = ""
Me.txtcustomername.Value = ""
Me.txtsuburb.Value = ""
Me.txtstate.Value = ""
Me.txtjobnumber.SetFocus


End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub


Private Sub UserForm_Initialize()
Dim cEng As Range
Dim cFSR As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cEng In ws.Range("EngList")
With Me.cboengineer
.AddItem cEng.Value
End With
Next cEng

For Each cFSR In ws.Range("FSRList")
With Me.cboFSR
.AddItem cFSR.Value
End With
Next cFSR

Me.cboengineer.SetFocus

End Sub
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

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
Top