Need some help with rogue VB code please

[sp00kster]

I have some code that opens up another workbook (reminder.xlsm) and reports some status from the other workbook (which is password protected) and then closes it unless user wishes to open it via a msgbox. It all works great but the problem is that after a period of time of being open (lets say half hour to an hour), it attempts to re-open the reminder.xlsm sheet asking for the password. I am quite confused why if anyone can help it would be much appreciated.

'Adding in nuisance reminder
    If Worksheets("Measure Sheet").Range("G4").Value > "" Then 'Testing for job name and bypassing reminder if so
        Exit Sub
    Else
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        If fso.FileExists(ThisWorkbook.Path & "\..\Common\" & "Reminder.xlsm") Then 'Testing for existance of reminder.xlsm
            Dim y As Workbook
            Set y = Workbooks.Open(ThisWorkbook.Path & "\..\Common\" & "Reminder.xlsm", Password:="nope") 'Opening reminder.xlsm and checking or any "pending" status
            y.Visible = False ' Hiding Reminder as it checks
            If y.Sheets("All").Range("AB5").Value = 0 Then
                y.Close SaveChanges:=False
                GoTo Cont2
            Else
                Application.DisplayAlerts = False
                Application.ScreenUpdating = False
                msg = "You have " & wkb.Sheets("All").Range("AB5") & " pending reminders - Would you like to view them now?"
                y.Close SaveChanges:=False
                Application.ScreenUpdating = True
                Prmt = MsgBox(msg, vbCritical + vbYesNo, "Nuisance Reminder")
                If Prmt = vbNo Then
                    GoTo Cont2
                Else
                    Set y2 = Workbooks.Open(ThisWorkbook.Path & "\..\Common\" & "LaunchReminder.xlsm")
                    y2.Close SaveChanges
                End If
            End If
        Else
            msg = "The \Dropbox\Prefab\Common file Reminder.xlsm is either corrupt or missing"
            Prmt = MsgBox(msg, vbCritical + vbOKOnly, "Missing recommended file")
            Cancel = True
            Exit Sub
        End If
    End If

 

[Eric W]

What's the name of this macro? Is it an event handler? Does it trigger on Workbook_Open, or Sheet_Change, or SelectionChange? If not, does any other macro call this one?

 

[sp00kster]

What's the name of this macro? Is it an event handler? Does it trigger on Workbook_Open, or Sheet_Change, or SelectionChange? If not, does any other macro call this one?

Thanks for the respond - its on Workbook_Open

 

[Eric W]

Sorry, I thought about it, but I can't think of a reason it would attempt to rerun the Workbook_Open event handler. Very strange.

 

[Fluff]

Do you have a workbook_Open code in the reminder.xlsm workbook?

 

[sp00kster]

Sorry, I thought about it, but I can't think of a reason it would attempt to rerun the Workbook_Open event handler. Very strange.

Thx again for responding - Agree is very odd. Odder yet even if it were to find a way to loop - the Set y = Workbooks.Open has the provided password and it would just open the Reminder sheet rather than asking for the password while attempting to open

 

[sp00kster]

Do you have a workbook_Open code in the reminder.xlsm workbook?

Thx for responding and yes - but primarily deals with a inactivity time so that it does not get left open (see below)

Private Sub Workbook_Open()
    
    Call Refresh_Reminders
    
'Schedule the Save&CloseWorkbook procedure at 8:30 PM
    On Error Resume Next
    Application.OnTime VBA.TimeValue("20:30:00"), "SaveAndCloseWorkBook", , True
'Setting top ribbon as hidden for more viewing screen
    If Application.CommandBars("Ribbon").Height >= 100 Then
        SendKeys "^{F1}"
    End If
'Open to starting cell
    Sheets("All").Select
'Inactivity timer
'    Call SetTimer
End Sub

As well I am including the macro for Refresh_Reminders

Sub Refresh_Reminders()
    On Error Resume Next
    Sheets("All").Unprotect Password:=""
    NextFree = Range("C6:C" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row 'Excludes ("C1:C5")
    Range("C" & NextFree).Select
    
    Dim r As Range, c As Range
    Set r = Range("AA6:AA200")
    Application.ScreenUpdating = False
    For Each c In r
    If c.Value = "" Then
        c.EntireRow.Hidden = True
    Else
        c.EntireRow.Hidden = False
    End If
    Next c
    Application.ScreenUpdating = True
    Sheets("All").Protect Password:=""
End Sub

 

[Fluff]

Whilst it's not something I've ever used, I suspect that this line is the root cause of your problem.

Application.OnTime VBA.TimeValue("20:30:00"), "SaveAndCloseWorkBook", , True

I think that you need to cancel the OnTime somehow, otherwise it's still running in the background.

 

[sp00kster]

Whilst it's not something I've ever used, I suspect that this line is the root cause of your problem.

Application.OnTime VBA.TimeValue("20:30:00"), "SaveAndCloseWorkBook", , True

I think that you need to cancel the OnTime somehow, otherwise it's still running in the background.

Thx for responding

Perhaps but there are two other workbooks that also open the same Reminders sheet in the exact same fashion and does not happen with them. I will rem it out tonight and give it a test

 

[sp00kster]

Thx for responding

Perhaps but there are two other workbooks that also open the same Reminders sheet in the exact same fashion and does not happen with them. I will rem it out tonight and give it a test

Rem'd out 'Application.OnTime VBA.TimeValue("20:30:00"), "SaveAndCloseWorkBook", , True in the Reminder workbook and still trying to open up the Password protected Reminder workbook