Disabling events within an event handler
 

Hi,

I have 2 comboboxes created from the Control Toolbox. I want to set
the text of one combobox when the other is clicked. Here's a sample
code for the Change event for them. (line numbers are given for easy
reference - they dont exist in the code)

1 private sub Combobox1_Change()
2 do stuff
3 Combobox2.Text = "something"
4 end sub
5
6 private sub Combobox2_change()
7 do stuff
8 Combobox1.Text = "the other thing"
9 end sub

In the above, when Combobox1_Change() is executing, I do not want it
to go to Combobox2_change()... This is happening. I then replaced
line 3 with the following:
Application.EnableEvents = False
Combobox2.Text = "something"
Application.EnableEvents = True

Well, the problem continues. I tired other things like setting
Combobox1.Application.Events flag. Same result. It just does not
seem to ignore the Change event for Combobox2.

How do I make this happen?

Thanks a lot for any pointers & Best Regards,
-feltra

Disabling events within an event handler
 

One way to do it is to have a module or form level variable.


Private bBox1Changing As Boolean

Private Sub Combobox1_Change()
bBox1Changing = True
ComboBox2.Text = "something"
bBox1Changing = False
End Sub

Private Sub Combobox2_change()
If bBox1Changing Then Exit Sub
ComboBox1.Text = "the other thing"
End Sub



--
Hope that helps.

Vergel Adriano


"feltra" wrote:

Hi,

I have 2 comboboxes created from the Control Toolbox. I want to set
the text of one combobox when the other is clicked. Here's a sample
code for the Change event for them. (line numbers are given for easy
reference - they dont exist in the code)

1 private sub Combobox1_Change()
2 do stuff
3 Combobox2.Text = "something"
4 end sub
5
6 private sub Combobox2_change()
7 do stuff
8 Combobox1.Text = "the other thing"
9 end sub

In the above, when Combobox1_Change() is executing, I do not want it
to go to Combobox2_change()... This is happening. I then replaced
line 3 with the following:
Application.EnableEvents = False
Combobox2.Text = "something"
Application.EnableEvents = True

Well, the problem continues. I tired other things like setting
Combobox1.Application.Events flag. Same result. It just does not
seem to ignore the Change event for Combobox2.

How do I make this happen?

Thanks a lot for any pointers & Best Regards,
-feltra

Disabling events within an event handler
 

This isn't an event to excel. So you have to take care of it yourself.

Option Explicit
Public BlkProc As Boolean
private sub Combobox1_Change()
if blkproc = true then exit sub
do stuff
blkproc = true
Combobox2.Text = "something"
blkproc = false
end sub

private sub Combobox2_change()
if blkproc = true then exit sub
do stuff
blkproc = true
Combobox1.Text = "the other thing"
blkproc = false
end sub

feltra wrote:

Hi,

I have 2 comboboxes created from the Control Toolbox. I want to set
the text of one combobox when the other is clicked. Here's a sample
code for the Change event for them. (line numbers are given for easy
reference - they dont exist in the code)

1 private sub Combobox1_Change()
2 do stuff
3 Combobox2.Text = "something"
4 end sub
5
6 private sub Combobox2_change()
7 do stuff
8 Combobox1.Text = "the other thing"
9 end sub

In the above, when Combobox1_Change() is executing, I do not want it
to go to Combobox2_change()... This is happening. I then replaced
line 3 with the following:
Application.EnableEvents = False
Combobox2.Text = "something"
Application.EnableEvents = True

Well, the problem continues. I tired other things like setting
Combobox1.Application.Events flag. Same result. It just does not
seem to ignore the Change event for Combobox2.

How do I make this happen?

Thanks a lot for any pointers & Best Regards,
-feltra

--

Dave Peterson

Disabling events within an event handler
 

Hi Dave & Vergel,

Thanks a lot for the valuable inputs... Before your replies came in,
I kept searching the archives and found an older post with similar
solution (in the same group). Only I had to change the public "Dim"
to "Public" - and it works like a charm...

Sergei, I will try out declaring it as "Private" to outside the
procedure also... I am still new to Excel VBA, (coming from a Unix/C
bkground).

Thanks a million & Best Regards,
-feltra

Dave Peterson wrote:
This isn't an event to excel. So you have to take care of it yourself.

Option Explicit
Public BlkProc As Boolean
private sub Combobox1_Change()
if blkproc = true then exit sub
do stuff
blkproc = true
Combobox2.Text = "something"
blkproc = false
end sub

private sub Combobox2_change()
if blkproc = true then exit sub
do stuff
blkproc = true
Combobox1.Text = "the other thing"
blkproc = false
end sub

feltra wrote:

Hi,

I have 2 comboboxes created from the Control Toolbox. I want to set
the text of one combobox when the other is clicked. Here's a sample
code for the Change event for them. (line numbers are given for easy
reference - they dont exist in the code)

1 private sub Combobox1_Change()
2 do stuff
3 Combobox2.Text = "something"
4 end sub
5
6 private sub Combobox2_change()
7 do stuff
8 Combobox1.Text = "the other thing"
9 end sub

In the above, when Combobox1_Change() is executing, I do not want it
to go to Combobox2_change()... This is happening. I then replaced
line 3 with the following:
Application.EnableEvents = False
Combobox2.Text = "something"
Application.EnableEvents = True

Well, the problem continues. I tired other things like setting
Combobox1.Application.Events flag. Same result. It just does not
seem to ignore the Change event for Combobox2.

How do I make this happen?

Thanks a lot for any pointers & Best Regards,
-feltra

--

Dave Peterson

Disabling events within an event handler
 

Hi Vergel,

Sorry about using the wrong name (inside my earlier post).. I was
replying to another person (on another group) and somehow mixed up the
names...

Thanks & Regards,
-feltra

On Apr 8, 9:01 am, "feltra" wrote:
Hi Dave & Vergel,

Thanks a lot for the valuable inputs... Before your replies came in,
I kept searching the archives and found an older post with similar
solution (in the same group). Only I had to change the public "Dim"
to "Public" - and it works like a charm...

Sergei, I will try out declaring it as "Private" to outside the
procedure also... I am still new to Excel VBA, (coming from a Unix/C
bkground).

Thanks a million & Best Regards,
-feltra

Dave Peterson wrote:
This isn't an event to excel. So you have to take care of it yourself.

Option Explicit
Public BlkProc As Boolean
private sub Combobox1_Change()
if blkproc = true then exit sub
do stuff
blkproc = true
Combobox2.Text = "something"
blkproc = false
end sub

private sub Combobox2_change()
if blkproc = true then exit sub
do stuff
blkproc = true
Combobox1.Text = "the other thing"
blkproc = false
end sub

feltra wrote:

Hi,

I have 2 comboboxes created from the Control Toolbox. I want to set
the text of one combobox when the other is clicked. Here's a sample
code for the Change event for them. (line numbers are given for easy
reference - they dont exist in the code)

1 private sub Combobox1_Change()
2 do stuff
3 Combobox2.Text = "something"
4 end sub
5
6 private sub Combobox2_change()
7 do stuff
8 Combobox1.Text = "the other thing"
9 end sub

In the above, when Combobox1_Change() is executing, I do not want it
to go to Combobox2_change()... This is happening. I then replaced
line 3 with the following:
Application.EnableEvents = False
Combobox2.Text = "something"
Application.EnableEvents = True

Well, the problem continues. I tired other things like setting
Combobox1.Application.Events flag. Same result. It just does not
seem to ignore the Change event for Combobox2.

How do I make this happen?

Thanks a lot for any pointers & Best Regards,
-feltra

--

Dave Peterson