Re: Application.enable

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <MVM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:BE87302F-A721-489D-B81A-23A03594B092@xxxxxxxxxxxxxxxx
> > 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" <MVM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > news:25EC555B-351E-4675-8594-6FD83E61D411@xxxxxxxxxxxxxxxx
> > > > 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
> > >
> > >
> > >
>
>
>
.

Quantcast