Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if UserForm is loaded?
Hi,
I have a macro, which under some conditions load an UserForm, and under other conditions don't. And, on some of these conditions, the UserForm is only loaded, but not shown (for extraction certain information) To make sure, that I clean up my act, I have an Unload UserForm statement at the very end of the macro. But, for some weird obscure reason the Unload statement actually LOADS the userform before unloading it!!! So, my question is, how to test if the UserForm is already opened, so I only Unload it, if it is open??? TIA, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if UserForm is loaded?
Charlotte E laid this down on his screen :
Hi, I have a macro, which under some conditions load an UserForm, and under other conditions don't. And, on some of these conditions, the UserForm is only loaded, but not shown (for extraction certain information) To make sure, that I clean up my act, I have an Unload UserForm statement at the very end of the macro. But, for some weird obscure reason the Unload statement actually LOADS the userform before unloading it!!! So, my question is, how to test if the UserForm is already opened, so I only Unload it, if it is open??? TIA, You should check if it's already loaded and if so then unload it, otherwise do nothing. Code... If Not UserForm Is Nothing Then Unload UserForm -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if UserForm is loaded?
You should check if it's already loaded and if so then unload it, otherwise do nothing. That was the whole idea :-) Code... If Not UserForm Is Nothing Then Unload UserForm Problem is that every call to the UserForm forces it to load... But, I've solved it by setting/clearing a global variable, upon loading/unloading the UserForm - works :-) CE |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if UserForm is loaded?
I know you've worked around but for future reference try something like
this - Sub UnloadAllForms() Dim i As Long For i = UserForms.Count To 1 Step -1 Unload UserForms(i - 1) Next End Sub If a particular form is loaded you might want to run any clean up code before unloading the form. Regards, Peter T "Charlotte E" wrote in message ... Hi, I have a macro, which under some conditions load an UserForm, and under other conditions don't. And, on some of these conditions, the UserForm is only loaded, but not shown (for extraction certain information) To make sure, that I clean up my act, I have an Unload UserForm statement at the very end of the macro. But, for some weird obscure reason the Unload statement actually LOADS the userform before unloading it!!! So, my question is, how to test if the UserForm is already opened, so I only Unload it, if it is open??? TIA, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if UserForm is loaded?
Thanks, Peter, but once again: The Unload statement actually loads the
userform first before unloading it, if the userform is not loaded upon calling the unload-statement, thus your solution cannot be used, since it is important not to load the userform, because this sets off a chain of event, which is not wanted when unloading it. But, I really find it a lack of effencicy that that the unload statement actually loads the userform first!!! But, as said: I found a work around... CE "Peter T" wrote in message ... I know you've worked around but for future reference try something like this - Sub UnloadAllForms() Dim i As Long For i = UserForms.Count To 1 Step -1 Unload UserForms(i - 1) Next End Sub If a particular form is loaded you might want to run any clean up code before unloading the form. Regards, Peter T "Charlotte E" wrote in message ... Hi, I have a macro, which under some conditions load an UserForm, and under other conditions don't. And, on some of these conditions, the UserForm is only loaded, but not shown (for extraction certain information) To make sure, that I clean up my act, I have an Unload UserForm statement at the very end of the macro. But, for some weird obscure reason the Unload statement actually LOADS the userform before unloading it!!! So, my question is, how to test if the UserForm is already opened, so I only Unload it, if it is open??? TIA, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if UserForm is loaded?
Charlotte E wrote :
You should check if it's already loaded and if so then unload it, otherwise do nothing. That was the whole idea :-) Code... If Not UserForm Is Nothing Then Unload UserForm Problem is that every call to the UserForm forces it to load... But, I've solved it by setting/clearing a global variable, upon loading/unloading the UserForm - works :-) CE While that works well enough, it means you are loading an instance of the userform rather than the userform object. This requires memory overhead and more coding to manage it. The single line of code I posted doesn't call the userform nor cause it to load. It simply checks if it's in memory and unloads if it is. Otherwise, it does nothing which, according to you, "was the whole idea".<g -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if UserForm is loaded?
You may want to do some more testing.
Try creating a new userform1 and make sure it has a procedure like: Option Explicit Private Sub UserForm_Initialize() MsgBox "hi" End Sub Then step through your suggestion -- once with the userform loaded and once without the userform in memory. On 09/02/2010 17:46, GS wrote: Charlotte E wrote : You should check if it's already loaded and if so then unload it, otherwise do nothing. That was the whole idea :-) Code... If Not UserForm Is Nothing Then Unload UserForm Problem is that every call to the UserForm forces it to load... But, I've solved it by setting/clearing a global variable, upon loading/unloading the UserForm - works :-) CE While that works well enough, it means you are loading an instance of the userform rather than the userform object. This requires memory overhead and more coding to manage it. The single line of code I posted doesn't call the userform nor cause it to load. It simply checks if it's in memory and unloads if it is. Otherwise, it does nothing which, according to you, "was the whole idea".<g -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if UserForm is loaded?
Dave Peterson wrote :
You may want to do some more testing. Try creating a new userform1 and make sure it has a procedure like: Option Explicit Private Sub UserForm_Initialize() MsgBox "hi" End Sub Then step through your suggestion -- once with the userform loaded and once without the userform in memory. Well that was educational! Thanks for that. Seems using an object variable is actually the solution. Fact is I usually do load userforms that way (Set myform as new userform1) and so ass-u-me-d loading the object (userform1) gave the same behavior. I learned something here, so thanks for the lesson! On 09/02/2010 17:46, GS wrote: Charlotte E wrote : You should check if it's already loaded and if so then unload it, otherwise do nothing. That was the whole idea :-) Code... If Not UserForm Is Nothing Then Unload UserForm Problem is that every call to the UserForm forces it to load... But, I've solved it by setting/clearing a global variable, upon loading/unloading the UserForm - works :-) CE While that works well enough, it means you are loading an instance of the userform rather than the userform object. This requires memory overhead and more coding to manage it. The single line of code I posted doesn't call the userform nor cause it to load. It simply checks if it's in memory and unloads if it is. Otherwise, it does nothing which, according to you, "was the whole idea".<g -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if UserForm is loaded?
"Charlotte E" wrote in message news:cuGdnYoFNdZcf-
Thanks, Peter, but once again: The Unload statement actually loads the userform first before unloading it, if the userform is not loaded upon calling the unload-statement, thus your solution cannot be used, since it is important not to load the userform, because this sets off a chain of event, which is not wanted when unloading it. No, the example I posted only unloads Forms that are already loaded. If no Forms are loaded UserForms.Count will be zero, so the loop will not even start. But, I really find it a lack of effencicy that that the unload statement actually loads the userform first!!! Whenever you attempt to reference a Form it will load if it is not alrady loaded. It's not a question of efficiency or lack of it. Regards, Peter T "Peter T" wrote in message ... I know you've worked around but for future reference try something like this - Sub UnloadAllForms() Dim i As Long For i = UserForms.Count To 1 Step -1 Unload UserForms(i - 1) Next End Sub If a particular form is loaded you might want to run any clean up code before unloading the form. Regards, Peter T "Charlotte E" wrote in message ... Hi, I have a macro, which under some conditions load an UserForm, and under other conditions don't. And, on some of these conditions, the UserForm is only loaded, but not shown (for extraction certain information) To make sure, that I clean up my act, I have an Unload UserForm statement at the very end of the macro. But, for some weird obscure reason the Unload statement actually LOADS the userform before unloading it!!! So, my question is, how to test if the UserForm is already opened, so I only Unload it, if it is open??? TIA, |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if UserForm is loaded?
Whenever you attempt to reference a Form it will load if it is not alrady
loaded. It's not a question of efficiency or lack of it. As you can see by my previous post, I've gotten used to using Load/Unload events with forms since using COM and VB6. As Dave aptly points out, the Initialize event fires whenever a form is referenced. In VB6, the Load event doesn't fire unless you execute it in code. Either way, being a Rob Bovey student I've grown accustomed to using object variables when creating instances of a form/userform because that's what's exampled in most his books. Referencing the variable has different behavior than referencing a VBA userform directly is the lesson I've learned from Dave's suggestion. The problem lies where there's code in the Initialize event, which I rarely use in VB6 projects. Just another interesting difference between VB6 and VBA... <g -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if UserForm is loaded?
There are several differences with VB6 forms, however any attempt to
reference a VB6 Form also loads the form into memory, even say Unload Form1 as with VBA. Also, perhaps counter intuitively, the Unload statement does not fully Unload the VB6 form. If you are not using a variable you might want to do say - Unload Form1 ' to fire the QueryUnload event Set Form1 = Nothing ' trigger the Terminate event Regards, Peter T "GS" wrote in message ... Whenever you attempt to reference a Form it will load if it is not alrady loaded. It's not a question of efficiency or lack of it. As you can see by my previous post, I've gotten used to using Load/Unload events with forms since using COM and VB6. As Dave aptly points out, the Initialize event fires whenever a form is referenced. In VB6, the Load event doesn't fire unless you execute it in code. Either way, being a Rob Bovey student I've grown accustomed to using object variables when creating instances of a form/userform because that's what's exampled in most his books. Referencing the variable has different behavior than referencing a VBA userform directly is the lesson I've learned from Dave's suggestion. The problem lies where there's code in the Initialize event, which I rarely use in VB6 projects. Just another interesting difference between VB6 and VBA... <g -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if UserForm is loaded?
on 9/3/2010, Peter T supposed :
There are several differences with VB6 forms, however any attempt to reference a VB6 Form also loads the form into memory, even say Unload Form1 as with VBA. Also, perhaps counter intuitively, the Unload statement does not fully Unload the VB6 form. If you are not using a variable you might want to do say - Unload Form1 ' to fire the QueryUnload event Set Form1 = Nothing ' trigger the Terminate event I agree with your concept; -setting 'myFormVar = Nothing' is standard cleanup for me. What I was trying to emphasize is that the Initialize event fires whenever a form/userform is referenced, and so using code in that event is where the problem lies in this OP's case. Looking back at some old VBA code before using an object var to hold an instance of a userform, I was using my own 'Initialize' procedure rather than the event, wherein I loaded a userform, did whatever setup tasks I needed to do, then used Show to display it. Therefore, whenever I used the following code to query the userform I never had any problems because the Initialize event was never used.<g If Not Userform1 Is Nothing Then Unload Userform1 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Constantly loaded userform?? | Excel Programming | |||
Check if Workbook fully loaded... | Excel Programming | |||
Testing if Userform loaded | Excel Programming | |||
How check if form is loaded? | Excel Programming | |||
Detecting if a userform is loaded | Excel Programming |