Problem Opening Form

[AlexanderBB]

I've struck a bit of a nasty. It's taken a few days to isolate the problem but I'm not sure how to fix it.

It's starts in my Worksheet change event. This reads a checkbox in an unloaded Form to determine whether to run the change event code, or not.

The line is " If frmConfig.chkAutoEdit = true then..."

On stepping through with F8 you can see this runs frmConfig UserForm_Initialize(). I don't want this so have a Global Boolean variable set True and the first line there is "If EnterInhibit = True Then Exit Sub." (This is later set back to False.)

So far so good, but then the problem starts. The next time frmConfig is Opened (with command "frmConfig.Show") UserForm_Initialize() does not run. Stepping through shows it doesn't even try! The form still opens but because Initialise hasn't run it's all a mess.

It isn't anything to do with my Global boolean as that's not checked.

It's like my reading chkAutoEdit has flagged the Initialise event to not run the next time it should.

Is there a reason for this behaviour and what would be the best fix or workaround, please.

Thanks for any help. I need it !!!

Rgds, ABB

 

[Norie]

Why are you reading a value from an unloaded form?

When you do that the form is loaded and, as you've found out, it's Initialize event is triggered.

Does the checkbox even have a value when the form is unloaded, or is it always False?

 

[mikerickson]

I'm not clear what you want the user form to do, but have you considered moving some of your start up code to the Activate event.
Intialize will run the first time that a user form is referenced (e.g. UserForm1.Tag = "x")
Activate will run every time that the user form is Shown.

 

[AlexanderBB]

Thanks for the replies!!

Norie, I read the value from the unloaded form because that's where it is. It will always be either False or True and that's set by an ini file that's read in Workbook_Open.
I don't want the Form to Open, I just want to know the checkbox state.

Mike "Intialize will run the first time that a user form is referenced" - does that mean it will not (should not) run the next time the form is referenced ?
I'm just about to move it all to Activate and see how we go....

 

[Norie]

Why don't you read the value from the ini file?

 

[AlexanderBB]

Why don't you read the value from the ini file?

I do Norrie... bit of course the User may want to change it (or toggle it momentarily) so I store it as a checkbox value. Isn't that what they're for ??

 

[mikerickson]

does that mean it will not (should not) run the next time the form is referenced ?

In the following pseudo code, the Intialize event will fire during line 1 and during line 7 , before the .Tag gets it's new value. It will not fire at line 2,3, 4, 5, 6 or 8
The Activate code will run at line 2, again at line 4 and again at line 8

1: UserForm1.Tag = "smith"
2: UserForm1.Caption = "hello"
3: UserForm1.Show
4: UserForm1.Hide
5: UserForm1.Show
6: Unload UserForm1

7: UserForm1.Tag = "jones"
8: UserForm1.Show

If you want the Intialize event to run at other times, you can declare it a Public Sub and use the line

Userform1.UserForm_Initalize

either in the user form's code module or from any other module.

 

[AlexanderBB]

Mike, your suggestion about Activate seems to have nicely sorted the problem, thank you.
But your example code has confused me a bit.
You say The Activate code will run at line 2, again at line 4 and again at line 8. Why does
it not fire on Line 3 ? Which is the same as lines 5 & 8. .Show is what I'm using always *always" want Activate to fire.
Might it not under some circumstances ?

 

[Norie]

Controls aren't really meant to store values, that's why you need to save their values somewhere, for example in an ini file.

Why don't you store the value of the checkbox in a global variable?

You could change the value of that variable whenever the user changes the value of the checkbox and when you want the value you can get it from the global variable rather then the checkbox.

 

[AlexanderBB]

Norie, I haven't done it that way, and it's a bit late to change. How solid is a Global variable. Can it 'lose' it's value if e.g you're debugging or designing code on-the-fly ?