Link to home
Start Free TrialLog in
Avatar of sidwelle
sidwelleFlag for United States of America

asked on

VBA UserFrom on F5

How can I designate my main useform to be the form that is ran when the user hits F5 anywhere within the VBA editor ?


I don't want the Macro selector, just my form to start / Show.


Thanks 


Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

If you hijack F5 then you will not be able to display the GoTo Dialog which it is usually for. For this reason you cannot use that command.

Here's some better options

Run a macro
The standard way to assign a macro is this:

From the Home tab
  1. Press Alt+F8 to open the "Macro" dialog window
  2. Select the macro
  3. Click ‘Options…’
  4. Hold down the Shift key and type the letter A (or any other letter) and click ‘OK’

Then anytime you want to run the macro press Ctrl+Shift+A.
Avatar of Norie
Norie

What runs/shows when you hit F5 in the VBA Editor depends on what's active, and even where the cursor is located.

If a userform is active then when you press F5 it will show.

You only really get the dialog when it's not clear what should run, for example if a code window is active and the cursor is sitting outside  a procedure.
Avatar of sidwelle

ASKER

I may be distributing this excel sheet to other people in the office,  I don't want them to have to choose a macro, make sure a form is active or in focus.

Just hit F5 or the green arrow.

Thanks

FYI, The macro chooser does not show any options:

User generated image
I don't want them to have to choose a macro, make sure a form is active or in focus.
Using the method I described in my previous post, they don't have to do any of that; all they need to do is to press Ctrl+Shift+A, and if that is too hard to remember, a button could be added to some worksheet and all they need to do is click it. Here is a simple example.

29254677.xlsm
FYI, The macro chooser does not show any options:
The macro needs to be in a code Module like Module1.
sidwelle

How are the people you are distributing this to accessing the userform?

Stick a button on a worksheet and have it call a simple sub like this.
Sub Button1_Click()
    Userform1.Show
End Sub

Open in new window

press Ctrl+Shift+A,   Does nothing on my system ?!
(Nothing on the screen changes)

Windows play a wav, might be an error,  IDK ...
Does it work in my workbook?

What version of Excel are you running? My solution won't work in Excel 365.
See PICs
User generated image
User generated image
I could be wrong but it's my understanding that you can't use macros in Microsoft 365.
Excel Online doesn’t support VBA, just Office-js macros.

Desktop Excel, whether using a Microsoft 365 (Office365) subscription or Office 20xx perpetual version support both VBA and Office-js macros.
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sample workbook illustrating my suggestion

F5-to-display-userform.xlsm
That worked !

I want to leave the question open for a few days to wait and see if anyone has an solution to opening the form from the VBA editor.

Thank you.
...opening the form from the VBA editor.
Just double-click the form's name, or maybe I'm missing something.
That is a big ask for some of our users.
If your users are not familiar enough with VBA to know how to open a userform you certainly don't want them anywhere near your VBA.

What is it that you are trying to accomplish by having them open the userform from VBA?
Final and best solution:
1.  Add a public variable flag to the general section of one of the code modules.
2.  Inside of each Sub in the code modules and in the Form_Initialize Sub of each form module add code to check if the flag has been set to true.
       If not, show your startup form and exit sub.
3.   Add code to your startup forms Form_Initialize  Sub to set the flag to True.

Works perfectly,  All the users have to do is hit F5 and it always starts the form that you want you users to see first !
Done.
I know it's been asked before but why are the users opening the form from the VBA editor?
We created a tool in VBA that I can give out to techs to convert data files in the field.  Not this excel sheet.
Our security team has locked down the ability to run non-authorized apps.
This is our work-around.