User Form- GUI for macro

MS Excel: Automatically open a Form when spreadsheet is opened in Excel 2003/XP/2000/97


There are several "events" available within an Excel spreadsheet where you can place VBA code. In your case, we want to open the form when the "Workbook_Open" event fires.

To do this, press Alt-F11 to go to the Visual Basic editor.

Then select ThisWorkbook in the left window. In the right window, select Workbook in the first drop-down and Open in the second drop down. Then enter the following VBA code:

Private Sub Workbook_Open()

UserForm1.Show

End Sub

Now when the spreadsheet is opened, the form "UserForm1" will automatically open.





 

Creating PivotTable Reports and Charts with VBA in Excel 2010

This chapter will focus on how to use Visual Basic for Applications (VBA) in Microsoft Excel 2010 to create and manipulate Pivot Table reports and charts. Using code to create Pivot Table reports and charts can help you to do these tasks repeatedly and more efficiently

File Handling

In Microsoft Excel, I'd like to create a "Save As" macro that I can assign to a button. This macro should let me save my Excel spreadsheet while allowing me to select the file name.

How can I do this?

Answer: The following macro will perform a "File Save As" and then display a message box stating where the file was saved to.



Macro Code

The macro code looks like this:

Sub mcrSave()

'Retrieve file name to use for Save
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")

'If user specified file name, perform Save and display msgbox
If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlNormal

MsgBox "Save as " & fileSaveName
End If

End Sub

Introduction to Excel VBA

Introduction to Excel VBA

Just like you and I, Excel has a language too, the one it can speak and understand. This language is called as VBA (Visual Basic for Applications).

When you tell instructions to Excel in this VBA language, Excel can do what you tell it. Thus enabling you to program Excel so that you can automate a boring report, format a big&ugly chart, clean-up some messy data or just play some random noises.

What is a Macro then?

A macro is nothing but a set of instructions you give Excel in the VBA language.

Writing Your First Macro

In order to write your first VBA program (or Macro), you need to know the language first. This is where Excel’s tape recorder will help us.

Tape Recorder ??

Yes. Excel has a built-in tape recorder, that listens and records everything you do, in Excel’s own language, ie VBA.

Since we dont know any VBA, we will use this recorder to record our actions and then we will see recorded instructions (called as code in computer lingo) to understand how VBA looks like.

Our First VBA Macro – MakeMeRed()

Now that you understand some VBA jargon, lets move on and write our very first VBA Macro. The objective is simple. When we run this macro, it is going to color the currently selected cell with Red. Why red? Oh, red is pretty, bright and awesome – just like you.

This is how our macro is going to work when it is done.

6 steps to writing your first macro

Step 1: Select any cell & start macro recorder

This is the easiest part. Just select any cell and go to Developer Ribbon & click on Record Macro button.

Step 2: Give a name to your Macro

Specify a name for your macro. I called mine MakeMeRed. You can choose whatever you want. Just make sure there are no spaces or special characters in the name (except underscore)
Click OK when done.

Step 3: Fill the current cell with red color

This is easy as eating pie. Just go to Home ribbon and fill red color in the current cell.

Step 4: Stop Recording

Now that you have done the only step in our macro, its time to stop Excel’s tape recorder. Go to Developer ribbon and hit “stop recording” button.

Step 5: Assign your Macro to a button

Now go to Insert ribbon and draw a nice rectangle. Then, put some text like “click me to fill red” in it.
Then right click on the rectangle shape and go to Assign Macro. And select the MakeMeRed macro from the list shown. Click ok.

Step 6: Go ahead and play with your first macro

That is all. Now, we have linked the rectangle shape to your macro. Whenever you click it, Excel would drop a bucket of red paint in the selected cell(s).
Go ahead and play with this little macro of ours.

Understanding the MakeMeRed Macro Code

Now that your first macro is working, lets peek behind the scenes and understand what VBA instructions are required to fill a cell with red.
To do this, right click on your current sheet name (bottom left) and click on View code option. (You can also press ALT+F11 to do the same).
This opens Visual Basic Editor – a place where you can view & edit various VBA instructions (macros, code) to get things done in Excel.

Understanding the Visual Basic Editor:

Before understanding the MakeMeRed macro, we need to be familiar with VBE (Visual Basic Editor). See this drawing to understand it.

Viewing the VBA behind MakeMeRed

1. Select Module 1 from left side area of VBE (called as Project Explorer).
2. Double click on it to open it in Editor Area (top right, big white rectangle)
3. You can see the VBA Code behind MakeMeRed

If you have followed the instructions above, your code should look like this:

Sub MakeMeRed()
'
' MakeMeRed Macro
'
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 192
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

