What is the syntax to retrieve all open workbooks in Excel?

'wb.Name holds the name of the open workbook
Dim wb As Workbook
For Each wb In Application.Workbooks
 <code here with involving wb.Name>
Next wb
'example
Dim wb As Workbook
For Each wb In Application.Workbooks 'for each open workbook, pass the name to lookupEGA()
   MsgBox wb.Name
   lstWorkbooks.AddItem wb.Name
Next wb

 

Advertisements

What is the syntax to automatically run code when multi-page page changes?

Private Sub <multipage name>_Change()
   If Me.<multipage name>.Value = <pg index under which code should run> Then
      <code to be run when page is activated>
   End If
End Sub
'example for multipage named "mtp"
Private Sub mtp_Change() 'called whenever active page in multipage changes
   If Me.mtp.Value = 1 Then 'if user opens the "Show Guest List" page
        Call loadGuestLists 'automatically runs this code
   End If
End Sub

What is the syntax to return the index of an active multipage?

Me.<multi-page name>.Value
#returns an integer value
'example for multipage named "mtp"
Private Sub mtp_Change() 'called whenever user goes to new multipage
   If Me.mtp.Value = 1 Then 'if user opens the "Show Guest List" page
        Call loadGuestLists
   End If
End Sub

What is the syntax for adding a worksheet?

'to add a sheet at the very beginning of the Sheets tab
Worksheets.Add Before:=Worksheets(1)

'to add a sheet at the very beginning of the Sheets tab
Dim shtLast As Worksheet
Set shtLast = .Worksheets(.Worksheets.Count)
Set shtNew = Worksheets.Add(After:=shtLast)

'to add multiple sheets before the active sheet
ThisWorkbook.Worksheets.Add Count:=<number of sheets you want to add>

'to add a new sheet before the current active sheet
Dim sht as Worksheet
Set sht = ThisWorkbook.Worksheets.Add
sht.Name = "<name of new worksheet>"