Help needed to make Combo box code work in VB form for Excel 2003

[Amanda22]

Hello,

I am new to Visual Basic and have put together the following code for a form to enter basic details into a worksheet in Excel. On the first worksheet page, I have a macro button which opens the VB form. The form works and the details are entered in my second worksheet when I press OK. However, I am having a problem with the Combo boxes as they don't show any of the values that I have entered in the code for the form.

Could someone please have a look at my code and tell me where I am going wrong.

Many thanks in advance.

The code I currently have is:


Private Sub UserForm1_Initialize()

With TitleListBox
TitleListBox.AddItem "Mr", 1
TitleListBox.AddItem "Mrs", 2
TitleListBox.AddItem "Ms", 3
TitleListBox.AddItem "Miss", 4
TitleListBox.AddItem "Dr", 5
End With

TitleListBox_Click() = TitleListBox.Value = ""
FirstNameBox_Click() = FirstNameBox.Value = ""
SurnameBox_Click() = SurnameBox.Value = ""
HouseBox_Click() = HouseBox.Value = ""
RoadBox_Click() = RoadBox.Value = ""
TownBox_Click() = TownBox.Value = ""
CityBox_Click() = CityBox.Value = ""
CountyBox_Click() = CountyBox.Value = ""
PostcodeBox_Click() = PostcodeBox.Value = ""
DOBBOX_Click() = DOBBOX.Value = ""
NIBox_Click() = NIBox.Value = ""
ContactNameBox_Click() = ContactNameBox.Value = ""
ContactTelephoneBox_Click() = ContactTelephoneBox.Value = ""
ContactMobileBox_Click() = ContactMobileBox.Value = ""
ContactAddressBox_Click() = ContactAddressBox.Value = ""
DateofVisitBox_Click() = DateofVisitBox.Value = ""

With DurationListBox
.AddItem "15", 1
.AddItem "30", 2
.AddItem "45", 3
.AddItem "60", 4
.AddItem "75", 5
.AddItem "90", 6
.AddItem "90+", 7
End With

DurationListBox.Value = ""

With VisitListBox
.AddItem "1", 1
.AddItem "2", 2
.AddItem "3", 3
.AddItem "4", 4
.AddItem "5", 5
.AddItem "6", 6
.AddItem "7", 7
.AddItem "8", 8
.AddItem "9", 9
.AddItem "10", 10
End With

VisitListBox.Value = ""

ReferredHeardBox_Click() = ReferredHeardBox.Value = ""
LivingWithBox_Click() = LivingWithBox.Value = ""

With RelationshipListBox
.AddItem "Husband/Wife"
.AddItem "Civil Partner"
.AddItem "Parent"
.AddItem "Child"
.AddItem "Sibling"
.AddItem "Friend"
.AddItem "Landlord"
End With

RelationshipListBox.Value = ""

With HouseIsListBox
.AddItem "Privately Owned"
.AddItem "Rented"
.AddItem "Council Property"
.AddItem "Housing Trust"
.AddItem "Shared Ownership"
End With

HouseIsListBox.Value = ""

IfOtherBox_Click() = IfOtherBox.Value = ""

FirstNameBox.SetFocus
End Sub
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub ClearButton_Click()
Call UserForm1_Initialize

End Sub
Private Sub OkButton_Click()
ActiveWorkbook.Sheets("Basic Information").Activate
Range("A2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Offset(0, 1) = TitleListBox.Value
ActiveCell.Offset(0, 2) = FirstNameBox.Value
ActiveCell.Offset(0, 3) = SurnameBox.Value
ActiveCell.Offset(0, 4) = HouseBox.Value
ActiveCell.Offset(0, 5) = RoadBox.Value
ActiveCell.Offset(0, 6) = TownBox.Value
ActiveCell.Offset(0, 7) = CityBox.Value
ActiveCell.Offset(0, 8) = CountyBox.Value
ActiveCell.Offset(0, 9) = PostcodeBox.Value
ActiveCell.Offset(0, 10) = DOBBOX.Value
ActiveCell.Offset(0, 11) = NIBox.Value
ActiveCell.Offset(0, 12) = ContactNameBox.Value
ActiveCell.Offset(0, 13) = ContactTelephoneBox.Value
ActiveCell.Offset(0, 14) = ContactMobileBox.Value
ActiveCell.Offset(0, 15) = ContactAddressBox.Value
ActiveCell.Offset(0, 16) = DateofVisitBox.Value
ActiveCell.Offset(0, 17) = DurationListBox.Value
ActiveCell.Offset(0, 18) = VisitListBox.Value
ActiveCell.Offset(0, 19) = ReferredHeardBox.Value
ActiveCell.Offset(0, 22) = LivingWithBox.Value
ActiveCell.Offset(0, 23) = RelationshipListBox.Value
ActiveCell.Offset(0, 24) = HouseIsListBox.Value
ActiveCell.Offset(0, 25) = IfOtherBox.Value
If YesOptionButton = True Then
ActiveCell.Offset(0, 20).Value = "Yes"
Else: NoOptionButton = True
ActiveCell.Offset(0, 20).Value = "No"
End If

End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

Firstly remove the 1 from:

Private Sub UserForm1_Initialize()

Secondly I don't understand statements like:

TitleListBox_Click() = TitleListBox.Value = ""

It should be just:

TitleListBox.Value = ""

 

[Amanda22]

Thank you for your response Andrew.

I have amended the line to just

TitleListBox.Value = ""

I had named the Form as UserForm1 but will change it back if you think that will make a difference. As I said, the form works as far as sending the information to the worksheet, it is only the combo boxes that do not show the options I have added.

Any other suggestions would be much appreciated.

 

[Andrew Poulsom]

Event procedures for a UserForm always begin with UserForm_ not with the name of the UserForm.

 

[Amanda22]

Thank you again.

I have changed the name to just UserForm_ but now I get the error saying Run-time error '424' Object Required.

When I go to Debug, I have the following code for the Command button:

Private Sub CommandButton1_Click()
UserForm.Show
End Sub

This initially said UserForm1 but now neither of them will work.

Again, any advice is much appreciated.

 

[Andrew Poulsom]

It's only event procedures that start with the generic UserForm_. If you want to use the UserForm's methods you need to use its actual name:

UserForm1.Show

Confusing isn't it?

 

[Amanda22]

It is very confusing, yes! I'm still trying to get my head around the basics really.

I have tried to change it back to say UserForm1.Show but it just highlights it in yellow and will not work now when I click the command button on the worksheet.

 

[Andrew Poulsom]

What error do you get?

 

[Amanda22]

When I click the button, I now get:

Run-time error '-2147024809 (80070057)':

Invalid Argument

Then when I click Debug, I get the UserForm1.Show highlighted in yellow.

 

[Andrew Poulsom]

In the Visual Basic Editor choose Tools|Options and check 'Break in Class Module' on the General tab, then click OK. Click your CommandButton. What line is highlighted when you get the error now?