So much for a simple red paint!!!
Well, what can I say, Excel is rather verbose when it is recording.

Understanding the MakeMeRed VBA Code

Lets go thru the entire Macro code one line at a time.

• Sub MakeMeRed(): This line tells Excel that we are writing a new set of instructions. The word SUB indicates that the following lines of VBA are a sub-procedure (or sub-routine). Which in computer lingo means, a group of related instructions meant to be followed together to do something meaningful. The Sub-procedure ends when Excel sees the phrase “End Sub”
• Lines starting with a single quote (‘): These lines are comments. Excel will ignore anything you write after a single quote. These are meant for your understanding.
• With Selection.Interior: While filling a cell with Red color may seem like one step for you and I, it is in fact a lot of steps for your computer. And whenever you need to do a lot of operations on the same thing (in this case, selected cell), it is better to bunch all of them. This is where the WITH statement comes in to picture. When Excel sees With Seletion.Interior, Excel is going to think, “ok, I am going to do all the next operations on Selected Cell’s Interior until I see End With line“
• Lines starting with .: These are the lines that tell Excel to format the cell’s interior. In this case, the most important line is .Color = 192 which is telling Excel to fill Red color in the selected cell.
• End With: This marks the end of With block.
• End Sub: This marks the end of our little macro named MakeMeRed().

Few Tips to understand this macro better:

Once you are examining the macro code, here are a few ways to learn better.

• Change something: You can change almost any line of the macro to see what happens. For example, change .color = 192 to .color = 62 and save. Then come back to Excel and run your macro to see what happens.
• Delete something: You can remove some of the lines in the macro to see what happens. Remove the line .PatternColorIndex = xlAutomatic and run again to see what happens.

Types of MessageBox

Types of Message Box

The Two Basic Types of Message Box

In VBA Message Boxes fall into two basic categories,the MsgBox method and the MsgBox function.

The MsgBox Method

The method is the "verb" of the VBA language and as such carries out some sort of action, in this case displaying a message to the user. It has a button marked OK to allow the user to dismiss the message and they must do so before they can continue working in the program.

The MsgBox Function

Like any other function, this one returns a value. Use the MsgBox function to ask the user a question. The question must be one that can be answered with Yes, No, OK, Cancel, Abort, Retry or Ignore and the MsgBox function displays a message accompanied by two or more buttons (in certain pre-defined combinations) for the user to make their response. The value returned by the MsgBox function identifies which button the user clicked.

Both types of Message Box can also display a button marked Help allowing you to direct the user to the appropriate part of a Help file.

Writing the Code for a Message Box

When you type the keyword MsgBox followed a space the Visual Basic Editor displays a panel listing the various parameters appropriate to a Message Box (Fig. 3). The parameters are separated by commas. Those enclosed in square brackets are optional.

Fig. 3 The Visual Basic Editor's Auto Quick Info feature helps you provide the required parameters.

The parameter currently highlighted in bold is the one that the Visual Basic Editor is expecting you to supply now. After entering a parameter you must type a comma to move to the next one. Since some parameters are optional you might want to skip one and move to the next in the list. Type another comma to do this. Do not end the code statement with a comma. It is not necessary to type a comma if you are not going to supply any more parameters. The following table describes the Message Box parameters:

NOTE: If the Visual Basic Editor does not provide the context-sensitive help (Auto Quick Info as in Fig. 3 or Auto List Members as in Fig. 5) first check your typing. If you made a typing error the Visual Basic Editor will not be prompted to display its help. If help still fails to appear check that it is enabled by going to Tools > Options > Editor in the Visual Basic Editor. If the help disappears because, perhaps, you switched to another window or clicked somewhere else, you can force it to return by typing a [Backspace] then retyping the comma or space that prompted the help originally.

Fig. 5 The Visual Basic Editor's Auto List Members feature offers a list of choices.

Displaying a Message Using the MsgBox Method

Use the MsgBox method when you want to display a message providing information or maybe a warning that requires no other response from the user than a simple acknowledgement. A very useful application of this tool is to let the user know when a macro has finished its work. When you run a macro it is not always apparent when the macro has completed or, indeed if anything at all has happened! A simple message displayed by the macro on completion will confirm that it has run its course.
In this simple example, the macro adds a new worksheet to the active workbook in Excel. If you want to make it really simple you only have to provide the prompt (Listing 1) and your program will display a simple Message Box (Fig. 6).

Listing 1:
Sub AddWorksheet() Worksheets.Add
MsgBox "Macro finished." End Sub

Fig. 6 A simple Message Box with only the Prompt supplied.

