Dynamic named range and embedded combo box undo bug

Spaztick

New Member
Joined
Jun 10, 2010
Messages
6
I have come across what I think is an excel bug, or maybe I have done something wrong. I'm using excel 2007<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
I have created several dynamically named range that is used to fill active X combo boxes embedded on a worksheet.<o:p></o:p>
<o:p></o:p>
My named ranges are declared like this <o:p></o:p>
=OFFSET('Lookup Tables'!$G$1,1,0,COUNTA('Lookup Tables'!$G:$G),1)<o:p></o:p>
<o:p></o:p>
I then use the name in the ListFillRange property of a combo box that is on another worksheet. The combo boxes are linked to the cells underneath them.<o:p></o:p>
<o:p></o:p>
I also use vba code to update the ListFillRange to get around the problem where the combo boxes don't update the list. But this is unrelated to the problem.<o:p></o:p>
<o:p></o:p>
Private Sub ComboBox1_GotFocus()
ComboBox1.ListFillRange = "MyRange"
End Sub<o:p></o:p>

<o:p></o:p>
This appeared to works fine without any problem.<o:p></o:p>
Eventually I noticed that I experience strange behavior whenever I used 'Undo' (button or ctrl z). <o:p></o:p>
<o:p></o:p>
If I was on a different worksheet than the one with the combo boxes and used 'undo' excel would undo the last change properly but would also paste the combo boxes linked cells onto that worksheet.<o:p></o:p>
So if the linked cells were B1 B2 and B3 those values would appear on the current worksheet in cells B1 B2 and B3 including all the formatting.<o:p></o:p>
<o:p></o:p>
Also if I opened another workbook and tried to undo while the problem workbook is open it crashes excel.<o:p></o:p>
<o:p></o:p>
Removing all the vba code didn't fix the problem.<o:p></o:p>
Changing the dynamic ranges to static did fix the problem.<o:p></o:p>
<o:p></o:p>
I just wanted to know if I have done something wrong or if it is a excel bug?<o:p></o:p>
Has anyone come across this before?<o:p></o:p>
<o:p></o:p>
Will I have similar problems with dynamic ranges and a combo box on form?<o:p></o:p>
Should I try vba code to change a static named range when a new entry is added?<o:p></o:p>
<o:p></o:p>
I hope that is clear enough, thanks in advance for any help.<o:p></o:p>
 
I'm sorry for the bump but I'm experiencing this very same problem (but in Excel 2003) and so far have not been able to find a solution.

When I use "undo" on certain sheets Excel pastes the combobox linked cells (of those using a dynamic named range) on this same sheet. This causes some annoying issues as I'm using some of these cells for other data and Excel just overwrites them as soon as I use "undo".

Has anyone encountered this same problem and found a solution? I've only recently discovered the use of dynamic named ranges (and find them very useful!) and use them mostly for comboxes.

Many thanks in advance!

BTW: I have no VBA knowledge whatsoever...
 
Upvote 0
Hi I had the same issue... and from your post I was able to understand it was the 'defined name' in the 'listfillrange' and not the 'linkedcell' reference was causing the bug.

By chance, assigning the defined name values to the combo boxes can be done in one line

YourSheetName.YourComboxboxName.List = [YourDefinedName].value

which can be easily placed in a 'on event' macro. (http://blog.livedoor.jp/andrewe/archives/cat_415504.html)


Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.column

Case
[ListNationalities].column
YourSheetName.YourComboxboxName.List = [YourDefinedName].value

Case
[ListPositions].column
YourSheetName2.YourComboxboxName2.List = [YourDefinedName2].value

Case
[ListGrades].column
YourSheetName3.YourComboxboxName3.List = [YourDefinedName3].value
End Select

End Sub

I hope this will give you some ideas...
Heriss
 
Upvote 0
Hi all, I wasn't able to resolve the problem, I guess because I couldn't get the macro from Heriss to work. Can someone please report back on the resolution to this problem. I am not too sure what the purpose of the Case construct is?

thanks

Nelson
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top