Spin Button
In the toolbox the spin button has this icon .
You can ask a user to enter a value directly in a text box but you can make things a little more attaractive by using a text box and a spin button.
The spin button is not really used by itself. Because the spin button does not show its value it is usually used with a text box. The text box shows a number and by clicking on the arrows of the spin button the value in the text box is increased (or decreased) by 1, or 5 or 10...by whatever value that is set within the properties of the spin button.
Properties
Among the other properties of the spin buttons are:
- Min is the minimum value of the spin button. It can be negative
- Max is the maximum value of the spin button. It can be negative
- Small is the value of the change when the user clicks on the arrows
- Large is the value of the change when the user clicks on the scroll bar of the spin button.
Thursday, September 1, 2011
Command Buttons in VBA for Excel
In the toolbox the command button has this icon . The command button is a very active control and there is always VBA code behind it.
The command buttons are usually placed at the bottom of the form and serve to complete the transaction for which the form has been created. The caption of these buttons are usually "Go" , "Run" , "Submit" , "Cancel" , etc.
Properties
Among the other properties of the command button are:
- WordWrap to be able to write more that one line on a button,
- ControlTipText which generates a small comment box when the user moves the mouse over the control. You can use this property to give explanations and instructions about the command button,
Adding a Command Button to a Userform
To add a command button to a userform you left click on its icon in the toolbox. You move the cursor to the userform, you click again and the command button appears. You can then resize it to your liking. If you double click on the command button icon in the toolbox you can then click on the form as many times as you need command buttons. When you are finished adding command buttons just click once on the command button icon of the toolbox.
VBA Code
Most of the VBA code (VBA sentences) is created within the command button when you develop simple userforms. Here are two exercises creating VBA code within the command button.
The command buttons are usually placed at the bottom of the form and serve to complete the transaction for which the form has been created. The caption of these buttons are usually "Go" , "Run" , "Submit" , "Cancel" , etc.
Properties
Among the other properties of the command button are:
- WordWrap to be able to write more that one line on a button,
- ControlTipText which generates a small comment box when the user moves the mouse over the control. You can use this property to give explanations and instructions about the command button,
Adding a Command Button to a Userform
To add a command button to a userform you left click on its icon in the toolbox. You move the cursor to the userform, you click again and the command button appears. You can then resize it to your liking. If you double click on the command button icon in the toolbox you can then click on the form as many times as you need command buttons. When you are finished adding command buttons just click once on the command button icon of the toolbox.
VBA Code
Most of the VBA code (VBA sentences) is created within the command button when you develop simple userforms. Here are two exercises creating VBA code within the command button.
VBA for Excel for Worksheets
To develop a VBA procedure that is triggered by an event relating to the worksheet (when you select it, when you leave it...) see the VBA lesson on events.
Sheets
You access a worksheet named " Balance" with:
Sheets("Balance").Select
Note that the word "Sheets" is plural and always use the quotes within the parenthesis
You cannot select a sheet that is hidden so you will need to write:
Sheets("Balance").Visible= True
Sheets("Balance").Select
and then if you want to hide the sheet again:
Sheets("Balance").Visible= False
The name of a sheet must not have more than 31 characters and should not include certain special characters like " ? : \ / [ ]" . If you do not respect these rules your procedure will crash.
The following lines of code will generate an error message:
Sheets("Sheet1").Name= "Balance and Introduction to Numbers" because there are more than 31 characters including the spaces
Sheets("Sheet1").Name= " Balance: Introduction" because of the special character :
Sheets("Sheet1" ).Name= " " because the name cannot be blank
You can not go directly from a sheet to a cell on another sheet. For example if the active sheet is "Balance" and you want tot go to cell A1 of a sheet named " Results" you cannot write:
Sheets("Results").Range("A1").Select
You must take two steps:
Sheets("Results").Select
Range("A1").Select
Sheets
You access a worksheet named " Balance" with:
Sheets("Balance").Select
Note that the word "Sheets" is plural and always use the quotes within the parenthesis
You cannot select a sheet that is hidden so you will need to write:
Sheets("Balance").Visible= True
Sheets("Balance").Select
and then if you want to hide the sheet again:
Sheets("Balance").Visible= False
The name of a sheet must not have more than 31 characters and should not include certain special characters like " ? : \ / [ ]" . If you do not respect these rules your procedure will crash.
The following lines of code will generate an error message:
Sheets("Sheet1").Name= "Balance and Introduction to Numbers" because there are more than 31 characters including the spaces
Sheets("Sheet1").Name= " Balance: Introduction" because of the special character :
Sheets("Sheet1" ).Name= " " because the name cannot be blank
You can not go directly from a sheet to a cell on another sheet. For example if the active sheet is "Balance" and you want tot go to cell A1 of a sheet named " Results" you cannot write:
Sheets("Results").Range("A1").Select
You must take two steps:
Sheets("Results").Select
Range("A1").Select
Cells, Ranges, Columns and Rows in VBA for Excel
A lot of VBA beginners start their career using Cells. For example:
Cells(1,1).Select is the same thing as Range("A1").Select and
Cells(11,31).Select is the same as Range("AE11").Select.
We strongly recommend that you use Range instead of Cells to work with cells and groups of cells. It makes your sentences much clearer and you are not forced to remember that column AE is column 31.
The only time that you will use Cells is when you want to select all the cells of a worksheet. For example:
Cells.Select
To select all cells and then to empty all cells of values or formulas you will use:
Cells.ClearContents
Range
To select a single cell you will write:
Range("A1").Select
To select a set of contiguous cells you will use the colon and write:
Range("A1:G5").Select
To select a set of non contiguous cells you will use the comma and write:
Range("A1,A5,B4").Select
To select a set of non contiguous cells and a range you will use both the colon and the comma:
Range("A1,A5,B4:B8").Select
Offset
The Offset property is the one that you will use the most with Range to move around the sheet.
To move one cell down (from B2 to B3): Range("B2").Offset(1,0).Select
To move one cell to the right (from B2 to C2): Range("B2").Offset(0,1).Select
To move one cell up (from B2 to B1): Range("B2").Offset(-1,0).Select
To move one cell to the left (from B2 to A2): Range("B2").Offset(0,-1).Select
To move one cell down from the selected cell:
ActiveCell.Offset(1,0).Select
As you notice the first argument between the parentheses for Offset is the number of rows and the second one is the number of columns. So to move from A1 to G6 you will need:
Range("A1").Offset(5,6).Select
You will use very often the following piece of code . It selects a cell and 4 more to the right to be copied/pasted somewhere else:
Range(ActiveCell,ActiveCell.Offset(0,4)).Copy
Notice the comma after the first ActiveCell and the double closing parentheses before the Copy.
Cells(1,1).Select is the same thing as Range("A1").Select and
Cells(11,31).Select is the same as Range("AE11").Select.
We strongly recommend that you use Range instead of Cells to work with cells and groups of cells. It makes your sentences much clearer and you are not forced to remember that column AE is column 31.
The only time that you will use Cells is when you want to select all the cells of a worksheet. For example:
Cells.Select
To select all cells and then to empty all cells of values or formulas you will use:
Cells.ClearContents
Range
To select a single cell you will write:
Range("A1").Select
To select a set of contiguous cells you will use the colon and write:
Range("A1:G5").Select
To select a set of non contiguous cells you will use the comma and write:
Range("A1,A5,B4").Select
To select a set of non contiguous cells and a range you will use both the colon and the comma:
Range("A1,A5,B4:B8").Select
Offset
The Offset property is the one that you will use the most with Range to move around the sheet.
To move one cell down (from B2 to B3): Range("B2").Offset(1,0).Select
To move one cell to the right (from B2 to C2): Range("B2").Offset(0,1).Select
To move one cell up (from B2 to B1): Range("B2").Offset(-1,0).Select
To move one cell to the left (from B2 to A2): Range("B2").Offset(0,-1).Select
To move one cell down from the selected cell:
ActiveCell.Offset(1,0).Select
As you notice the first argument between the parentheses for Offset is the number of rows and the second one is the number of columns. So to move from A1 to G6 you will need:
Range("A1").Offset(5,6).Select
You will use very often the following piece of code . It selects a cell and 4 more to the right to be copied/pasted somewhere else:
Range(ActiveCell,ActiveCell.Offset(0,4)).Copy
Notice the comma after the first ActiveCell and the double closing parentheses before the Copy.
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.
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 InputBoxTest()
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 InputBoxTest.
The value 695 is entered in cell A1 and the following message box appears.
Step 4: Delete the macro in the Visual Basic Editor and the value 695 from cell A1
Subscribe to:
Posts (Atom)