Combobox will not show list

J

John

Hi Everyone
I've got a userform with a combo box that will not show the list unless I press
the button to activate [ Private Sub UserForm1_Initialize() ] then everything is
ok.
My codes are below, can anyone figure out what I forgot ?
Thanking you in advance
Regards
John

Private Sub UserForm1_Initialize()
OptionButton1 = False
OptionButton2 = False
OptionButton3 = False
OptionButton4 = False
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
CheckBox1 = False
CheckBox2 = False
With ComboBox1
.AddItem "Walking"
.AddItem "Running"
.AddItem "Tennis"
.AddItem "Bicycle"
.AddItem "Hockey"
End With
ComboBox1.Value = ""
End Sub
'Exit Button

Private Sub CommandButton2_Click()
Unload UserForm1
End Sub

'Clear Button
Private Sub CommandButton3_Click()
Call UserForm1_Initialize
End Sub

'Date window
Private Sub TextBox1_enter()
Me.TextBox1.Value = Now
End Sub

'"OK" Button
Private Sub commandbutton1_Click()

Dim RowCount As Long
Sheets("sheet1").Activate
Range("A4").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = TextBox1.Value
ActiveCell.Offset(0, 2) = TextBox2.Value
ActiveCell.Offset(0, 6) = TextBox3.Value
ActiveCell.Offset(0, 5) = ComboBox1.Value

If OptionButton1 = True Then
ActiveCell.Offset(0, 1).Value = "Breakfast"
ElseIf OptionButton2 = True Then
ActiveCell.Offset(0, 1).Value = "Lunch"
ElseIf OptionButton3 = True Then
ActiveCell.Offset(0, 1).Value = "Dinner"
Else
ActiveCell.Offset(0, 1).Value = "Bedtime"

End If
If CheckBox1 = True Then
ActiveCell.Offset(0, 3).Value = "Yes"
Else
ActiveCell.Offset(0, 3).Value = "No"
End If
If CheckBox2 = True Then
ActiveCell.Offset(0, 4).Value = "Yes"
Else
ActiveCell.Offset(0, 4).Value = "No"
End If
Range("a4").Select
End Sub
 
M

Mike

Try this
Private Sub UserForm_Initialize()
OptionButton1 = False
OptionButton2 = False
OptionButton3 = False
OptionButton4 = False
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
CheckBox1 = False
CheckBox2 = False
With ComboBox1
.AddItem "Walking"
.AddItem "Running"
.AddItem "Tennis"
.AddItem "Bicycle"
.AddItem "Hockey"
End With
ComboBox1.Value = ""
End Sub
 
J

John

Hi Mike
Hours playing around trying to figure out what's wrong.
Went back to my books, check with the Internet.
I'm just starting to learn VBA and this one, is etched in my brain.
Thank you very much
Regards
John
Mike said:
Try this
Private Sub UserForm_Initialize()
OptionButton1 = False
OptionButton2 = False
OptionButton3 = False
OptionButton4 = False
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
CheckBox1 = False
CheckBox2 = False
With ComboBox1
.AddItem "Walking"
.AddItem "Running"
.AddItem "Tennis"
.AddItem "Bicycle"
.AddItem "Hockey"
End With
ComboBox1.Value = ""
End Sub


John said:
Hi Everyone
I've got a userform with a combo box that will not show the list unless I
press
the button to activate [ Private Sub UserForm1_Initialize() ] then everything
is
ok.
My codes are below, can anyone figure out what I forgot ?
Thanking you in advance
Regards
John

Private Sub UserForm1_Initialize()
OptionButton1 = False
OptionButton2 = False
OptionButton3 = False
OptionButton4 = False
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
CheckBox1 = False
CheckBox2 = False
With ComboBox1
.AddItem "Walking"
.AddItem "Running"
.AddItem "Tennis"
.AddItem "Bicycle"
.AddItem "Hockey"
End With
ComboBox1.Value = ""
End Sub
'Exit Button

Private Sub CommandButton2_Click()
Unload UserForm1
End Sub

'Clear Button
Private Sub CommandButton3_Click()
Call UserForm1_Initialize
End Sub

'Date window
Private Sub TextBox1_enter()
Me.TextBox1.Value = Now
End Sub

'"OK" Button
Private Sub commandbutton1_Click()

Dim RowCount As Long
Sheets("sheet1").Activate
Range("A4").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = TextBox1.Value
ActiveCell.Offset(0, 2) = TextBox2.Value
ActiveCell.Offset(0, 6) = TextBox3.Value
ActiveCell.Offset(0, 5) = ComboBox1.Value

If OptionButton1 = True Then
ActiveCell.Offset(0, 1).Value = "Breakfast"
ElseIf OptionButton2 = True Then
ActiveCell.Offset(0, 1).Value = "Lunch"
ElseIf OptionButton3 = True Then
ActiveCell.Offset(0, 1).Value = "Dinner"
Else
ActiveCell.Offset(0, 1).Value = "Bedtime"

End If
If CheckBox1 = True Then
ActiveCell.Offset(0, 3).Value = "Yes"
Else
ActiveCell.Offset(0, 3).Value = "No"
End If
If CheckBox2 = True Then
ActiveCell.Offset(0, 4).Value = "Yes"
Else
ActiveCell.Offset(0, 4).Value = "No"
End If
Range("a4").Select
End Sub

