Form 'Refresh' problem

S

Stuart

I have a userform to get user's print options.
It contains 2 textboxes (displaying instructions),
4 listboxes (offering options) and OK and Cancel
commandbuttons.

First time through the routine, the form displays
correctly, I choose the first listbox option and then
Ok. The routine then appears to run and exit correctly.

If I immediately run the routine again, then the first
listbox is empty of text. If I restart Excel, then the form
displays correctly, but again, just for the first time.

Where might I look for my error, please?

Regards.
 
I

iwrk4dedpr

Your problem is a bit vague. It would help if you posted some code fo
us to see.


How are your listboxes on the form populated?

Have you tried stepping through the code on both the first run an
second runs to try to notice if there is a difference in the path th
code takes
 
B

Bob Phillips

I think we need to see the code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

David

Stuart wrote
If I immediately run the routine again, then the first
listbox is empty of text. If I restart Excel, then the form
displays correctly, but again, just for the first time.
Click to expand...

Just a guess, since no code is posted, but if you currently use Userform1
_Initialize to set the listbox text, try Userform1_Activate instead.
 
S

Stuart

Here is the initialising code in the form:

Private Sub UserForm_Initialize()
'Fill the ListBoxes
With GetUserPrintOptions.ListBox1
.RowSource = ""
.AddItem "You want to print EVERY Worksheet in EVERY chosen Workbook"
End With
With GetUserPrintOptions.ListBox2
.RowSource = ""
.AddItem "You will want to hide Column(s)"
End With
With GetUserPrintOptions.ListBox3
.RowSource = ""
.AddItem "You want to include the printing of pages that total '0.00'"
End With
With GetUserPrintOptions.ListBox4
.RowSource = ""
.AddItem "You want to include the printing of pages with no totals"
End With
End Sub

Here is the module code:
With GetUserPrintOptions
.Show
If .OkButton.Tag = "Selected" Then
If .ListBox1.Selected(0) Then
Global_PrintAllBooks_Sheets = True
End If
If .ListBox2.Selected(0) Then
HideCols = True
With GetUserHideColumnOptions
If .ListBox1.Selected(0) = True Then
Global_HideSameCols = True
End If
End With
End If
If .ListBox3.Selected(0) Then
PrintZeroPages = True
With GetUserPrintZeroPagesOptions
If .ListBox1.Selected(0) = True Then
Global_PrintZeroPages = True
End If
End With
End If
If .ListBox4.Selected(0) Then
PrintBlankPages = True
With GetUserPrintBlankPagesOptions
If .ListBox1.Selected(0) = True Then
Global_PrintBlankPages = True
End If
End With
End If
Else
'Cancel Button was pressed so set defaults
Global_PrintAllBooks_Sheets = False
Global_HideCols = False
Global_PrintZeroPages = False
Global_PrintBlankPages = False
End If
End With
Unload GetUserPrintOptions
Unload GetUserPrintBlankPagesOptions
Unload GetUserHideColumnOptions
Unload GetUserPrintZeroPagesOptions

As you can see, selecting listboxes 2, 3, or 4 results in further
forms being shown.
If I select one or more of these, then different errors result, namely
a selected entry in the original form becomes unselected, and I have
to repeat the selection again. If this happens then the routine will run
the first time, but (as per the original post) when I run it again without
restarting excel, then that entry on the original form will be missing.

Regards.
 
T

Tom Ogilvy

Initialize only fires when the form is created. You need to unload the form
each time (rather than hide it) to get the initialize code to fire the next
time you show the form.

--
Regards,
Tom Ogilvy

Stuart said:
Here is the initialising code in the form:

Private Sub UserForm_Initialize()
'Fill the ListBoxes
With GetUserPrintOptions.ListBox1
.RowSource = ""
.AddItem "You want to print EVERY Worksheet in EVERY chosen Workbook"
End With
With GetUserPrintOptions.ListBox2
.RowSource = ""
.AddItem "You will want to hide Column(s)"
End With
With GetUserPrintOptions.ListBox3
.RowSource = ""
.AddItem "You want to include the printing of pages that total '0.00'"
End With
With GetUserPrintOptions.ListBox4
.RowSource = ""
.AddItem "You want to include the printing of pages with no totals"
End With
End Sub

Here is the module code:
With GetUserPrintOptions
.Show
If .OkButton.Tag = "Selected" Then
If .ListBox1.Selected(0) Then
Global_PrintAllBooks_Sheets = True
End If
If .ListBox2.Selected(0) Then
HideCols = True
With GetUserHideColumnOptions
If .ListBox1.Selected(0) = True Then
Global_HideSameCols = True
End If
End With
End If
If .ListBox3.Selected(0) Then
PrintZeroPages = True
With GetUserPrintZeroPagesOptions
If .ListBox1.Selected(0) = True Then
Global_PrintZeroPages = True
End If
End With
End If
If .ListBox4.Selected(0) Then
PrintBlankPages = True
With GetUserPrintBlankPagesOptions
If .ListBox1.Selected(0) = True Then
Global_PrintBlankPages = True
End If
End With
End If
Else
'Cancel Button was pressed so set defaults
Global_PrintAllBooks_Sheets = False
Global_HideCols = False
Global_PrintZeroPages = False
Global_PrintBlankPages = False
End If
End With
Unload GetUserPrintOptions
Unload GetUserPrintBlankPagesOptions
Unload GetUserHideColumnOptions
Unload GetUserPrintZeroPagesOptions

As you can see, selecting listboxes 2, 3, or 4 results in further
forms being shown.
If I select one or more of these, then different errors result, namely
a selected entry in the original form becomes unselected, and I have
to repeat the selection again. If this happens then the routine will run
the first time, but (as per the original post) when I run it again without
restarting excel, then that entry on the original form will be missing.

Regards.
Click to expand...
 
S

Stuart

Many thanks.

so in the module
Unload GetUserPrintOptions

and in the code behind the form
Private Sub UserForm_QueryClose _
(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormCode Then
Unload Me
End If
End Sub

As an aside, why/when to use the Terminate Event, after the
QueryClose, please?

Regards.


Tom Ogilvy said:
Initialize only fires when the form is created. You need to unload the form
each time (rather than hide it) to get the initialize code to fire the next
time you show the form.
Click to expand...
 
B

Bob Phillips

Terminate automatically fires when you Unload the form. You don't get a
choice on that, you do get a choice as to whether you addany code there.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top