Waiting for Input

**magness** · 01-13-2010, 01:14 PM

On the SetNum sheet is an ordered list of data sets. (the first set is set 0) The data itself is stored on a different sheet (Data). I would like the user to click the ModifySet button, enter a number, and have the form pop up with the previously entered data.

The SetNum control is an invisible label used for tracking the current set number. Its default value is 0.

The code for the button:

Please Login or Register  to view this content.

In theory, this is what should be pulling of the previous data entered. All of the controls have a corresponding named column in "Data". On Error is included since not all controls (such as labels) need/have their own column.

Please Login or Register  to view this content.

Here is what I get from the two Debug.Print commands.

Please Login or Register  to view this content.

What I get is the UserForm coming up after I've input the SetNum with a SetNum.Caption of the correct value, but the data is always from Set0 (the default value for SetNum.Caption)

Am I just overlooking something?

Thanks,
Magness

**Leith Ross** · 01-13-2010, 02:00 PM

Hello Magness,

You are attempting to assign values to the UserForm before the UserForm has been loaded into memory. The Show command loads the UserForm into memory and then displays it. The Load command will place the UserForm into memory but not display the form. This allows for the properties and variables on the sheet to be set before displaying the form.

Please Login or Register  to view this content.

**magness** · 01-13-2010, 03:03 PM

I had tried this (and did so again just now), but get the same results.

My button for adding a new set has this:

Please Login or Register  to view this content.

And the set number is passing to label caption without Load SetForm here.

Would Load SetForm let me refer to controls without adding the SetFrom. to the front?

**Leith Ross** · 01-13-2010, 03:43 PM

Hello Magness,

You should post your workbook for review.

**magness** · 01-13-2010, 03:59 PM

Here you go. Thanks for taking a look at it. I apologize in advance for my naming conventions (or lack there of).

**Leith Ross** · 01-13-2010, 06:42 PM

Hello Magness,

I moved the variable "SetNum" from the General Declarations section of the UserForm to the top of Module1. This way it is visible to all parts of the project code as long as VBA is running.

Please Login or Register  to view this content.

**magness** · 01-14-2010, 09:37 AM

And it worked for you? I'm getting the same issue.

As far as I can tell UserForm_Initialize() is running as soon as anything mentions SetForm. (setting a value, loading, ect...) I'll try moving the _Initialize code to each of the button to work around it, but I had thought that _Initialize wouldn't run until the UserForm was called. Is this incorrect?

**magness** · 01-14-2010, 11:44 AM

Ok, I moved the code out of UserForm_Initialize() to it's own procedure and it works fine. I just call it from the command buttons that need to populate the form.

The question that remains is precisely what triggers UserForm_Initialize()? That seems to have been the root cause and a better understanding of that I think would fully solve this issue. Should I post a new thread for this question?

**Leith Ross** · 01-14-2010, 02:49 PM

Hello Magness,

Whenever a UserForm is loaded into memory, the Initialize event is fired. Both the Load and Show commands will load the UserForm object into memory. The key difference is Show checks if the UserForm is already loaded. If not it will load the UserForm and then display it. The purpose of this event is to setup control properties, and assign static or initial value settings. This event is followed by the Activate event.

**magness** · 01-14-2010, 04:31 PM

Ok, that is what I had thought. Then I guess that by trying to assign a value in the UserForm with "SetForm.SetNum.Caption = " was loading the form and triggering the Initialize even though I didn't specifically use the Load command.

Thanks