I have some ActiveX controls on a sheet - they have macros attached to them,
and they have a unique linked cell each.

When I clearcontents the linked cell the control (a ComboBox) still shows
the last lot of data that was in there. The only way I can get rid of it is
to select a blank line, and then delete the zero that it produces (rather
cumbersome and inelegant). Is there a setting I can change? Or a line of code
I can inset into the clearing macro? Regards, Brett

I could not duplicate your problem, but I found that the combobox in the
test I set up did not respond to the click event code unless it was a
change. Don't know if this is standard or not. I will check it out later.


"Brettjg" <(E-Mail Removed)> wrote in message
news61A9FD3-4CF4-4C2D-89B7-(E-Mail Removed)...
>I have some ActiveX controls on a sheet - they have macros attached to
>them,
> and they have a unique linked cell each.
>
> When I clearcontents the linked cell the control (a ComboBox) still shows
> the last lot of data that was in there. The only way I can get rid of it
> is
> to select a blank line, and then delete the zero that it produces (rather
> cumbersome and inelegant). Is there a setting I can change? Or a line of
> code
> I can inset into the clearing macro? Regards, Brett

Yes that's right if you select the same thing it just sits there looking
dopey at you ( who in my particular case, is sitting there loking dopey at
it!). Brett

"JLGWhiz" wrote:

> I could not duplicate your problem, but I found that the combobox in the
> test I set up did not respond to the click event code unless it was a
> change. Don't know if this is standard or not. I will check it out later.
>
>
> "Brettjg" <(E-Mail Removed)> wrote in message
> news61A9FD3-4CF4-4C2D-89B7-(E-Mail Removed)...
> >I have some ActiveX controls on a sheet - they have macros attached to
> >them,
> > and they have a unique linked cell each.
> >
> > When I clearcontents the linked cell the control (a ComboBox) still shows
> > the last lot of data that was in there. The only way I can get rid of it
> > is
> > to select a blank line, and then delete the zero that it produces (rather
> > cumbersome and inelegant). Is there a setting I can change? Or a line of
> > code
> > I can inset into the clearing macro? Regards, Brett
>
>
>

You may want to drop the linked cell. They can cause other trouble, too.

I think I'd just use code to update the cell (combobox1_change??).

Brettjg wrote:
>
> I have some ActiveX controls on a sheet - they have macros attached to them,
> and they have a unique linked cell each.
>
> When I clearcontents the linked cell the control (a ComboBox) still shows
> the last lot of data that was in there. The only way I can get rid of it is
> to select a blank line, and then delete the zero that it produces (rather
> cumbersome and inelegant). Is there a setting I can change? Or a line of code
> I can inset into the clearing macro? Regards, Brett

--

Dave Peterson

Hi Dave

I have a suspicion that dropping the linked cell will cause too much
re-coding, but if JLGWhiz comes up with something later it may help.

"Dave Peterson" wrote:

> You may want to drop the linked cell. They can cause other trouble, too.
>
> I think I'd just use code to update the cell (combobox1_change??).
>
> Brettjg wrote:
> >
> > I have some ActiveX controls on a sheet - they have macros attached to them,
> > and they have a unique linked cell each.
> >
> > When I clearcontents the linked cell the control (a ComboBox) still shows
> > the last lot of data that was in there. The only way I can get rid of it is
> > to select a blank line, and then delete the zero that it produces (rather
> > cumbersome and inelegant). Is there a setting I can change? Or a line of code
> > I can inset into the clearing macro? Regards, Brett
>
> --
>
> Dave Peterson
>

I suppose I should use ComboBoxes more, but I prefer ListBoxes. The best
that I can determine, the click event for the ComboBox fires when an item
other than that shown in the display window is clicked in the drop down
window. It does not fire any other time. It fires for the change event if
a user makes a manual change to the value in the display window or if a
different value is clicked in the drop down list. I had thought that the
click event should fire if the control itself was clicked, but that is not
the case.

I still cannot reproduce your conditions with the linked cell. Mine
disappears in the cell if the control is cleared and in the control if the
cell is cleared. That is the expected result, so I do not know what to tell
you about the cause of condition. Maybe Dave's suggestion to just drop the
linked cell is the best answer.


"Brettjg" <(E-Mail Removed)> wrote in message
news:FBB84E8E-D349-4BB8-A71A-(E-Mail Removed)...
> Yes that's right if you select the same thing it just sits there looking
> dopey at you ( who in my particular case, is sitting there loking dopey at
> it!). Brett
>
> "JLGWhiz" wrote:
>
>> I could not duplicate your problem, but I found that the combobox in the
>> test I set up did not respond to the click event code unless it was a
>> change. Don't know if this is standard or not. I will check it out
>> later.
>>
>>
>> "Brettjg" <(E-Mail Removed)> wrote in message
>> news61A9FD3-4CF4-4C2D-89B7-(E-Mail Removed)...
>> >I have some ActiveX controls on a sheet - they have macros attached to
>> >them,
>> > and they have a unique linked cell each.
>> >
>> > When I clearcontents the linked cell the control (a ComboBox) still
>> > shows
>> > the last lot of data that was in there. The only way I can get rid of
>> > it
>> > is
>> > to select a blank line, and then delete the zero that it produces
>> > (rather
>> > cumbersome and inelegant). Is there a setting I can change? Or a line
>> > of
>> > code
>> > I can inset into the clearing macro? Regards, Brett
>>
>>
>>

OK, thanks for that. I may have to drop the linked cell depending on how much
re-coding it involves? Regards, Brett

"JLGWhiz" wrote:

> I suppose I should use ComboBoxes more, but I prefer ListBoxes. The best
> that I can determine, the click event for the ComboBox fires when an item
> other than that shown in the display window is clicked in the drop down
> window. It does not fire any other time. It fires for the change event if
> a user makes a manual change to the value in the display window or if a
> different value is clicked in the drop down list. I had thought that the
> click event should fire if the control itself was clicked, but that is not
> the case.
>
> I still cannot reproduce your conditions with the linked cell. Mine
> disappears in the cell if the control is cleared and in the control if the
> cell is cleared. That is the expected result, so I do not know what to tell
> you about the cause of condition. Maybe Dave's suggestion to just drop the
> linked cell is the best answer.
>
>
> "Brettjg" <(E-Mail Removed)> wrote in message
> news:FBB84E8E-D349-4BB8-A71A-(E-Mail Removed)...
> > Yes that's right if you select the same thing it just sits there looking
> > dopey at you ( who in my particular case, is sitting there loking dopey at
> > it!). Brett
> >
> > "JLGWhiz" wrote:
> >
> >> I could not duplicate your problem, but I found that the combobox in the
> >> test I set up did not respond to the click event code unless it was a
> >> change. Don't know if this is standard or not. I will check it out
> >> later.
> >>
> >>
> >> "Brettjg" <(E-Mail Removed)> wrote in message
> >> news61A9FD3-4CF4-4C2D-89B7-(E-Mail Removed)...
> >> >I have some ActiveX controls on a sheet - they have macros attached to
> >> >them,
> >> > and they have a unique linked cell each.
> >> >
> >> > When I clearcontents the linked cell the control (a ComboBox) still
> >> > shows
> >> > the last lot of data that was in there. The only way I can get rid of
> >> > it
> >> > is
> >> > to select a blank line, and then delete the zero that it produces
> >> > (rather
> >> > cumbersome and inelegant). Is there a setting I can change? Or a line
> >> > of
> >> > code
> >> > I can inset into the clearing macro? Regards, Brett
> >>
> >>
> >>
>
>
>

THIS POST IS NO LONGER REQUIRED. Thanks to all.

"Brettjg" wrote:

> Hi Dave
>
> I have a suspicion that dropping the linked cell will cause too much
> re-coding, but if JLGWhiz comes up with something later it may help.
>
> "Dave Peterson" wrote:
>
> > You may want to drop the linked cell. They can cause other trouble, too.
> >
> > I think I'd just use code to update the cell (combobox1_change??).
> >
> > Brettjg wrote:
> > >
> > > I have some ActiveX controls on a sheet - they have macros attached to them,
> > > and they have a unique linked cell each.
> > >
> > > When I clearcontents the linked cell the control (a ComboBox) still shows
> > > the last lot of data that was in there. The only way I can get rid of it is
> > > to select a blank line, and then delete the zero that it produces (rather
> > > cumbersome and inelegant). Is there a setting I can change? Or a line of code
> > > I can inset into the clearing macro? Regards, Brett
> >
> > --
> >
> > Dave Peterson
> >