Hi all,

I am a begginer in programming Excel forms, and I ran into
an issue I don't know how to resolve. Please help!

I have a multi-selection list box, and I use "on Change"
event to do some things whenever user changes selections.
It works fine, but I have problem initializing the list.

When the form is activated, I want to restore the
selections saved when a user closed the form. So I go item
by item and set their "Selected" status according to the
status array I saved on exit. However, every time it
triggers "Change" event, and everything gets screwed.

Is there a way to turn-off the Change event while
manupulating control values programmatically? Or have the
control to react only on changes casued by the user? Or
maybe I am taking a completely wrong approach?

Thanks!
RADO

Edwin,

thanks a lot! It works. I appreciate your time and advice.

Sincerely -
RADO


"Edwin Tam (MS MVP)" wrote in message
...
RADO, I tell you that you raised a VERY interesting topic!
You topic relates to:
1) You got some code which will be executed upon the Change event of a
control.
2) You got some other code which will change the control in (1).
3) You don't want the code in (1) to execute when (2) is change (1).

This is a frequently encountered issue in Excel VBA programming when
you're developing a function with more extensive use of Userform and
controls.

OK. Let me tell you a technique to resolved this type of problem.

Consider the scenario below:
1) You got an userform.
2) There is a simple listbox
3) On Activate of userform, you want to add an item "abc" to the listbox.
4) When the form is displayed, when the user click onto the listbox item,
you want to show a msgbox.
5) On Activate of userform, you don't want the code in (4) to be executed.

Solution:
=====
1) Use the code below in the userform
2) Note the Boolean variable "changing". When setting it to TRUE, it
represent "some code is doing some changes". When FALSE, "no code is
changing anything", therefore, any change is caused by the user.

'-------------------------------------------------
Option Explicit
Dim changing As Boolean

Private Sub ListBox1_Change()
If changing = False Then
MsgBox "Listbox Change code executed!"
End If
End Sub

Private Sub UserForm_Activate()
changing = True
ListBox1.AddItem "ABC"
changing = False
End Sub
'--------------------------------------------------------



----- RADO wrote: -----

Hi all,

I am a begginer in programming Excel forms, and I ran into
an issue I don't know how to resolve. Please help!

I have a multi-selection list box, and I use "on Change"
event to do some things whenever user changes selections.
It works fine, but I have problem initializing the list.

When the form is activated, I want to restore the
selections saved when a user closed the form. So I go item
by item and set their "Selected" status according to the
status array I saved on exit. However, every time it
triggers "Change" event, and everything gets screwed.

Is there a way to turn-off the Change event while
manupulating control values programmatically? Or have the
control to react only on changes casued by the user? Or
maybe I am taking a completely wrong approach?

Thanks!
RADO