Problems using ComboBox

[David1976]

Hi all,

First of all, I can't get ComboBox to show first item in ComboBox.


Private Sub ENTER_Click()
Dim newdata As Variant
Dim temp As Integer
Dim X As Integer
temp = 21
ComboBox.Clear
Sheet2.Activate

X = WorksheetFunction.CountA(Range("A:A")) - temp
newdata = TextBox
Cells(X, 2).Value = newdata
End Sub
Private Sub ComboBox_Change()
ComboBox.AddItem "Red", 0
ComboBox.AddItem "Blue", 1
ComboBox.AddItem "Green", 2
ComboBox.AddItem "White", 3
ComboBox.AddItem "Yellow", 4
ComboBox.AddItem "Purple", 5
WorkInProgess
End Sub
'
Private Sub WorkInProgess()
If ComboBox.Text = "Red" Then
Else
Failed
End If
End Sub

Private Sub Failed()
Unload Me
End Sub
Private Sub btnExit_Click()
Unload Me
End Sub

Next I don't really understand
X = WorksheetFunction.CountA(Range("A:A"))

I have used Failed() to Exit program on condition. I don't want the code to proceed after a condition isn't true.

[SamT]

There are some Application functions that VBA can use.

To use an Excel formlua function. it must be preceded by either the WorksheetFunction Object or an Object variable set to the WorksheetFunction Object


Dim WsF as Object
Set WsF = WorksheetFunction
X = WsF.CountA( . . .

This lets VBA use many of the Functions you can use in worksheet formulas.

The Excel Forumla Bar will show Help on the Function





The ComboBox Change Event occurs every time you press a key while "in" the Combobox. The Exit Event is usually better.

I see that you are using "Combobox" and "Textbox" as Control Names. You should avoid using VBA and Excel words as names.

It is better to use the UserForm_Initialize sub to load items into your controls