Coding error in my command box - Excel VBA

Question

I have got some code here, but I get the following error:

Run-time error 1004

Application-defined or object-defined error

So hopefully someone can help me figure out what I am doing wrong. Still very new to all of this...

What I want my userform to do

I have a userform which is two pages, it uses multipage.

I want all my users to fill in details on the first page, called 'Main'. On this form is an option-button question: "Did the customer ask about a product today?".

If the answer to this question is 'no', the form should end after the command button is clicked, sending the relevant info to the worksheet.

If the answer to this question is 'yes', the form should automatically send the user to the 'Extra' page. The user completes this page, hits the command button on this second page, and the form will send the info from both pages into the same row of the worksheet.

My Code

For the command button on the 'Main' page

Private Sub CommandButton1_Click()

Dim emptyRow As Long

Dim closeForm As Boolean

'The form should close, unless ProductEnquiryYes is true
closeForm = True

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


'Did customer ask about product?
If ProductEnquiryYes.Value = True Then
Me.MultiPage1.Value = 1
closeForm = False
Cells(emptyRow, 20).Value = 1
End If

If ProductEnquiryNo.Value = True Then
    Cells(emptyRow, 21).Value = 1
End If

'Balance Enquiry
If BalanceEnquiry.Value = True Then
    Cells(emptyRow, 23).Value = 1
End If

'Close Userform
If closeForm Then Unload Me

End Sub

For the command button on the 'Extra' page

Private Sub CommandButton2_Click()

'If this checkbox is True, send value of 1 to the worksheet
If CAOpen.Value = True Then
    Cells(emptyRow, 63).Value = 1
End If

Unload Me

End Sub

The command button works for the first page, and is returning the values correctly into the worksheet. However, the command button on the second page is not correct, and I do not know why. It's probably something obvious, so please forgive me.

When I hit debug, it is specifically this line that is highlighted in yellow:

Cells(emptyRow, 63).Value = 1

Thanks for the help!!

smagnan · Accepted Answer · 2014-07-11 08:38:08Z

2

Actually I think that the error is you use emptyRow in

Private Sub CommandButton2_Click()

'If this checkbox is True, send value of 1 to the worksheet
If CAOpen.Value = True Then
    Cells(emptyRow, 63).Value = 1
End If

Unload Me

End Sub

But it has not been initialized! in CommandButton1_Click you have

Dim emptyRow As Long

so emptyRow is not global, but a local variable.

You have to recreate and recalculate emptyRow :

Private Sub CommandButton2_Click()

Dim emptyRow As Long
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


'If this checkbox is True, send value of 1 to the worksheet
If CAOpen.Value = True Then
    Cells(emptyRow, 63).Value = 1
End If

Unload Me

End Sub

You can use the Option Explicit option to be more strict.

answered Jul 11, 2014 at 8:38

smagnan

1,26718 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

lummers Over a year ago

Hi smagnan -- excellent, thank you. This now makes the userform work. However, I want the values from the second command button to go into the same row. Now, the values go into the empty row below. Any ideas?

smagnan Over a year ago

@AliLumsden didn't get well what you mean by "same row": If you mean the row above the empty row you can use the Offset function: msdn.microsoft.com/en-us/library/office/… Like: Cells(emptyRow, 63).Offset(-1,0).Value = 1 ... or I didn't get what you want

smagnan Over a year ago

NOTE: If the above didn't answer your last comment, you can check the answer as accepted and ask a new question with more detail about this specific problem: It will help future users because it's not easy to explain a solution in the comments and because theese are two different questions.

lummers Over a year ago

Hi smagman, I accepted your answer and +vote. Thanks for the help.. still struggling with some errors, but for a different reason now ^.^

Collectives™ on Stack Overflow

Coding error in my command box - Excel VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related