Application.enable
 

When I change a value in a combobox, say 16th combobox
why is that 15 and then 14 code also run at the end of 16.
This is written in excel 2003 vba.

why applications.enableevents = false is not working

Any help is greatly appreciated.
MVM

---------------------------- code -------------
Private Sub cmbGrade14_Change()
Call blockDataEntry(cmbGrade14, 14)
End Sub
Private Sub cmbGrade15_Change()
Call blockDataEntry(cmbGrade15, 15)
End Sub
Private Sub cmbGrade16_Change()
Call blockDataEntry(cmbGrade16, 16)
End Sub

public sub blockDataEntry(c as combobox, r as integer)
Application.enableEvents = False
........
Application.enableEvents = true
End Sub

Application.enable
 

The enableevents setting is an setting in the Excel object library. Object
in the control toolbox toolbar are in the MS forms 2.0 library, so they have
no knowledge of nor are they governed by the EnableEvents setting. Only
Excel proper events adhere to this.

You will need to use a public boolean variable to allow events to terminate
upon entry when it is set. Therefore you code needs to set the variable to
true (a variable like

Public bBlockEvents as Boolean

Private Sub cmbGrade14_Change()
if bBlockEvents then exit sub
bBlockEvents = True
Call blockDataEntry(cmbGrade14, 14)
bBlockEvents = False
End Sub

as an example or perhaps you can do the setting and unsetting in
blockDataEntry.

--
Regards,
Tom Ogilvy



"MVM" wrote in message
...
When I change a value in a combobox, say 16th combobox
why is that 15 and then 14 code also run at the end of 16.
This is written in excel 2003 vba.

why applications.enableevents = false is not working

Any help is greatly appreciated.
MVM

---------------------------- code -------------
Private Sub cmbGrade14_Change()
Call blockDataEntry(cmbGrade14, 14)
End Sub
Private Sub cmbGrade15_Change()
Call blockDataEntry(cmbGrade15, 15)
End Sub
Private Sub cmbGrade16_Change()
Call blockDataEntry(cmbGrade16, 16)
End Sub

public sub blockDataEntry(c as combobox, r as integer)
Application.enableEvents = False
.......
Application.enableEvents = true
End Sub

Application.enable
 

Tom
Thank you for the suggestion and educating me.
When I put these comboboxes, i copied and pasted. Is this creating an array
of controls and looping through all of them, even though there is no reason
to trigger them. If so, is there any simple way to make these controls
independent, i mean not arrayed.

Thanks
MVM

"Tom Ogilvy" wrote:

The enableevents setting is an setting in the Excel object library. Object
in the control toolbox toolbar are in the MS forms 2.0 library, so they have
no knowledge of nor are they governed by the EnableEvents setting. Only
Excel proper events adhere to this.

You will need to use a public boolean variable to allow events to terminate
upon entry when it is set. Therefore you code needs to set the variable to
true (a variable like

Public bBlockEvents as Boolean

Private Sub cmbGrade14_Change()
if bBlockEvents then exit sub
bBlockEvents = True
Call blockDataEntry(cmbGrade14, 14)
bBlockEvents = False
End Sub

as an example or perhaps you can do the setting and unsetting in
blockDataEntry.

--
Regards,
Tom Ogilvy



"MVM" wrote in message
...
When I change a value in a combobox, say 16th combobox
why is that 15 and then 14 code also run at the end of 16.
This is written in excel 2003 vba.

why applications.enableevents = false is not working

Any help is greatly appreciated.
MVM

---------------------------- code -------------
Private Sub cmbGrade14_Change()
Call blockDataEntry(cmbGrade14, 14)
End Sub
Private Sub cmbGrade15_Change()
Call blockDataEntry(cmbGrade15, 15)
End Sub
Private Sub cmbGrade16_Change()
Call blockDataEntry(cmbGrade16, 16)
End Sub

public sub blockDataEntry(c as combobox, r as integer)
Application.enableEvents = False
.......
Application.enableEvents = true
End Sub

Application.enable
 

No, there is no built in array of controls and copying does not create an
array of controls. Once copied, the control is independent of the original
as long as you change any linked cells. If you had linked cells before the
copy and didn't change them, then this could be the source of your pain.

--
Regards,
Tom Ogilvy



"MVM" wrote in message
...
Tom
Thank you for the suggestion and educating me.
When I put these comboboxes, i copied and pasted. Is this creating an
array
of controls and looping through all of them, even though there is no
reason
to trigger them. If so, is there any simple way to make these controls
independent, i mean not arrayed.

Thanks
MVM

"Tom Ogilvy" wrote:

The enableevents setting is an setting in the Excel object library.
Object
in the control toolbox toolbar are in the MS forms 2.0 library, so they
have
no knowledge of nor are they governed by the EnableEvents setting. Only
Excel proper events adhere to this.

You will need to use a public boolean variable to allow events to
terminate
upon entry when it is set. Therefore you code needs to set the variable
to
true (a variable like

Public bBlockEvents as Boolean

Private Sub cmbGrade14_Change()
if bBlockEvents then exit sub
bBlockEvents = True
Call blockDataEntry(cmbGrade14, 14)
bBlockEvents = False
End Sub

as an example or perhaps you can do the setting and unsetting in
blockDataEntry.

--
Regards,
Tom Ogilvy



"MVM" wrote in message
...
When I change a value in a combobox, say 16th combobox
why is that 15 and then 14 code also run at the end of 16.
This is written in excel 2003 vba.

why applications.enableevents = false is not working

Any help is greatly appreciated.
MVM

---------------------------- code -------------
Private Sub cmbGrade14_Change()
Call blockDataEntry(cmbGrade14, 14)
End Sub
Private Sub cmbGrade15_Change()
Call blockDataEntry(cmbGrade15, 15)
End Sub
Private Sub cmbGrade16_Change()
Call blockDataEntry(cmbGrade16, 16)
End Sub

public sub blockDataEntry(c as combobox, r as integer)
Application.enableEvents = False
.......
Application.enableEvents = true
End Sub

Application.enable
 

I am still having problem.
No, I did not link the cells with the control combobox.

under
commandbutton_click event ihave the following lines

With mySh
Application.EnableEvents = False
Range(.Cells(5, 10), .Cells(5, 17)).Clear
Range(.Cells(51, 3), .Cells(51, 10)).Clear
.....
each of these range.clear statements trigger all combobox_change events one
by one. These cells have no relation with any of the combos.

could you educate or guide me to understnd this.
thanks

MVM

"Tom Ogilvy" wrote:

No, there is no built in array of controls and copying does not create an
array of controls. Once copied, the control is independent of the original
as long as you change any linked cells. If you had linked cells before the
copy and didn't change them, then this could be the source of your pain.

--
Regards,
Tom Ogilvy



"MVM" wrote in message
...
Tom
Thank you for the suggestion and educating me.
When I put these comboboxes, i copied and pasted. Is this creating an
array
of controls and looping through all of them, even though there is no
reason
to trigger them. If so, is there any simple way to make these controls
independent, i mean not arrayed.

Thanks
MVM

"Tom Ogilvy" wrote:

The enableevents setting is an setting in the Excel object library.
Object
in the control toolbox toolbar are in the MS forms 2.0 library, so they
have
no knowledge of nor are they governed by the EnableEvents setting. Only
Excel proper events adhere to this.

You will need to use a public boolean variable to allow events to
terminate
upon entry when it is set. Therefore you code needs to set the variable
to
true (a variable like

Public bBlockEvents as Boolean

Private Sub cmbGrade14_Change()
if bBlockEvents then exit sub
bBlockEvents = True
Call blockDataEntry(cmbGrade14, 14)
bBlockEvents = False
End Sub

as an example or perhaps you can do the setting and unsetting in
blockDataEntry.

--
Regards,
Tom Ogilvy



"MVM" wrote in message
...
When I change a value in a combobox, say 16th combobox
why is that 15 and then 14 code also run at the end of 16.
This is written in excel 2003 vba.

why applications.enableevents = false is not working

Any help is greatly appreciated.
MVM

---------------------------- code -------------
Private Sub cmbGrade14_Change()
Call blockDataEntry(cmbGrade14, 14)
End Sub
Private Sub cmbGrade15_Change()
Call blockDataEntry(cmbGrade15, 15)
End Sub
Private Sub cmbGrade16_Change()
Call blockDataEntry(cmbGrade16, 16)
End Sub

public sub blockDataEntry(c as combobox, r as integer)
Application.enableEvents = False
.......
Application.enableEvents = true
End Sub

Application.enable
 

You say you have no links and the cells have no relation to the comboboxes.

I have never experienced this and it certainly isn't predictable.

I can only assume that somewhere there is a link or a relationship.

--
Regards,
Tom Ogilvy


"MVM" wrote in message
...
I am still having problem.
No, I did not link the cells with the control combobox.

under
commandbutton_click event ihave the following lines

With mySh
Application.EnableEvents = False
Range(.Cells(5, 10), .Cells(5, 17)).Clear
Range(.Cells(51, 3), .Cells(51, 10)).Clear
....
each of these range.clear statements trigger all combobox_change events
one
by one. These cells have no relation with any of the combos.

could you educate or guide me to understnd this.
thanks

MVM

"Tom Ogilvy" wrote:

No, there is no built in array of controls and copying does not create
an
array of controls. Once copied, the control is independent of the
original
as long as you change any linked cells. If you had linked cells before
the
copy and didn't change them, then this could be the source of your pain.

--
Regards,
Tom Ogilvy



"MVM" wrote in message
...
Tom
Thank you for the suggestion and educating me.
When I put these comboboxes, i copied and pasted. Is this creating an
array
of controls and looping through all of them, even though there is no
reason
to trigger them. If so, is there any simple way to make these
controls
independent, i mean not arrayed.

Thanks
MVM

"Tom Ogilvy" wrote:

The enableevents setting is an setting in the Excel object library.
Object
in the control toolbox toolbar are in the MS forms 2.0 library, so
they
have
no knowledge of nor are they governed by the EnableEvents setting.
Only
Excel proper events adhere to this.

You will need to use a public boolean variable to allow events to
terminate
upon entry when it is set. Therefore you code needs to set the
variable
to
true (a variable like

Public bBlockEvents as Boolean

Private Sub cmbGrade14_Change()
if bBlockEvents then exit sub
bBlockEvents = True
Call blockDataEntry(cmbGrade14, 14)
bBlockEvents = False
End Sub

as an example or perhaps you can do the setting and unsetting in
blockDataEntry.

--
Regards,
Tom Ogilvy



"MVM" wrote in message
...
When I change a value in a combobox, say 16th combobox
why is that 15 and then 14 code also run at the end of 16.
This is written in excel 2003 vba.

why applications.enableevents = false is not working

Any help is greatly appreciated.
MVM

---------------------------- code -------------
Private Sub cmbGrade14_Change()
Call blockDataEntry(cmbGrade14, 14)
End Sub
Private Sub cmbGrade15_Change()
Call blockDataEntry(cmbGrade15, 15)
End Sub
Private Sub cmbGrade16_Change()
Call blockDataEntry(cmbGrade16, 16)
End Sub

public sub blockDataEntry(c as combobox, r as integer)
Application.enableEvents = False
.......
Application.enableEvents = true
End Sub

Application.enable
 

I simply inserted a new sheet and try to enter a number in a cell on that
sheet. It tiggers all the comboboxes change events one after the other.
MVM

"Tom Ogilvy" wrote:

You say you have no links and the cells have no relation to the comboboxes.

I have never experienced this and it certainly isn't predictable.

I can only assume that somewhere there is a link or a relationship.

--
Regards,
Tom Ogilvy


"MVM" wrote in message
...
I am still having problem.
No, I did not link the cells with the control combobox.

under
commandbutton_click event ihave the following lines

With mySh
Application.EnableEvents = False
Range(.Cells(5, 10), .Cells(5, 17)).Clear
Range(.Cells(51, 3), .Cells(51, 10)).Clear
....
each of these range.clear statements trigger all combobox_change events
one
by one. These cells have no relation with any of the combos.

could you educate or guide me to understnd this.
thanks

MVM

"Tom Ogilvy" wrote:

No, there is no built in array of controls and copying does not create
an
array of controls. Once copied, the control is independent of the
original
as long as you change any linked cells. If you had linked cells before
the
copy and didn't change them, then this could be the source of your pain.

--
Regards,
Tom Ogilvy



"MVM" wrote in message
...
Tom
Thank you for the suggestion and educating me.
When I put these comboboxes, i copied and pasted. Is this creating an
array
of controls and looping through all of them, even though there is no
reason
to trigger them. If so, is there any simple way to make these
controls
independent, i mean not arrayed.

Thanks
MVM

"Tom Ogilvy" wrote:

The enableevents setting is an setting in the Excel object library.
Object
in the control toolbox toolbar are in the MS forms 2.0 library, so
they
have
no knowledge of nor are they governed by the EnableEvents setting.
Only
Excel proper events adhere to this.

You will need to use a public boolean variable to allow events to
terminate
upon entry when it is set. Therefore you code needs to set the
variable
to
true (a variable like

Public bBlockEvents as Boolean

Private Sub cmbGrade14_Change()
if bBlockEvents then exit sub
bBlockEvents = True
Call blockDataEntry(cmbGrade14, 14)
bBlockEvents = False
End Sub

as an example or perhaps you can do the setting and unsetting in
blockDataEntry.

--
Regards,
Tom Ogilvy



"MVM" wrote in message
...
When I change a value in a combobox, say 16th combobox
why is that 15 and then 14 code also run at the end of 16.
This is written in excel 2003 vba.

why applications.enableevents = false is not working

Any help is greatly appreciated.
MVM

---------------------------- code -------------
Private Sub cmbGrade14_Change()
Call blockDataEntry(cmbGrade14, 14)
End Sub
Private Sub cmbGrade15_Change()
Call blockDataEntry(cmbGrade15, 15)
End Sub
Private Sub cmbGrade16_Change()
Call blockDataEntry(cmbGrade16, 16)
End Sub

public sub blockDataEntry(c as combobox, r as integer)
Application.enableEvents = False
.......
Application.enableEvents = true
End Sub