我正在尝试简化财务报表中的数据输入,因此我尝试使用Excel Visual Basic制作表格。
到目前为止,我制作了2个用户窗体,后来我制作了5个。我制作了用户窗体,以便数据输入操作员可以简化窗体的设计,因为文本框太多,然后将扇区划分为5个用户窗体以简化该窗体。
要在扇区之间移动,操作员可以使用命令按钮跳转到另一个用户窗体。
当操作员完成所有3个用户窗体的数据输入后,他将返回主用户窗体以一次将所有数据输入excel。
我的问题是,我发现很难在用户窗体之间进行连接以从每个用户窗体获取值,以便最终可以使用主用户窗体或用户窗体1上的1个命令按钮一次将所有值输入至excel。
我的命令按钮代码是这样的:
Private Sub cmdAddData_Click()
'Copy input values to sheet.
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Summary")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lRow, 1).Value = Me.txtNo.Value
.Cells(lRow, 2).Value = Me.txtKode.Value
.Cells(lRow, 3).Value = Me.txtNamaPerusahaan.Value
.Cells(lRow, 4).Value = Me.txtSector.Value
.Cells(lRow, 5).Value = Me.txtTime.Value
'UserForm2Begin'
.Cells(lRow, 7).Value = Me.txtKas.Value
.Cells(lRow, 8).Value = Me.txtInvestasi.Value
.Cells(lRow, 9).Value = Me.txtDanaTerbatas.Value
.Cells(lRow, 10).Value = Me.txtPiutangUsaha.Value
'UserForm2End'
End With
'Clear input controls.
Me.txtNo.Value = ""
Me.txtKode.Value = ""
Me.txtNamaPerusahaan.Value = ""
Me.txtSector.Value = ""
Me.txtTime.Value = ""
'Userform2Begin'
Me.txtKas.Value = ""
Me.txtInvestasi.Value = ""
Me.txtDanaTerbatas.Value = ""
Me.txtPiutangUsaha.Value = ""
'Userform2End'
提前致谢
如果您声明要公开阅读的每个文本字段,则可以这样进行:
Class MainForm
Form firstPage
Form secondPage
...
Private Sub cmdAddData_Click()
'Copy input values to sheet.
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Summary")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lRow, 1).Value = firstPage.txtNo.Value
.Cells(lRow, 2).Value = firstPage.txtKode.Value
.Cells(lRow, 3).Value = firstPage.txtNamaPerusahaan.Value
.Cells(lRow, 4).Value = firstPage.txtSector.Value
.Cells(lRow, 5).Value = firstPage.txtTime.Value
'UserForm2Begin'
.Cells(lRow, 7).Value = secondPage.txtKas.Value
.Cells(lRow, 8).Value = secondPage.txtInvestasi.Value
.Cells(lRow, 9).Value = secondPage.txtDanaTerbatas.Value
.Cells(lRow, 10).Value = secondPage.txtPiutangUsaha.Value
'UserForm2End'
End With
'Clear input controls.
Me.txtNo.Value = ""
Me.txtKode.Value = ""
Me.txtNamaPerusahaan.Value = ""
firstPage.txtSector.Value = ""
firstPage.txtTime.Value = ""
'Userform2Begin'
secondPage.txtKas.Value = ""
secondPage.txtInvestasi.Value = ""
secondPage.txtDanaTerbatas.Value = ""
secondPage.txtPiutangUsaha.Value = ""
End Sub
End Class
如前所述,如果要使用此方法,则需要将可见性设置为public(在图形编辑器中,其attribute为Modifiers
)
更好的解决方案是在Mainfrom中声明一个对象,该对象具有以后要在Excel文件中拥有的所有值的属性。然后将此对象赋予每种形式,例如在构造函数中,然后填充它。然后,在Mainform中,您可以读取对象的所有属性并将其写入文件。用于保存数据的对象可能是这样的:
Class DataObject
Public txtNo as String
Public txtKode as String
...
End Class
您在用户可以看到的第一个表单中声明它,然后将其提供给随后的每个表单
Class FirstForm
Dim data as DataObject
...
private sub openNextWindow()
dim sec as SecondForm= new SecondForm(DataObject)
...
end sub
end class
直到您最终在cmdAddData处执行以下操作:
Private Sub cmdAddData_Click()
'Copy input values to sheet.
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Summary")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lRow, 1).Value = data.txtNo
.Cells(lRow, 2).Value = data.txtKode
...
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句