Userform 'loses' array info? What could cause this?

Userform 'loses' array info? What could cause this?

I have a series of userforms in Excel97. One in particular has a bunch of
radiobuttons and comboboxes that are populated dynamically as the user
interacts with the form. For example, when the user selects one of 7
radiobuttons in frameA, that populates the text on the radiobuttons in
frameB, and when one of those is selected, it populates the text on
radiobuttons in frameC, and so on.

On occassion, the form "loses" all of the text that should be populating
those buttons (and other controls as well). The userform has the default
text from design view in the VBE, and clicking on the form changes the state
of the radiobuttons, but the buttons aren't populated with the text.

This only happens every once in a while, and always seems to be related to
either (a) closing and opening of the userform while working in the
document, and/or (b) switching between applications. It doesn't happen often
enough for me to have anything more than antecdotal evidence of when it
occurs- I'm having trouble even replicating the problem.

Has anyone else ever had this problem, and if so, what types of things
should I be checking for in my code to prevent this from happening?

Thanks,
Keith

-- 
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

date: Thu, 8 Jun 2006 08:30:32 -0400 author: KR

Re: Userform 'loses' array info? What could cause this?

Hi KR,

This group deals with VBA in Word. If you are dealing with Excel, you will 
have a better chance of getting an expert reply if you post to 
microsoft.public.excel.programming

-- 
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

"KR"  wrote in message 
news:%23KPmWdviGHA.2456@TK2MSFTNGP04.phx.gbl...
>I have a series of userforms in Excel97. One in particular has a bunch of
> radiobuttons and comboboxes that are populated dynamically as the user
> interacts with the form. For example, when the user selects one of 7
> radiobuttons in frameA, that populates the text on the radiobuttons in
> frameB, and when one of those is selected, it populates the text on
> radiobuttons in frameC, and so on.
>
> On occassion, the form "loses" all of the text that should be populating
> those buttons (and other controls as well). The userform has the default
> text from design view in the VBE, and clicking on the form changes the 
> state
> of the radiobuttons, but the buttons aren't populated with the text.
>
> This only happens every once in a while, and always seems to be related to
> either (a) closing and opening of the userform while working in the
> document, and/or (b) switching between applications. It doesn't happen 
> often
> enough for me to have anything more than antecdotal evidence of when it
> occurs- I'm having trouble even replicating the problem.
>
> Has anyone else ever had this problem, and if so, what types of things
> should I be checking for in my code to prevent this from happening?
>
> Thanks,
> Keith
>
> -- 
> The enclosed questions or comments are entirely mine and don't represent 
> the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>

date: Thu, 8 Jun 2006 13:57:39 +0100 author: Jonathan West

Re: Userform 'loses' array info? What could cause this?

Sorry for the confusion- the file I described was Word97, not Excel- I just
spend a lot more time working in Excel, so it was just a slip up while
typing.

So the question stands; any idea what could cause a Word <g> userform/VBA to
lose arrays in memory?


"Jonathan West"  wrote in message
news:eZHhM7viGHA.3496@TK2MSFTNGP02.phx.gbl...
> Hi KR,
>
> This group deals with VBA in Word. If you are dealing with Excel, you will
> have a better chance of getting an expert reply if you post to
> microsoft.public.excel.programming
>
> -- 
> Regards
> Jonathan West - Word MVP
> www.intelligentdocuments.co.uk
> Please reply to the newsgroup
> Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org
>
> "KR"  wrote in message
> news:%23KPmWdviGHA.2456@TK2MSFTNGP04.phx.gbl...
> >I have a series of userforms in Excel97. One in particular has a bunch of
> > radiobuttons and comboboxes that are populated dynamically as the user
> > interacts with the form. For example, when the user selects one of 7
> > radiobuttons in frameA, that populates the text on the radiobuttons in
> > frameB, and when one of those is selected, it populates the text on
> > radiobuttons in frameC, and so on.
> >
> > On occassion, the form "loses" all of the text that should be populating
> > those buttons (and other controls as well). The userform has the default
> > text from design view in the VBE, and clicking on the form changes the
> > state
> > of the radiobuttons, but the buttons aren't populated with the text.
> >
> > This only happens every once in a while, and always seems to be related
to
> > either (a) closing and opening of the userform while working in the
> > document, and/or (b) switching between applications. It doesn't happen
> > often
> > enough for me to have anything more than antecdotal evidence of when it
> > occurs- I'm having trouble even replicating the problem.
> >
> > Has anyone else ever had this problem, and if so, what types of things
> > should I be checking for in my code to prevent this from happening?
> >
> > Thanks,
> > Keith
> >
> > -- 
> > The enclosed questions or comments are entirely mine and don't represent
> > the
> > thoughts, views, or policy of my employer. Any errors or omissions are
my
> > own.
> >
> >
>

