PDA

View Full Version : Solved: VBA UserForm/ComboBox



Crash0ng
12-02-2012, 03:13 PM
Hello,

I have a VBA programme that uses UserForm and ComboBox.

The UserForm also has "OK", "Cancel" and "Clear" buttons.

The ComboBox is populated with several options;

With AnimalComboBox
.AddItem "Dog"
.AddItem "Cat"
.AddItem "Horse"
End With

When run, the userform appears but the ComboBox dropdown is not populated, only when you press "Clear" do the options become available.

Any help greatly appreciated.

JKwan
12-02-2012, 04:01 PM
try adding this right after Horse

.ListIndex = 0

Crash0ng
12-02-2012, 04:10 PM
Added that but it doesn't resolve the issue. All that seems to do is once I click "Clear", the dropdown box has "Dog" instead of it being blank.

Thanks anyway :)

JKwan
12-02-2012, 07:32 PM
well, maybe you should post your full code.....

Crash0ng
12-02-2012, 08:10 PM
As requested;

Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub ClearButton_Click()
Call AnimalUserForm_Initialize
End Sub

Private Sub AnimalUserForm_Initialize()

'Empty AnimalNameTextBox
AnimalNameTextBox.Value = ""

'Empty OwnerTextBox
OwnerTextBox.Value = ""

'Empty FoodCodeTextBox
FoodCodeTextBox.Value = ""

'Empty AnimalCategoryComboBox
AnimalCategoryComboBox.Clear

'Fill AnimalCategoryComboBox
With AnimalCategoryComboBox
.AddItem "Dog"
.AddItem "Cat"
.AddItem "Horse"
End With

'Set Focus on AnimalNameTextBox
AnimalNameTextBox.SetFocus
End Sub

Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet1 Active
Sheets(1).Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Export Data to worksheet
Cells(emptyRow, 1).Value = AnimalNameTextBox.Value
Cells(emptyRow, 2).Value = OwnerTextBox.Value
Cells(emptyRow, 3).Value = FoodCodeTextBox.Value
Cells(emptyRow, 4).Value = AnimalCategoryComboBox.Value

End Sub

JKwan
12-03-2012, 08:37 AM
Well, what is happening is that you "don't" have an Initialize procedure, well at least you changed it. Change your AnimalUserForm_Initialize to UserForm_Initialize and you will find that things will work better.

Crash0ng
12-03-2012, 03:15 PM
Thank you very much, that sorted it! You sir are a genius :D