1 / 28

CST-273-01 Microsoft VBA R. Juhl

CST-273-01 Microsoft VBA R. Juhl. VBA UserForms. VBA User Forms & Additional Controls. User Forms are part of the VBA Object Library Available to all Microsoft Office Applications Design User Forms using Active X controls for inclusion in VBA Programs UserForms The Option Button Control

bayle
Download Presentation

CST-273-01 Microsoft VBA R. Juhl

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CST-273-01Microsoft VBAR. Juhl VBA UserForms

  2. VBA User Forms & Additional Controls • User Forms are part of the VBA Object Library • Available to all Microsoft Office Applications • Design User Forms using Active X controls for inclusion in VBA Programs • UserForms • The Option Button Control • The Scroll Bar • The Frame Control • The RefEdit Control • The MultiPage Control • The List Box and Combo Box Controls • Custom data types and enumerations

  3. VBA User Forms & Additional Controls • VBA User Forms are not the same as VB forms • They do not have the same features • No minimize / maximize buttons • Fewer properties / methods available for altering the appearance and behaviour of the UserForm object • Included in VBA to allow programmers to build custom user interfaces for Office applications • Adding a UserForm to a project • Select • Insert  UserForm • From the menu bar in the VBA IDE

  4. VBA User Forms & Additional Controls

  5. VBA User Forms & Additional Controls • Properites of the UserForm Object • Name • Sets the name of the UserForm object for use as code reference to the object • BackColor • Sets the background color of the form • Caption • Sets the text displayed in the title bar • Height • Sets the height of the form • StartUpPosition • Sets the position of the form on the screen when displayed • Width • Sets the width of the form • Other properties include • BorderColor, BorderStyle, SpecialEffect (aesthetic appeal)

  6. VBA User Forms & Additional Controls • Forms represent entities in a VBA project and have their own code window. • To view the code window associatd with the UserForm object • Select • View  Code • From the menu bar • Or press F7 with the form selected • Or double click the form • Some Selected Event Procedures of the UserForm Object • Activate() Triggered when the userform is activated • Initialize() Triggered when the userform is loaded • QueryClose() Triggered when the userform is closed or unloaded • Terminate() Triggered when the userform is closed or unloaded

  7. VBA User Forms & Additional Controls • Initialize() • This event of the userform is triggered when the form is first loaded • Excellent location for code that initializes program variables and controls. • Activate() • Not triggered when the form is loaded • Used to initialize however will not take affect unless the UserFormis active (selected) • QueryClose() & Terminate() • Events are triggered when the UserForm is unloaded from memory • Good locations for code that clears memory or ends program

  8. VBA User Forms & Additional Controls • Adding Active X Controls to a Form • User Form is a container object • Used to hold other objects • When a form is added the Control Toolbox automatically appears • If not select • View  toolbox from the menu bar • A few extra controls appear when viewed with a form • Multipage Control • Frame Control

  9. VBA User Forms & Additional Controls • Active X controls are added to a from in the same manner as they are added to a worksheet • When added to a form you access the Active X control properties via the properties window • You access the event procedures associated with Active X controls via the form module that contains them

  10. VBA User Forms & Additional Controls • Showing and Hiding Froms • To display a form from the Excel application call the Show() method of the UserForm object in a procedure that can be triggered from Excel • A public procedure in a standard module • An event procedure from an object module • Syntax: UserFormName.Show • To load a user form into Memory without displaying it use: Load UserFormName • The UserForm object and all its components can be accessed programmatically after loading it into memory.

  11. VBA User Forms & Additional Controls • To hide a form from the user • Call the Hide() method • retains programmatic control, of the UserForm Object • To remove a form from system memory • Call the UnLoad() method • UnLoadUserFormName

  12. VBA User Forms & Additional Controls • Modal Forms • The Show() Method of the UserForm takes an optional Boolean parameter • Specifies whether or not the form is Modal • Default value (unspecified) is True • Creates a Modal Form • A Modal Form Must be • addressed by the user • Closed by the user or program before any other part of the Excel program can be accessed. • If the UserForm is Modeless • The user may select between any open windows in the Excel Application • Modeless forms only work with Office 2000 and later • MyUserForm.ShowvbModeless • MyUserForm.ShowvbModal

  13. VBA User Forms & Additional Controls • To determine which Version of Excel is running on a users computer, test the version property of the Application Object • Application.Version

  14. VBA User Forms & Additional Controls • Designing Custom Boxes Using Forms • Expands beyond using InputBox() and MsgBox() functions. • The Option Button Control • Similar to a Check Box Control • Offers the user a selection from a group of possibilities • Option buttons are grouped by the container on which they have been added. • No matter how many Option Buttons are added • You can only choose one at any given time

  15. VBA User Forms & Additional Controls

  16. VBA User Forms & Additional Controls • The click event is the most commonly used event procedure with the Option Button Control. • Triggered whenever the user changes the state of an Option Button • Good location for code that processes the user’s change made to the value property of the control • Boolean : True / False • The Frame Control • Groups Active X Controls on a form • May be related by content • Groups / organizes controls on a from for aesthetic apperarence

  17. VBA User Forms & Additional Controls • Refedit Control • Provides an interface for users to select a range of cells from a worksheet. • Program then uses the selected range for some task • Provides a textual reference for the selected range from within the edit region of the control • To test: • Just add the control to a form and select a range of cells from a worksheet • No code required to operate • You may read the range selected with the refEdit control with the Text or ValueProperties

  18. VBA User Forms & Additional Controls • Adding Sound to your VBA Program • Microsoft removed support for playing sound files in Excel several versions ago • Choices to play sound……………….. • Active X control • Widnows API • The Windows API, informally WinAPI, is Microsoft's core set of (APIs) available in the Microsoft Windows operating systems. • All Windows programs must interact with the Windows API regardless of the language. • An application programming interface (API) is a set of functions, procedures, methods, classes or protocols that an operating system, library or service provides to support requests made by computer programs.

  19. VBA User Forms & Additional Controls • The Windows API is comprised of numerous procedures that provide progrmmatic access to the features of the windows OS • Windows functions • File functions • Etc • API’s are stored in the system directory of Windows as .dll files (dynamically linked library) • Dynamic linking means that the subroutines of a library are loaded into an application program at runtime, rather than being linked in at compile time, and remain as separate files on disk

  20. VBA User Forms & Additional Controls • There can be dozens of procedures stored within a single .dll file • Conceptually the same as writing procedures, functions and sub routines in VBA or any programming language • Normally the Windows API is left as an advanced programming topic. • Using the Windows API can be dangerous as it bypasses the safty features built into VBA • To prevent the misuse of systems resources • To prevent system crashes

  21. VBA User Forms & Additional Controls • Using the Windows API to play sound is easy • To use a function from the Windows API inVBA • Open a code module • Use the Declare statement in the general declarations to create a reference to the external procedure • Windows API function Public Declare Function sndPlaySoundA lib “winmm.dll” _ (ByVal lpszSoundName As String, ByVal uFlags) As Long • winmm.dll is a module for the Windows Multimedia API, which contains low-level audio and joystick functions.

  22. VBA User Forms & Additional Controls Public Declare Function sndPlaySoundA lib “winmm.dll” _ (ByVal lpszSoundName As String, ByVal uFlags) As Long • Declare Statement • Used at module level to declare references to external procedures in a dynamic-link library (DLL). • Syntax • [Public | Private] DeclareSubnameLib"libname" [Alias"aliasname"] [([arglist])]

  23. VBA User Forms & Additional Controls Public Declare Function sndPlaySoundA lib “winmm.dll” _ (ByVal lpszSoundName As String, ByVal uFlags) As Long • (arglist) • ByVal • Indicates that the argument is passed by value. • A way of passing the value of an argument to a procedure instead of passing the address (ByRef). This allows the procedure to access a copy of the variable. As a result, the variable's actual value can't be changed by the procedure to which it is passed. • ByRef • Indicates that the argument is passed by reference • A way of passing the address of an argument to a procedure instead of passing the value. This allows the procedure to access the actual variable. As a result, the variable's actual value can be changed by the procedure to which it is passed. Unless otherwise specified, arguments are passed by reference.

  24. VBA User Forms & Additional Controls Public Declare Function sndPlaySoundA lib “winmm.dll” _ (ByVal lpszSoundName As String, ByVal uFlags) As Long • Creates a reference to the sndPlaySoundA() function found in the winmm.dll file • Capitalization is important and will not be auto corrected • Function accepts two arguments as listed in the declaration. • Argument lpszSoundName represents the string specifying the filename and path to the .wav file to be played • The argument uFlags represents the integer used to denote whether or not the program execution should • [1] proceed immediately • [0] wait until the file is done playing

  25. VBA User Forms & Additional Controls Public Declare Function sndPlaySoundA lib “winmm.dll” _ (ByVal lpszSoundName As String, ByVal uFlags) As Long • The sndPlaySoundA() function returns a value of type long that can be discarded. • Calls to the sndPlaySoundA() function from a VBA procedure appear as: sndPlaySoundA “Path to .wav file”, 1 returnVal = sndPlaySoundA(“Path to .wav file”, 0)

  26. VBA User Forms & Additional Controls • Playing Wav Files via the Windows API • Enter the following code for playing wav files in a new standard module named “General” • Easier to export code to other Applications !!! Public Declare Function sndPlaySoundA Lib "winmm.dll" _ (ByVal lpszSoundName As String, ByVal uFlags) As Long Public Sub PlayWav(filePath As String) sndPlaySoundA filePath, 1 End Sub • Add the function call in your code modules where needed • Include the path and file name of the wav file

  27. VBA User Forms & Additional Controls

  28. VBA User Forms & Additional Controls

More Related