Sheet.activate not activating specified sheet-CodePudding

So I have officially run into a brick wall. I have written a macro that will allow the user, upon password entry, to unlock and unhide all sheets except for the one containing the password. At the end of the macro, I want to go to a specific sheet.

I have tried every reference to that sheet known to mankind, including both sheet code names and indexes, but the "sheet.activate" event just will not trigger. I have tried setting screen updating to true, both before and after the sheet.activate command. Events are enabled. I have disabled all Excel addins. I have tried everything I can possibly think of and everything I've found in forums all over the web. As you'll see from the code, I've even added basic time delays to the code at each step of the activating sequence, all to no avail. I've tried the activate sequence at different parts of the code. At the completion of the code, the best I get is activate the first sheet in my workbook. I even have Option Explicit enabled, and it sheds no light.

Now, the weird thing is, if I take everything out of the code except for the activate events, it works perfectly. If I step through the code line by line, the activate lines work. But if I compile the code and run the whole sub - nada. What the heck am I missing here?

Here is my complete code for this sub:

Option Explicit
Sub UnProtectAll()

    Application.ScreenUpdating = False
    
        Dim pPrompt As String
        Dim bkPswrd As String
        
        inputPass_box.Show
        pPrompt = inputPass_box.passInput.Value
        bkPswrd = Worksheets("Password List").Cells(3, 2)
        
        If pPrompt = "" Then
            MsgBox "You didn't enter anything...", vbInformation, "No password"
            UnProtectAll
        ElseIf pPrompt = bkPswrd Then
    
            Dim ws As Worksheet
    
            For Each ws In ActiveWorkbook.Worksheets
                ws.Unprotect bkPswrd
            Next ws
            
            ThisWorkbook.Worksheets("Folder History").Visible = xlSheetVisible
            ThisWorkbook.Worksheets("Master List").Visible = xlSheetVisible
            ThisWorkbook.Worksheets("File Formats").Visible = xlSheetVisible
            Unload inputPass_box
            Worksheets("Change Sheet").Shapes("Button 1").Visible = False
            Application.ScreenUpdating = True
    
            Application.Wait (Now   TimeValue("0:00:03"))
            ThisWorkbook.Worksheets("Folder History").Select
            ThisWorkbook.Worksheets("Folder History").Activate
            Application.Wait (Now   TimeValue("0:00:03"))
            ThisWorkbook.Worksheets("Change Sheet").Select
            ThisWorkbook.Worksheets("Change Sheet").Activate
        Else
            MsgBox "You have entered an incorrect password. Please check your password and try again.", vbCritical, "Wrong Password!"
            UnProtectAll
        End If
    
End Sub

CodePudding user response：

Nevermind. I just figured it out, but here's my answer in case someone else needs help with the same issue. The line

Unload inputPass_box

was throwing it off for some reason ("inputPass_box" is a UserForm used as an input box). If someone would be so kind as to set me straight on this, I would be much obliged, but to my very limited knowledge, it seems that this line triggers the UserForm_QueryClose event, as in my project this event contains and "End" command to prevent the userform from passing any data back to the sub, as this would trigger several other events when I want closing or cancelling the user form to stop rather than displaying any of the error messages contained in the code block above.

CodePudding user response：

pPrompt = InputBox("please input password")

    bkPswrd = Worksheets("Password List").Cells(3, 2)
    
    If pPrompt = "" Then
        MsgBox "You didn't enter anything...", vbInformation, "No password"
        UnProtectAll
    ElseIf pPrompt = bkPswrd Then

use "InputBox" no problem,There may be a problem with the inputPass box