.
Click to expand...
Click to expand...
 
D

Dave Peterson

Just to add to Mike's response...

Mike changed the name of the procedure back to Userform_initialize. This is the
name of one of the standard events--you can't change it (well, and expect it to
work).


Hi Everyone
I've got a userform with a combo box that will not show the list unless I press
the button to activate [ Private Sub UserForm1_Initialize() ] then everything is
ok.
My codes are below, can anyone figure out what I forgot ?
Thanking you in advance
Regards
John

Private Sub UserForm1_Initialize()
OptionButton1 = False
OptionButton2 = False
OptionButton3 = False
OptionButton4 = False
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
CheckBox1 = False
CheckBox2 = False
With ComboBox1
.AddItem "Walking"
.AddItem "Running"
.AddItem "Tennis"
.AddItem "Bicycle"
.AddItem "Hockey"
End With
ComboBox1.Value = ""
End Sub
'Exit Button

Private Sub CommandButton2_Click()
Unload UserForm1
End Sub

'Clear Button
Private Sub CommandButton3_Click()
Call UserForm1_Initialize
End Sub

'Date window
Private Sub TextBox1_enter()
Me.TextBox1.Value = Now
End Sub

'"OK" Button
Private Sub commandbutton1_Click()

Dim RowCount As Long
Sheets("sheet1").Activate
Range("A4").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = TextBox1.Value
ActiveCell.Offset(0, 2) = TextBox2.Value
ActiveCell.Offset(0, 6) = TextBox3.Value
ActiveCell.Offset(0, 5) = ComboBox1.Value

If OptionButton1 = True Then
ActiveCell.Offset(0, 1).Value = "Breakfast"
ElseIf OptionButton2 = True Then
ActiveCell.Offset(0, 1).Value = "Lunch"
ElseIf OptionButton3 = True Then
ActiveCell.Offset(0, 1).Value = "Dinner"
Else
ActiveCell.Offset(0, 1).Value = "Bedtime"

End If
If CheckBox1 = True Then
ActiveCell.Offset(0, 3).Value = "Yes"
Else
ActiveCell.Offset(0, 3).Value = "No"
End If
If CheckBox2 = True Then
ActiveCell.Offset(0, 4).Value = "Yes"
Else
ActiveCell.Offset(0, 4).Value = "No"
End If
Range("a4").Select
End Sub
Click to expand...
 
J

John

Hi Dave
Just learning the hardway.
Regards
John
Dave Peterson said:
Just to add to Mike's response...

Mike changed the name of the procedure back to Userform_initialize. This is
the
name of one of the standard events--you can't change it (well, and expect it
to
work).


Hi Everyone
I've got a userform with a combo box that will not show the list unless I
press
the button to activate [ Private Sub UserForm1_Initialize() ] then everything
is
ok.
My codes are below, can anyone figure out what I forgot ?
Thanking you in advance
Regards
John

Private Sub UserForm1_Initialize()
OptionButton1 = False
OptionButton2 = False
OptionButton3 = False
OptionButton4 = False
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
CheckBox1 = False
CheckBox2 = False
With ComboBox1
.AddItem "Walking"
.AddItem "Running"
.AddItem "Tennis"
.AddItem "Bicycle"
.AddItem "Hockey"
End With
ComboBox1.Value = ""
End Sub
'Exit Button

Private Sub CommandButton2_Click()
Unload UserForm1
End Sub

'Clear Button
Private Sub CommandButton3_Click()
Call UserForm1_Initialize
End Sub

'Date window
Private Sub TextBox1_enter()
Me.TextBox1.Value = Now
End Sub

'"OK" Button
Private Sub commandbutton1_Click()

Dim RowCount As Long
Sheets("sheet1").Activate
Range("A4").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = TextBox1.Value
ActiveCell.Offset(0, 2) = TextBox2.Value
ActiveCell.Offset(0, 6) = TextBox3.Value
ActiveCell.Offset(0, 5) = ComboBox1.Value

If OptionButton1 = True Then
ActiveCell.Offset(0, 1).Value = "Breakfast"
ElseIf OptionButton2 = True Then
ActiveCell.Offset(0, 1).Value = "Lunch"
ElseIf OptionButton3 = True Then
ActiveCell.Offset(0, 1).Value = "Dinner"
Else
ActiveCell.Offset(0, 1).Value = "Bedtime"

End If
If CheckBox1 = True Then
ActiveCell.Offset(0, 3).Value = "Yes"
Else
ActiveCell.Offset(0, 3).Value = "No"
End If
If CheckBox2 = True Then
ActiveCell.Offset(0, 4).Value = "Yes"
Else
ActiveCell.Offset(0, 4).Value = "No"
End If
Range("a4").Select
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Runtime error 91: Object variable or With block variable not set 8
My macro doesn't work if sheet is protected but it works if not protected 2
Word macro that can call cells out of Excel for a find and replace 0
VBA Coding Help for Beginner 0
Copy filtered data to sheet 2 1
Making a macro only work on certain files 2
Entering data from a userform into multiple sheets 2
Optionbutton Not working 3

Top