adding a drop down list to an existing userform

ssil2000

New Member
Joined
Jan 12, 2014
Messages
4
Hi all,

I have a userform which works great an has been for years, I am trying to make it hat little bit better by adding a drop down list (combo box?) with the static information I deal with to save me typing it every time, currently it is simply a text field that I enter data into each time, I have tried to ad a combo box but I cannot seem to be able to get it to work, I can either access a list within the sheet or imbed it into the code im not fussed either way, I just don't know why its so hard I am sure I a missing something simple, no matter what I try the initialize command fails saying missing object

any help would be appreciated.

kind regards,

Sergio
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,381
Hi Sergio and welcome to the forum.

What is the combobox name?
Can you show your code?

Here's example code to populate a combobox with a list.

Code:
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Initialize()
    ComboBox1.List = Array("Jan", "Feb", "Mar")
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 

ssil2000

New Member
Joined
Jan 12, 2014
Messages
4
Thanks Alpha, here is the code that opens the userform

Code:
Sub OpenCourseBookingForm()
    frmCourseBooking.Show
End Sub

which goes to this

Code:
Private Sub UserForm_Initialize()
    txtName.Value = ""
    txtAddress.Value = ""
    txtOrderno.Value = ""
    txtEngOrdno.Value = ""
    txtPourdate.Value = ""
    txtCubes.Value = ""
    TextReoDate.Value = ""
    OptNor = False
    OptBia = False
    OptOne = False
    OptARC = False
    OptSpray = False
    optSemi = False
    chkTest = True
    chkAggregate = True
    chkAggregate32 = False
    txtName.SetFocus
    OptContactAlessandro = False
    OptContactAlex = False
    chkTrench = True
    txtTrenchdate.Value = ""
    OptWhiteAnt = True
End Sub

then the info inputted in the form is outputted as this

Code:
Private Sub cmdOK_Click()
    ActiveWorkbook.Sheets("Bookings").Activate
    Range("B8").Select
    Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(0, 1).Select
    End If
    Loop Until IsEmpty(ActiveCell) = True
    ActiveCell.Value = txtName.Value
    ActiveCell.Offset(1, 0) = txtAddress.Value
    ActiveCell.Offset(2, 0) = txtOrderno.Value
    ActiveCell.Offset(3, 0) = Format(txtPourdate.Value, "dd mmm yy")
    ActiveCell.Offset(5, 0) = txtCubes.Value
    If OptSpray = True Then
        ActiveCell.Offset(6, 0).Value = Format(txtPourdate.Value, "dd mmm yy")
    ElseIf OptNoSpray = True Then
        ActiveCell.Offset(6, 0).Value = ""
    End If
    ActiveCell.Offset(8, 0) = Format(TextReoDate.Value, "dd mmm yy")
    If OptNor = True Then
        ActiveCell.Offset(9, 0).Value = "N"
    ElseIf OptBia = True Then
        ActiveCell.Offset(9, 0).Value = "B"
    ElseIf OptOne = True Then
        ActiveCell.Offset(9, 0).Value = "O"
    ElseIf OptARC = True Then
        ActiveCell.Offset(9, 0).Value = ""
    End If
If OptWhiteAnt = True Then
    ActiveCell.Offset(10, 0).Value = "after 3pm"
ElseIf OptNoWhiteAnt = True Then
    ActiveCell.Offset(10, 0).Value = "No"
    If optSemi = True Then
        ActiveCell.Offset(11, 0).Value = "Yes"
        End If
    ElseIf optNo = True Then
        ActiveCell.Offset(11, 0).Value = "No"
    End If
    If chkTest = True Then
        ActiveCell.Offset(12, 0).Value = "No"
    Else
        ActiveCell.Offset(12, 0).Value = "Yes"
    End If
    If chkAggregate = True Then
        ActiveCell.Offset(13, 0).Value = "20mpa"
    ElseIf chkAggregate32 = True Then
            ActiveCell.Offset(13, 0).Value = "32mpa"
    End If
    If OptContactAlessandro = True Then
        ActiveCell.Offset(14, 0).Value = "A"
    ElseIf OptContactAlex = True Then
        ActiveCell.Offset(14, 0).Value = "Al"
    Else
        ActiveCell.Offset(14, 0).Value = "TBA"
End If
    ActiveCell.Offset(15, 0) = txtEngOrdno.Value
 If chkTrench = True Then
 ActiveCell.Offset(16, 0) = Format(txtTrenchdate.Value, "dd mmm yy")
 ElseIf chkTrench = False Then
 ActiveCell.Offset(16, 0).Value = "NO TRENCH INSPECTION"
End If
    
    Call UserForm_Initialize
    Range("B8").Select
End Sub

so what I am trying to do is replace txtName.Value with the value I choose from the drop down list

this is all the code before I messed with it this all works currently

thanks again for helping it is much appreciated