You can customise the Message Box further by adding an icon and a title, and maybe a little more information in the message (Listing 2, Fig. 7).

Listing 2:
Sub AddWorksheet()
   Worksheets.Add
   MsgBox "A new worksheet has been added." _
                 , vbInformation + vbOKOnly, "Macro finished"
End Sub

Fig. 7 This Message Box displays an icon and a custom Title

NOTE: In Listing 2 I have specified vbOKOnly in the Buttons parameter but since this is the default I could have omitted it and achieved the same result. Also, I have written the MsgBox statement on two lines, breaking the statement with a [Space] and [Underscore]. This was done only to fit the code on this written page. The statement could equally have been written as a single line.


With some modification to the macro additional information can be automatically displayed in the Message Box (Listing 3, Fig. 8).
Listing 3:

Sub AddWorksheet()
    Dim sht As Worksheet Set sht = Worksheets.Add
    MsgBox "A new worksheet has been added." & vbCrLf & _
                  "The new sheet is: " & sht.Name _
                   , vbInformation + vbOKOnly, "Macro finished"
End Sub

Fig. 8 This message contains a forced line break and some variable data.

In this example, the new worksheet is declared as an object variable so that its name can be retrieved and included in the message. The MsgBox statement also includes a forced line break using the vbCrLf constant to display the message on two lines.

Offering a Choice Using the MsgBox Function

A multi-button Message Box allows your macro to interact with the user by offering them a number of choices. When the user clicks one of the buttons it returns the value of the constant represented by that button. Your code can then make use of this value to take an appropriate course of action. This is achieved by reading the value returned by the MsgBox function directly into a variable then using an If Statement or Case Statement to execute the relevant code.

Note that, when MsgBox is used as a function its parameters must be enclosed in parentheses (round brackets).

Checking the User's Response with an If Statement
In this example the user is asked to confirm their request to add a new worksheet (Fig. 9).

Fig. 9 A Yes/No Message Box asks the user for confirmation.

The code (Listing 4) shows that the user's choice is recorded in the variable named "Response" (I usually use this name but any relevant and VBA legal name, such as "Answer" or "Choice" will do) which is then examined by the If Statement. If the user clicked the No button all that is necessary is to cancel the macro. The expression Exit Sub is used for this. Since there can only be one other response (Yes) any code following the If Statement will be executed if that choice is made.

Listing 4:

Sub AddWorksheet()

   Dim Response As VbMsgBoxResult

   Response = MsgBox("Do you want to add a new worksheet?", vbQuestion + vbYesNo) 

   If Response = vbNo Then
       Exit Sub 

       Worksheets.Add

MsgBox "A new worksheet has been added.", vbInformation

End Sub

The If Statement can be elaborated if, for example, you want to confirm the cancellation of the macro. In this example (Listing 5) the Exit Sub expression has been omitted since the macro terminates immediately after the If Statement anyway.

Listing 5:

Sub AddWorksheet()

   Dim Response As VbMsgBoxResult

   Response = MsgBox("Do you want to add a new worksheet?", vbQuestion + vbYesNo)

   If Response = vbNo Then

      MsgBox "No worksheet was added", vbInformation 

   Else

      Worksheets.Add 

       MsgBox "A new worksheet has been added.", vbInformation

    End If

End Sub

If more than two choices are offered (e.g. Yes, No, Cancel) the If Statement must have an additional clause. The next example (Fig. 10) offers to give a particular name to the new worksheet.

Fig. 10 This Message Box offers three choices.

There are three possible courses of action so the If Statement is a little more complex (Listing 6).

Listing 6:

Sub AddWorksheet()

      Dim Response As VbMsgBoxResult

      Dim sht As Worksheet

      Response = MsgBox("Do you want to name the sheet with a timestamp?" _

      , vbQuestion + vbYesNoCancel)

     If Response = vbYes Then

          Set sht = Worksheets.Add

          sht.Name = Format(Now, "yyyy-mm-dd_hhnnss")

     ElseIf Response = vbNo Then

          Worksheets.Add

     Else

           Exit Sub

     End If

           MsgBox "Macro finished.", vbInformation

End Sub

Whether the user chooses Yes or No a sheet is created and both choices continue after the If Statement to show a final Message Box confirming completion of the macro. If the user did not click either Yes or No then they must have clicked Cancel so this response can be dealt with in the Else part of the If Statement.

NOTE: I chose to name the worksheet using a timestamp since this is one way to ensure that each is given a unique name. Attempting to give a worksheet a name that already exists would cause an error. This isn't a problem since I could write code to deal with this eventuality but for these examples I wanted to keep the code simple.

Checking the User's Response with a Case Statement