date: Thu, 8 Jun 2006 14:40:07 -0400 author: KR

Re: Userform 'loses' array info? What could cause this?

Hi KR,

Without the code and without any kind of repeatable sequence of steps that 
causes it, anything I suggest will be pure speculation.

1. Do you have code which clears the array? Have you tracked down all the 
circumstances in which it might be called?

2. How do you open and close the form? Do you have multiple copies of it 
open at any time?

3. How are you declaring the array that contains the text to be used? How 
are you assigning the values to the array? Are you sure that the array 
containing the correct values is always available to the routines that are 
putting the values into the captions of the controls?

-- 
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

"KR"  wrote in message 
news:upyx4ryiGHA.1320@TK2MSFTNGP04.phx.gbl...
> Sorry for the confusion- the file I described was Word97, not Excel- I 
> just
> spend a lot more time working in Excel, so it was just a slip up while
> typing.
>
> So the question stands; any idea what could cause a Word <g> userform/VBA 
> to
> lose arrays in memory?
>
>
> "Jonathan West"  wrote in message
> news:eZHhM7viGHA.3496@TK2MSFTNGP02.phx.gbl...
>> Hi KR,
>>
>> This group deals with VBA in Word. If you are dealing with Excel, you 
>> will
>> have a better chance of getting an expert reply if you post to
>> microsoft.public.excel.programming
>>
>> -- 
>> Regards
>> Jonathan West - Word MVP
>> www.intelligentdocuments.co.uk
>> Please reply to the newsgroup
>> Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org
>>
>> "KR"  wrote in message
>> news:%23KPmWdviGHA.2456@TK2MSFTNGP04.phx.gbl...
>> >I have a series of userforms in Excel97. One in particular has a bunch 
>> >of
>> > radiobuttons and comboboxes that are populated dynamically as the user
>> > interacts with the form. For example, when the user selects one of 7
>> > radiobuttons in frameA, that populates the text on the radiobuttons in
>> > frameB, and when one of those is selected, it populates the text on
>> > radiobuttons in frameC, and so on.
>> >
>> > On occassion, the form "loses" all of the text that should be 
>> > populating
>> > those buttons (and other controls as well). The userform has the 
>> > default
>> > text from design view in the VBE, and clicking on the form changes the
>> > state
>> > of the radiobuttons, but the buttons aren't populated with the text.
>> >
>> > This only happens every once in a while, and always seems to be related
> to
>> > either (a) closing and opening of the userform while working in the
>> > document, and/or (b) switching between applications. It doesn't happen
>> > often
>> > enough for me to have anything more than antecdotal evidence of when it
>> > occurs- I'm having trouble even replicating the problem.
>> >
>> > Has anyone else ever had this problem, and if so, what types of things
>> > should I be checking for in my code to prevent this from happening?
>> >
>> > Thanks,
>> > Keith
>> >
>> > -- 
>> > The enclosed questions or comments are entirely mine and don't 
>> > represent
>> > the
>> > thoughts, views, or policy of my employer. Any errors or omissions are
> my
>> > own.
>> >
>> >
>>
>
>

date: Wed, 14 Jun 2006 10:27:27 +0100 author: Jonathan West

Re: Userform 'loses' array info? What could cause this? (New info)

Excellent questions; I'll try to respond as accurately and completely as I
can (at least without posting all the code, which would be really, really
long). My new piece of information is that I think I've figured out a 100%
correllation with this problem; I have a close/exit command button on my
userform which works fine and never causes this problem:

