adding a drop down list to an existing userform

S

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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
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]
 
Upvote 0
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
 
Upvote 0
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]
 
Upvote 0
Thanks Alpha I am getting object required here when I run the macro
Code: 
Sub OpenCourseBookingForm()
    frmCourseBooking.Show
End Sub
 
Upvote 0
It doesn't recognize frmCourseBooking as an object. Double-check the spelling of its name.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Show different values to drop down box
Replies
1
Views
175
Scott Huish
Scott Huish
K
  • Solved
I have code that works for creating a drop-down list on a WORKSHEET that allows you to select multiple items... can this also be done on a USERFORM?
Replies
2
Views
150
kbishop94
K
J
  • Solved
Auto-populate cell if data validation list has only one option
Replies
7
Views
208
Jeffrey Mahoney
Jeffrey Mahoney
R
  • Question
VBA Loop Through Data Validation Drop Down List
Replies
5
Views
308
rlv01
R
L
  • Question
Picture appear when certain drop down list info selected.
2
Replies
16
Views
598
kevin9999
K

Forum statistics

Threads
1,214,649
Messages
6,120,728
Members
448,987
Latest member
marion_davis

Share this page

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
Back
Top