Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Run excel VBA function when sheet is clicked

Tags:

excel

vba

I was wondering if there was a way to run a VBA script when I open a sheet in the workbook.

For example, I have a workbook called "Inventory" and I want to run an "InitiateInventoryValues" Function when the "View Inventory" sheet is opened.

Can anybody please help me on this?

like image 787
puissant Avatar asked Oct 24 '11 11:10

puissant


People also ask

How do I run macros automatically while opening workbook in Excel VBA?

Click Developer > Visual Basic. In the VBA Project Explorer on the left hand side, expand the VBA Project folder for your workbook, then double-click the ThisWorkbook module. Paste your recorded code in the Sub procedure between the Sub and End Sub lines. Close the Visual Basic Editor (you don't have to save anything).


1 Answers

Double click the "Workbook" icon in VBE and use this event. It will trigger everytime you activate a different sheet by clicking its tab. If the tab is the one named "View Inventory", your code will run (once) when the sheet is activated:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If Sh.Name = "View Inventory" Then
    'Do your code
End If

End Sub
like image 87
aevanko Avatar answered Sep 21 '22 00:09

aevanko