Excel 2007 Combobox LinkedCell problem

[GlennUK]

Hi all,

I am having an intermittent problem with a Combobox. It’s an ActiveX Combobox, embedded in a worksheet, with the LinkedCell property set to point to a cell in the worksheet.

Most of the time everything is OK, but occasionally the Combobox updates a cell in another sheet, or even another workbook.

Does anyone know what to do about this?


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[GlennUK]

It seems to be some kind of error involving the Undo functionality. Performing an Undo also fetches the ComboBox choice and puts it into cell C2 of whatever book is active when the Undo is performed.

I am trying to replicate this error in a new workbook, with no luck so far.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

enjoying 2007 Glenn?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Ha ha Geoff,

so you noticed my recent postings. Yes, having such a lovely time with Excel 2007. NOT!

I do seem to be hitting a few bugs don't I. Such fun.

As for this one, have recreated the bug, and it seems to be caused by having these:
a) A list of cells described by a dynamically defined name formula ... =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A$1:$A$14)-1,1)
b) A comboxbox ( ActiveX ), as an embedded object in another sheet in the book, with ListFillRange being the previously defined name, and LinkedCell of C2.

Then any change in any book followed by Undo causes the selected entry from the combobox to magically appear in the book that you are editing, in cell C2. Hilarious.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

"Hilarious"

It would make me CRY!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

Hi Skip,

oh no, crying is for when you realise that this bug has been corrupting your data for the last 2 months without you noticing. ( that's the user )

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[xlbo]

Hi Glenn - yeh noticed your 2007 themed postings for a little bit now - worrying that something as useful as dynamic named ranges is associated with what appears to be a bug...

Pretty happy that we're staying on 2003 for a while!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Hi Geoff,

I've just been playing on a machine that only has Excel 2003, and I get the same thing again.

Can you recreate this on your Excel? ( it would be really worrying if that really is a bug, and also exists in 2003 too )

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[xlbo]

Hi Glenn - can't seem to replicate

I have 2 workbooks. 1st workbook with ActiveX combobox. ListFillRange set to dr_List which is defined as =OFFSET(Sheet2!$A$1,1,1,COUNTA(Sheet2!$B:$B),1) - this range is on a seperate sheet in the same workbook

Linked cell set to C2 on sheet which houses combobox

2nd workbook - changed a cell and then used undo - no issues.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Hi Geoff,

I can reproduce this on more than 1 machine here. So, it seems to be some setting or conflict on the machines here.

Thanks for trying that.

The workaround I'll be doing will be to use code to generate a fixed defined name instead of using a dynamically defined name for the list.



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[xlbo]

No probs Glenn - that's what I would've suggested for a workaround.

Must be some local settings rather than Excel itself

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Hi Geoff,

yes, must be some local settings. I shall tell the technical guys here, and see if they've got any ideas.



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

Hi all,

I have now reproduced this problem at home ( Excel 2003 ), so it doesn't appear to be local settings specific to the company I'm working in!?!?

I'm not going to explore this any more at the moment, and just do the workaround instead.


Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[xlbo]

Weird that I can't reproduce it on mine :-(

oh well - more important thingsa to do eh Glenn!


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

More detail ... my 2003 experiments were within the same workbook, one sheet for list, one sheet for combobox, one sheet for dummy data for testing of change and Undo.

The error seems to restricted to changes and Undo within the workbook. The users error occurs in any workbook that is open at the same time, so there seems to be some different in mode of operation when the error occurs between 2003 and 2007.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[xlbo]

ah ok - when I tried I used 2 different workbooks

Just tried with 1 workbook and still no dice....lunch time I think!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Yep, lunch time. Had enough of this for now.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Rofeu]

you could try making the LinkedCell a named range specific to the worksheet and reference that.

No idea if that'll work, but one can always try.

Cheers,

Roel

 

[GlennUK]

Tried that, no luck.

Thanks for the idea.



Cheers, Glenn.

Beauty is in the eye of the beerholder.