Mel Smith
Well-known Member
- Joined
- Dec 13, 2005
- Messages
- 1,023
- Office Version
- 365
- Platform
- Windows
I have designed a UserForm that I wish to use for data entry and I am hoping that it is possible to code my “Submit Data” CommandButton (CommandButton2) to do this. My form has 4 Comboboxes with dropdown lists and what I would like to do is have the input from textboxes put into the workbook dependent upon the values showing in the ComboBoxes. The input from the text boxes would go into columns B, D, E etc. The destination sheet would be dependent upon the value of ComboBox3, and the row is dependent upon the value of Combobox2 and ComboBox4. In addition, if the value of ComboBox4 is “First” a “1” is put into column C (on the relevant row as previously described). If the value of ComboBox4 is “Second” the contents of the textboxes is put in the row below in columns B, D, E etc.
For example, if the ComboBox values are: County Championship, Somerset (a), Player21, and First the textbox data would be submitted to: the sheet named “Player21”, on the row that has “Somerset (a) in (that is within a range of A4:A31). Other competition types are within separate ranges, A57:A77 etc.)
My code so far is:
Many thanks,
Mel
For example, if the ComboBox values are: County Championship, Somerset (a), Player21, and First the textbox data would be submitted to: the sheet named “Player21”, on the row that has “Somerset (a) in (that is within a range of A4:A31). Other competition types are within separate ranges, A57:A77 etc.)
My code so far is:
VBA Code:
Private Sub UserForm_Initialize()
Dim cell As Range
With ComboBox1
.AddItem "County Championship"
.AddItem "Royal London One Day Cup"
.AddItem "Vitality T20 Blast"
.AddItem "Second XI Championship"
.AddItem "Second XI 50 Over Friendly"
.AddItem "Second XI T20 (SET20)"
.AddItem "Second XI Friendlies"
End With
For Each cell In Sheets(1).Range("A4, A6, A8, A10, A12, A14, A16, A18, A20, A22, A24, A26, A28, A30")
ComboBox2.AddItem cell.Value
Next
ComboBox3.List = Sheets(9).Range("B5:B64").Value
With ComboBox4
.AddItem "First"
.AddItem "Second"
End With
End Sub
Private Sub CommandButton3_Click()
Dim z As Control
For Each z In UserForm1.Controls
If TypeName(z) = "TextBox" Then
z.Value = ""
End If
Next z
'ComboBox1.Clear
'ComboBox2.Clear
ComboBox3.Clear
ComboBox4.Clear
End Sub
Private Sub CommandButton4_Click()
Unload Me
End Sub
Many thanks,
Mel
Last edited by a moderator: