3

How do I keep the values that where inputted into the excel Userform from clearing itself out once I click on the finish button? So whenever I call upon the Userform through a Commandbutton the previously filled field information is left the way it is and not to be cleared basically.

This Userform consist of MultiPage which is designed to take in the inputs from the user and place them in excel cells once the user has hit the finish button. Within the Userform contains Commandbuttons which toggles an hide/unhide feature which will also have to be saved somewhere

The Finish page (where the user will close/exit the page) looks like this: enter image description here

Code for the finish button so far:

enter image description here

GoldFusion
  • 149
  • 1
  • 14
  • My trick? Keep them on a hidden worksheet. – Damian Mar 08 '19 at 18:16
  • So is there no way for the userform to store the information by itself? – GoldFusion Mar 08 '19 at 18:17
  • Not reliably. It'll get refreshed by all sorts of Excel actions, such as saving a workbook with changes. – Josh Eller Mar 08 '19 at 18:18
  • you can keep them on variables while the macro is working, but once its finished... there is no way that I know – Damian Mar 08 '19 at 18:19
  • 2
    The form itself can't store data. You can save in a sheet as suggested above, in a separate file, in the registry, in a database or several other places. Hidden sheet or the registry are the simplest and most efficient. – Sam Mar 08 '19 at 18:20
  • 1
    However, if you're asking how to keep the values available for the short period between the Finish click and vba shifting those values to a worksheet, that may be a different question. – Mistella Mar 08 '19 at 18:22
  • Just use global variables...??? They're in scope as long as the workbook is open. – Absinthe Mar 08 '19 at 18:27
  • @Absinthe A global variable would be lost at the end of run-time... no? – dwirony Mar 08 '19 at 18:30
  • @dwirony Ok sorry, to expand; I meant store all the form values in globals & repop on form load. The globals remain as long as the workbook containing the form is open. – Absinthe Mar 08 '19 at 18:33

1 Answers1

4

I'm not sure why everyone is saying there is no way to do this - you should be able to use:

Private Sub BtnFinish_Click()
    Me.Hide
End Sub

And all previous values will be kept. Someone correct me if I'm wrong :).

I should add that once the EXCEL.EXE is closed, the values are lost.

dwirony
  • 5,487
  • 3
  • 21
  • 43
  • I think the misunderstanding is: does the OP what to use the userform to keep variables between the parent workbook being opened/closed/etc. ; between closing the userform and saving the values to a worksheet ; or maybe being able to reopen the userform with the prior values? – Mistella Mar 08 '19 at 18:24
  • @Mistella Yes I think that might be the case too - OP needs to clarify what he is trying to do. – dwirony Mar 08 '19 at 18:26
  • 1
    Hiding the form will work... unless the user clicks that pesky [X] button, which would destroy the form instance and its state. Make sure you handle the `QueryClose` event and set the `Cancel` argument to `True` to prevent that when the `CloseMode` is `VbQueryClose.vbFormControlMenu`: you'll also want to do `Me.Hide` then. – Mathieu Guindon Mar 08 '19 at 18:42
  • 1
    @MathieuGuindon I was trying to find your blog where you talk about `Unload Me` vs `Me.Hide` - could you share that link here? – dwirony Mar 08 '19 at 20:02
  • FWIW I was tempted to post a way-overboard answer here that suggested pulling the entire state out of the form's code-behind and into separate "model" classes (one per tab), but the OP does't have nearly enough information about their model for that.. and I need to post less on SO and more on my blog! ;-) – Mathieu Guindon Mar 08 '19 at 20:04
  • @dwirony :-) FYI a related follow up link dealing with modeless Userforms at SO: [Destroy a modeless Userform instance properly](https://stackoverflow.com/questions/47357708/vba-destroy-a-modeless-userform-instance-properly) – T.M. Mar 21 '19 at 10:18