Combo Boxes in Excel VBA

[AnnaWnz]

Hi all

I am trying to get a user to make a selection from a combo box on opening the spreadsheet.

So far I have:

(In Micrsoft Excel Projects - This Workbook)
Private Sub Workbook_Open()
Port = Range("D5&quot.Value
If Port = "" Then
MsgBox "error"
Call frmPort_Open
End If
End Sub

(in Forms)
Private Sub frmPort_Intialize()
cboPort.AddItem "One"
cboPort.AddItem "Two"
cboPort.AddItem "Three"
cboPort.AddItem "Four"
End Sub

Public Sub frmPort_Open()
FrmPort.Show
End Sub

frmPort is the name of the form
cboPort is the name of the combobox

However, when this code runs, I get the error "Compile Error: Function of Sub not Defined" on the line Call frmPort_Open. And the form never appears. If I move the Public Sub frmPort_Open () to be in In Micrsoft Excel Projects - This Workbook, the form shows, but there are no values in my combo box.

What have I got wrong???

Cheers
Anna

 

[Loomah]

Don't actually know the technical reasons why but use

Private Sub UserForm_Initialize()
    cboPort.AddItem "One"
    cboPort.AddItem "Two"
    cboPort.AddItem "Three"
    cboPort.AddItem "Four"
End Sub

instead of Private Sub frmPort_Intialize()
(really don't know why!)
and

Call frmPort.frmPort_Open

in your calling proc

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[SkipVought]

anna,
Do you really need the userform? Or does the user just need to make a selection?

You could put the combobox directly on a worksheet, make it visible when the workbook is opened (or any other time) and invisible after the user makes a selection.

Here's the code...
In the Workbook Object...

Private Sub Workbook_Open()
    With Worksheets("Sheet1").ComboBox1
        .Visible = True
        .AddItem "one"
        .AddItem "two"
        .AddItem "three"
        .AddItem "four"
'put it where you want
        .Top = [A1].Top
        .Left = [A1].Left
    End With
End Sub

In the Worksheet Object...

Private Sub ComboBox1_Change()
    With ComboBox1
'put the selected value on the sheet if/where you want
        [A1].Value = .Value
        .Visible = False
    End With
End Sub

Hpe this helps
Skip,
SkipAndMary1017@mindspring.com

 

[RobBroekhuis]

Skip's suggestion is a good one. If you do want to go with the userform approach, keep the frmport_open sub outside of your userform code. Even when declared public, the subs inside the code sheets can't be found from outside.
Rob