Glaswegian
11-20-2008, 07:28 AM
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.
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
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.
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.
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
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.