I can't wrap my head how force 1,2 or both msgbox's as a user fills out 1,2 or both data tabs

[Aaron DOJ]

OK I need some serious help here, my head hurts trying to think of the best way to write this up and keep the code as small as possible, please let me explain...

My manager wanted me to update a completely non-functioning spreadsheet in which I did, but now wants me to add another tab in which a user can fill out information as well and I am running into issues. I will try to keep this short, how can I get the BeforePrint code below to function under the following parameters:
*Both tabs/sheets could be filled out and used by one user, so its possible I would need each MsgBox to show up when printing (is there a way to make the msgbox show up only if that tab is up and actively trying to be printed?) I am guessing if I put in something like

If Sheet1.Cells(7, 2).Value <> "e.g. First Name Last Name" And Sheet5.Cells(6, 1).Value <> "e.g. First Name Last Name" Then...not sure what to follow

in theory this would mean they put in their name and if either cell was populated ,then I would know which msgbox was needed? or something like that.

*I don't want either of the cells to be empty, which is why I have the first statement to begin with, but with the new tab I have the new msgbox...it's possible and most likely that a user is only using one tab so I need it to be and/or option I am assuming?

Private Sub Workbook_BeforePrint(Cancel As Boolean)[TABLE="width: 905"]
<tbody>[TR]
[TD="class: xl64, width: 905"][TABLE="width: 905"]
<tbody>[TR]
[TD]If Sheet1.Cells(11, 3).Value = "e.g. M-F" Then[/TD]
[/TR]
[TR]
[TD]            MsgBox "Please Enter your normal working days in Section 6, thank you.", vbExclamation[/TD]
[/TR]
[TR]
[TD]            Cancel = True[/TD]
[/TR]
[TR]
[TD]        Exit Sub[/TD]
[/TR]
[TR]
[TD]End If
[TABLE="width: 905"]
<tbody>[TR]
[TD]If Sheet5.Cells(6, 6).Value = "e.g. M-F" Then[/TD]
[/TR]
[TR]
[TD]            MsgBox "Please Enter your normal working days in Section 2, thank you.", vbExclamation[/TD]
[/TR]
[TR]
[TD]            Cancel = True[/TD]
[/TR]
[TR]
[TD]        Exit Sub[/TD]
[/TR]
[TR]
[TD]End If
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Sorry if this is confusing to read, it is confusing to try and think of; and worse to write up

Might be best to enforce only using one tab at a time...hope to hear something back, please ask questions if needed it might help me to explain and think it out better.....been a really long day for this newbie on the VBA front hehe.

 

[JonXL]

Does checking which sheet is active work?

If ActiveSheet <> Sheet5 And ActiveSheet <> Sheet1 Then Exit Sub

That would go as the first line of your Sub...

 

[Aaron DOJ]

Thank you JonXL,

I can give that a go and see how it all pans out...I didn't even know about that command

-Aaron

 

[Aaron DOJ]

JonXL,

Thankfully I have my code split up by sheet because what I ended up having to do was

If ActiveSheet.CodeName = "Sheet1" Then
'then at the end of all of my Sheet1 code add
End If
If ActiveSheet.CodeName = "Sheet5" Then
'then at the end of all of my Sheet5 code add
End If

Thank you!! omg that took so much stress off my plate, the only issue I can see would be if someone ended up uses both tabs and didn't save or try to print the first tab and just moved onto the 2nd tab, they wont see any of the msg boxes from errors/required fields in the first tab. That just might have to be something we will have to deal with...should be an extremely low probability of that happening I would think, but can you think of anything that might prevent that from happening, some sort of loop check say if
Sheet1.Cells(7, 2).Value <> "e.g. First Name Last Name" ?

 

[JonXL]

I didn't test what I posted, so I am glad you were able to translate it into something that worked.

As to your issue of preventing printing depending on what is in other sheets, there is no reason you can't run the verification for both sheets when your users attempt to print either one. In fact, you can place your checks in a sub (or function, if you'd prefer) and just call it from the before print procedure. For example, would something like this work?

In your print routine, use:

If ActiveSheet.CodeName = "Sheet1" Or ActiveSheet.CodeName = "Sheet5" Then

    If ValidateEntries <> vbNullString Then

        MsgBox ValidateEntries, vbExclamation

        Cancel = True

            Exit Sub

    End If

End If

And somewhere in the same Module, add this:

Private Function ValidateEntries() As String


If {You're missing stuff for Sheet1} Then ValidateEntries = "Please Enter your normal working days in Section 6, thank you.": Exit Function
If {You're missing stuff for Sheet5} Then ValidateEntries = "Please Enter your normal working days in Section 2, thank you.": Exit Function

ValidateEntries = vbNullString

End Function

You can place your Sheet1 and Sheet5 checks in the {you're missing stuff for Sheet#} portions.