Sergio
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,381
Try this. Change the array list to suit.

Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
[B]    ComboBox1.List = Array("Jan", "Feb", "Mar")
    ComboBox1.SetFocus
    [COLOR=green]'txtName.Value = ""[/COLOR][/B]
    txtAddress.Value = ""
    txtOrderno.Value = ""
    txtEngOrdno.Value = ""
    txtPourdate.Value = ""
    txtCubes.Value = ""
    TextReoDate.Value = ""
    OptNor = [COLOR=darkblue]False[/COLOR]
    OptBia = [COLOR=darkblue]False[/COLOR]
    OptOne = [COLOR=darkblue]False[/COLOR]
    OptARC = [COLOR=darkblue]False[/COLOR]
    OptSpray = [COLOR=darkblue]False[/COLOR]
    optSemi = [COLOR=darkblue]False[/COLOR]
    chkTest = [COLOR=darkblue]True[/COLOR]
    chkAggregate = [COLOR=darkblue]True[/COLOR]
    chkAggregate32 = [COLOR=darkblue]False[/COLOR]
    [B][COLOR=green]'txtName.SetFocus[/COLOR][/B]
    OptContactAlessandro = [COLOR=darkblue]False[/COLOR]
    OptContactAlex = [COLOR=darkblue]False[/COLOR]
    chkTrench = [COLOR=darkblue]True[/COLOR]
    txtTrenchdate.Value = ""
    OptWhiteAnt = [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdOK_Click()

[B]    [COLOR=darkblue]If[/COLOR] Combobx1.ListIndex = -1 [COLOR=darkblue]Then[/COLOR]
        MsgBox "Please select a name first. ", vbExclamation, "No Name Selected"
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/B]

    ActiveWorkbook.Sheets("Bookings").Activate
    Range("B8").Select
    [COLOR=darkblue]Do[/COLOR]
    [COLOR=darkblue]If[/COLOR] IsEmpty(ActiveCell) = [COLOR=darkblue]False[/COLOR] [COLOR=darkblue]Then[/COLOR]
        ActiveCell.Offset(0, 1).Select
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Loop[/COLOR] [COLOR=darkblue]Until[/COLOR] IsEmpty(ActiveCell) = [COLOR=darkblue]True[/COLOR]
    
    [B]ActiveCell.Value = ComboBox1.Value[/B]
    
    ActiveCell.Offset(1, 0) = txtAddress.Value
    ActiveCell.Offset(2, 0) = txtOrderno.Value
    ActiveCell.Offset(3, 0) = Format(txtPourdate.Value, "dd mmm yy")
    ActiveCell.Offset(5, 0) = txtCubes.Value
    
    [COLOR=darkblue]If[/COLOR] OptSpray = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
        ActiveCell.Offset(6, 0).Value = Format(txtPourdate.Value, "dd mmm yy")
    [COLOR=darkblue]ElseIf[/COLOR] OptNoSpray = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
        ActiveCell.Offset(6, 0).Value = ""
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    ActiveCell.Offset(8, 0) = Format(TextReoDate.Value, "dd mmm yy")
    [COLOR=darkblue]If[/COLOR] OptNor = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
        ActiveCell.Offset(9, 0).Value = "N"
    [COLOR=darkblue]ElseIf[/COLOR] OptBia = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
        ActiveCell.Offset(9, 0).Value = "B"
    [COLOR=darkblue]ElseIf[/COLOR] OptOne = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
        ActiveCell.Offset(9, 0).Value = "O"
    [COLOR=darkblue]ElseIf[/COLOR] OptARC = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
        ActiveCell.Offset(9, 0).Value = ""
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
If OptWhiteAnt = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
    ActiveCell.Offset(10, 0).Value = "after 3pm"
[COLOR=darkblue]Else[COLOR=darkblue]If[/COLOR][/COLOR] OptNoWhiteAnt = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
    ActiveCell.Offset(10, 0).Value = "No"
    [COLOR=darkblue]If[/COLOR] optSemi = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
        ActiveCell.Offset(11, 0).Value = "Yes"
        [COLOR=darkblue]End[/COLOR] If
    [COLOR=darkblue]Else[COLOR=darkblue]If[/COLOR][/COLOR] optNo = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
        ActiveCell.Offset(11, 0).Value = "No"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    If chkTest = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
        ActiveCell.Offset(12, 0).Value = "No"
    [COLOR=darkblue]Else[/COLOR]
        ActiveCell.Offset(12, 0).Value = "Yes"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    If chkAggregate = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
        ActiveCell.Offset(13, 0).Value = "20mpa"
    [COLOR=darkblue]Else[COLOR=darkblue]If[/COLOR][/COLOR] chkAggregate32 = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
            ActiveCell.Offset(13, 0).Value = "32mpa"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    If OptContactAlessandro = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
        ActiveCell.Offset(14, 0).Value = "A"
    [COLOR=darkblue]Else[COLOR=darkblue]If[/COLOR][/COLOR] OptContactAlex = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
        ActiveCell.Offset(14, 0).Value = "Al"
    [COLOR=darkblue]Else[/COLOR]
        ActiveCell.Offset(14, 0).Value = "TBA"
[COLOR=darkblue]End[/COLOR] If
    ActiveCell.Offset(15, 0) = txtEngOrdno.Value
 If chkTrench = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
 ActiveCell.Offset(16, 0) = Format(txtTrenchdate.Value, "dd mmm yy")
 [COLOR=darkblue]ElseIf[/COLOR] chkTrench = [COLOR=darkblue]False[/COLOR] [COLOR=darkblue]Then[/COLOR]
 ActiveCell.Offset(16, 0).Value = "NO TRENCH INSPECTION"
[COLOR=darkblue]End[/COLOR] If
    
    [COLOR=darkblue]Call[/COLOR] UserForm_Initialize
    Range("B8").Select
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 

ssil2000

New Member
Joined
Jan 12, 2014
Messages
4

ADVERTISEMENT

Thanks Alpha I am getting object required here when I run the macro
Code:
Sub OpenCourseBookingForm()
    frmCourseBooking.Show
End Sub
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,381
It doesn't recognize frmCourseBooking as an object. Double-check the spelling of its name.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,804
Messages
5,598,158
Members
414,214
Latest member
marketingnumbersguy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top