To assign a control key, you need to write a macro
that "opens" the form.
Sub Show_form()
userform1. Show
End Sub
then assign THAT macro to the control key.
Now, here's a tricky thing about forms:
If you want to know which item in the listbox the
user has selected, you have to loop through the
listbox elements and find out if an element is selected.
so, for Userform1 and Listbox1 you can:
For I = 0 To UserForm1.ListBox1.ListCount - 1
If (UserForm1.ListBox1.Selected(I)) Then
msgbox "Macro selected: " & UserForm1.ListBox1.List(I)
Exit For
End If
Next I
Now the tricky part:
Let's say you want to get rid of the form before you run the macro.
There are two ways:
UserForm1.Hide
Unload UserForm1
the difference is, if you Hide the form, the values are still there,
but if you "Unload" the form, the values are lost,
So:
UserForm1.Hide
For I = 0 To UserForm1.ListBox1.ListCount - 1
If (UserForm1.ListBox1.Selected(I)) Then
msgbox "Macro selected: " & UserForm1.ListBox1.List(I)
Exit For
End If
Next I
Unload UserForm1
is ok,
but
Unload UserForm1
For I = 0 To UserForm1.ListBox1.ListCount - 1
If (UserForm1.ListBox1.Selected(I)) Then
msgbox "Macro selected: " & UserForm1.ListBox1.List(I)
Exit For
End If
Next I
UserForm1.Hide
will not work
Now, here's something else.
to RUN the macro that was selected,
I THINK (I've not tried it) you have to use:
Call UserForm1.ListBox1.List(I)
keep in mind that the items in the Listbox MUST be the ACTUAL
Macro names!
If the macro is called "Macro1" and you
have the listbox say: "Macro 1" so that it is easier to read,
then it will not work because "Call Macro 1" isn't valid!
If you want to use "Friendly Names", then you'll have to do something
like:
UserForm1.Hide
For I = 0 To UserForm1.ListBox1.ListCount - 1
If (UserForm1.ListBox1.Selected(I)) Then
msgbox "Macro selected: " & UserForm1.ListBox1.List(I)
Select Case Ucase(UserForm1.ListBox1.List(I))
Case Ucase("Macro 1")
Call Macro1
Case Ucase("Macro 2")
Call Macro2
Case Ucase("Macro 3")
Call Macro3
Case Else
msgbox "Selection:" & chr(13) & UserForm1.ListBox1.List(I) &
" Not Recognized"
Exit sub
End Select
Exit For
End If
Next I
Unload UserForm1
hope this didn't get too confusing.
Paul
On Nov 2, 11:58 am, CPGH <ch...@vvsii.com> wrote:
> Hi Rolf,
>
> Futher to this post, a couple of quick questions:
>
> 1. How do I assign the form to a ctrl+key so that when the user wants
> to select a macro to run on the form that is the first thing that pops
> up?
>
> 2. How do I call a form from a macro in a module? This way if I want
> to get input from a user during a macro that is already running to ask
> something like a radial selection of a couple of options they can
> select the option needed and click ok to continue.
>
> Thanks,
> Chris
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