Private Sub CmdExit_Click()
    EnableAll
    ClearAll  '<-actually hides all the controls, doesn't clear anything-
but this is the code that works anyway
    ufCST.hide
End Sub

but it appears that when I click the 'x' on the upper right of the form to
close it, then re-open the form using my toolbar button, I lose my ability
to populate the controls from the array. So, it appears that closing the
form in this fashion does something that I don't understand. As noted below,
I have confirmed that the array itself is still in memory, so it is just
that this method of closing and re-opening the form somehow "disconnects"
the form from the code that updates the captions.

I'll answer in-line with your questions below, in the hopes that something
I'm doing is just stupid enough (and obvious) enough that someone here can
catch it and correct me. :)

> 1. Do you have code which clears the array? Have you tracked down all the
> circumstances in which it might be called?

The source file is a .dot; when clicked it opens a new file as expected.
There is a toolbar in the template to load the userform. In both the
Document_New and the Document_Open code, I load the array which is in a
regular module (has option private module at the top). I don't believe any
of this is a problem, because the arrays always load properly, the problem
is that I occassionally lose them :-/

To my knowledge, I never clear the array- it is loaded when the document is
loaded, and stays in memory so it can be used at any time. I also don't
clear the captions on my target controls- when updating them, I write over
the new captions (populating with a new section of source data from the
array), and hide the controls I don't use at that moment

When this problem occurs, the controls show the captions that I assigned in
the VBE during development, which is the same as what I named them, so I
could keep track. I never assign those VBE names to captions  in my code.
For example, my second group of option buttons might be called optB1, optB2,
optB3, etc. and that is also their VBE captions. When running the program I
assign sentences to the captions, but when this problem occurs, the buttons
(in the userform, live in the document) revert to optB1, optB2, etc. as if
nothing had ever been assigned to them, and when I click on a control that
should assign captions from the array, the captions on the live userform
don't change. The controls themselves still appear to be 'live', as option
buttons do change value (true/false).

> 2. How do you open and close the form? Do you have multiple copies of it
> open at any time?

I have a custom toolbar, and one button is assigned to open the form. The
form is modal, and I've never experienced having more than one copy open-
hopefully that can't happen, if I understand correctly the toolbar button
can't be re-clicked until the modal form is closed.

It was your question about closing the form that led me to test both ways of
closing it and thus realizing that closing using the 'x' is what is causing
the problem.  :)

> 3. How are you declaring the array that contains the text to be used? How
> are you assigning the values to the array? Are you sure that the array
> containing the correct values is always available to the routines that are
> putting the values into the captions of the controls?

