Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

The example in this section demonstrates how you can write VBA code that writes more VBA code. The AddButtonAndCode procedure does the following:

  1. Inserts a new worksheet.

  2. Adds an ActiveX CommandButton control to the worksheet.

  3. Adjusts the position, size , and caption of the CommandButton.

  4. Inserts an event handler procedure for the CommandButton named CommandButton1_ Click in the sheet's code module. This procedure simply activates Sheet1 .

The AddButtonAndCode procedure follows .

Sub AddButtonAndCode() Dim NewSheet As Worksheet Dim NewButton As OLEObject ' Add the sheet Set NewSheet = Sheets.Add ' Add a CommandButton Set NewButton = NewSheet.OLEObjects.Add _ ("Forms.CommandButton.1") With NewButton .Left = 4 .Top = 4 .Width = 100 .Height = 24 .Object.Caption = "Return to Sheet1" End With ' Add the event handler code Code = "Sub CommandButton1_Click()" & vbCrLf Code = Code & " On Error Resume Next" & vbCrLf Code = Code & " Sheets(""Sheet1"").Activate" & vbCrLf Code = Code & " If Err <> 0 Then" & vbCrLf Code = Code & " MsgBox ""Cannot activate Sheet1.""" _ & vbCrLf Code = Code & " End If" & vbCrLf Code = Code & "End Sub" With ActiveWorkbook.VBProject. _ VBComponents(NewSheet.Name).CodeModule NextLine = .CountOfLines + 1 .InsertLines NextLine, Code End With End Sub

Figure 28-6 shows the worksheet and the CommandButton control that were added by the AddButtonAndCode procedure.

Figure 28-6: This sheet, the CommandButton, and its event handler were added by using VBA.

CD-ROM  

This example is available on the companion CD-ROM. The filename is  add button and code.xlsm .

The tricky part of this procedure is inserting the VBA code into the code module for the new worksheet. The code is stored in a variable named Code , with each instruction separated by a carriage return and linefeed sequence. The InsertLines method adds the code to the code module for the inserted worksheet.

The NextLine variable stores the number of existing lines in the module incremented by one. This ensures that the procedure is added to the end of the module. If you simply insert the code beginning at line 1, it causes an error if the user 's system is set up to add an Option Explicit statement to each module automatically.

Figure 28-7 shows the procedure that is created by the AddButtonAndCode procedure in its new home in the code window.

Figure 28-7: VBA generated this event handler procedure.

Related

Категории