How To Create A VBA Form In Excel

Now let’s enter the properties for the initial form, you’ll see that I’ve only used 5 of the 35 or so available properties, that’s the thing with programming, don’t get put off by the massive array of things that have to be adjusted, chances are you’ll only need to use a small subset, and it’s usually the same subset each time.

Next we need to add some controls to the form, in this case two text boxes and one command button to close it.

Firstly we need to ensure the vba toolbox is displayed, its context sensitive and is only visible when a form in design mode has focus.

So click on your form then choose View, then Toolbox from you vba IDE. Find where your toolbox appears on your desktop then drag the command button and the textbox twice to your form.

You can either manually size and position them using the mouse or better still use the properties panel to set their width, height, left and top positions (first click on the control, then enter those properties). Typing values into the properties panel is the best way to ensure that everything is sized and positioned evenly and the way you like.

After sizing and positioning your controls you should get something that looks like this.

Adding Some Code To The Form

We need to add some code to the vba form to make everything work, so first lets open the VBA code module that’s "hidden" in the form.

Select the form in the project window then double click the view code button, the "code behind the form" module will become active in the main code window.

Type in the following code, then your "good to go" note for the purposes of this demo you don’t need lines 7 through 11, those lines enable the functionality in the YouTube video that demos double clicking on a range with certain data being captured on the form.

01  Option Explicit
02
03  Private Sub cmdClose_Click()
04      Unload Me ' Closes the form
05  End Sub
06
07  Public Property Let aaaRowInfo(ByRef vData As Variant)
08      ' vData in this instance is a two element array
09      Me.txtCountryName = vData(1)
10      Me.txtRegion = vData(0)
11  End Property
12
13  Public Property Let aaaRegion(ByVal region As String)
14      Me.txtRegion.Value = region
15  End Property
16

Make The Form Work

We now need to write the code and add a button to launch the form (the download that goes with this article has code to launch from a double click on the sheet also, but this would take too long here)

The first step is to put a button on the spreadsheet, from the developer tab (make sure you’ve added the developer tab to your ribbon menu) choose "insert" from the "Controls" section and click the button indicated by the arrow below, use this to draw a button on your spreadsheet.

Now it’s time to add some code, so first we need to add a code module to the vba project, so from within your vba project choose Insert then Module. If it’s the first module added it will get the default name "Module 1" this can changed via the properties window (and yes that would be a good idea).

Double Click on Module 1 and add the following code, given that you’ve already named the form frmGrabRow (you have, haven’t you?).

01  Option Explicit
02
03  Sub launchMyForm()
04    frmGrabRow.aaaRegion = "Some Region"
05    frmGrabRow.Show
06  End Sub

Assign The Macro Button To The launchMyForm() code

Now the finishing touch is make that button run the launchMyForm code. So you need to right click over the button and choose "Assign Macro…"

Then click on the macro you created above and choose OK!

You can then click the button and the form will run, happy coding…

Wanna grab me a coffee? – Then Click This Green Box

If you find this article or videos really helpful, then consider grabbing me a coffee, it takes a lot of work and expense to keep this site going, the more caffine I have the harder I can work.

And if you do grab me a coffee (just click anywhere on this green area), thanks for your support 😉