I am trying to ease the data entry from financial report, so i try to make forms using Excel Visual Basic.
I made 2 Userform so far ,later i will made 5. I made userform so the data entry operator can have simple design of the form, because the textboxs are so many,then I partitioned the sector into 5 userform to simplifiy it.
To move between Sector, the operator can jump to another userform using command button.
When the operator has finished the data entry from all 3 userform, he will comeback to the main Userform to Entry the data all at once into excel.
My problem is, i find it difficult to connect between userform to take the value from each userform so that finally the value can be inputted to excel all at once using 1 command button at the main userform or userform1.
My code for the command button is this:
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'
Thanks in advance
If you declare every textfield that you want to read from public you can do it like this:
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
As mentioned if you want to use this approach, you need to set the visibility to public (in the graphical editor its the attribute Modifiers
)
The better solution would be you declare an object in the Mainfrom that has attributes for all values you want to have in you Excel file later. Then give this object to every form, for example in the constructor, and fill it. In you Mainform you can then read all attributes form the object and write them to the file. The Object for holding you data would somthing like this:
Class DataObject
Public txtNo as String
Public txtKode as String
...
End Class
You declare it in the first Form that the user can see and then give it to every form that follows
Class FirstForm
Dim data as DataObject
...
private sub openNextWindow()
dim sec as SecondForm= new SecondForm(DataObject)
...
end sub
end class
Until you finally are at your cmdAddData where you do it like this:
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] 삭제
몇 마디 만하겠습니다