Microsoft Office Tutorials and References
In Depth Information
CountInsertRows = InputBox( _
“Enter the number of rows to be inserted:”, _
“Insert how many rows below the active cell?”)
‘Verify that a number was entered.
‘The Val function returns the numbers contained in a string as a numeric value.
If CountInsertRows = “” Or Val(CountInsertRows) < 1 Then Exit Sub
‘Insert as many rows as the number that was entered.
‘The Resize property returns a Range object based on the number of rows
‘and columns in the new range. The number that was entered in the InputBox
‘represents how many rows shall be inserted. The count of columns, which is
‘the other optional argument for Resize, need not be specified because it is
‘only rows being inserted.
Rows(ActiveCell.Row + 1).Resize(Val(CountInsertRows)).Insert
In this lesson, you write a macro that includes a single-line If statement, an If…Then structure, a
Select Case structure, a message box to ask the user a Yes or No question, and an InputBox to
accept a text entry from the user.
For this lesson, the active worksheet is currently protected with a password, and you ask the
workbook’s users if they want to unprotect the worksheet. If they answer No, the macro will terminate.
If they answer Yes, the macro will proceed to ask them for the password. If the attempted password
is incorrect, the user will be informed of that, the worksheet will remain protected, and the macro
will terminate. If the attempted password is correct, the user will then be allowed to unprotect the
Start by opening a new workbook and password protecting Sheet1 with the password
“hello” (without the quotes, all lowercase just as you see it here).
With your Sheet1 worksheet protected, press Alt+F11 to go to the Visual Basic Editor.
From the menu at the top of the VBE, click Insert ➪ Module.
In the module you just created, type Sub PasswordTest and press Enter. VBA will
automatically place a pair of empty parentheses at the end of the Sub line, followed by an empty line,
and the End Sub line below that. Your macro will look like this so far: