Userform object error 424, help

D

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.

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:

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
As to the problem itself, you should put a breakpoint on the userform12.show code. Then, click the button to open the userform, and use F8 to step through the code. See where it breaks down.

As to the code, this is a lot of code for only straightforward tasks. For instance, you can replace:

Code: 
'Empty R1    R1.Clear


    'Fill R1
    With R1
        .AddItem "LNP"
        .AddItem "WP"
        .AddItem "VHEE"
    End With

with:

Code: 
R1.List = Array("LNP", "WP", "VHEE")

And afterwards:

Code: 
R2.List = R1.List
R3.List = R1.List
R4.List = R1.List
...

Also, clearing all those textboxes is not needed. Unchecking the checkboxes is not needed...
 
Upvote 0
Thank you! I had some misspelling :/

Do you know how I transfer "check marks" form a form into a table? And, how do I make sure my information goes into a table instead of overwriting it?

TIA
 
Upvote 0
Delimaster said:
Do you know how I transfer "check marks" form a form into a table?
Click to expand...

Put the .Value property of the checkbox (True or False) into the table, in the correct column and correct row.

Delimaster said:
And, how do I make sure my information goes into a table instead of overwriting it?
Click to expand...

Put the data in the table, in the correct column and CERTAINLY the correct row. If not, you risk overwriting data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Application-defined or object defined Error when Filtering Listbox
Replies
4
Views
132
Peter_SSs
Peter_SSs
A
  • Solved
Userform - ComboBox with DropDown List Not Showing when Macro is Run
Replies
9
Views
5K
sykes
sykes
A
  • Solved
Why Can I not get headers in my list box
Replies
4
Views
2K
Andrew1234
A
N
  • Question
Can't initialize a userform always saying invalid procedure or call error
Replies
0
Views
269
nmc
N
J
  • Question
VBA Code Help - Search Form
Replies
1
Views
863
James006
James006

Forum statistics

Threads
1,214,561
Messages
6,120,239
Members
448,951
Latest member
jennlynn

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top