VBA 表单接受输入并保存到 Excel 工作表

VBA Form takes input and saves to Excel worksheet

提问by Erica

Getting errors when I run a user form in VBA. Created a user form in VBA to take input for multiple test variables. Would like to have user click 'CommandBUtton2' button and have data saved to a worksheet in Excel. Need to be able to clear form after saving data in order to continue updating worksheet with new test data.
UserForm1 receives input and has a button that switches to UserForm2. UserForm2 takes input and has a button to submit (which should save inputted data into excel worksheet..it should lol) Module1 code:

在 VBA 中运行用户表单时出现错误。在 VBA 中创建了一个用户表单以获取多个测试变量的输入。希望用户单击“CommandBUtton2”按钮并将数据保存到 Excel 中的工作表中。需要能够在保存数据后清除表单，以便使用新的测试数据继续更新工作表。
UserForm1 接收输入并有一个切换到 UserForm2 的按钮。UserForm2 接受输入并有一个提交按钮（它应该将输入的数据保存到 Excel 工作表中......它应该哈哈）模块 1 代码：

' Code behind Module1 to store public values
Public myText1 As String
Public myText2 As String
Public myText3 As String
Public myText4 As String
Public myText5 As String
Public myText6 As String
Public myText7 As String
Public myText8 As String
Public myText9 As String
Public myText10 As String
Public myText11 As String
Public myText12 As String
Public myText13 As String
Public myText14 As String
Public myText15 As String
Public myText16 As String
Public myText17 As String
Public myText18 As String
Public myText19 As String
Public myText20 As String
Public myText21 As String
Public myText22 As String
Public myText23 As String
Public myText24 As String
Public myText25 As String
Public myText26 As String
Public myText27 As String
Public myText28 As String
Public myText29 As String
Public myText30 As String
Public myText31 As String
Public myText32 As String
Public myText33 As String
Public myText34 As String
Public myText35 As String
Public myText36 As String
Public myText37 As String
Public myText38 As String
Public myText39 As String
Public myText40 As String
Public myText41 As String
Public myText42 As String
Public myText43 As String
Public myText44 As String
Public myText45 As String
Public myText46 As String
Public myText47 As String
Public myText48 As String
Public myText49 As String
Public myText50 As String
Public myText51 As String
Public myText52 As String
Public myText53 As String
Public myText54 As String
Public myText55 As String
Public myText56 As String
Public myText57 As String
Public myText58 As String
Public myText59 As String
Public myText60 As String
Public myText61 As String
Public myText62 As String
Public myText63 As String
Public myText64 As String
Public myText65 As String
Public myText66 As String
Public myText67 As String
Public myText68 As String
Public myText69 As String
Public myText70 As String
Public AdditionalNotes As String

' Code behind Module1 to actually use the values
Public Sub PrintVals()
   'Assign a macro to the OK button
   Dim emptyRow As Long
  'Make Sheet2 Active
   Sheets(2).Activate
   'Determine emptyRow
   emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
  'Export Data to worksheet
  Cells(emptyRow, 1).Value = myText1
  Cells(emptyRow, 2).Value = myText2
  Cells(emptyRow, 3).Value = myText3
Cells(emptyRow, 4).Value = myText4
Cells(emptyRow, 5).Value = myText5
Cells(emptyRow, 6).Value = myText6
Cells(emptyRow, 7).Value = myText7
Cells(emptyRow, 8).Value = myText8
Cells(emptyRow, 9).Value = myText9
Cells(emptyRow, 10).Value = myText10
Cells(emptyRow, 11).Value = myText11
Cells(emptyRow, 12).Value = myText12
Cells(emptyRow, 13).Value = myText13
Cells(emptyRow, 14).Value = myText14
Cells(emptyRow, 15).Value = myText15
Cells(emptyRow, 16).Value = myText16
Cells(emptyRow, 17).Value = myText60 
Cells(emptyRow, 18).Value = myText17
Cells(emptyRow, 19).Value = myText61 
Cells(emptyRow, 20).Value = myText18
Cells(emptyRow, 21).Value = myText62 
Cells(emptyRow, 22).Value = myText19
Cells(emptyRow, 23).Value = myText20
Cells(emptyRow, 24).Value = myText21
Cells(emptyRow, 25).Value = myText22
Cells(emptyRow, 26).Value = myText23
Cells(emptyRow, 27).Value = myText24
Cells(emptyRow, 28).Value = myText25
Cells(emptyRow, 29).Value = myText63 
Cells(emptyRow, 30).Value = myText26
Cells(emptyRow, 31).Value = myText64 
Cells(emptyRow, 32).Value = myText27
Cells(emptyRow, 33).Value = myText65 
Cells(emptyRow, 34).Value = myText28
Cells(emptyRow, 35).Value = myText29
Cells(emptyRow, 36).Value = myText30
Cells(emptyRow, 37).Value = myText31
Cells(emptyRow, 38).Value = myText32
Cells(emptyRow, 39).Value = myText33
Cells(emptyRow, 40).Value = myText34
Cells(emptyRow, 41).Value = myText66 
Cells(emptyRow, 42).Value = myText35
Cells(emptyRow, 43).Value = myText67 
Cells(emptyRow, 44).Value = myText36
Cells(emptyRow, 45).Value = myText37
Cells(emptyRow, 46).Value = myText38
Cells(emptyRow, 47).Value = myText68 
Cells(emptyRow, 48).Value = myText39
Cells(emptyRow, 49).Value = myText40
Cells(emptyRow, 50).Value = myText41
Cells(emptyRow, 51).Value = myText42
Cells(emptyRow, 52).Value = myText43
Cells(emptyRow, 53).Value = myText44
Cells(emptyRow, 54).Value = myText45
Cells(emptyRow, 55).Value = myText46
Cells(emptyRow, 56).Value = myText47
Cells(emptyRow, 57).Value = myText48
Cells(emptyRow, 58).Value = myText49
Cells(emptyRow, 59).Value = myText50
Cells(emptyRow, 60).Value = myText51
Cells(emptyRow, 61).Value = myText52
Cells(emptyRow, 62).Value = myText53
Cells(emptyRow, 63).Value = myText69 
Cells(emptyRow, 64).Value = myText54
Cells(emptyRow, 65).Value = myText55
Cells(emptyRow, 66).Value = myText56
Cells(emptyRow, 67).Value = myText57
Cells(emptyRow, 68).Value = myText70 
Cells(emptyRow, 69).Value = myText58
Cells(emptyRow, 70).Value = AdditionalNotes

End Sub

结束子

' Code behind Form1 Initialize text boxes
Private Sub UserForm1_Initialize(UserForm1)
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
TextBox9.Value = ""
TextBox10.Value = ""
TextBox11.Value = ""
TextBox12.Value = ""
TextBox13.Value = ""
TextBox14.Value = ""
TextBox15.Value = ""
TextBox16.Value = ""
TextBox17.Value = ""
TextBox18.Value = ""
TextBox19.Value = ""
TextBox20.Value = ""
TextBox21.Value = ""
TextBox22.Value = ""
TextBox23.Value = ""
TextBox24.Value = ""
TextBox25.Value = ""
TextBox26.Value = ""
TextBox27.Value = ""
TextBox28.Value = ""
TextBox29.Value = ""
TextBox30.Value = ""
TextBox31.Value = ""
TextBox33.Value = ""
TextBox34.Value = ""
TextBox35.Value = ""
TextBox36.Value = ""
TextBox37.Value = ""
TextBox38.Value = ""
TextBox39.Value = ""
TextBox40.Value = ""
TextBox41.Value = ""
TextBox42.Value = ""
TextBox43.Value = ""
TextBox44.Value = ""
TextBox45.Value = ""
TextBox46.Value = ""
TextBox47.Value = ""
TextBox48.Value = ""
TextBox49.Value = ""
TextBox50.Value = ""
TextBox51.Value = ""
TextBox52.Value = ""
TextBox53.Value = ""
TextBox54.Value = ""
TextBox55.Value = ""
TextBox56.Value = ""
TextBox57.Value = ""
TextBox58.Value = ""
TextBox59.Value = ""
'Good values
TextBox60.Value = "14" 
TextBox61.Value = "Responds" 
TextBox62.Value = "00 00 00 00 00 00 00 00" 
TextBox63.Value = "< 0.005"
TextBox64.Value = "4.5" 
TextBox65.Value = "2" 
TextBox66.Value = "100" 
TextBox67.Value = "11-16" 
TextBox68.Value = "5" 
TextBox69.Value = "6" 
TextBox70.Value = "10-11" 
End Sub

 Private Sub UserForm2_Initialize(UserForm2)
'Empty Additional Notes
TextBoxAdditionalNotes.Value = ""
End Sub

' Code behind Form1 Set variables on module
Private Sub CommandButton1_Click(UserForm1)
Module1.myText1 = TextBox1.Value
Module1.myText2 = TextBox2.Value
Module1.myText3 = TextBox3.Value
Module1.myText4 = TextBox4.Value
Module1.myText5 = TextBox5.Value
Module1.myText6 = TextBox6.Value
Module1.myText7 = TextBox7.Value
Module1.myText8 = TextBox8.Value
Module1.myText9 = TextBox9.Value
Module1.myText10 = TextBox10.Value
Module1.myText11 = TextBox11.Value
Module1.myText12 = TextBox12.Value
Module1.myText13 = TextBox13.Value
Module1.myText14 = TextBox14.Value
Module1.myText15 = TextBox15.Value
Module1.myText16 = TextBox16.Value
Module1.myText17 = TextBox17.Value
Module1.myText18 = TextBox18.Value
Module1.myText19 = TextBox19.Value
Module1.myText20 = TextBox20.Value
Module1.myText21 = TextBox21.Value
Module1.myText22 = TextBox22.Value
Module1.myText23 = TextBox23.Value
Module1.myText24 = TextBox24.Value
Module1.myText25 = TextBox25.Value
Module1.myText26 = TextBox26.Value
Module1.myText27 = TextBox27.Value
Module1.myText28 = TextBox28.Value
Module1.myText29 = TextBox29.Value
Module1.myText30 = TextBox30.Value
Module1.myText31 = TextBox31.Value
Module1.myText32 = TextBox32.Value
Module1.myText33 = TextBox33.Value
Module1.myText34 = TextBox34.Value
Module1.myText35 = TextBox35.Value
Module1.myText36 = TextBox36.Value
Module1.myText37 = TextBox37.Value
Module1.myText38 = TextBox38.Value
Module1.myText39 = TextBox39.Value
Module1.myText40 = TextBox40.Value
Module1.myText41 = TextBox41.Value
Module1.myText42 = TextBox42.Value
Module1.myText43 = TextBox43.Value
Module1.myText44 = TextBox44.Value
Module1.myText45 = TextBox45.Value
Module1.myText46 = TextBox46.Value
Module1.myText47 = TextBox47.Value
Module1.myText48 = TextBox48.Value
Module1.myText49 = TextBox49.Value
Module1.myText50 = TextBox50.Value
Module1.myText51 = TextBox51.Value
Module1.myText52 = TextBox52.Value
Module1.myText53 = TextBox53.Value
Module1.myText54 = TextBox54.Value
Module1.myText55 = TextBox55.Value
Module1.myText56 = TextBox56.Value
Module1.myText57 = TextBox57.Value
Module1.myText58 = TextBox58.Value
Module1.myText59 = TextBox59.Value
Module1.myText60 = TextBox60.Value
Module1.myText61 = TextBox61.Value
Module1.myText62 = TextBox62.Value
Module1.myText63 = TextBox63.Value
Module1.myText64 = TextBox64.Value
Module1.myText65 = TextBox65.Value
Module1.myText66 = TextBox66.Value
Module1.myText67 = TextBox67.Value
Module1.myText68 = TextBox68.Value
Module1.myText69 = TextBox69.Value
Module1.myText70 = TextBox70.Value
Module1.AdditionalNotes = TextBoxAdditionalNotes.Value

UserForm2.Show
End Sub

' Code behind Form2 only calls module function
 Private Sub CommandButton2_Click(UserForm2)
 PrintVals
 End Sub

When I run UserForm1 object: No errors but clicking commandbutton1 doesnt bring up UserForm2

