Combobox and LinkedCell issue

[Glaswegian]

Hi

I'm having an issue setting the LinkedCell value in a combobox. The box is on a sheet and the ListFillRange and LinkedCell will change depending on the choice of 2 checkboxes. Therefore if checkbox 1 is selected then the user should see values from list A in the box; if checkbox 2 is selected then it should be values from list B. The LinkedCell value is then used later and copied to another sheet. Rows are hidden or unhidden on this other sheet depending on the checkbox selection.

I am able to change the ListFillRange with no problems, but the LinkedCell refuses to change.
[vba]Private Sub CheckBox9_Click()
Dim myCB As OLEObject

If [AC11] = True Then [AD11] = False
If [AD11] = True Then [AC11] = False

Set myCB = Sheets("FRCM").OLEObjects("Combobox5")

If CheckBox9 = True Then
With myCB
.ListFillRange = "TypeCB"
.LinkedCell = Sheets("FRCM").Range("AH26")
.Object.ListIndex = -1
End With
With Sheets("GB5420 FRCM")
.Rows("31:32").Hidden = True
.Rows("33:34").Hidden = False
End With
Else
With myCB
.ListFillRange = "TypeYB"
.LinkedCell = Sheets("FRCM").Range("AH21")
.Object.ListIndex = -1
End With
With Sheets("GB5420 FRCM")
.Rows("33:34").Hidden = True
.Rows("31:32").Hidden = False
End With
End If
End Sub[/vba]
I'm guessing it's something to do with the combobox Property, but I really don't understand why ListFillRange works and LinkedCell does not.

Any help greatly appreciated - thanks.

[Bob Phillips]

[vba]

Private Sub CheckBox9_Click()
Dim myCB As OLEObject

If [AC11] = True Then [AD11] = False
If [AD11] = True Then [AC11] = False

Set myCB = Sheets("FRCM").OLEObjects("Combobox5")

If CheckBox9 = True Then
With myCB
.ListFillRange = "TypeCB"
.LinkedCell = Sheets("FRCM").Range("AH26").Address
.Object.ListIndex = -1
End With
With Sheets("GB5420 FRCM")
.Rows("31:32").Hidden = True
.Rows("33:34").Hidden = False
End With
Else
With myCB
.ListFillRange = "TypeYB"
.LinkedCell = Sheets("FRCM").Range("AH21").Address
.Object.ListIndex = -1
End With
With Sheets("GB5420 FRCM")
.Rows("33:34").Hidden = True
.Rows("31:32").Hidden = False
End With
End If
End Sub
[/vba]

BTW, are you the same guy that administers TechSupportForum?

[Glaswegian]

Superb! Thanks.

Why did the code need "Address"?

BTW, are you the same guy that administers TechSupportForum?

Lol - I'm just a Mod at TSF - nothing as lofty as an Admin...

[Bob Phillips]

It needed address because you want the address string, otherwise it defaults to value. That is why it is ALWAYS best to use the full declaration of a property, not let it default.

Mod or Admin, it is a worthy site, so congratulations on your efforts.

[Glaswegian]

It needed address because you want the address string, otherwise it defaults to value. That is why it is ALWAYS best to use the full declaration of a property, not let it default.

Ah, of course - thanks. I don't do enough Excel these days so I've propbably slipped into somne bad habits.

Mod or Admin, it is a worthy site, so congratulations on your efforts.

Thanks for that - we try our best.