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.
Any help is greatly appreciated.
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.
Code:
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.
Last edited: