| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correct code to Exit Userform, with out Unloading data
What is the best way to close off a userform without unloading the data.
Userform.hide simply hides the userform, but is this good enough as a close? As it still stores the data in the userform if something was placed there. Corey.... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Correct code to Exit Userform, with out Unloading data
Corey
Hide is the only way to dismiss the form and persist the data, Unload removes it from memory and returns the controls to their design time state and it cannot be read -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... What is the best way to close off a userform without unloading the data. Userform.hide simply hides the userform, but is this good enough as a close? As it still stores the data in the userform if something was placed there. Corey.... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Correct code to Exit Userform, with out Unloading data
So to clear the data that was entered and to use the .hide,
The userform should be coded to clear all values upon Initializing to remove any values that were entered previously if the userform is then re-.shown? Corey.... "Nick Hodge" wrote in message ... Corey Hide is the only way to dismiss the form and persist the data, Unload removes it from memory and returns the controls to their design time state and it cannot be read -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... What is the best way to close off a userform without unloading the data. Userform.hide simply hides the userform, but is this good enough as a close? As it still stores the data in the userform if something was placed there. Corey.... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Correct code to Exit Userform, with out Unloading data
Corey
A normal procedure would be 'UserForm1 initialisation (Either design time or runtime) UserForm1.Show (Shows and loads into memory, fires UserForm_Initialize() event) 'do what you want with the form UserForm1.Hide 'Read all the controls and place or do with them what you will Unload UserForm1 'This removes all the values and renders form unreadable (out of memory) 'Start routine again -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So to clear the data that was entered and to use the .hide, The userform should be coded to clear all values upon Initializing to remove any values that were entered previously if the userform is then re-.shown? Corey.... "Nick Hodge" wrote in message ... Corey Hide is the only way to dismiss the form and persist the data, Unload removes it from memory and returns the controls to their design time state and it cannot be read -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... What is the best way to close off a userform without unloading the data. Userform.hide simply hides the userform, but is this good enough as a close? As it still stores the data in the userform if something was placed there. Corey.... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Correct code to Exit Userform, with out Unloading data
So if you .Hide THEN Unload, the values are not updated into a sheet?
But Unload without .Hide does ? Corey.... "Nick Hodge" wrote in message ... Corey A normal procedure would be 'UserForm1 initialisation (Either design time or runtime) UserForm1.Show (Shows and loads into memory, fires UserForm_Initialize() event) 'do what you want with the form UserForm1.Hide 'Read all the controls and place or do with them what you will Unload UserForm1 'This removes all the values and renders form unreadable (out of memory) 'Start routine again -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So to clear the data that was entered and to use the .hide, The userform should be coded to clear all values upon Initializing to remove any values that were entered previously if the userform is then re-.shown? Corey.... "Nick Hodge" wrote in message ... Corey Hide is the only way to dismiss the form and persist the data, Unload removes it from memory and returns the controls to their design time state and it cannot be read -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... What is the best way to close off a userform without unloading the data. Userform.hide simply hides the userform, but is this good enough as a close? As it still stores the data in the userform if something was placed there. Corey.... |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Correct code to Exit Userform, with out Unloading data
Corey
Hiding keeps the form in memory and therefore the values in any controls (You will have to do something with them, nothing happens automatically). Providing you DON'T unload it you can continue to use the same values, or different ones if you either re-show the form or change the values/properties of controls in code. Once you unload the form, the data in the controls are gone, forever and you can do nothing with them until you re-show the form and reset the values of the controls, etc. -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So if you .Hide THEN Unload, the values are not updated into a sheet? But Unload without .Hide does ? Corey... "Nick Hodge" wrote in message ... Corey A normal procedure would be 'UserForm1 initialisation (Either design time or runtime) UserForm1.Show (Shows and loads into memory, fires UserForm_Initialize() event) 'do what you want with the form UserForm1.Hide 'Read all the controls and place or do with them what you will Unload UserForm1 'This removes all the values and renders form unreadable (out of memory) 'Start routine again -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So to clear the data that was entered and to use the .hide, The userform should be coded to clear all values upon Initializing to remove any values that were entered previously if the userform is then re-.shown? Corey.... "Nick Hodge" wrote in message ... Corey Hide is the only way to dismiss the form and persist the data, Unload removes it from memory and returns the controls to their design time state and it cannot be read -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... What is the best way to close off a userform without unloading the data. Userform.hide simply hides the userform, but is this good enough as a close? As it still stores the data in the userform if something was placed there. Corey.... |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Correct code to Exit Userform, with out Unloading data
Corey
I think the key here is nothing (updating cells, etc) happen automatically, you need to set the values on the sheet from the hidden form, then when you have done this and need the values no longer, unload the form. -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So if you .Hide THEN Unload, the values are not updated into a sheet? But Unload without .Hide does ? Corey.... "Nick Hodge" wrote in message ... Corey A normal procedure would be 'UserForm1 initialisation (Either design time or runtime) UserForm1.Show (Shows and loads into memory, fires UserForm_Initialize() event) 'do what you want with the form UserForm1.Hide 'Read all the controls and place or do with them what you will Unload UserForm1 'This removes all the values and renders form unreadable (out of memory) 'Start routine again -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So to clear the data that was entered and to use the .hide, The userform should be coded to clear all values upon Initializing to remove any values that were entered previously if the userform is then re-.shown? Corey.... "Nick Hodge" wrote in message ... Corey Hide is the only way to dismiss the form and persist the data, Unload removes it from memory and returns the controls to their design time state and it cannot be read -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... What is the best way to close off a userform without unloading the data. Userform.hide simply hides the userform, but is this good enough as a close? As it still stores the data in the userform if something was placed there. Corey.... |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Correct code to Exit Userform, with out Unloading data
If controls are linked to cells, then values are updated when the cells
change - no relation to unload or hide if you use code to write values to cells, then when the code runs the cells are updated. No relation to unload or hide What Nick was say is that a lot of people hide the userform (modal) so the code that called then can continue to run. In that code, there is code to utililize the values in the controls of the userform such as v = Userform1.Textbox1.Value when done extracting values, then the userform is unloaded. If you don't need to access any values from the calling code, then by all means, unload the userform to close it rather than using the hide command. Understand what each method done, then utilize them appropriately to achieve the results you need. -- regards, Tom Ogilvy "Corey" wrote in message ... So if you .Hide THEN Unload, the values are not updated into a sheet? But Unload without .Hide does ? Corey.... "Nick Hodge" wrote in message ... Corey A normal procedure would be 'UserForm1 initialisation (Either design time or runtime) UserForm1.Show (Shows and loads into memory, fires UserForm_Initialize() event) 'do what you want with the form UserForm1.Hide 'Read all the controls and place or do with them what you will Unload UserForm1 'This removes all the values and renders form unreadable (out of memory) 'Start routine again -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So to clear the data that was entered and to use the .hide, The userform should be coded to clear all values upon Initializing to remove any values that were entered previously if the userform is then re-.shown? Corey.... "Nick Hodge" wrote in message ... Corey Hide is the only way to dismiss the form and persist the data, Unload removes it from memory and returns the controls to their design time state and it cannot be read -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... What is the best way to close off a userform without unloading the data. Userform.hide simply hides the userform, but is this good enough as a close? As it still stores the data in the userform if something was placed there. Corey.... |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Correct code to Exit Userform, with out Unloading data
Tom
Thanks...it's late in the day here, I was having trouble explaining...hopefully Corey has it now -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Tom Ogilvy" wrote in message ... If controls are linked to cells, then values are updated when the cells change - no relation to unload or hide if you use code to write values to cells, then when the code runs the cells are updated. No relation to unload or hide What Nick was say is that a lot of people hide the userform (modal) so the code that called then can continue to run. In that code, there is code to utililize the values in the controls of the userform such as v = Userform1.Textbox1.Value when done extracting values, then the userform is unloaded. If you don't need to access any values from the calling code, then by all means, unload the userform to close it rather than using the hide command. Understand what each method done, then utilize them appropriately to achieve the results you need. -- regards, Tom Ogilvy "Corey" wrote in message ... So if you .Hide THEN Unload, the values are not updated into a sheet? But Unload without .Hide does ? Corey.... "Nick Hodge" wrote in message ... Corey A normal procedure would be 'UserForm1 initialisation (Either design time or runtime) UserForm1.Show (Shows and loads into memory, fires UserForm_Initialize() event) 'do what you want with the form UserForm1.Hide 'Read all the controls and place or do with them what you will Unload UserForm1 'This removes all the values and renders form unreadable (out of memory) 'Start routine again -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So to clear the data that was entered and to use the .hide, The userform should be coded to clear all values upon Initializing to remove any values that were entered previously if the userform is then re-.shown? Corey.... "Nick Hodge" wrote in message ... Corey Hide is the only way to dismiss the form and persist the data, Unload removes it from memory and returns the controls to their design time state and it cannot be read -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... What is the best way to close off a userform without unloading the data. Userform.hide simply hides the userform, but is this good enough as a close? As it still stores the data in the userform if something was placed there. Corey.... |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Correct code to Exit Userform, with out Unloading data
Nick,
If i complete the userform the values selected are input into a sheet. Therefore if part way through the userform, i decide to cancel the userform. IF i: userform.hide unload me Still unload what data WAS entered or as it was Hiden are the values then Deleted ? Corey.... "Nick Hodge" wrote in message ... Corey Hiding keeps the form in memory and therefore the values in any controls (You will have to do something with them, nothing happens automatically). Providing you DON'T unload it you can continue to use the same values, or different ones if you either re-show the form or change the values/properties of controls in code. Once you unload the form, the data in the controls are gone, forever and you can do nothing with them until you re-show the form and reset the values of the controls, etc. -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So if you .Hide THEN Unload, the values are not updated into a sheet? But Unload without .Hide does ? Corey... "Nick Hodge" wrote in message ... Corey A normal procedure would be 'UserForm1 initialisation (Either design time or runtime) UserForm1.Show (Shows and loads into memory, fires UserForm_Initialize() event) 'do what you want with the form UserForm1.Hide 'Read all the controls and place or do with them what you will Unload UserForm1 'This removes all the values and renders form unreadable (out of memory) 'Start routine again -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So to clear the data that was entered and to use the .hide, The userform should be coded to clear all values upon Initializing to remove any values that were entered previously if the userform is then re-.shown? Corey.... "Nick Hodge" wrote in message ... Corey Hide is the only way to dismiss the form and persist the data, Unload removes it from memory and returns the controls to their design time state and it cannot be read -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... What is the best way to close off a userform without unloading the data. Userform.hide simply hides the userform, but is this good enough as a close? As it still stores the data in the userform if something was placed there. Corey.... |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Correct code to Exit Userform, with out Unloading data
Corey
If the controls allow linked cells (ControlSource Property in a comboBox for example) then the value will enter automatically on the sheet and they will not be destroyed when you unload the form, equally if you have explicitly placed the data on the sheet using code, then that data will persist after the form is unloaded/hidden or whatever. If you try and get the values after the form has been unloaded they will be gone. The worksheet will still show the data though -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... Nick, If i complete the userform the values selected are input into a sheet. Therefore if part way through the userform, i decide to cancel the userform. IF i: userform.hide unload me Still unload what data WAS entered or as it was Hiden are the values then Deleted ? Corey.... "Nick Hodge" wrote in message ... Corey Hiding keeps the form in memory and therefore the values in any controls (You will have to do something with them, nothing happens automatically). Providing you DON'T unload it you can continue to use the same values, or different ones if you either re-show the form or change the values/properties of controls in code. Once you unload the form, the data in the controls are gone, forever and you can do nothing with them until you re-show the form and reset the values of the controls, etc. -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So if you .Hide THEN Unload, the values are not updated into a sheet? But Unload without .Hide does ? Corey... "Nick Hodge" wrote in message ... Corey A normal procedure would be 'UserForm1 initialisation (Either design time or runtime) UserForm1.Show (Shows and loads into memory, fires UserForm_Initialize() event) 'do what you want with the form UserForm1.Hide 'Read all the controls and place or do with them what you will Unload UserForm1 'This removes all the values and renders form unreadable (out of memory) 'Start routine again -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So to clear the data that was entered and to use the .hide, The userform should be coded to clear all values upon Initializing to remove any values that were entered previously if the userform is then re-.shown? Corey.... "Nick Hodge" wrote in message ... Corey Hide is the only way to dismiss the form and persist the data, Unload removes it from memory and returns the controls to their design time state and it cannot be read -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... What is the best way to close off a userform without unloading the data. Userform.hide simply hides the userform, but is this good enough as a close? As it still stores the data in the userform if something was placed there. Corey.... |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Correct code to Exit Userform, with out Unloading data
when done extracting values, then the userform is unloaded.
AFAIK if the form is merely hidden the form as an object together with its properties will stay in memory until explicitly unloaded even after the procedure that show'd the modal form ends, at least in xl2000 and later (in xl97, if hidden, it unloads when the procedure that show'd the modal form ends). I may be on a similar time zone to Nick and it's late for me too, apologies if I've misinterpreted what you are saying. Regards, Peter T "Tom Ogilvy" wrote in message ... If controls are linked to cells, then values are updated when the cells change - no relation to unload or hide if you use code to write values to cells, then when the code runs the cells are updated. No relation to unload or hide What Nick was say is that a lot of people hide the userform (modal) so the code that called then can continue to run. In that code, there is code to utililize the values in the controls of the userform such as v = Userform1.Textbox1.Value when done extracting values, then the userform is unloaded. If you don't need to access any values from the calling code, then by all means, unload the userform to close it rather than using the hide command. Understand what each method done, then utilize them appropriately to achieve the results you need. -- regards, Tom Ogilvy "Corey" wrote in message ... So if you .Hide THEN Unload, the values are not updated into a sheet? But Unload without .Hide does ? Corey.... "Nick Hodge" wrote in message ... Corey A normal procedure would be 'UserForm1 initialisation (Either design time or runtime) UserForm1.Show (Shows and loads into memory, fires UserForm_Initialize() event) 'do what you want with the form UserForm1.Hide 'Read all the controls and place or do with them what you will Unload UserForm1 'This removes all the values and renders form unreadable (out of memory) 'Start routine again -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So to clear the data that was entered and to use the .hide, The userform should be coded to clear all values upon Initializing to remove any values that were entered previously if the userform is then re-.shown? Corey.... "Nick Hodge" wrote in message ... Corey Hide is the only way to dismiss the form and persist the data, Unload removes it from memory and returns the controls to their design time state and it cannot be read -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... What is the best way to close off a userform without unloading the data. Userform.hide simply hides the userform, but is this good enough as a close? As it still stores the data in the userform if something was placed there. Corey.... |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Correct code to Exit Userform, with out Unloading data
Thanks.
It is not 100% clear to me,But clearer than before i posted. Corey.... "Nick Hodge" wrote in message ... Corey If the controls allow linked cells (ControlSource Property in a comboBox for example) then the value will enter automatically on the sheet and they will not be destroyed when you unload the form, equally if you have explicitly placed the data on the sheet using code, then that data will persist after the form is unloaded/hidden or whatever. If you try and get the values after the form has been unloaded they will be gone. The worksheet will still show the data though -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... Nick, If i complete the userform the values selected are input into a sheet. Therefore if part way through the userform, i decide to cancel the userform. IF i: userform.hide unload me Still unload what data WAS entered or as it was Hiden are the values then Deleted ? Corey.... "Nick Hodge" wrote in message ... Corey Hiding keeps the form in memory and therefore the values in any controls (You will have to do something with them, nothing happens automatically). Providing you DON'T unload it you can continue to use the same values, or different ones if you either re-show the form or change the values/properties of controls in code. Once you unload the form, the data in the controls are gone, forever and you can do nothing with them until you re-show the form and reset the values of the controls, etc. -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So if you .Hide THEN Unload, the values are not updated into a sheet? But Unload without .Hide does ? Corey... "Nick Hodge" wrote in message ... Corey A normal procedure would be 'UserForm1 initialisation (Either design time or runtime) UserForm1.Show (Shows and loads into memory, fires UserForm_Initialize() event) 'do what you want with the form UserForm1.Hide 'Read all the controls and place or do with them what you will Unload UserForm1 'This removes all the values and renders form unreadable (out of memory) 'Start routine again -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So to clear the data that was entered and to use the .hide, The userform should be coded to clear all values upon Initializing to remove any values that were entered previously if the userform is then re-.shown? Corey.... "Nick Hodge" wrote in message ... Corey Hide is the only way to dismiss the form and persist the data, Unload removes it from memory and returns the controls to their design time state and it cannot be read -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... What is the best way to close off a userform without unloading the data. Userform.hide simply hides the userform, but is this good enough as a close? As it still stores the data in the userform if something was placed there. Corey.... |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Correct code to Exit Userform, with out Unloading data
I said nothing about that. But thanks for the additional insights.
-- Regards, Tom Ogilvy "Peter T" <peter_t@discussions wrote in message ... when done extracting values, then the userform is unloaded. AFAIK if the form is merely hidden the form as an object together with its properties will stay in memory until explicitly unloaded even after the procedure that show'd the modal form ends, at least in xl2000 and later (in xl97, if hidden, it unloads when the procedure that show'd the modal form ends). I may be on a similar time zone to Nick and it's late for me too, apologies if I've misinterpreted what you are saying. Regards, Peter T "Tom Ogilvy" wrote in message ... If controls are linked to cells, then values are updated when the cells change - no relation to unload or hide if you use code to write values to cells, then when the code runs the cells are updated. No relation to unload or hide What Nick was say is that a lot of people hide the userform (modal) so the code that called then can continue to run. In that code, there is code to utililize the values in the controls of the userform such as v = Userform1.Textbox1.Value when done extracting values, then the userform is unloaded. If you don't need to access any values from the calling code, then by all means, unload the userform to close it rather than using the hide command. Understand what each method done, then utilize them appropriately to achieve the results you need. -- regards, Tom Ogilvy "Corey" wrote in message ... So if you .Hide THEN Unload, the values are not updated into a sheet? But Unload without .Hide does ? Corey.... "Nick Hodge" wrote in message ... Corey A normal procedure would be 'UserForm1 initialisation (Either design time or runtime) UserForm1.Show (Shows and loads into memory, fires UserForm_Initialize() event) 'do what you want with the form UserForm1.Hide 'Read all the controls and place or do with them what you will Unload UserForm1 'This removes all the values and renders form unreadable (out of memory) 'Start routine again -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... So to clear the data that was entered and to use the .hide, The userform should be coded to clear all values upon Initializing to remove any values that were entered previously if the userform is then re-.shown? Corey.... "Nick Hodge" wrote in message ... Corey Hide is the only way to dismiss the form and persist the data, Unload removes it from memory and returns the controls to their design time state and it cannot be read -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Corey" wrote in message ... What is the best way to close off a userform without unloading the data. Userform.hide simply hides the userform, but is this good enough as a close? As it still stores the data in the userform if something was placed there. Corey.... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Userform to grab,correct and restore data | Excel Programming | |||
| Getting error unloading Userform?? | Excel Programming | |||
| Unloading UserForm not working | Excel Programming | |||
| Unloading userform in MSComm event | Excel Programming | |||
| Not Unloading a UserForm | Excel Programming | |||
Linear Mode
