Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form 'Refresh' problem
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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form 'Refresh' problem
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form 'Refresh' problem
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) "Stuart" wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form 'Refresh' problem
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. Just a guess, since no code is posted, but if you currently use Userform1 _Initialize to set the listbox text, try Userform1_Activate instead. -- David |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form 'Refresh' problem
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. "iwrk4dedpr " wrote in message ... Your problem is a bit vague. It would help if you posted some code for us to see. How are your listboxes on the form populated? Have you tried stepping through the code on both the first run and second runs to try to notice if there is a difference in the path the code takes? --- Message posted from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form 'Refresh' problem
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" wrote in message ... 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. "iwrk4dedpr " wrote in message ... Your problem is a bit vague. It would help if you posted some code for us to see. How are your listboxes on the form populated? Have you tried stepping through the code on both the first run and second runs to try to notice if there is a difference in the path the code takes? --- Message posted from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form 'Refresh' problem
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" wrote in message ... 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" wrote in message ... 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. "iwrk4dedpr " wrote in message ... Your problem is a bit vague. It would help if you posted some code for us to see. How are your listboxes on the form populated? Have you tried stepping through the code on both the first run and second runs to try to notice if there is a difference in the path the code takes? --- Message posted from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form 'Refresh' problem
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) "Stuart" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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. "iwrk4dedpr " wrote in message ... Your problem is a bit vague. It would help if you posted some code for us to see. How are your listboxes on the form populated? Have you tried stepping through the code on both the first run and second runs to try to notice if there is a difference in the path the code takes? --- Message posted from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change and refresh OLEObject showing Excel chart on Access form. | Charts and Charting in Excel | |||
Form.Refresh doesn't work...help !! | Excel Programming | |||
Refresh label on user form with RefEdit | Excel Programming | |||
How do I refresh a form in excel? | Excel Programming | |||
Form will not refresh without an unneeded msgbox | Excel Programming |