Combobox problem

  • Thread starter Thread starter Ian Coates
  • Start date Start date
I

Ian Coates

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?
 
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
 
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
 
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.
 
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
 
Back
Top