VBA - can this be done?

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
832
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:

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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.
 

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
832
Office Version
  1. 365
Platform
  1. Windows
Oops - thank you, Peter
 

Mel Smith

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,486
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
 

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
832
Office Version
  1. 365
Platform
  1. Windows
Happy to do that - what's the best way, though?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,486
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Happy to do that - what's the best way, though?

Use a file sharing program like Dropbox & place link to it here - include sample data with your workbook

dave
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,486
Office Version
  1. 2019
Platform
  1. Windows
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
 

Mel Smith

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

Watch MrExcel Video

Forum statistics

Threads
1,127,803
Messages
5,626,997
Members
416,214
Latest member
boston814

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