Delimaster
New Member
- Joined
- Apr 17, 2014
- Messages
- 12
I'm new to VBA, and I have created a userform, but cannot get the command button on the sheet to open it. Seems so simple, but i'm missing something obvious I fear.
I'm trying to create a form that will allow information to be entered into single uf and distributed to different worksheets in the workbook.
It says the userform.show work.
any help is appreciated.
I'm trying to create a form that will allow information to be entered into single uf and distributed to different worksheets in the workbook.
It says the userform.show work.
any help is appreciated.
Code:
Private Sub Commandbutton1_Click()
userform12.Show
End Sub
Code:
Private Sub CANCEL_Click()
Unload Me
End Sub
Private Sub CLEAR_Click()
'Call userform
UserForm_Initialize
End Sub
Private Sub ENTER_Click()
Dim emptyRow As Long
'Make sheet1 active
Sheet1.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = FirstNameTextBox.Value
Cells(emptyRow, 2).Value = LastNametextbox.Value
Cells(emptyRow, 3).Value = AddressTextBox.Value
Cells(emptyRow, 4).Value = CityTextBox.Value
Cells(emptyRow, 5).Value = StateTextBox.Value
Cells(emptyRow, 6).Value = ZipCodeTextBox.Value
Cells(emptyRow, 7).Value = SSNTextBox.Value
Cells(emptyRow, 8).Value = IncomeTextBox.Value
Cells(emptyRow, 9).Value = SPFNTextBox.Value
Cells(emptyRow, 10).Value = SPLNTextBox.Value
Cells(emptyRow, 11).Value = SPSSNTextBox.Value
Cells(emptyRow, 12).Value = DEP1FN.Value
Cells(emptyRow, 13).Value = DEP1LN.Value
Cells(emptyRow, 14).Value = DEP1SSN.Value
Cells(emptyRow, 15).Value = DEP2FN.Value
Cells(emptyRow, 16).Value = DEP2LN.Value
Cells(emptyRow, 17).Value = DEP2SSN.Value
Cells(emptyRow, 18).Value = DEP3FN.Value
Cells(emptyRow, 19).Value = DEP3LN.Value
Cells(emptyRow, 20).Value = DEP3SSN.Value
'Make sheet2 active
Sheet2.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = FirstNameTextBox.Value
Cells(emptyRow, 2).Value = LastNametextbox.Value
Cells(emptyRow, 3).Value = HW_JAN.Value
Cells(emptyRow, 4).Value = HW_FEB.Value
Cells(emptyRow, 5).Value = HW_MAR.Value
Cells(emptyRow, 6).Value = HW_APR.Value
Cells(emptyRow, 7).Value = HW_MAY.Value
Cells(emptyRow, 8).Value = HW_JUN.Value
Cells(emptyRow, 9).Value = HW_JUL.Value
Cells(emptyRow, 10).Value = HW_AUG.Value
Cells(emptyRow, 11).Value = HW_SEP.Value
Cells(emptyRow, 12).Value = HW_OCT.Value
Cells(emptyRow, 13).Value = HW_NOV.Value
Cells(emptyRow, 14).Value = HW_DEC.Value
'Make sheet6 active
Sheet6.Acivate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = FirstNameTextBox.Value
Cells(emptyRow, 2).Value = LastNametextbox.Value
If OC_JAN.Value = True Then Cells(emptyRow, 3).Value = OC_JAN.Caption
If OC_FEB.Value = True Then Cells(emptyRow, 4).Value = OC_FEB.Caption
If OC_MAR.Value = True Then Cells(emptyRow, 5).Value = OC_MAR.Caption
If OC_APR.Value = True Then Cells(emptyRow, 6).Value = OC_APR.Caption
If OC_MAY.Value = True Then Cells(emptyRow, 7).Value = OC_MAY.Caption
If OC_JUN.Value = True Then Cells(emptyRow, 8).Value = OC_JUN.Caption
If OC_JUL.Value = True Then Cells(emptyRow, 9).Value = OC_JUL.Caption
If OC_AUG.Value = True Then Cells(emptyRow, 10).Value = OC_AUG.Caption
If OC_SEP.Value = True Then Cells(emptyRow, 11).Value = OC_SEP.Caption
If OC_OCT.Value = True Then Cells(emptyRow, 12).Value = OC_OCT.Caption
If OC_NOV.Value = True Then Cells(emptyRow, 13).Value = OC_NOV.Caption
If OC_DEC.Value = True Then Cells(emptyRow, 14).Value = OC_DEC.Caption
End Sub
Private Sub UserForm_Initialize()
'Empty FirstNameTextBox
FirstNameTextBox.Value = ""
'Empty LastNametextBox
LastNametextbox.Value = ""
'Empty AddressTextBox
AddressTextBox.Value = ""
'Empty CityTextBox
CityTextBox.Value = ""
'Empty StatetextBox
StateTextBox.Value = ""
'Empty ZipCodeTextBox
ZipCodeTextBox.Value = ""
'Empty SSNTextBox
SSNTextBox.Value = ""
'Empty IncomeTextBox
IncomeTextBox.Value = ""
'Empty HW_JAN
HW_JAN.Value = ""
'Empty HW_FEB
HW_FEB.Value = ""
'Empty HW_MAR
HW_MAR.Value = ""
'Empty HW_APR
HW_APR.Value = ""
'Empty HW_JUN
HW_JUN.Value = ""
'Empty HW_JUL
HW_JUL.Value = ""
'Empty HW_AUG
HW_AUG.Value = ""
'Empty HW_SEP
HW_SEP.Value = ""
'Empty HW_OCT
HW_OCT.Value = ""
'Empty HW_NOV
HW_NOV.Value = ""
'Empty HW_DEC
HW_DEC.Value = ""
'Empty R1
R1.CLEAR
'Fill R1
With R1
.AddItem "LNP"
.AddItem "WP"
.AddItem "VHEE"
End With
'Empty R2
R2.CLEAR
'Fill R2
With R2
.AddItem "LNP"
.AddItem "WP"
.AddItem "VHEE"
End With
'Empty R3
R3.CLEAR
'Fill R3
With R3
.AddItem "LNP"
.AddItem "WP"
.AddItem "VHEE"
End With
'Empty R4
R4.CLEAR
'Fill R4
With R4
.AddItem "LNP"
.AddItem "WP"
.AddItem "VHEE"
End With
'Empty R5
R5.CLEAR
'Fill R5
With R5
.AddItem "LNP"
.AddItem "WP"
.AddItem "VHEE"
End With
'Empty R6
R6.CLEAR
'Fill R6
With R6
.AddItem "LNP"
.AddItem "WP"
.AddItem "VHEE"
End With
'Empty R7
R7.CLEAR
'Fill R7
With R7
.AddItem "LNP"
.AddItem "WP"
.AddItem "VHEE"
End With
'Empty R8
R8.CLEAR
'Fill R8
With R8
.AddItem "LNP"
.AddItem "WP"
.AddItem "VHEE"
End With
'Empty R9
R9.CLEAR
'Fill R9
With R9
.AddItem "LNP"
.AddItem "WP"
.AddItem "VHEE"
End With
'Empty R10
R10.CLEAR
'Fill R10
With R10
.AddItem "LNP"
.AddItem "WP"
.AddItem "VHEE"
End With
'Empty R11
R11.CLEAR
'Fill R11
With R11
.AddItem "LNP"
.AddItem "WP"
.AddItem "VHEE"
End With
'Empty R12
R12.CLEAR
'Fill R12
With R12
.AddItem "LNP"
.AddItem "WP"
.AddItem "VHEE"
End With
'Uncheck OC_JAN
OC_JAN.Value = False
'Uncheck OC_FEB
OC_FEB.Value = False
'Uncheck OC_MAR
OC_MAR.Value = False
'Uncheck OC_APR
OC_APR.Value = False
'Uncheck OC_MAY
OC_MAY.Value = False
'Uncheck OC_JUN
OC_JUN.Value = False
'Uncheck OC_JUL
OC_JUL.Value = False
'Uncheck OC_AUG
OC_AUG.Value = False
'Uncheck OC_SEP
OC_SEP.Value = False
'Uncheck OC_OCT
OC_OCT.Value = False
'Uncheck OC_NOV
OC_NOV.Value = False
'Uncheck OC_DEC
OC_DEC.Value = False
'Empty SPFNTextBox
SPFNTextBox.Value = ""
'Empty SPLNTextBox
SPLNTextBox.Value = ""
'Empty SPSSNTextBox
SPSSNTextBox.Value = ""
'Empty DEP1FN
DEP1FN.Value = ""
'Empty DEP1LN
DEP1LN.Value = ""
'Empty DEP1SSN
DEP1SSN.Value = ""
'Empty DEP2FN
DEP2FN.Value = ""
'Empty DEP2LN
DEP2LN.Value = ""
'Empty DEP2SSN
DEP2SSN.Value = ""
'Empty DEP3FN
DEP3LFN.Value = ""
'Empty DEP3LN
SPFNTextBox.Value = ""
'Empty DEP3SSN
DEP3SSN.Value = ""
'Empty SPFNTextBox
SPFNTextBox.Value = ""
'Empty SPLNTextBox
SPLNTextBox.Value = ""
'Uncheck SD1
SD1.Value = False
'Uncheck SD2
SD2.Value = False
'Uncheck SD3
SD3.Value = False
'Uncheck SD4
SD4.Value = False
'Uncheck SD5
SD5.Value = False
'Uncheck SD6
SD6.Value = False
'Uncheck SD7
SD7.Value = False
'Uncheck SD8
SD8.Value = False
'Uncheck SD9
SD9.Value = False
'Uncheck SD10
SD10.Value = False
'Uncheck SD11
SD11.Value = False
'Uncheck SD12
SD12.Value = False
End Sub
Last edited: