Do userforms need initialization? - < Object variable or With block variable not set>

[yky]

Do userforms need special initialization?

I have two userforms, userform1 and userform2. Excel crashes when it comes to the line "userform2.show".

I checked both forms' properties in the debug window. Userform1.name was userform1. That is fine. However, when it comes to userform2.name, the debug window showed "< Object variable or With block variable not set>" and its type was integer, not string.

I have codes in userform1's initialization routine but not userform2's. That makes me wonder whether I need to initialize userform2.

 

[yky]

Are you prepared to make the workbook available for inspection, either via a file-sharing site or via email?

The data can be removed.

I'd be more than happy to send you the code if you would give me your email address.

I did a search for "userform" on the module (only one). The only thing that contains the search word is "UserForm2.Show" and that is exactly where the Excel crashes. The following is declaration of variables in the module. I don't think variable name is the problem.

Option Explicit

Public voucherDate As Date
Public sourcePath As String
Public sourceFileName As String
Public destinationPath As String
Public destinationFileName As String
Public strLocation As String
Public strUnit As String
Public strTitle As String
Public strVoucherName As String
Public intLastRow As Integer



Sub voucher()

Dim Count As Integer
Dim CountRow As Integer
Dim ItemNo As String
Dim empDiscount As Integer
Dim voucherName As String
Dim salesName As String
Dim Outlet As String
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim cell As Range
Dim cell1 As Range
Dim rng3 As Range

 

[Norie]

Without seeing all the code and knowing where it's located it's pretty hard to tell.

By the way, something I forgot to ask, why do you want to display 2 userforms at the same time and how are they related to each other?

 

[yky]

OK. I'm beginning to see the problem, maybe.

I have UserForm1.Show in sheet1's BeforeDoubleClick event and UserForm2.Show in module "main". Excel always crashes when it executes UserForm2.Show.

If I put UserForm2.Show in sheet1's BeforeDoubleClick event, right after UserForm1.Show (so, that event contains only two lines, UserForm1.Show and UserForm2.Show), there is no problem.

Anyone knows why?

Sorry! My mistake. Should be three lines.

Cancel = True
UserForm1.Show
UserForm2.Show

 

[xenou]

I have UserForm1.Show in sheet1's BeforeDoubleClick event and UserForm2.Show in module "main". Excel always crashes when it executes UserForm2.Show.

If UserForm2.Show is in "main" what triggers the execution of the code. It seems you are saying it's not in the BeforeDoubleClick event in Sheet1, which is when UserForm1 gets loaded. So I'm not sure why UserForm2 would show at all.

 

[yky]

What about the code for the other form?

The following code in userform1 is the only relevant code. Option buttons only set the value of variables. Other than that, there is no action.
=============================================
Private Sub CommandButton1_Click()
voucherDate = Format(TextBox1.Text, "dd/mm/yyyy")

If (OptionButton1.Value + OptionButton2.Value + OptionButton3.Value + OptionButton4.Value + OptionButton5.Value + OptionButton6.Value + OptionButton7.Value + OptionButton8.Value = 0) Then
MsgBox ("You must make a selection.")

Else

Call voucher '<- this is the only module
Unload Me

End If


End Sub

 

[xenou]

^^ nothing in the above code has anything (that I can see) to do with UserForm2.
See my previous post - how is UserForm2 being loaded if not in the same DoubleClick Event as UserForm1?

 

[yky]

Without seeing all the code and knowing where it's located it's pretty hard to tell.

By the way, something I forgot to ask, why do you want to display 2 userforms at the same time and how are they related to each other?

I have a spreadsheet on which users enter data. Once done, they double-click a cell labeled "Done". The userform1 is shown. Users then make some selection by clicking on option buttons and enter a date to a text box. When finished, users click on an OK button which calls the module which processes the data. After the processing is done, I would like to ask users whether they have more data to enter, hence, userform2. The two forms do not appear at the same time. They appear one after the other.

If I put the userform2 in the module, Excel crashes. If I put it in the sheet1's BeforeDoubleClick event and right after UserForm1.Show, Excel is happy about it.

 

[Norie]

If all the 2nd userform does is ask the user if they want to enter more data why not use a message box.

 

[RoryA]

It appeared from your initial posts that userform2 was not getting capitalised to Userform2 in which case I still think you have something else called userform2.

 

[yky]

If UserForm2.Show is in "main" what triggers the execution of the code. It seems you are saying it's not in the BeforeDoubleClick event in Sheet1, which is when UserForm1 gets loaded. So I'm not sure why UserForm2 would show at all.

In sheet1's BeforeDoubleClick event. It calls main (if user clicks an option button. See the code I posted earlier. Main contains voucher().) and at the end of voucher(), it calls userform2 (and Excel crashes).