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

 

[mikerickson]

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 ?

Because I made a typo. Activate will be at 3, 5 & 8

 

[Norie]

If you are worried about a variable losing it's value why not store it somewhere else where it won't lose it's value, for example an ini file.

 

[snb_]

You can store anything you like in customdocumentproperties:
It makes any .ini file redundant.

Sub M_snb()
    ThisWorkbook.CustomDocumentProperties.Add "snb_001", False, 2, True
    MsgBox ThisWorkbook.CustomDocumentProperties("snb_001")
    ThisWorkbook.CustomDocumentProperties("snb_001") = False
    MsgBox ThisWorkbook.CustomDocumentProperties("snb_001")
End Sub

 

[AlexanderBB]

Thanks Rick, all understood and tests show it's all working 100% now. Relatively easy fix but it took a while to sort as it showed up after
some major changes which turned out to be unrelated. But of course, were blamed first !!
Norie, I think we're going round in circles - it's already in an ini file.

I had a quick look at CustomDocumentProperties but Excel (2003) gave no Help with F1 nor did it provide any balloon tips for the various arguments.
I didn't quite see how it might replace 50 differnet items, some are integer or strings, not just booleans. Maybe the way I've done things isn't optimum
and I was thinking of storing them in an Access table as there's an existing mdb file used for other things. It's a big change and it is working OK so I dunno....

Thanks for the idea though and all the responses.

Cheers, ABB