Hi All,
I am way out of my depth here and appreciate any help you can give.

I have a worksheet with 4 Combo Boxes but would like to disable either the
3rd and/or 4th depending on the contents of a particular cell. ie. If A1 =
"2" only ComboBox1 and ComboBox2 enabled. If A1 = "3" then the 1st 3
ComboBoxes enabled (or ComboBox4 disabled) etc etc.

Can this be done and if so could someone please tell me how?

Many thanks!

Hi
Here's one way working in Excel 2003 SP2.
Place code below into the worksheet code (Right click on sheet tab View
Code)
Please note that the sheet codename (Feuil1) is used to access its objects,
you will therefore need to amend with your sheet's codename.
Please note that this example assumes there are only comboxes (from the
controls toolbar) in your sheet.
You may also consider setting the Enable property to False for all of your
comboboxes instead of using the sample code in the Worksheet_Activate event.

Private Sub Worksheet_Activate()
For Each obj In Feuil1.OLEObjects
obj.Enabled = False
Next obj
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
If Target.Value < 0 And Target.Value < "" Then
For i = 1 To Target.Value
Feuil1.OLEObjects("Combobox" & i).Enabled = True
Next i
End If
End Sub

HTH
Cordially
Pascal


"Anniem" a écrit dans le message de news:
...
Hi All,
I am way out of my depth here and appreciate any help you can give.

I have a worksheet with 4 Combo Boxes but would like to disable either
the
3rd and/or 4th depending on the contents of a particular cell. ie. If A1
=
"2" only ComboBox1 and ComboBox2 enabled. If A1 = "3" then the 1st 3
ComboBoxes enabled (or ComboBox4 disabled) etc etc.

Can this be done and if so could someone please tell me how?

Many thanks!

Maybe this one a little better than the previous suggestion:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
If Target.Value < "" And Target.Value <= 4 Then
For Each ctrl In Feuil1.OLEObjects
ctrl.Enabled = False
Next ctrl
For i = 1 To Target.Value
On Error Resume Next
Feuil1.OLEObjects("Combobox" & i).Enabled = True
On Error GoTo 0
Next i
End If
End Sub

Cordially
Pascal

"papou" a écrit dans le message de news:
...
Hi
Here's one way working in Excel 2003 SP2.
Place code below into the worksheet code (Right click on sheet tab View
Code)
Please note that the sheet codename (Feuil1) is used to access its
objects, you will therefore need to amend with your sheet's codename.
Please note that this example assumes there are only comboxes (from the
controls toolbar) in your sheet.
You may also consider setting the Enable property to False for all of your
comboboxes instead of using the sample code in the Worksheet_Activate
event.

Private Sub Worksheet_Activate()
For Each obj In Feuil1.OLEObjects
obj.Enabled = False
Next obj
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
If Target.Value < 0 And Target.Value < "" Then
For i = 1 To Target.Value
Feuil1.OLEObjects("Combobox" & i).Enabled = True
Next i
End If
End Sub

HTH
Cordially
Pascal


"Anniem" a écrit dans le message de
news: ...
Hi All,
I am way out of my depth here and appreciate any help you can give.

I have a worksheet with 4 Combo Boxes but would like to disable either
the
3rd and/or 4th depending on the contents of a particular cell. ie. If A1
=
"2" only ComboBox1 and ComboBox2 enabled. If A1 = "3" then the 1st 3
ComboBoxes enabled (or ComboBox4 disabled) etc etc.

Can this be done and if so could someone please tell me how?

Many thanks!

Hi Pascal,

For some reason the reply that I sent a few days ago hasn't shown on the
forum. However, thank for you reply. As you suggested, I've reset the
Enable property of the ComboBoxes and tried the code from your 1st response.
No joy yet but I will keep trying.

"papou" wrote:

Hi
Here's one way working in Excel 2003 SP2.
Place code below into the worksheet code (Right click on sheet tab View
Code)
Please note that the sheet codename (Feuil1) is used to access its objects,
you will therefore need to amend with your sheet's codename.
Please note that this example assumes there are only comboxes (from the
controls toolbar) in your sheet.
You may also consider setting the Enable property to False for all of your
comboboxes instead of using the sample code in the Worksheet_Activate event.

Private Sub Worksheet_Activate()
For Each obj In Feuil1.OLEObjects
obj.Enabled = False
Next obj
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
If Target.Value < 0 And Target.Value < "" Then
For i = 1 To Target.Value
Feuil1.OLEObjects("Combobox" & i).Enabled = True
Next i
End If
End Sub

HTH
Cordially
Pascal


"Anniem" a écrit dans le message de news:
...
Hi All,
I am way out of my depth here and appreciate any help you can give.

I have a worksheet with 4 Combo Boxes but would like to disable either
the
3rd and/or 4th depending on the contents of a particular cell. ie. If A1
=
"2" only ComboBox1 and ComboBox2 enabled. If A1 = "3" then the 1st 3
ComboBoxes enabled (or ComboBox4 disabled) etc etc.

Can this be done and if so could someone please tell me how?

Many thanks!