In a module, I have each element of the array individually assigned (for
technical reasons, I can't call an external text file to load all my info);
in an option private module I have hundreds of lines like:
MultItemArray(1, 2, 2, 1, 1) = "How do you address any adverse impacts on
society of your services and operations?"

I was able to confirm that the array itself is still in memory by updating
the application.statusbar with an element of my array when I click on the
optionbuttons- so now I know that the array is still there, it just isn't
being applied to the captions after the 'x' is used to close the form.

I have to admit, I'm confused! Any and all tips or suggestions of what to
check next would be greatly appreciated!

Thanks!!!
Keith


>
> -- 
> Regards
> Jonathan West - Word MVP
> www.intelligentdocuments.co.uk
> Please reply to the newsgroup
> Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org
>
> "KR"  wrote in message
> news:upyx4ryiGHA.1320@TK2MSFTNGP04.phx.gbl...
> > Sorry for the confusion- the file I described was Word97, not Excel- I
> > just
> > spend a lot more time working in Excel, so it was just a slip up while
> > typing.
> >
> > So the question stands; any idea what could cause a Word <g>
userform/VBA
> > to
> > lose arrays in memory?
> >
> >
> > "Jonathan West"  wrote in message
> > news:eZHhM7viGHA.3496@TK2MSFTNGP02.phx.gbl...
> >> Hi KR,
> >>
> >> This group deals with VBA in Word. If you are dealing with Excel, you
> >> will
> >> have a better chance of getting an expert reply if you post to
> >> microsoft.public.excel.programming
> >>
> >> -- 
> >> Regards
> >> Jonathan West - Word MVP
> >> www.intelligentdocuments.co.uk
> >> Please reply to the newsgroup
> >> Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org
> >>
> >> "KR"  wrote in message
> >> news:%23KPmWdviGHA.2456@TK2MSFTNGP04.phx.gbl...
> >> >I have a series of userforms in Excel97. One in particular has a bunch
> >> >of
> >> > radiobuttons and comboboxes that are populated dynamically as the
user
> >> > interacts with the form. For example, when the user selects one of 7
> >> > radiobuttons in frameA, that populates the text on the radiobuttons
in
> >> > frameB, and when one of those is selected, it populates the text on
> >> > radiobuttons in frameC, and so on.
> >> >
> >> > On occassion, the form "loses" all of the text that should be
> >> > populating
> >> > those buttons (and other controls as well). The userform has the
> >> > default
> >> > text from design view in the VBE, and clicking on the form changes
the
> >> > state
> >> > of the radiobuttons, but the buttons aren't populated with the text.
> >> >
> >> > This only happens every once in a while, and always seems to be
related
> > to
> >> > either (a) closing and opening of the userform while working in the
> >> > document, and/or (b) switching between applications. It doesn't
happen
> >> > often
> >> > enough for me to have anything more than antecdotal evidence of when
it
> >> > occurs- I'm having trouble even replicating the problem.
> >> >
> >> > Has anyone else ever had this problem, and if so, what types of
things
> >> > should I be checking for in my code to prevent this from happening?
> >> >
> >> > Thanks,
> >> > Keith
> >> >
> >> > -- 
> >> > The enclosed questions or comments are entirely mine and don't
> >> > represent
> >> > the
> >> > thoughts, views, or policy of my employer. Any errors or omissions
are
> > my
> >> > own.
> >> >
> >> >
> >>
> >
> >
>

date: Thu, 15 Jun 2006 11:40:56 -0400 author: KR

Re: Userform 'loses' array info? What could cause this?

Actually, the response I just posted was incomplete  :(

Turns out that I can get controls from "further down" my code to repopulate
when I close with the 'x' button, I must have just missed re-setting the
captions on the main set of option buttons, which is an easy fix. I'll have
to do more testing to see if I can replicate the situation where the entire
grid refuses to re-populate with the array values. I'll post again if I find
out anything new and helpful.

Thanks,
Keith

"Jonathan West"  wrote in message
news:uVwN295jGHA.3848@TK2MSFTNGP04.phx.gbl...
> Hi KR,
>
> Without the code and without any kind of repeatable sequence of steps that
> causes it, anything I suggest will be pure speculation.
>
> 1. Do you have code which clears the array? Have you tracked down all the
> circumstances in which it might be called?
>
> 2. How do you open and close the form? Do you have multiple copies of it
> open at any time?
>
> 3. How are you declaring the array that contains the text to be used? How
> are you assigning the values to the array? Are you sure that the array
> containing the correct values is always available to the routines that are
> putting the values into the captions of the controls?
>
> -- 
> Regards
> Jonathan West - Word MVP
> www.intelligentdocuments.co.uk
> Please reply to the newsgroup
> Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org
>
> "KR"  wrote in message
> news:upyx4ryiGHA.1320@TK2MSFTNGP04.phx.gbl...
> > Sorry for the confusion- the file I described was Word97, not Excel- I
> > just
> > spend a lot more time working in Excel, so it was just a slip up while
> > typing.
> >
> > So the question stands; any idea what could cause a Word <g>
userform/VBA
> > to
> > lose arrays in memory?
> >
> >
> > "Jonathan West"  wrote in message
> > news:eZHhM7viGHA.3496@TK2MSFTNGP02.phx.gbl...
> >> Hi KR,
> >>
> >> This group deals with VBA in Word. If you are dealing with Excel, you
> >> will
> >> have a better chance of getting an expert reply if you post to
> >> microsoft.public.excel.programming
> >>
> >> -- 
> >> Regards
> >> Jonathan West - Word MVP
> >> www.intelligentdocuments.co.uk
> >> Please reply to the newsgroup
> >> Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org
> >>
> >> "KR"  wrote in message
> >> news:%23KPmWdviGHA.2456@TK2MSFTNGP04.phx.gbl...
> >> >I have a series of userforms in Excel97. One in particular has a bunch
> >> >of
> >> > radiobuttons and comboboxes that are populated dynamically as the
user
> >> > interacts with the form. For example, when the user selects one of 7
> >> > radiobuttons in frameA, that populates the text on the radiobuttons
in
> >> > frameB, and when one of those is selected, it populates the text on
> >> > radiobuttons in frameC, and so on.
> >> >
> >> > On occassion, the form "loses" all of the text that should be
> >> > populating
> >> > those buttons (and other controls as well). The userform has the
> >> > default
> >> > text from design view in the VBE, and clicking on the form changes
the
> >> > state
> >> > of the radiobuttons, but the buttons aren't populated with the text.
> >> >
> >> > This only happens every once in a while, and always seems to be
related
> > to
> >> > either (a) closing and opening of the userform while working in the
> >> > document, and/or (b) switching between applications. It doesn't
happen
> >> > often
> >> > enough for me to have anything more than antecdotal evidence of when
it
> >> > occurs- I'm having trouble even replicating the problem.
> >> >
> >> > Has anyone else ever had this problem, and if so, what types of
things
> >> > should I be checking for in my code to prevent this from happening?
> >> >
> >> > Thanks,
> >> > Keith
> >> >
> >> > -- 
> >> > The enclosed questions or comments are entirely mine and don't
> >> > represent
> >> > the
> >> > thoughts, views, or policy of my employer. Any errors or omissions
are
> > my
> >> > own.
> >> >
> >> >
> >>
> >
> >
>

date: Thu, 15 Jun 2006 11:46:22 -0400 author: KR

Re: Userform 'loses' array info? What could cause this? (New info)

Hi Keith

See comments inline...

"KR"  wrote in message
news:OPWMaIJkGHA.4040@TK2MSFTNGP05.phx.gbl...
> Excellent questions; I'll try to respond as accurately and completely as I
> can (at least without posting all the code, which would be really, really
> long). My new piece of information is that I think I've figured out a 100%
> correllation with this problem; I have a close/exit command button on my
> userform which works fine and never causes this problem:
>
> Private Sub CmdExit_Click()
>    EnableAll
>    ClearAll  '<-actually hides all the controls, doesn't clear anything-
> but this is the code that works anyway
>    ufCST.hide
> End Sub
>
> but it appears that when I click the 'x' on the upper right of the form to
> close it, then re-open the form using my toolbar button, I lose my ability
> to populate the controls from the array.

Aha!

> So, it appears that closing the
> form in this fashion does something that I don't understand.

It's OK, I think you have given me enough to go on.

The key point is that when you click the cmdExit button, you are are hiding
the form, but not unloading it from memory. That means it is still there but
invisible, with all your current settings, until you next use the Show
command to display it again.

When you click the X in the top corner, you are actually unloading the form
from memory, so that next time you use the Show command to display it, it
starts up again with default settings.

There are a number of different possible approaches to the problem.

1. You could ensure the form is populated with the appropriate current
settings whenever it is loaded. This can be done by putting code into the
UserForm_Initialize event.

2. You disable the X altogether. The following article shows you how.

How to disable the X close button on a UserForm
http://word.mvps.org/faqs/userforms/DisableClose.htm

3. You put code into the UserForm_QueryClose event, which automatically runs
when you click the X, and force the form to hide instead of closing.
Something like this would do.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = 1 'this tells the form not to unload
    EnableAll
    ClearAll
    Me.Hide    'this hides the form
End Sub

4. You make sure that the form is actually unloaded instead of being merely 
hidden. For this you change "ufCST.hide" (which would be better as "Me.Hide" 
anyway) to "Unload Me".


These measures, either singly or in some combination, will almost certainly
fix the problem.


-- 
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

date: Thu, 15 Jun 2006 17:53:52 +0100 author: Jonathan West