VBA userform: copy the input data and paste them in different worksheet based on month

Question

After reading multiple forums and tried many times I still couldn't achieve this.

I'm new to VBA, so please bear with the ugly code. Any advice to improve the code is very much welcome

Private Sub CommandButton1_Click()
''''''validation''''''

Me.ComboBox1.Style = 2

If Me.TextBox1.Value = "" And Me.TextBox2.Value = "" And Me.TextBox3.Value = "" Then
    MsgBox "You're entering an empty form."
    Exit Sub
End If

If Me.TextBox1.Value = "" Then
    MsgBox "Don't forget to enter the day of the month.", vbCritical
    Exit Sub
End If

If Me.TextBox1.Value > 31 Then
    MsgBox "Please enter the correct date", vbCritical
    Exit Sub
End If

If Me.TextBox1.Value > 29 And Me.ComboBox1.Value = "February" Then
    MsgBox "there's no 30 or 31 days in February", vbCritical
    Exit Sub
End If

If Me.TextBox1.Value = 31 And Me.ComboBox1.Value = "April" Then
    MsgBox "there is only 30 days in this month", vbCritical
    Exit Sub
End If

If Me.TextBox1.Value = 31 And Me.ComboBox1.Value = "June" Then
    MsgBox "there is only 30 days in this month", vbCritical
    Exit Sub
End If

If Me.TextBox1.Value = 31 And Me.ComboBox1.Value = "September" Then
    MsgBox "there is only 30 days in this month", vbCritical
    Exit Sub
End If

If Me.TextBox1.Value = 31 And Me.ComboBox1.Value = "November" Then
    MsgBox "there is only 30 days in this month", vbCritical
    Exit Sub
End If

If VBA.IsNumeric(Me.TextBox1.Value) = False Then
    MsgBox "Please enter a number for date.", vbCritical
    Exit Sub
End If

If Me.ComboBox1.Value = "" Then
    MsgBox "Don't forget the month.", vbCritical
    Exit Sub
End If

If Me.TextBox2.Value = "" Then
    MsgBox "Don't forget to enter the amount of money you've spent.", vbCritical
    Exit Sub
End If


'''set that the sheet will find the next empty row'''
n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row
'''set that the sheet will find the next empty row'''


sh.Range("A" & n + 1).Value = Me.TextBox1 & Me.ComboBox1 & Me.Label3
If Me.OptionButton1.Value = True Then sh.Range("b" & n + 1).Value = "Cash"
If Me.OptionButton2.Value = True Then sh.Range("b" & n + 1).Value = "Card"
sh.Range("c" & n + 1).Value = Me.TextBox2
sh.Range("d" & n + 1).Value = Me.TextBox3

''''clear the box after submission'''''
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.ComboBox1.Value = ""
Me.OptionButton1.Value = False
Me.OptionButton2.Value = False
MsgBox "Thank You!"
''''clear the box after submission'''''


End Sub

Private Sub CommandButton2_Click()

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.ComboBox1.Value = ""
Me.OptionButton1.Value = False
Me.OptionButton2.Value = False


End Sub

Private Sub UserForm_Activate()
With Me.ComboBox1
     .Clear
     .AddItem ""
     .AddItem "January"
     .AddItem "February"
     .AddItem "March"
     .AddItem "April"
     .AddItem "May"
     .AddItem "June"
     .AddItem "July"
     .AddItem "August"
     .AddItem "September"
     .AddItem "October"
     .AddItem "November"
     .AddItem "December"
     End With

End Sub

So the input by survey participants will be stored like this:

12-Feb-2018    Cash    12.99    Food
13-Feb-2018    Cash     4.95    Train
14-Feb-2018    Card    19.99    Movie
14-Feb-2018    Cash    36.95    Clothes
1-Mar-2018     Cash    18.99    Grocery
29-Mar-2018    Cash    20.00    Petrol
2-Apr-2018     Card    49.99    Hardware

But i do not want this list.

I want the VBA to send these records to different worksheets based on their respective months i.e. January, February, March, April....
Sum up the total spending beneath the last entry of the 3rd column.
the following part is not practical if there are a lot more options... for this work I'm lucky there are only April June September and November...any suggestion to improve this line is very much welcome..
```
If Me.TextBox1.Value = 31 And Me.ComboBox1.Value = "April" Then
    MsgBox "there is only 30 days in this month", vbCritical
    Exit Sub
End If
```

Thanks so much for your advice!

Pᴇʜ Pᴇʜ · Accepted Answer · 2018-06-08T12:00:28

First of all I recommend to rename your TextBoxes and ComboBoxes. TextBox1 is a very bad name because no one knows what this means. Better to use something like txtInputDay and cmbInputMonth which is much easier to read/understand and maintain.

If you check the date if it is valid like you did you need to write much code and and it does not reflect leap years where February only has 29 days vs. 28 days. I recommend to let the user input the whole date and then check it with the IsDate Function if it is a valid date or not.

If you still prefer to test it yourself, I recommend to reduce all these messages to one like "The date you entered is no valid date". This is much easier then to have a individual message for everything (is empty, is a number, is between 1 and 28/29/30/31).

For example

Dim InputDay As Long
InputDay = Val(Me.TextBox1.Value)

Dim InputMonth As String
InputMonth = Me.ComboBox1.Value

Dim MaxDaysInMonth As Long
'get the max days in the selected month
Select Case InputMonth
    Case "April", "June", "September", "November" 'Month with only 30 days
        MaxDaysInMonth = 30

    Case "February" '28 days or 29 on leap years
        If Month(DateSerial(Year(Date), 2, 29)) = 2 Then 'is it a leap year
            MaxDaysInMonth = 29
        Else
            MaxDaysInMonth = 28
        End If

    Case Else 'all other months have 31 days
        MaxDaysInMonth = 31
End Select

'check if input exceeds max days of month
If InputDay < 1 Or InputDay > MaxDaysInMonth Then
    MsgBox "Date is not valid"
    Exit Sub
End If

If you want each data in a different sheet you can access sheets like

Dim MonthSheet as Worksheet
Set MonthSheet = Worksheets(InputMonth) 'take name of the selected month as sheet name

Dim n As Long
n = MonthSheet.Range("A" & MonthSheet.Rows.Count).End(xlUp).Row 'find last used row in month sheet

MonthSheet.Range("A" & n + 1).Value = … 'fill in some data

VBA userform: copy the input data and paste them in different worksheet based on month

1 Answers