The decision to use an If Statement or a Case Statement is often a matter of personal choice. I usually prefer to use a Case Statement when there is a larger number of choices because the syntax is simpler and easier to read and understand.

In this example (Listing 7) a clause, or Case, has been included for each possible button choice, although I could have used the expression Case Else as a "catch all" for the third choice instead of Case vbCancel since if the user had not chosen Yes or No the only remaining option is Cancel.

Listing 7:

Sub AddWorksheet()
    Dim Response As VbMsgBoxResult
    Dim sht As Worksheet
    Response = MsgBox("Do you want to name the sheet with a timestamp?" _
                       , vbQuestion + vbYesNoCancel)
    Select Case Response
              Case vbYes
                       Set sht = Worksheets.Add
                       sht.Name = Format(Now, "yyyy-mm-dd_hhnnss")
              Case vbNo
                       Worksheets.Add
              Case vbCancel
                       Exit Sub
     End Select
MsgBox "Macro finished.", vbInformation
End Sub

Defining the Default Button

When a Message Box has more than one button, one of them is the default. It appears highlighted when the Message Box is displayed and is automatically "clicked" if the user presses the [Enter] key on their keyboard. Unfortunately, people are often eager to proceed and press [Enter] without properly reading the message, sometimes with disastrous results!

Unless you decide otherwise the first (leftmost) button is the default which is usually Yes or OK but you can help the user inadvertently making the wrong choice by defining which button is the default, and making this the safest option. Do this by adding an additional constant to the Buttons parameter.

Compare the two message boxes illustrated here (Fig. 11). For the Message Box on the left no default button was specified so the first button (Yes) is highlighted. The MsgBox statement for the Message Box on the right uses the vbDefaultButton2 constant to make the No button the default. You can choose vbDefaultButton1 to vbDefaultButton4 (counting from left to right).

Fig. 11 You can specify which button is the default.

NOTE: For single-button Message Boxes and multi-button Message Boxes having a Cancel button the user can dismiss the message by clicking its close button or by pressing the [Esc] key on their keyboard. If a multi-button Message Box does not include a Cancel button its close button is automatically disabled to force the user to make a choice from the available buttons.

Programming Note

In these examples the Response variable has been declared as the VbMsgBoxResult data type (indicating that it refers to one of the collection of VBA Message Box constants). This data type was introduced with Office 2000 so, if you are programming for Office 97 (or might require your macros to be compatible with this version) you should use the Integer data type instead, for example:

Dim Response As Integer

Since each constant also has a numerical value the Integer data type will suffice in this context.

Message and Input Boxes

Message and Input Boxes (MsgBox, InputBox) in Excel

In VBA for Excel the message box (MsgBox) is the primary tool to interact with the user. For example you might want to tell the user that a long macro has finished running.

Exercise 1

Step 1: Open a new workbook and use the ALT/F11 keys to move to the Visual Basic Editor.

Step 2: Copy/Paste the following macro from here into the code window of any sheet.

Sub demo1()
Sheets("Sheet1").Select
Range("A1").Value = 695
MsgBox "The macro has finished running"
End Sub

Notice the space following MsgBox and the use of quotation marks surrounding the text

Step 3: Use the ALT/F11 keys to go back to Excel and run the macro demo1.

The value 695 is entered in cell A1 and the following message box appears.

Exercise 2

You might want to tell the user where he will find the result.

Step 1: Use the ALT/F11 keys to move to the Visual Basic Editor.

Step 2: Copy/Paste the following macro from here into the code window of any sheet.

Sub demo2()
Sheets("Sheet1").Select
Range("A1").Value = 695
MsgBox "The result is in cell ""A1"""
End Sub

Notice the space following MsgBox, the use of quotation marks surrounding the text and the double quotation mars around A1 because we want the address to show on the message box between quotation marks.

Step 3: Use the ALT/F11 keys to go back to Excel and run the macro demo2.

The value 695 is entered in cell A1 and the following message box appears

Exercise 3

Instead of telling the user that the value is in cell A1, you might want to tell him what the result is in the message box itself.

Step 1: Use the ALT/F11 keys to move to the Visual Basic Editor.

Step 2: Copy/Paste the following macro from here into the code window of any sheet.

Sub demo3()
Sheets("Sheet1").Select
Range("A1").Value = 695
MsgBox "The result is " & Range("A1").Value
End Sub

Notice the space following MsgBox, the use of quotation marks surrounding the text, the space at the end of the text and the spaces surrounding the ampersand.

Step 3: Use the ALT/F11 keys to go back to Excel and run the macro demo3.

The value 695 is entered in cell A1 and the following message box appears