This example uses a Spreadsheet control to create a simple loan payment calculator in a UserForm. The finished product is shown in Figure 15-16. The user can enter loan information into column B, and the monthly payment is calculated (by using a formula) and then displayed in the bottom-right cell.
This example is primarily for illustration only.Using a Spreadsheet control is overkill. It is much more efficient to use EditBox controls to get the information and calculate the loan payment by using VBA code.
To create this UserForm, start with a new workbook and follow the steps presented next. Remember: Make sure to first add the Spreadsheet control to your Toolbox. (See the earlier section, "Adding the Spreadsheet control to a UserForm.")
1. Insert a new UserForm and add a Spreadsheet control. Don't change its default name (Spreadsheetl).
By default, the spreadsheet displays with a toolbar, row and column headings, scroll bars, and a sheet selector tab. To keep the interface as clean as possible, get rid of these items later.
2. Select any cell in the Spreadsheet control; then right-click and choose Commands and Options from the shortcut menu.
You'll see the tabbed dialog box shown in Figure 15-17.
3. Click the Workbook tab and delete Sheet2 and Sheet3. Then remove the check marks from the Horizontal Scrollbar, Vertical Scrollbar, Sheet Selector, and Toolbar check boxes.
4. In column A, enter the text shown in Figure 15-16. Then adjust the width of column A so that it's wide enough to handle the text.
5. Enter some number into B1:B3. Then enter the following formula into cell B5:
6. Select B1:B3 and then click the Format tab in the Commands and Options dialog box. Click the key icon to unlock the selected cells. (The other cells will remain locked, which is the default.)
7. Click the Sheet tab in the Commands and Options dialog box. In the Viewable Range box, enter A1:B5.
This essentially hides all the unused rows and columns.
8. Next, remove the check marks from the Row Headers and the Column Headers check boxes.
9. Finally, add two CommandButton controls. One, named CancelButton, will be the Cancel button. The other (named PasteButton) will execute code that pastes the calculated result to the active cell in the Excel worksheet.
Now it's time to add some VBA code. In the preceding steps, the three input cells were unlocked. Locking cells, however, has no effect unless the sheet is protected (just like Excel). Therefore, add some code to protect the sheet when the UserForm is initialized. You could protect the sheet at design time (from the Commands and Options dialog box), but that makes it impossible to edit the sheet — and it's easy to forget to protect it again after you make changes. Protecting the sheet at runtime ensures that the sheet will be protected, and the user can only change the input cells.
The simple code that follows does the job:
Private Sub UserForm_Initialize()
Spreadsheetl.ActiveSheet.Protect End Sub
The UserForm button labeled Paste Payment, when clicked, executes the following code:
Private Sub PasteButton_Click()
ActiveCell.Value = Spreadsheet1.Range("B5") Unload Me End Sub
This procedure simply puts the contents of cell B5 (from the Spreadsheet control) into the active cell on the Excel worksheet and then unloads the UserForm. Finally, you need an event handler procedure for the Cancel button:
Private Sub CancelButton_Click()
I really can't think of too many good reasons to use a Spreadsheet control in a UserForm. However, it's nice to know that this feature is available should you need it.
Was this article helpful?