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

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

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

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

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