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" <fel...@gmail.com> 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