I have a workbook that opens a user-form when the workbook is opened. This has caused a problem because it then disables all open excel workbooks so users cant make changes to other workbooks Is t. I have an Excel VBA userform that loads with some data from a spreadsheet when it opens/initialises. I am also wanting to include in the userform a chart image (the chart exists on the spreadsheet). Can anybody help? I am hoping to avoid the image being stored in a folder (i would prefer it to be stored in the file) but would reconsider if that. Excel VBA Userform. Userform in VBA are customized user-defined forms which are made to take input from a user in the format of a form, it has different sets of controls to add such as text boxes, checkboxes labels, etc to guide a user to input a value and it stores the value in the worksheet, every part of user form has a unique code with it. Userform is an object within the Excel interface.

1. Plots In Excel Userform Tutorial
2. View Userform In Excel Sheet
3. Excel Userform Tutorial
4. Plots In Excel Userform Spreadsheet
5. Vba Excel Userform Example
6. Advanced Excel Userform Examples
7. Excel Vba Userform

The Excel VBA UserForm allows you to create a new Excel custom Window with select Form or ActiveX controls such a Button, ListBox, CheckBox and other controls. You can Show or Hide the UserForm and customize it as needed.

Below you will find a complete tutorial on how to create and customize your own Excel VBA UserForm.

Create VBA UserForm

User Forms fill the gap where a Message Box won’t do. UserForms allow you to create custom forms with Buttons, Combo-boxes, Scrollbars and many more controls.

Open the VBA Project to add a new UserForm

A new UserForm will be inserted into your VBA Project under a new branch called Forms.

Open the new UserForm and the Toolbox to view controls

Let’s create a simple form. First we need to see what form controls can we add to it. To view the Toolbox go to the View menu and select Toolbox:

Add Button to UserForm

Now let’s add a simple button to our new form. To do that we simply need to drag it from the Toolbox. Hit the Button icon and drag it to the new form:

To add code to your Button – double-click the button on the UserForm. This will navigate you to the code section of the UserForm. You can also go to the View code section as shown below:
This should create the following code stub:

Let’s just add a simple MessageBox to the new form to see that the button works:

Show / Hide User Form

Now as we have created our new UserForm let’s put it to the test. Below a simple example of how to execute your UserForm:

Try clicking on the button to see that it works!
If you want to close the VBA UserForm use the Hide procedure. I replaced the contents of the Button Click procedure above as per below:

UserForm Events

VBA Events are procedures that run automatically when something happens. VBA UserForm events are similar to VBA Worksheet or VBA Workbook events. The following is a list of available VBA UserForm events for Excel:

Adding UserForm Events

To add Events to your UserForm use the drop-downs in the VBA Module of the Form:
Below are examples UserForm events:

In Chapter 15, I describe a way to display a chart in a UserForm. The technique saves the chart as a GIF file and then loads the GIF file into an Image control on the UserForm.

The example in this section uses that same technique but adds a new twist: The chart is created on the fly and uses the data in the row of the active cell. Figure 18-9 shows an example.

Figure 18-9: The chart in this UserForm is created on-the-fly from the data in the active row.

The UserForm for this example is very simple. It contains an Image control and a CommandButton (Close). The worksheet that contains the data has a button that executes the following procedure:

Sub ShowChart()

Dim UserRow As Long UserRow = ActiveCell.Row

Plots In Excel Userform Tutorial

If UserRow < 2 Or IsEmpty(Cells(UserRow, 1)) Then MsgBox _

'Move the cell cursor to a row that contains data.' Exit Sub End If

CreateChart UserRow UserForm1.Show End Sub

Because the chart is based on the data in the row of the active cell, the procedure warns the user if the cell cursor is in an invalid row. If the active cell is appropriate, ShowChart calls the CreateChart procedure to create the chart and then displays the UserForm.

The CreateChart procedure shown in Listing 18-3 accepts one argument, which represents the row of the active cell. This procedure originated from a macro recording that I cleaned up to make more general.

Listing 18-3: Automatically Generating a Chart without User Interaction

Sub CreateChart(r)

View Userform In Excel Sheet

Dim TempChart As Chart Dim CatTitles As Range

Excel Userform Tutorial

Dim SrcRange As Range, SourceData As Range

Application.ScreenUpdating = False

Set CatTitles = ActiveSheet.Range('A2:F2')

Set SrcRange = ActiveSheet.Range(Cells(r, 1), Cells(r, 6))

Set SourceData = Union(CatTitles, SrcRange)

Plots In Excel Userform Spreadsheet

Set TempChart = Charts.Add

With TempChart

.ChartType = xlColumnClustered

.SetSourceData Source:=SourceData, PlotBy:=xlRows .HasLegend = False

.PlotArea.Interior.Colorlndex = xlNone .Axes(xlValue).MajorGridlines.Delete .ApplyDataLabels Type:=xlDataLabelsShowValue, _ LegendKey:=False .ChartTitle.Font.Size = 14 .ChartTitle.Font.Bold = True .Axes(xlValue).MaximumScale = 0.6 .Axes(xlCategory).TickLabels.Font.Size = 10 .Axes(xlCategory).TickLabels.Orientation = _ xlHorizontal

Vba Excel Userform Example

.Location Where:=xlLocationAsObject, Name:='Sheet1' End With

' Adjust the ChartObject's size With ActiveSheet.ChartObjects(1) .Width = 300

Continued

Listing 18-3 (Continued)

.Height = 150 .Visible = False End With End Sub

When the CreateChart procedure ends, the worksheet contains a ChartObject with a chart of the data in the row of the active cell. However, the ChartObject is not visible because ScreenUpdating was turned off and its Visible property was set to False.

The final instruction of the ShowChart procedure loads the UserForm. Following is a listing of the UserForm_Initialize procedure. This procedure saves the chart as a GIF file, deletes the ChartObject, and loads the GIF file into the Image control.

Private Sub UserForm_Initialize() Dim CurrentChart As Chart Dim Fname As String

Set CurrentChart = ActiveSheet.ChartObjects(1).Chart Save chart as GIF

Fname = ThisWorkbook.Path & Application.PathSeparator & 'temp.gif'

Advanced Excel Userform Examples

CurrentChart.Export FileName:=Fname, FilterName:='GIF' ActiveSheet.ChartObjects(1).Delete

Excel Vba Userform

' Show the chart

Imagel.Picture = LoadPicture(Fname) Application.ScreenUpdating = True End Sub

This workbook is available on the companion CD-ROM.

Continue reading here: Understanding Chart Events

Was this article helpful?