Excel VBA Userform with Multiple Pages - Easy Excel Macros

7 Pages • 840 Words • PDF • 378.1 KB
Uploaded at 2021-09-24 17:05

This document was submitted by our user and they confirm that they have the consent to share it. Assuming that you are writer or own the copyright of this document, report to us by using this DMCA report button.


Excel  Easy #1 Excel tutorial on the net

Excel

Introduction

Basics

Functions

Data Analysis

Userform with Multiple Pages

  Below we will look at a program in Excel VBA which creates a Userform that contains multiple pages. This userform also contains images. The Multipage Control contains two pages. At page 1, the user can fill in his/her personal information.

VBA

300 Examples

Search Entire Site

Ask us

Go

Chapter    Userform

At page 2, the user can indicate which painting he/she likes the most.

Learn more, it's easy Userform and Ranges Currency Converter Progress Indicator Multiple List Box Selections Multicolumn Combo Box Dependent Combo Boxes Loop through Controls Controls Collection Userform with Multiple Pages Interactive Userform

Download Excel File userform-with-multiple-pages.xlsm

Download Images

Evita los errores de packaging

mountains.jpg sunset.jpg

Inspecciona artes en segundos

beach.jpg

Controla texto, imágenes, códigos de barra, códigos QR, impresión en braille, etc.

winter.jpg

arviscan.com

Follow Excel Easy

ABRIR To create this Userform, execute the following steps.

Become an Excel Pro

1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer. 2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen

300 Examples

should be set up as below.  

3. Add the Multipage control, labels, text boxes (first at the top, the second below the first), frame, option buttons (first at the left, the second at the right), list box, Image control and command button. Once this has been completed, the result should be consistent with the empty version of the Userform shown earlier. For example, create a Multipage control by clicking on Multipage from the Toolbox. Next, you can drag a Multipage control on the Userform. When you arrive at the Gender frame, remember to draw this frame first before you place the two option buttons in it. 4. You can change the names and the captions of the controls. Names are used in the Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of the controls, but it is not necessary here because we only have a few controls in this example. To change the caption of the Userform, Multipage tabs, labels, frame, option buttons and command button, click View, Properties Window and click on each control. 5. To show the Userform, place a command button on your worksheet and add the following code line: Private Sub CommandButton1_Click() UserForm1.Show

End Sub We are now going to create the Sub UserForm_Initialize. When you use the Show method for the Userform, this sub will automatically be executed. 6. Open the Visual Basic Editor. 7. In the Project Explorer, right click on UserForm1 and then click View Code. 8. Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list. 9. Add the following code lines: Private Sub UserForm_Initialize() With ListBox1 .AddItem "Mountains" .AddItem "Sunset" .AddItem "Beach" .AddItem "Winter" End With End Sub Explanation: the list box on page 2 will be filled. We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we select an item from the list box or when we click on the OK button.

Evita los errores de packaging Inspecciona artes en segundos Controla texto, imágenes, códigos de barra, códigos QR, impresión en braille, etc. arviscan.com

ABRIR

10. Download the images (right side of this page) and add them to "C:\test\" 11. In the Project Explorer, double click on UserForm1. 12. Double click on the list box. 13. Add the following code lines: Private Sub ListBox1_Click() If ListBox1.ListIndex = 0 Then Image1.Picture = LoadPicture("C:\test\Mountains.jpg") End If If ListBox1.ListIndex = 1 Then Image1.Picture = LoadPicture("C:\test\Sunset.jpg") End If If ListBox1.ListIndex = 2 Then Image1.Picture = LoadPicture("C:\test\Beach.jpg") End If If ListBox1.ListIndex = 3 Then Image1.Picture = LoadPicture("C:\test\Winter.jpg") End If End Sub Explanation: these code lines load a picture depending on the selected item in the list box. 14. Double click on the OK button. 15. Add the following code lines: Private Sub CommandButton1_Click() Dim emptyRow As Long 'Make Sheet1 active Sheet1.Activate 'Determine emptyRow emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1 'Transfer information Cells(emptyRow, 1).Value = TextBox1.Value

Cells(emptyRow, 2).Value = TextBox2.Value If OptionButton1.Value = True Then Cells(emptyRow, 3).Value = "Male" Else Cells(emptyRow, 3).Value = "Female" End If Cells(emptyRow, 4).Value = ListBox1.Value 'Close Userform Unload Me End Sub Explanation: first, we activate Sheet1. Next, we determine emptyRow. The variable emptyRow is the first empty row and increases every time a record is added. Next, we transfer the information from the Userform to the specific columns of emptyRow. Finally, we close the Userform. 16. Exit the Visual Basic Editor, enter the labels shown below into row 1 and test the Userform. Result:

Baldosa Autoencastrable Autoinstalable. No se decolora.

10/11 Completed! Learn much more about userforms > Go to Next Chapter: Range

USERFORM WITH MULTIPLE PAGES • FREE EXCEL HELP • COPY RIGHT (C) 2010-2020 • ALL RIGHTS RESERVED MICROSOFT EXCEL TUTORIAL | HOW TO USE VBA & MACROS | EXCEL FORMULAS | FUNCTIONS IN EXCEL
Excel VBA Userform with Multiple Pages - Easy Excel Macros

Related documents

7 Pages • 840 Words • PDF • 378.1 KB

25 Pages • 1,605 Words • PDF • 2.2 MB

40 Pages • 10,131 Words • PDF • 3.4 MB

47 Pages • 3,075 Words • PDF • 3.4 MB

1 Pages • 802 Words • PDF • 561 KB

356 Pages • 55,185 Words • PDF • 20.9 MB

1 Pages • PDF • 509.1 KB

11 Pages • 4,864 Words • PDF • 102.9 KB

132 Pages • 47,243 Words • PDF • 5.9 MB

316 Pages • 100,636 Words • PDF • 13.3 MB

9 Pages • 1,677 Words • PDF • 2.2 MB