时间戳

首页 > 解决方案 > VBA用户表单：复制输入数据并根据月份将它们粘贴到不同的工作表中

vba - VBA用户表单：复制输入数据并根据月份将它们粘贴到不同的工作表中

问题描述

在阅读了多个论坛并尝试了很多次之后，我仍然无法做到这一点。

我是 VBA 新手，所以请忍受丑陋的代码。非常欢迎任何改进代码的建议

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

因此，调查参与者的输入将像这样存储：

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

但我不想要这份清单。

  1. 我希望 VBA 根据它们各自的月份（即一月、二月、三月、四月......）将这些记录发送到不同的工作表。

  2. 总结第三列最后一项下方的总支出。

  3. 如果有更多选择，则以下部分不实用...对于这项工作，我很幸运只有四月六月九月和十一月...非常欢迎任何改进这条线的建议..

    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

非常感谢您的建议！

标签： vbaexcel

解决方案

首先，我建议重命名您的 TextBoxes 和 ComboBoxes。 TextBox1是一个非常糟糕的名字，因为没有人知道这意味着什么。最好使用类似的东西txtInputDay，并且cmbInputMonth更容易阅读/理解和维护。

如果您检查日期是否像您一样有效，您需要编写很多代码，并且它不反映闰年，2 月只有 29 天而不是 28 天。我建议让用户输入整个日期，然后使用IsDate 函数检查它是否是有效日期。

如果您仍然喜欢自己测试，我建议将所有这些消息减少为“您输入的日期不是有效日期”之类的消息。这比为所有内容（为空、为数字、介于 1 和 28/29/30/31 之间）提供单独的消息要容易得多。

例如

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

如果您希望每个数据在不同的工作表中，您可以访问工作表，例如

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

推荐阅读