I have developed a userform in EXCEL 2016 for PC. If the user launches the userform (from a button my add-in adds to the ribbon) from the same workbook/worksheet as the last launch or show, then I want the userform to remember all the users settings from the last use. Therefore, when the user is finished with a particular use, I simply hide the form. This works well so far.
HOWEVER, if the user shows the userform subsequently from a different worksheet or workbook, then EXCEL "snaps" back to the original workbook/worksheet the userform was launched from. In this case, I want the userform to reset and appear in the "new" active workbook/worksheet.
I can imagine pseudocode like this:
Public wb0 As Workbook
Public ws0 As Worksheet
' IF the userform is being used for the 1st time
Public Sub UserForm_Initialize()
yada yada yada...
wb0 = current workbook
ws0 = current worksheet
End Sub
Private Sub UserForm_Activate()
If wb0 <> ActiveWorkbook OR ws0 <> ActiveWorkSheet Then
(**Force the userform to open in the current workbook/worksheet**)
Call Reset
wb0 = current workbook
ws0 = current worksheet
End If
End Sub
However, I am not sure how to:
1) Test whether the current active workbook and worksheet are the same as the active workbook and worksheet from the last use. Should I set wb0 to the Name property of the ActiveWorkbook?
wb0 = ActiveWorkbook.Name
And then the test would be:
If wb0 <> ActiveWorkbook.Name ...
2) Also, not sure how to force the userform to open in the current worksheet.
====
Or, perhaps it would be best to have something like:
Private Sub UserForm_Activate()
If wb0 <> ActiveWorkbook OR ws0 <> ActiveWorkSheet Then
Unload userform
Reload userform
End If
End Sub
That way everything would be automatically reset...
====
Can someone please advise?
Thanks!!
Dan