当我运行 UserForm1 对象时：没有错误但单击 commandbutton1 不会显示 UserForm2

Any help solving is appreciated! Thanks!

任何帮助解决表示赞赏！谢谢！

采纳答案by Ted

Public is very abnormal in VBA Forms. Your controls, like a TextBox, can be seen publicly from other forms. So for instance, UserForm1 can see the textbox value on UserForm2 and vice versa. So the following works if textboxes exist in both forms cases:

Public 在 VBA Forms 中非常不正常。您的控件（如 TextBox）可以从其他表单公开查看。例如，UserForm1 可以看到 UserForm2 上的文本框值，反之亦然。因此，如果两种形式的情况下都存在文本框，则以下内容有效：

' Is the button that launches the second form
Private Sub CommandButton1_Click()
  UserForm2.TextBox1.Value = Me.TextBox1.Value
  UserForm2.TextBox2.Value = Me.TextBox2.Value
  UserForm2.TextBox3.Value = Me.TextBox3.Value
  UserForm2.Show
End Sub

Also, if no text boxes exist on UserForm2, UserForm2 should still be able to access the values directly from the text boxes in UserForm1 like:

此外，如果 UserForm2 上不存在文本框，则 UserForm2 仍应能够直接从 UserForm1 中的文本框访问值，例如：

Private Sub CommandButton1_Click()
   'Assign a macro to the OK button
   Dim emptyRow As Long
   'Make Sheet2 Active
   Sheets(2).Activate
   'Determine emptyRow
   emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
   'Export Data to worksheet  
   Cells(emptyRow, 1).Value = UserForm1.TextBox1.Value
End Sub

But in many cases it is most useful to put your shared content on a Module instead. This way the public variables will be directly visible to all other forms, modules etc.

但在许多情况下，将共享内容放在模块上是最有用的。这样，公共变量将对所有其他表单、模块等直接可见。

' Code behinde Module1 to store public values
Public myText1 As String
Public myText2 As String

' Code behind Module1 to actually use the values
Public Sub PrintVals()
   'Assign a macro to the OK button
    Dim emptyRow As Long
    'Make Sheet2 Active
   Sheets(2).Activate
   'Determine emptyRow  
   emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
   'Export Data to worksheet
   Cells(emptyRow, 1).Value = myText1
   Cells(emptyRow, 2).Value = myText2
End Sub

' Code behind Form1 Initialize text boxes
Private Sub Form_Initialize()
    TextBox1.Value = "1234"
    TextBox2.Value = "ABC"
End Sub

End Sub

' Code behind Form1 Set variables on module
Private Sub CommandButton1_Click()
   Module1.myText1 = TextBox1.Value
   Module1.myText2 = TextBox2.Value
   UserForm2.Show
End Sub

' Code behind Form2 only calls module function
Private Sub CommandButton1_Click()
  PrintVals
End Sub

[Edit after several posts]

[在几篇文章后编辑]

Try starting a new excel file that doesn't have any of your current code. Create two new forms. Add just three textboxes onto UserForm1, and one command button onto both form 1 and form 2. Start with just the code from my solution above. And see if you can get that to work. If it works, then we can try to figure out what's different between the code above and your code and remove those differences if you can, or you can just flesh out the working example until it has all the textboxes you need. If it doesn't work then there may be something different between the excel versions or something fundamental like that.

尝试启动一个没有任何当前代码的新 Excel 文件。创建两个新表单。只在 UserForm1 上添加三个文本框，在表单 1 和表单 2 上添加一个命令按钮。从我上面的解决方案中的代码开始。看看你能不能让它发挥作用。如果它有效，那么我们可以尝试找出上面的代码和您的代码之间的不同之处，并尽可能删除这些差异，或者您可以充实工作示例，直到它拥有您需要的所有文本框。如果它不起作用，那么 excel 版本或类似的基本版本之间可能会有所不同。

My project window looks like this:

我的项目窗口如下所示：

So the possibly important thing here is that all my forms are part of one Book. If your forms are in different books, we will have to modify the code to handle that.

所以这里可能很重要的一点是，我所有的表格都是一本书的一部分。如果您的表单在不同的书籍中，我们将不得不修改代码来处理它。