Selecting a new sheet with VBA (Not as simple as it sounds)

[Will S]

Heya all,

As the title says this might not be as simple as it seems (Or at least it is too tough for me with my whole 1 and a bit months of using VBA >.>)

They Simple part:
I need a message box to appear whenever you try and leave a sheet and the Cell "AC1" says "Incomplete".

It's okay for them to actually move on, just need to tell them it's incomplete and preferably keep doing it every time they select a sheet that isn't the one that says "Incomplete" till they are so annoyed they fix it.

The difficult (maybe) part:
The sheet with "Incomplete" that I want to always lead them back to has a name generated by a cell range named "New Heater" in the sheet "Standard Information" so it could be called ANYTHING. So far I've got the following macro that I was going to put into the code for each sheet other than the one I want them to fix:

One thing I noticed and edited for, in the first line of the code it references "ActiveSheet" as I want it to only reference the sheet named after NewHeater I'll need a way of putting it's name there as well, I've got a few plans of my own but are in the process of testing them out, will edit again depending on results.

Sub Test()


If (ActiveSheet.Range("AC1").Text = "Incomplete") Then


    Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String
 
    ' Promt
    strPrompt = "Some checked sections of the Transfer Information are not complete, are you sure you want to change?"
 
    ' Dialog's Title
    strTitle = "Are you ready to change sheets?"
 
    'Display MessageBox
    iRet = MsgBox(strPrompt, vbYesNo, strTitle)
     
     ' Check pressed button
    If iRet = vbNo Then

[Something to open a sheet that has the same name as the cell range "NewHeater" in Worksheet "Standard Information"]


    Else
[SIZE=3]
[/SIZE]

[SIZE=2][COLOR=#000000]  [FONT=arial] MsgBox [/FONT][FONT=arial]"Okay then, but this message WILL keep appearing until you complete the Transfer Info."[/FONT]
[/COLOR][/SIZE]
    End If
    End If
    
End Sub


Private Sub Worksheet_Activate()


Call Test




End Sub

Any help is greatly appreciated.

 

[Norie]

Why not use the worksheet event Deactivate?

Option Explicit

Private Sub Worksheet_Deactivate()
Dim iRet As Integer
Dim strPrompt As String
Dim strTitle As String

    If Me.Range("AC1").Text = "Incomplete" Then

        ' Promt
        strPrompt = "Some checked sections of the Transfer Information are not complete, are you sure you want to change?"

        ' Dialog's Title
        strTitle = "Are you ready to change sheets?"

        'Display MessageBox
        iRet = MsgBox(strPrompt, vbYesNo, strTitle)

        ' Check pressed button
        If iRet = vbNo Then

            Application.Goto Me.Range("AC1")

        Else
            MsgBox "Okay then, but this message WILL keep appearing until you complete the Transfer Info."

        End If
    End If

End Sub

 

[Will S]

I tried that originally, for some reason it didn't work and as I want it to come up every time they change to a sheet that isn't the one they need to fix I didn't bother looking into it. One of the main issues with that was that in the application for this is a form that product data is entered into and for some reason that when the sheet I'm working on was originally designed, they decided it was best to have a button assigned with a macro to take a hidden sheet "Work Template" and rename it according to the range "NewHeater", then applying all the links.

In preference I'd like to have it so that it would use something that sets the name of the sheet to equal the value of the cell range, but no idea if that's possible. Also the sheet is (Or at least can be made to) only be the second sheet in the workbook, is there anyway that this can be used to link things up? I tried to use something like:

Dim NewHeaterSheet As Worksheet
Set NewHeaterSheet = Sheet2




If (NewHeaterSheet.Range("AC1").Text = "Incomplete") Then

but I'm pretty sure that's just random gibberish in VBA.

 

[Norie]

Sorry I don't follow.

The code I posted will prevent the user leaving a sheet if AC91 is 'Incomplete' and it will be triggered whenever the user tries to move to another sheet.

It goes in the module of the worksheet(s) you want to check before allowing the user to leave, and it doesn't need the sheet name as Me refers to the worksheet.

 

[Will S]

Ah, I spotted the issue when I tested it out. It does work, is there a way to get it so that it triggers even when not starting on the page that has "Incomplete" on it? I'd prefer if the error message showed whenever you tried to go to any page that wasn't the one with errors.

Maybe having an activate macro on the other sheets that quickly flash to that sheet and then trigger the other macro you suggested? I don't mind it being a bit choppy, just move motivation for them to fix the errors.