VBA - can this be done?

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
1,023
Office Version
  1. 365
Platform
  1. 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:
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:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
I have fixed it for you this time but please use code tags when posting vba code. See my signature block below for more info.
 
Upvote 0
I apologise for 'bumping' this but I'm really stuck. Can this be done? Is there a VBA expert/guru that can help, please?

Mel
 
Upvote 0
I apologise for 'bumping' this but I'm really stuck. Can this be done? Is there a VBA expert/guru that can help, please?

Mel

probably but with complex project always helpful to forum if you could put copy of your workbook in a dropbox

Dave
 
Upvote 0
Happy to do that - what's the best way, though?
 
Upvote 0
Hi,
I don't have much time to assist with your project at moment but would like to know what is the purpose of the space between each county name on your template?

Dave
 
Upvote 0
The first line (across each player’s sheet) is for data from their first innings and the second row is for data from their second and so on for each match.

Mel
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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