[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

How to use Forms in VBA in Excel

Dear Experts,
I have created two simple macro in attached excel file. I want on click of some combination of keys (like ctrl + k), a form (already created) shall appear which will facilitate the selection of Macro to be run.


Please suggest how to do it.

Thanks in advance,
Nilesh.
Example.xls
0
tnilesh2
Asked:
tnilesh2
  • 2
1 Solution
 
Rob HensonIT & Database AssistantCommented:
To have a User Form appear you need to use the .Show function so the macro would be:

Sub ShowForm()

UserForm1.Show    Substitute "UserForm1" for the name of your Form.

End Sub

In the Macros window then highlight the ShowForm macro choose the Options button. here you can allocate a key combination to that macro.

In the subsequent macros include UserForm1.Hide to clear it.

Thanks
Rob H
0
 
broro183Commented:
hi Nilesh,

This is just an aside, as I think Rob H has given the answer to your question.

I believe it is best to include [shift] in the shortcut key combinations that you assign to your macros. The reason for this is that I think there are fewer "native excel shortcuts" that include the [shift] key. I mention this because your example of [ctrl + k] is a "native excel shortcut" for inserting a hyperlink. Any key combination that you assign over-rides any native shortcut for the same combination. This can be very confusing for people & can result in accidental errors being made. Here are a list of shortcuts in excel 2003: http://office.microsoft.com/en-us/excel-help/keyboard-shortcuts-HP005203781.aspx 

An example is when I once pressed [ctrl + s] in a file and expected the file to be saved, because this is what [ctrl + s] "normally" does. However, someone else had over-ridden the native shortcut, and the result was a very frustrated me, as I watched some rows/columns & sheets being deleted in front of my eyes & then I had the extra shock of seeing the progressbar at the bottom of the screen as the file saved itself (as part of the macro). I lost over half an hour of work & was not happy. Ever since that experience I always use the "ListMacro_ShortCutKeys" addin BEFORE doing any work on a file from someone else. This addin was created by a fellow Kiwi, Ivan F Moala, and is available from: http://www.xcelfiles.com/GetShortCutKeys.html 


hth
Rob
0
 
tnilesh2Author Commented:
Dear robhenson,
Thanks for solution.

Dear broro183,
Thanks for suggestion, here I am working with a excel file having 5-6 macros so it is difficult for end user to remember the shortcut keys (or macro name) for each macro & as you rightly explained it may lead to run wrong macro & data may be lost.

To avoid confusion I thought - to provide a user form to end user so that user can select the appropriate task / macro.

Although, I have learned alot from experts like you.
Thanks for your support.
0
 
tnilesh2Author Commented:
Thank you very much.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now