Pass parameter to a form and then open it?
 

[Excel 2000]
Is there a combobox-style Inputbox hidden away in Excel anywhere? Or is it
possible to pass parameters to a form as it loads?

I have a macro, which in the middle of the code requires input from the
user.
Presents a form with a combobox on it.
The combobox is filled with a list - lets call it MyList.
Based on the value of a variable in the macro (call it MyValue), a
specific entry in the list is already selected.
The user then either accepts the offered value or selects a different
value from the list.
The macro then stores the result in a variable (MyResult) and continues
the code.

I don't want to use a standard inputbox because many of the entries are
long strings, and it will be irritating to type them in.
But using a form is complicated by the need to pass the default list
entry to the form.

I hope I've explained my problem clearly - can anyone suggest a solution?

Darren

Pass parameter to a form and then open it?
 

Darren,

Here is an example of some code that loads a form combobox from a worksheet
range, and then selects an item based jupon a value in B1.

Load UserForm1
With UserForm1
.ComboBox1.RowSource = Range("A1:A10").Address(False, False)
.ComboBox1.ListIndex = Range("B1").Value
.Show
End With

Post back if you need further explanation .

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Darren Hill" wrote in message
...
[Excel 2000]
Is there a combobox-style Inputbox hidden away in Excel anywhere? Or is it
possible to pass parameters to a form as it loads?

I have a macro, which in the middle of the code requires input from the
user.
Presents a form with a combobox on it.
The combobox is filled with a list - lets call it MyList.
Based on the value of a variable in the macro (call it MyValue), a
specific entry in the list is already selected.
The user then either accepts the offered value or selects a different
value from the list.
The macro then stores the result in a variable (MyResult) and
continues
the code.

I don't want to use a standard inputbox because many of the entries
are
long strings, and it will be irritating to type them in.
But using a form is complicated by the need to pass the default list
entry to the form.

I hope I've explained my problem clearly - can anyone suggest a solution?

Darren

Pass parameter to a form and then open it?
 

Here is an adaptation of Bob's code that uses your examples:

Load UserForm1
With UserForm1
.ComboBox1. = Range("MyList")
.ComboBox1.Value = MyValue
.Show
msgbox .Combobox1.Value
End With
Unload Userform1

in userform1

You need to disable the users ability to close userform1 except by using a
close button you provide. This can be done with the queryclose event. then
have you button hide the userform so you can get the value of the combobox,
or as an alternate approach, you can use a public variable to capture the
selection using the click event.

--
Regards,
Tom Ogilvy

Darren Hill wrote in message
...
[Excel 2000]
Is there a combobox-style Inputbox hidden away in Excel anywhere? Or is it
possible to pass parameters to a form as it loads?

I have a macro, which in the middle of the code requires input from the
user.
Presents a form with a combobox on it.
The combobox is filled with a list - lets call it MyList.
Based on the value of a variable in the macro (call it MyValue), a
specific entry in the list is already selected.
The user then either accepts the offered value or selects a different
value from the list.
The macro then stores the result in a variable (MyResult) and
continues
the code.

I don't want to use a standard inputbox because many of the entries
are
long strings, and it will be irritating to type them in.
But using a form is complicated by the need to pass the default list
entry to the form.

I hope I've explained my problem clearly - can anyone suggest a solution?

Darren

Pass parameter to a form and then open it?
 

Thanks Tom and Bob, that's exactly what I needed.
Somehow, although I use Unload a lot, I had overlooked the Load Userform
method :)
I had come up with a workaround using public variables but I like this
method better (along with the form.hide method Tom suggests to finish off
the routine - I doubt that would have occurred to me).

--
Darren
"Tom Ogilvy" wrote in message
...
Here is an adaptation of Bob's code that uses your examples:

Load UserForm1
With UserForm1
.ComboBox1. = Range("MyList")
.ComboBox1.Value = MyValue
.Show
msgbox .Combobox1.Value
End With
Unload Userform1

in userform1

You need to disable the users ability to close userform1 except by using a
close button you provide. This can be done with the queryclose event.
then
have you button hide the userform so you can get the value of the
combobox,
or as an alternate approach, you can use a public variable to capture the
selection using the click event.

--
Regards,
Tom Ogilvy

Darren Hill wrote in message
...
[Excel 2000]
Is there a combobox-style Inputbox hidden away in Excel anywhere? Or is
it
possible to pass parameters to a form as it loads?

I have a macro, which in the middle of the code requires input from the
user.
Presents a form with a combobox on it.
The combobox is filled with a list - lets call it MyList.
Based on the value of a variable in the macro (call it MyValue), a
specific entry in the list is already selected.
The user then either accepts the offered value or selects a
different
value from the list.
The macro then stores the result in a variable (MyResult) and
continues
the code.

I don't want to use a standard inputbox because many of the entries
are
long strings, and it will be irritating to type them in.
But using a form is complicated by the need to pass the default list
entry to the form.

I hope I've explained my problem clearly - can anyone suggest a
solution?

Darren