run time error 424 object required - how to load control on Userfo

[Janis]

I know what the problem is but not how to solve it. The problem is there is
a list on the
worksheet that gets edited by the users so it has to stay on sheet 1. I
need to capture this range and fill the list box. I have the variable range
which I think needs to be in a procedure/module not in a userForm? So in
that case how do I access it from the userForm?

The other question is how do I get it to default on the first item in the
listbox?

The last question is do I write separate combox events to run a procedure
for each one?
Basically comboBox1 is a list of names. Combox3 is a choice of print one
name or print all names in a procedure.

Thanks,


Private Sub UserForm_Initialize()
Set rng = ActiveSheet.UsedRange


ComboBox1.AddItem "Print Single"
ComboBox1.AddItem "Print Multiple"
ComboBox1.AddItem "Exit"
ComboBox3.AddItem Date
'ComboBox1.ListIndent = 1
ListBox1.ColumnCount = rng
End Sub

 

[FSt1]

hi
1. setting a listbox's row source is part of the Userform_Initialize() sub.
i use this line in one of you forms.
b1.RowSource = ThisWorkbook.Sheets("Sheet1") _
.Range("A212").Address(external:=True)
the form is in my personal.xls which is why external = true ie another file.
if the form is in the active workbook, you don't need that.
2. right click in design mode, click view code. you load comboboxes pretty
much the same way you do list boxes. set the rowsource in the initialization.

Regards
FSt1

 

[Janis]

Thanks for your speedy reply. I will try this out and if I can't get it to
work I'll get back to the list. This is urgent.

 

[FSt1]

you're welcome
regards
FSt1

Thanks for your speedy reply. I will try this out and if I can't get it to
work I'll get back to the list. This is urgent.

 

[Dave Peterson]

Another option...

First, I don't like to use the .usedrange property. It can be more than I
expect. If I can pick out a column that always has data in it when the row is
used, I'll depend on that to determine the number of rows that the database has.

Same thing with depending on a row to determine the number of columns that the
database has.

And I wouldn't use ActiveSheet if I know I want to pick up data from Sheet1.

If you know the names of the procedures that each combobox option provides, you
could translate the option into the name of that procedure and then use
application.run to start it.

I don't think I'd use the _change event for the combobox to start things off.
I'd have an "Ok" button and a "Cancel Button" on the userform (eliminate the
"exit" option from the combobox???).

I created a small userform with 3 comboboxes, 1 listbox, and a couple of
commandbuttons.

This is the code behind the userform:

Option Explicit
Private Sub ListBox1_Change()
Call OkButtonChk
End Sub
Private Sub ComboBox1_Change()
Call OkButtonChk
End Sub
Private Sub ComboBox2_Change()
Call OkButtonChk
End Sub
Private Sub ComboBox3_Change()
Call OkButtonChk
End Sub
Private Sub CommandButton1_Click()
Dim myMacName As String
Select Case LCase(Me.ComboBox1.Value)
Case Is = LCase("Print Single"): myMacName = "aaa"
Case Is = LCase("Print Multiple"): myMacName = "bbb"
End Select

Application.Run myMacName, Me.ComboBox2.Value, Me.ComboBox3.Value

End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim Rng As Range
Dim wks As Worksheet
Dim LastRow As Long
Dim LastCol As Long

Set wks = Worksheets("sheet1")
With wks
'Set Rng = .UsedRange
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
'headers in row 1, so start with row 2
Set Rng = .Range("A2", .Cells(LastRow, LastCol))
End With

With Me.ComboBox1
.AddItem "Print Single"
.AddItem "Print Multiple"
'.AddItem "Exit"
.ListIndex = 0 'first item in the dropdown
End With

With Me.ComboBox2
.AddItem "a"
.AddItem "b"
End With

With Me.ComboBox3
.AddItem Date
End With

With Me.ListBox1
.ColumnHeads = True
.ColumnCount = Rng.Columns.Count
.RowSource = Rng.Address(external:=True)
End With

With Me.CommandButton1
.Caption = "Ok"
.Default = True
.Enabled = False
End With

With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
.Enabled = True
End With

End Sub
Private Sub OkButtonChk()

Dim AllOk As Boolean

AllOk = True
If Me.ListBox1.ListIndex < 0 Then
AllOk = False
End If

If Me.ComboBox1.ListIndex < 0 Then
AllOk = False
End If

If Me.ComboBox2.ListIndex < 0 Then
AllOk = False
End If

If Me.ComboBox3.ListIndex < 0 Then
AllOk = False
End If

Me.CommandButton1.Enabled = AllOk

End Sub

And this code is in a General Module:

Option Explicit
Sub aaa(Parm1 As String, Parm2 As String)
MsgBox "aaa" & vbLf & Parm1 & vbLf & Parm2
End Sub
Sub bbb(Parm1 As String, Parm2 As String)
MsgBox "bbb" & vbLf & Parm1 & vbLf & Parm2
End Sub