How to use Forms in VBA in Excel

Posted on 2011-10-10
Last Modified: 2012-05-12
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,
Question by:tnilesh2
    LVL 31

    Accepted Solution

    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.

    Rob H
    LVL 10

    Expert Comment

    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:

    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:


    Author Comment

    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.

    Author Closing Comment

    Thank you very much.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now