maenicure1981.netlify.com

VBA TextBox Control on the UserForm. Please find more details about VBA ActiveX TextBox Control on the UserForm. Go To Developer Tab and then click Visual Basic from the Code or Press Alt+F11. Go To Insert Menu, Click UserForm. Please find the screenshot for the same. Drag the TextBox control on the Userform from the Toolbox. In Excel worksheet go to developer’s tab and click on the visual basic editor. Now in the insert tab click on userform to open a userform window. We need to add buttons to this userform in order to get input from the user. We can see that a toolbox is also opened.

1. Plots In Excel Userform Excel
2. Advanced Excel Userform Examples

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

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)

Dim TempChart As Chart Dim CatTitles As Range

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)

Set TempChart = Charts.Add

With TempChart

.ChartType = xlColumnClustered

Plots In Excel Userform Excel

.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

.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

Advanced Excel Userform Examples

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

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

' 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?