Need help with ComboBox in VBA userform

[leopardhawk]

I am using Excel 2010 and have placed a command button in my worksheet that opens a UserForm I created in VBA. The form itself has OK, CANCEL and CLEAR buttons and they all work perfectly. The form puts the data exactly where it is supposed to on the worksheet. Everything works perfectly EXCEPT my ComboBox (s) of which I have six on the form. They are all blank and I cannot figure out why. I could really use some help with this if someone has the time. Here is the code:


Private Sub ArrivalDTPicker_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)

End Sub

Private Sub BookingDTPicker_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)

End Sub

Private Sub CancelCommandButton_Click()

Unload Me

End Sub

Private Sub ClearCommandButton_Click()

Call NewBookingUserForm_Initialize

End Sub

Private Sub DepartureDTPicker_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)

End Sub

Private Sub OKCommandButton_Click()

Dim emptyRow As Long

'Make Sheet3 Active
Sheets(3).Activate

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

'Export Data to worksheet
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = ArrivalDTPicker.Value
Cells(emptyRow, 3).Value = DepartureDTPicker.Value
Cells(emptyRow, 4).Value = BookingDTPicker.Value
Cells(emptyRow, 5).Value = AdultsComboBox.Value
Cells(emptyRow, 6).Value = ChildrenComboBox.Value
Cells(emptyRow, 7).Value = DepositTextBox.Value
Cells(emptyRow, 8).Value = OrderNumberTextBox.Value
Cells(emptyRow, 9).Value = CreditCardComboBox.Value
Cells(emptyRow, 10).Value = CardNumberTextBox.Value
Cells(emptyRow, 11).Value = CardNameTextBox.Value
Cells(emptyRow, 12).Value = MonthComboBox.Value
Cells(emptyRow, 12).Value = YearComboBox.Value
Cells(emptyRow, 13).Value = SecurityTextBox.Value
Cells(emptyRow, 14).Value = PhoneTextBox.Value
Cells(emptyRow, 15).Value = AddressTextBox.Value
Cells(emptyRow, 16).Value = EmailTextBox.Value
Cells(emptyRow, 17).Value = WebsiteComboBox.Value

End Sub

Private Sub NewBookingUserForm_Initialize()

'Empty NameTextBox
NameTextBox.Value = ""

'Empty AddressTextBox
AddressTextBox.Value = ""

'Empty PhoneTextBox
PhoneTextBox.Value = ""

'Empty EmailTextBox
EmailTextBox.Value = ""

'Empty AdultsComboBox
AdultsComboBox.Clear

'Fill AdultsComboBox
With AdultsComboBox
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
End With

'Empty ChildrenComboBox
ChildrenComboBox.Clear

'Fill ChildrenComboBox
With ChildrenComboBox
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
End With

'Empty CreditCardComboBox
CreditCardComboBox.Clear

'Fill CreditCardComboBox
With CreditCardComboBox
.AddItem "Visa"
.AddItem "MasterCard"
.AddItem "Discover"
End With

'Empty CardNumberTextBox
CardNumberTextBox.Value = ""

'Empty CardNameTextBox
CardNameTextBox.Value = ""

'Empty SecurityTextBox
SecurityTextBox.Value = ""

'Empty MonthComboBox
MonthComboBox.Clear

'Fill MonthComboBox
With MonthComboBox
.AddItem "01"
.AddItem "02"
.AddItem "03"
.AddItem "04"
.AddItem "05"
.AddItem "06"
.AddItem "07"
.AddItem "08"
.AddItem "09"
.AddItem "10"
.AddItem "11"
.AddItem "12"
End With

'Empty YearComboBox
YearComboBox.Clear

'Fill YearComboBox
With YearComboBox
.AddItem "12"
.AddItem "13"
.AddItem "14"
.AddItem "15"
.AddItem "16"
.AddItem "17"
.AddItem "18"
.AddItem "19"
.AddItem "20"
.AddItem "21"
.AddItem "22"
End With

'Empty DepositTextBox
DepositTextBox.Value = ""

'Empty OrderNumberTextBox
OrderNumberTextBox.Value = ""

'Empty WebsiteComboBox
WebsiteComboBox.Clear

'Fill WebsiteComboBox
With WebsiteComboBox
.AddItem "cnn"
.AddItem "fox"
.AddItem "nbc"
End With

'Set Focus on NameTextBox
NameTextBox.SetFocus

End Sub

Private Sub NewBookingUserForm_Click()

End Sub

Private Sub UserForm_Click()

End Sub

 

[ColinKJ]

Hi leopardhawk,

One solution:

Private Sub UserForm_Initialize()
ComboBox1.List = Array("01", "02", "03")
End Sub

 

[leopardhawk]

Colin, I tried adding your line of code to the 'initialize' portion of the VBA code for the userform and I still have the same problem.

I figure there must be an easy solution here but I am just not seeing it.

HELP!!!

 

[ColinKJ]

Hi leopardhawk,

Not sure why it's not working for you, I've but together an example WB called "Load_CombBox.xlsm", which you can download from:

http://www.box.com/s/jxtxzfuuv867rjp2ljdy

when you open it, you will see a button, when you click on the button it opens a Userform with a Comb Box.

See if it works, then look at the code and see how it works.

 

[Norie]

Change NewBookingUserForm_Initialize() to UserForm_Initialize().

 

[leopardhawk]

Sorry Norie, an oversight on my part, it was not intentional. Just me in panic mode.

Apologies..!

 

[Norie]

So it still isn't working when you have UserForm instead of NewBookingUserForm?

 

[leopardhawk]

Hi Norie, while I don't understand the reasons why, the form is now working as it should, including all the comboboxes. Thank you so much for helping me.