Suffixes for sub command

[Regouin]

Hello,

does anyone know where I can find all the suffixes for the sub command which make the sub run when a certain action is taken (i.e. opening a workbook, changing a cell, renaming a sheet, clicking a button, etc) I have figured some of these out but would like to know which ones are possible.

TIA
Frank

[Jacob Hilderbrand]

You can see these in the VBE. From the first drop down (above the Code Window) select Worksheet (when in a Worksheet module) or Workbook (when in the ThisWorkbook module) and from the second drop down select the event.

Or in Excel VBA help search for Worksheet Object Events or Workbook Object Events or Application Object Events. Application Object Events require you to create a custom class module. Check this KB Entry for an example.

Application Object Events
NewWorkbook
SheetActivate
SheetBeforeDoubleClick
SheetBeforeRightClick
SheetCalculate
SheetChange
SheetDeactivate
SheetFollowHyperlink
SheetSelectionChange
SheetPivotTableUpdate
WindowActivate
WindowDeactivate
WindowResize
WorkbookActivate
WorkbookAddinInstall
WorkbookAddinUninstall
WorkbookBeforeClose
WorkbookBeforePrint
WorkbookBeforeSave
WorkbookDeactivate
WorkbookNewSheet
WorkbookOpen
WorkbookPivotTableCloseConnection
WorkbookPivotTableOpenConnection

Workbook Object Events
Activate
AddinInstall
AddinUninstall
BeforeClose
BeforePrint
BeforeSave
Deactivate
NewSheet
Open
PivotTableCloseConnection
PivotTableOpenConnection
SheetActivate
SheetBeforeDoubleClick
SheetBeforeRightClick
SheetCalculate
SheetChange
SheetDeactivate
SheetFollowHyperlink
SheetPivotTableUpdate
SheetSelectionChange
WindowActivate
WindowDeactivate
WindowResize

Worksheet Object Events
Activate
BeforeDoubleClick
BeforeRightClick
Calculate
Change
Deactivate
FollowHyperlink
PivotTableUpdate
SelectionChange

[Regouin]

thanks again Jake, I've been looking all over the place but couldn't find the right term to look for in the help index.

[Jacob Hilderbrand]

You're Welcome

Take Care

[Regouin]

Now I am trying to have the sheet show a userform upon opening

I make a class module and insert the following

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Hoofdmenu.Show
End Sub

now I have certified my Macro's so excel turns them on automatically, now it takes a long time to load now but it doesnt show a userform upon opening.

[Jacob Hilderbrand]

You don't need a Class Module. Just use the Open Event in the ThisWorkbook module.

[Regouin]

how does that look like in VBA code, I now just put this

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Hoofdmenu.Show
End Sub

in the this workbook module

TIA

[Jacob Hilderbrand]

No, select the event from the drop downs so it is entered correctly. It is important that name and arguments are exactly correct.

[Regouin]

got it working like it should thanks.

I am going to put up a big DRJ-shrine in my house for being the VBA master that you are

[Jacob Hilderbrand]

Always glad to help.