Strange problem in Excel combo box

[emil]

I have in Excel a range of combo boxes, which are linked with cells. When I
type or select an item it appear in the linked cell, and formula
=COUNTA(T11:T36) display the right number of cells that are not empty in the
range (T11:T36). Then I delete an Item from combo box within backspace tab
(in linked cell it disappear), but formula’s result is unchanged. Why and
what can I do?
Thank for any help.
Emil

 

[HelpExcel.com]

Emil,

You can switch to a Control based combo-box and load your data via code.

 

[emil]

Hi
Eddie

Thank you very much for your time but I think that not this is my problem.
First, I don’t know other formula, similarly with “=COUNTA()â€.
Second, I need to use the result of “=COUNTA()†in a “Loop()“.
Here is my cod:
Sub MyLoop_Macro1()
Application.DisplayAlerts = False
For c = 1 To Sheets("My Sheet name").Range("T8").Value

Call Macro1
Next c

In Sheet1 I have 25 ComboBoxes
e.g. Properties for ComboBox1:
LinkedCell T12
ListFillRange P2318
Properties for ComboBox2:
LinkedCell T13
ListFillRange P2318
And so on.

In the Cell ("T8") there is “=COUNTA(T11:T36)â€
Moreover, how I said in my first post, it works fine when I type or select a
new item in an empty ComboBox. The text appear in the linked cell, and
formula
=COUNTA(T11:T36) display the right number of cells that are not empty in the
range (T11:T36).
Then I delete an Item from ComboBox (e.g. it was completed unintentionally)
within backspace tab, in linked cell it disappear too, but formula’s result
remain unchanged, and My Loop works wrong.
I thank more again.
Emil

 

[Patrick Molloy]

you can 'force' the combobox to select the first item if the value is null,
like when you backspace out
go to the sheet's code page and add this, replacing the combobox name
appropriately...

Private Sub ComboBox2_Change()
With ComboBox2
If .ListIndex = -1 Then
.ListIndex = 0
End If
End With
End Sub

 

[emil]

Hi
Patrick Molloy
Thank you for your interest and answer.
Unfortunately, the result is the same.
I found a solution. Not so elegant but work:
Select the linked cell and clear all.
Regards
Emil