Excel VBA Macro Programming

You can view the results of your form by pressing F5 , but you need to be able to link it into your code. You can use the Show method to do this:

UserForm1.Show

When this statement is executed, command is transferred to the UserForm1 window. Any code in the Initialize event is executed and then awaits user intervention. This could be the user closing the window or clicking an OK button on the form.

You can also hide a form once it has done its work. You use the Hide method:

UserForm1.Hide

The only other decision is from where you fire the form off. You need to connect it to a user event, such as when a new sheet is added to the workbook. Remember how in Chapter 1 you added a ‚“Hello World ‚½ message box to the NewSheet event? You can do the same thing to display a form. Double-click ThisWorkBook in the Project tree to open up the Workbook module. On the drop-down on the left-hand side of the module window, select Workbook and then NewSheet from the right-hand drop-down. Enter the code as follows :

Private Sub Workbook_NewSheet(ByVal Sh As Object) UserForm1.Show End Sub

Now go to the spreadsheet and insert a new sheet. Your form will appear.