Triggering Change_Events after Form Initialization

[JimRWR]

Hi.

I have a form whose controls are created and populated at runtime. I also
have two class modules, each of which handles a change_event for a particular
control. I received the Runtime Error 91: Variable or With block variable
not set. Having read several posts supressing form-level events, I created a
module-level, public Boolean variable in order to turn change_events on and
off. This has resolved the Runtime error, and all controls are now created
and populated. At this point, changing a combobox value should trigger its
change_event. However, even though I've turned the events back on at the end
of the module, nothing happens. Below is relevant code:

<module level>
Public ChangePrice as Boolean
Sub Edit()
ChangePrice = False 'disables change_events
<Form initialized, controls created and populated.
ChangePrice = True 'enables change_events

<class module>
Sub ctrl1_Change()
If ChangePrice = True then
cls(1).ctrl1.value = something
Else
Exit Sub
End If
End Sub

Why doesn't the change_event fire after I enable it?

TIA,

Jim

 

[Jan Karel Pieterse]

Hi JimRWR,

Why doesn't the change_event fire after I enable it?

Does the event in the class fire at all? (Set a break point in the
class module in each event routine)

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[JimRWR]

Hi, Jan.

Yes, the events start to fire, but just before exiting the event routine,
I've got message boxes that show the state of the ChangePrice variable.
False is displayed in the message box for each row of controls added. When
all controls have been added, the messagebox (in the module-level routine)
shows a value of True. But if I go ahead and change a combobox value,
nothing happens.

Thanks,

Jim

 

[Jan Karel Pieterse]

Hi JimRWR,

Yes, the events start to fire, but just before exiting the event routine,
I've got message boxes that show the state of the ChangePrice variable.
False is displayed in the message box for each row of controls added. When
all controls have been added, the messagebox (in the module-level routine)
shows a value of True. But if I go ahead and change a combobox value,
nothing happens.

Well, there must be something in your code that causes the variable to contain
something else than you expect it to have. I'd put a break watch on
ChangePrice and have VBA halt when it changes its value.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[JimRWR]

Jan:

Just realized that when I reset the value of ChangeRate to True, I'm doing
so in the standard module, not the change_event class module. So it looks as
if ChangeRate never gets reset to True in the class module. Two questions:

1. Since I've declared ChangeRate as Public, should its value pass from the
standard module to the class module?

2. If I need to change the value to true in the class_module, how do I tell
the class module that the form is initialized and as such the change_event
should be turned back on?

Thanks!

Jim

 

[Jan Karel Pieterse]

Hi JimRWR,

1. Since I've declared ChangeRate as Public, should its value pass from the
standard module to the class module?

As long as it is declared publicly in a normal module this variable is visible
in your entire project.

2. If I need to change the value to true in the class_module, how do I tell
the class module that the form is initialized and as such the change_event
should be turned back on?

You just set ChangePrice to its proper value. That is really all there is to
it.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com