Combobox problem
 

I have a combobox which changes certain cells on a spreadsheet depending on
the CB selection and the content of several other cells. The problem I have
is that if one particular cell does not have a value entered, the data in
the code assumes a certain set of conditions. If data is then entered in the
cell, the other data is not corrected as it depends on CB LostFocus.

Is there a way to disable the CB but still leave it visible until certain
cells in the sheet are populated? I know about the Enabled property, but I'm
not sure how to detect the presence of data in particular cells except from
within a subroutine. I suspect I need to use the Worksheet SelectionChange,
but I only want the code to test conditions when particular cells are
changed.

Am I being thick, here?

Combobox problem
 

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if application.CountA(Range("A1,B9,C13,D21") < 4 then
me.combobox1.enabled = False
Else
me.Combobox1.enabled = True
End if
End Sub

--
Regards,
Tom Ogilvy

Ian Coates wrote in message
...
I have a combobox which changes certain cells on a spreadsheet depending
on
the CB selection and the content of several other cells. The problem I
have
is that if one particular cell does not have a value entered, the data in
the code assumes a certain set of conditions. If data is then entered in
the
cell, the other data is not corrected as it depends on CB LostFocus.

Is there a way to disable the CB but still leave it visible until certain
cells in the sheet are populated? I know about the Enabled property, but
I'm
not sure how to detect the presence of data in particular cells except
from
within a subroutine. I suspect I need to use the Worksheet
SelectionChange,
but I only want the code to test conditions when particular cells are
changed.

Am I being thick, here?

Combobox problem
 

I'm having a problem with this, Tom.

I copied the code directly into my VBA editor and changed the cell (now O2)
and the criteria (now <1) but it doesn't work. I startd to have a look at
the help regarding the terms used. I cam unstuck with CountA which doesn't
appear in help. Also, what is the significance of me before combobox1?

Ian

"Tom Ogilvy" wrote in message
...
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if application.CountA(Range("A1,B9,C13,D21") < 4 then
me.combobox1.enabled = False
Else
me.Combobox1.enabled = True
End if
End Sub

--
Regards,
Tom Ogilvy

Ian Coates wrote in message
...
I have a combobox which changes certain cells on a spreadsheet depending
on
the CB selection and the content of several other cells. The problem I
have
is that if one particular cell does not have a value entered, the data
in
the code assumes a certain set of conditions. If data is then entered in
the
cell, the other data is not corrected as it depends on CB LostFocus.

Is there a way to disable the CB but still leave it visible until
certain
cells in the sheet are populated? I know about the Enabled property, but
I'm
not sure how to detect the presence of data in particular cells except
from
within a subroutine. I suspect I need to use the Worksheet
SelectionChange,
but I only want the code to test conditions when particular cells are
changed.

Am I being thick, here?

Combobox problem
 

You originally said there were multiple cells that needed to have values.

If you only want to test if O2 is empty

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if Target.Count 1 then exit sub
if Target.Address = "$O$2" then
if isempty(me.Range(""O2")) then
me.combobox1.enabled = False
Else
me.Combobox1.enabled = True
End if
End If
End Sub

Me refers to the sheet holding the code.

--
Regards,
Tom Ogilvy



"Ian Coates" wrote in message
...
I'm having a problem with this, Tom.

I copied the code directly into my VBA editor and changed the cell (now
O2)
and the criteria (now <1) but it doesn't work. I startd to have a look at
the help regarding the terms used. I cam unstuck with CountA which doesn't
appear in help. Also, what is the significance of me before combobox1?

Ian

"Tom Ogilvy" wrote in message
...
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if application.CountA(Range("A1,B9,C13,D21") < 4 then
me.combobox1.enabled = False
Else
me.Combobox1.enabled = True
End if
End Sub

--
Regards,
Tom Ogilvy

Ian Coates wrote in message
...
I have a combobox which changes certain cells on a spreadsheet
depending
on
the CB selection and the content of several other cells. The problem I
have
is that if one particular cell does not have a value entered, the data
in
the code assumes a certain set of conditions. If data is then entered
in
the
cell, the other data is not corrected as it depends on CB LostFocus.

Is there a way to disable the CB but still leave it visible until
certain
cells in the sheet are populated? I know about the Enabled property,
but
I'm
not sure how to detect the presence of data in particular cells except
from
within a subroutine. I suspect I need to use the Worksheet
SelectionChange,
but I only want the code to test conditions when particular cells are
changed.

Am I being thick, here?

Combobox problem
 

Sorry Tom. I didn't realise I'd have to us edifferent code for single
and multiple cells. Your single cell solution worked perfectly and I
have a multiple cell requirement in another spreadsheet so I'll check
our your other solution when I get round to that.

Many thanks

Ian



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!