Unload UserForm resets project

[scottdk]

When I use Unload Userform, my VBA Project appears to be reset and any global variables lose thier values, whereas if I call Me.Hide they remain intact. Is there a reason for this? (There are no unhandled errors)

I'm developing an Excel addin with a right click menu to bring up a form and process the value in the cell which was right clicked in any open workbook, so I initialise the variable APP on workbook open (and also reinitialize it whenever my forms close but my APP_SheetBeforeRightClick event still only fires every second time!)

[vba]Public WithEvents APP As Application
Public g_RightClickValue As String
Public g_StartupValue As String

Private Sub Workbook_Open()
Set APP = Application
g_StartupValue Value = Now()
End Sub

Private Sub APP_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
g_RightClickValue = Target.value
'Set up right click menu
'......
end sub[/vba] I discovered the reason while I was preparing an example to post here, and as such my workaround is to 1) Cancel "UserForm_QueryClose" and 2) always use "Me.Hide" rather than "Unload Me". Wouldn't it be valid to want to unload a form and clear it from Memory without resetting the VBA project? Am I doing something wrong? I have attached an example spreadsheet to demonstrate this.

Excel Help implies the memory used by the form is reclaimed, but not the workbook's variables:

When an object is unloaded, it's removed from memory and all memory associated with the object is reclaimed. Until it is placed in memory again using the Load statement, a user can't interact with an object, and the object can't be manipulated programmatically.

I can live with using Me.Hide, but I hope my question will help somebody else who is stumped by it, and an enlightened person will explain why...

[scottdk]

I can live with using Me.Hide

But life is tougher.

I have to reinitialize every control and variable every time I show the form after I close it with Me.Hide. I create a lot of option buttons with event classes (it's a questionnaire, and I tally up the answers using event classes that now need to be killed). Previously when I closed the form with Unload Me, every time I opened it it was squeaky clean. Maybe there IS a good reason to remove the form from memory every time with Unload Me. But how can I keep my APP workbook variable capturing events?

[Aflatoon]

Your sample works perfectly for me, as I would expect. If I right click a cell that is not empty, the button is added. I click it and the form pops up. I unload it. Right-click an empty cell and the button is not there. Right-click a populated cell, and the button reappears.

I would suggest however that forms should not unload themselves anyway - the calling code should create them using a variable and handle disposing of them as well.

[scottdk]

Thanks Aflatoon. Did you notice that if you right click on a non-empty cell every time (and always select Unload), then the rightclick button shows the correct text only every second time?

Is it possible using a UserForm variable to completely unload the form from memory but still keep the APP (Excel Application) variable initialized in order to always capture the current Right Click event in any open workbook?

[Aflatoon]

Thanks Aflatoon. Did you notice that if you right click on a non-empty cell every time (and always select Unload), then the rightclick button shows the correct text only every second time?

No. Do you have the VBIDE visible when running this? If so, close it, then try again.

Is it possible using a UserForm variable to completely unload the form from memory but still keep the APP (Excel Application) variable initialized in order to always capture the current Right Click event in any open workbook?

I would use something like:
[vba]
Dim frm As frmTest
Set frm = New frmTest
Debug.Print Now(), "LoadTest", g_TestValue, g_AnotherValue

frm.lblTest = g_TestValue
frm.lblAnotherValue = g_AnotherValue
frm.Show
Set frm = Nothing
[/vba]

[mikerickson]

Are all the Public variables declared in a normal module, rather than a sheet or userform's code module?