Getting error unloading Userform??

[Ed]

In a certain workbook, code in the ThisWorkbook module creates a new menu
with one item when the workbook opens. The one item calls a macro in a
general module in the workbook. The macro has one item:
Load UserForm1

The Form_Initialize code gets the name of every sheet in the workbook and
populates a ComboBox control. The form has an "Exit" CommandButton, with
two lines of code:
Set wb = Nothing
Unload Me

When I click the Exit button, the form unloads and disappears, but the code
then errors with 91: Object variable or With variable not set. The VBE
opens with the
Load UserForm1
line in the original macro highlighted in yellow.

How should I fix this?

Ed

 

[Jim Cone]

Ed,

Not quite sure what is calling whom when, but I believe
you should...
1. change the code for the exit button from
Unload Me to Me.Hide
2. and then...
add the line "Unload UserForm1" following the
"UserForm1.Show" line, where ever that is.

Regards,
Jim Cone
San Francisco, USA

In a certain workbook, code in the ThisWorkbook module creates a new menu
with one item when the workbook opens. The one item calls a macro in a
general module in the workbook. The macro has one item:
Load UserForm1

The Form_Initialize code gets the name of every sheet in the workbook and
populates a ComboBox control. The form has an "Exit" CommandButton, with
two lines of code:
Set wb = Nothing
Unload Me

When I click the Exit button, the form unloads and disappears, but the code
then errors with 91: Object variable or With variable not set. The VBE
opens with the
Load UserForm1
line in the original macro highlighted in yellow.

How should I fix this?

Ed

 

[Ed]

UserForm1.Show is in the Initialize code, right after the ComboBox loop
ends. If I put it there, wouldn't it unload the form right after showing
it?

Ed

 

[Jim Cone]

Ed,

No it won't. The normal sequence is...

'------------------
Sub Demo
'code here to check stuff

UserForm1.Show
'do stuff while form is shown - click option buttons etc.
'then hide the form by clicking a button on the form (Me.Hide)

'code here to check for any settings on the form, for instance
'an entry in a textbox or which option button was clicked.

Unload UserForm1 '(Me can't be used)
'additional code here
Set UserForm1 = Nothing
End Sub
'-------------------
Jim Cone
San Francisco, USA



UserForm1.Show is in the Initialize code, right after the ComboBox loop
ends. If I put it there, wouldn't it unload the form right after showing
it?
Ed

Ed,

Not quite sure what is calling whom when, but I believe
you should...
1. change the code for the exit button from
Unload Me to Me.Hide
2. and then...
add the line "Unload UserForm1" following the
"UserForm1.Show" line, where ever that is.

Regards,
Jim Cone
San Francisco, USA

In a certain workbook, code in the ThisWorkbook module creates a new menu
with one item when the workbook opens. The one item calls a macro in a
general module in the workbook. The macro has one item:
Load UserForm1

The Form_Initialize code gets the name of every sheet in the workbook and
populates a ComboBox control. The form has an "Exit" CommandButton, with
two lines of code:
Set wb = Nothing
Unload Me
When I click the Exit button, the form unloads and disappears, but the

code then errors with 91: Object variable or With variable not set. The VBE