PDA

View Full Version : [SOLVED] Suffixes for sub command



Regouin
03-03-2005, 01:02 AM
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
03-03-2005, 01:10 AM
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 (http://www.vbaexpress.com/kb/getarticle.php?kb_id=298) 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
03-03-2005, 01:18 AM
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
03-03-2005, 01:24 AM
You're Welcome :beerchug:

Take Care

Regouin
03-03-2005, 02:34 AM
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
03-03-2005, 02:43 AM
You don't need a Class Module. Just use the Open Event in the ThisWorkbook module.

Regouin
03-03-2005, 02:47 AM
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
03-03-2005, 02:49 AM
No, select the event from the drop downs so it is entered correctly. It is important that name and arguments are exactly correct.

Regouin
03-03-2005, 02:59 AM
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
03-03-2005, 03:05 AM
Always glad to help. :biggrin: