How to program multi-select list box?

[RADO]

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

 

[Guest]

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 listbo
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 userfor
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 Explici
Dim changing As Boolea

Private Sub ListBox1_Change(
If changing = False The
MsgBox "Listbox Change code executed!
End I
End Su

Private Sub UserForm_Activate(
changing = Tru
ListBox1.AddItem "ABC
changing = Fals
End Su
'-------------------------------------------------------


----- 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
RAD

 

[RADO]

Edwin,

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

Sincerely -
RADO

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.