Workbook Open Event

[cameron213]

I have a workbook with three sheets. I am wondering if is is possible to run an on open event that performs functions on Sheet1?

For example, the following looks through the D column and if the date in that column matches today's date a toggle button is displayed. I want the toggle button to be hidden by default when the workbook is opened and then unhidden if the criterion is matched.

Sub Revisit()

Dim xdate As Date
Dim lastrow As Long
Dim c As Range
Dim Rng As Range
Dim count As Long
count = 0
lastrow = Range("A" & Rows.count).End(xlUp).Row

Set Rng = Range("D2:D" & lastrow)
For Each c In Rng
    xdate = c.Value
       If Month(Date) = Month(xdate) And Year(Date) = Year(xdate) Then
      
      count = count + 1
       Else
     
       End If
   Next

If count > 0 Then

ToggleButton1.Visible = True
ToggleButton1.BackColor = RGB(255, 0, 0)
ToggleButton1.Caption = "Re-Visit"
ToggleButton1.Font.Bold = True
ToggleButton1.Font.Size = 18
Else
ToggleButton1.Visible = False
End If


End Sub

How do I go about setting this up to run on workbook open?

[cameron213]

I've figured it out. Was easier than I thought.

[HaHoBe]

Hi, cameron213,

If you solve a problem yourself before anyone else has responded, post your solution to complete the thread.

Maybe

Private Sub Workbook_Open()
Dim rngCell As Range
Dim blnShow As Boolean
  
For Each rngCell In Sheets("Sheet1").Range("D2:D" & Sheets("Sheet1").Range("A" & Rows.count).End(xlUp).Row)
  With rngCell
    If Month(Date) = Month(.Value) And Year(Date) = Year(.Value) Then
      blnShow = True
      Exit For
    End If
  End With
Next rngCell

  
With Sheets("Sheet1").ToggleButton1
  If blnShow Then
    .Visible = True
    .BackColor = RGB(255, 0, 0)
    .Caption = "Re-Visit"
    .Font.Bold = True
    .Font.Size = 18
  Else
    .Visible = False
  End If
End With

End Sub

Code goes into ThisWorkbook.

If you found a solution by yourself you should consider to mark the thread as Solved via Thread Tools over the first post of the thread

Ciao,
Holger