Combo box is not listening to its linked cell

[Simon W]

Hi,

I have a combo box (from the forms menu) that sets the value in its
linked cell just as it should.

However, if I change the value in the linked cell directly, the value
shown in the combo box window does not update to reflect the change.
I'm sure I have had combo boxes in the past (i.e. pre excel 2003)
where this wasn't a problem. I seem to get the same problem if I use a
VBA combo box from the controls menu.

Does anyone have any idea how to make sure the combo box and linked
cell keep in contact? I don't want to use the VBA combo boxes unless I
have to but is there something in their properties that can ensure
this two-way behaviour?

Thanks

 

[Dave Peterson]

I use xl2003 and I couldn't reproduce the problem.

Have you double checked that the linked cell and input range are correct?

If you do this in a brand new worksheet in a brand new workbook, do you have
trouble?

 

[Simon W]

I use xl2003 and I couldn't reproduce the problem.

Have you double checked that the linked cell and input range are correct?

If you do this in a brand new worksheet in a brand new workbook, do you have
trouble?

This is strange. I open excel, make a new combo box and it works in
the two-way fashion I want: I change the number in the linked cell
and it changes the entry in the combo window.

But then if I open my original workbook (a 30 sheet monster) the
original problem now affects the brand new combo box in the fresh
workbook and it stays that way if I close the big workbook.

If I make a second new workbook with a combo box in it, that one works
ok but the first one is still 'contaminated'.

If I close and reopen the first new workbook it now works again, so
closing and reopening seems to decontaminate it.

Does this mean there is a variable or property somewhere in my big
workbook that is causing the problem?

 

[Dave Peterson]

I've don't think this is controlled by a setting.

I think I'd start by trying replacing the existing dropdowns with new dropdowns.

If you do a few and it seems to fix the problem, maybe replacing all the
dropdowns with new ones would help.

Try this against a copy of your workbook:
Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myOldDDs As DropDowns 'with an S
Dim myOldDD As DropDown 'no S on this
Dim myNewDD As DropDown 'no S here, either
Dim newPFX As String

newPFX = "New_____"

For Each wks In ActiveWorkbook.Worksheets
Set myOldDDs = wks.DropDowns
For Each myOldDD In myOldDDs
Set myNewDD = wks.DropDowns.Add(0, 0, 0, 0)
With myOldDD
myNewDD.Top = .Top
myNewDD.Left = .Left
myNewDD.Width = .Width
myNewDD.Height = .Height
myNewDD.OnAction = .OnAction
myNewDD.ListFillRange = .ListFillRange
myNewDD.LinkedCell = .LinkedCell
myNewDD.Placement = .Placement
myNewDD.Visible = .Visible
myNewDD.Enabled = .Enabled
myNewDD.DropDownLines = .DropDownLines
myNewDD.PrintObject = .PrintObject
myNewDD.Name = newPFX & .Name
.Delete
End With
myNewDD.Name = Mid(myNewDD.Name, Len(newPFX) + 1)
Next myOldDD
Next wks
End Sub


Under minor testing, it looked like the old dropdowns were successfully replaced
with new ones.

 

[Simon W]

I've don't think this is controlled by a setting.

I think I'd start by trying replacing the existing dropdowns with new dropdowns.

If you do a few and it seems to fix the problem, maybe replacing all the
dropdowns with new ones would help.

Try this against a copy of your workbook:
Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myOldDDs As DropDowns 'with an S
Dim myOldDD As DropDown 'no S on this
Dim myNewDD As DropDown 'no S here, either
Dim newPFX As String

newPFX = "New_____"

For Each wks In ActiveWorkbook.Worksheets
Set myOldDDs = wks.DropDowns
For Each myOldDD In myOldDDs
Set myNewDD = wks.DropDowns.Add(0, 0, 0, 0)
With myOldDD
myNewDD.Top = .Top
myNewDD.Left = .Left
myNewDD.Width = .Width
myNewDD.Height = .Height
myNewDD.OnAction = .OnAction
myNewDD.ListFillRange = .ListFillRange
myNewDD.LinkedCell = .LinkedCell
myNewDD.Placement = .Placement
myNewDD.Visible = .Visible
myNewDD.Enabled = .Enabled
myNewDD.DropDownLines = .DropDownLines
myNewDD.PrintObject = .PrintObject
myNewDD.Name = newPFX & .Name
.Delete
End With
myNewDD.Name = Mid(myNewDD.Name, Len(newPFX) + 1)
Next myOldDD
Next wks
End Sub

Under minor testing, it looked like the old dropdowns were successfully replaced
with new ones.

Dave - can I first say thanks very much for your help with this. Much
appreciated.

Unfortunately replacing the dropdowns with new ones doesn't seem to
work (I tried replacing them one at a time and that didn't work; I ran
your macro a couple of times and while it seemed to work perfectly
both times as far as replacing the combo boxes goes, this didn't fix
the problem. I have even tried replacing the combo boxes with VBA
versions from the controls menu again and that doesn't seem to work
either.

Any other suggestions gratefully received.

Simon

 

[Dave Peterson]

I've never seen this disconnect.

The only thing that I can think of is the workbook has some corruption in it.

Maybe you can try a few things to see if they help.

#1. Save the file as .html. Then open that .html and resave that as a normal
workbook.

#2. Open the file in OpenOffice.org and save it there.
http://www.openoffice.org, a 60-104 meg download or a CD

Maybe doing one of these things will help clean up any corruption that's
creeping into the workbook.

If both of these fail, maybe it's time to start rebuilding that workbook from
scratch (yechhh!).

 

[sky.redoubt]

Hi, I have the same problem, but I cannot even make it work in a new
workbook. THere must be some hidden setting in Excel that controls the
flow of information from the linked cell to the ComboBox, I guess.
Peter

 

[jeff rosen]

I've seen this problem. Making sure Excel is in automatic recalculation mode (not manual mode) seems to solve it for me.