CODEWITHSUNDEEP
excelvba
Syed Kaleel Awn
Syed Kaleel Awn

Reputation: 43

How to run a macro only for the first time a particular sheet is activated after opening the workbook?

I want to run a macro when a particular sheet is selected. But the trick is I want to run it only the first time that sheet is selected after opening the workbook.

Upvotes: 1

Views: 8686

Answers (2)

rex
rex

Reputation: 3183

Sorry I originally misread your question.

Put this code in the worksheet code:

Public sheetOpenned As Boolean ' should go at the top of the page

Private Sub Worksheet_Activate()
    If sheetOpenned = False Then
        ' run your macro
        sheetOpenned = True
    End If
End Sub

Note though that this sub will not run when the workbook is opened and the sheet is the default one that's active. I suspect you would have to add another piece of code to handle that scenario in the Workbook_Open() event.

Upvotes: 2

Tony Dallimore
Tony Dallimore

Reputation: 12403

Look up Events within Excel VB Editor Help. There are lots of events and you can write routines to be called when one of interest occurs.

The routines below do not handle the user creating new worksheets or changing the sequence of existing ones but should give you some ideas for how to achieve the effect you seek.

Open a workbook and then the VB Editor. The project explorer is down the left hand side. Expand Microsoft Excel Objects if necessary by clicking the plus sign against it. Click ThisWorkbook and an empty code area will be displayed. Copy the code below in that code area. Save and close the workbook. Reopen the workbook and switch between sheets. Open the VB Editor to view the Debug.Print output. Close the workbook to see the final display.

Hope this helps.

Option Explicit
  Dim ActivateCount() As Long
Sub Workbook_Open()

  Dim InxS As Long

  Debug.Print "Workbook """ & ActiveWorkbook.Name & """ opened"

  ReDim ActivateCount(1 To Sheets.Count)
  ' Entries will be initialised to zero.

  ' SheetActivate is not called for the initial sheet

  For InxS = 1 To Sheets.Count
    If Sheets(InxS).Name = ActiveSheet.Name Then
      ActivateCount(InxS) = ActivateCount(InxS) + 1
      Debug.Print "Worksheet """ & ActiveSheet.Name & """ activation count = " & ActivateCount(InxS)
      Exit For
    End If
  Next

End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

  Dim InxS As Long

  For InxS = 1 To Sheets.Count
    If Sheets(InxS).Name = Sh.Name Then
      ActivateCount(InxS) = ActivateCount(InxS) + 1
      Exit For
    End If
  Next

  Debug.Print "Worksheet """ & Sh.Name & """ activation count = " & ActivateCount(InxS)

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)

  Dim InxS As Long
  Dim Text As String

  Text = "Activation counts"
  For InxS = 1 To Sheets.Count
    Text = Text & vbLf & "  " & ActivateCount(InxS) & " " & Sheets(InxS).Name
  Next
  Call MsgBox(Text, vbOKOnly)

End Sub

Upvotes: 0

Related Questions