Message Box with a twist...........

[5elpep]

Hi all, I have spent all evening trying to figure this out but to no
avail so here goes:

I am trying to build a macro which will do the following.

If worksheets "Sheet4" and "Sheet5" are hidden it unhides them and
exits the sub.

However if BOTH of these worksheets are not hidden AND cell A1 in
sheet4 + cell A1 in sheet5 <> 0
a message box is displayed prompting the user to respond yes or know.

On no the macro exits.

On yes sheets 4 & 5 are printed.


This is what I have so far.....

Any help would be greatly appreciated


_____________________________________________________________________________________________
' Macro3 Macro
' Macro recorded 14/03/2007 by elpep
' Keyboard Shortcut: Ctrl+r

If Sheets("Sheet4").Visible <> xlSheetVisible Then
Sheets("Sheet4").Visible = True

MsgBox("There seem to be pricing errors - do you wish to continue",
[VbMsgBoxStyle=vbYesNo], [,EOD Reval])

If mbRes = vbNo Then
Exit Sub

Else
Sheets(Array("Sheet4", "Sheet4")).Select
Sheets("Sheet3").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

End Sub
____________________________________________________________________________________________

 

[tylagusim]

Ignoring what you want to happen if only one of Seet4 or Sheet5 is
visible at any time, you might experiment with this macro:


Sub showHideSheets()

' If both hidden, unhide each and exit
If ((Sheets("Sheet4").Visible <> xlSheetVisible) And
(Sheets("Sheet5").Visible <> xlSheetVisible)) Then
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
GoTo byebye:
End If


If ((Sheets("Sheet4").Visible = xlSheetVisible) And
(Sheets("Sheet5").Visible = xlSheetVisible)) _
And (Sheets("Sheet4").Range("A1").Value +
Sheets("Sheet4").Range("A1").Value <> 0) Then

If (MsgBox("Print me?", vbQuestion + vbYesNo, "Decision
time") = vbYes) Then
Sheets(Array("Sheet4", "Sheet5")).PrintOut
End If

End If

byebye:

End Sub


/ Tyla /

 

[Guest]

I did not test this so it might need some tweaking.

Sub hidnShts()
WsArr = Array(Worksheets(1), Worksheets(2))
If Sheets("Sheet4").Visible = False And Sheets("Sheet5").Visible = False
Then
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
Exit sub
Else
If Sheets("Sheet4").Visible = True And Sheets("Sheet5").Visible = True
Then
If Worksheet(4).Range("$A$1").Value + Worksheets(5).Range("$A$1").Value <>
0 Then
PrntWs = MsbBox("Do you want to print the sheets?", vbYesNo + vbQuestion,
"PRINTOUT?")
If PrntWs = vbYes Then
WsArr.PrintOUt
End If
End If
End If
End If
End Sub

 

[Tom Ogilvy]

Sub Macro3()
' Macro recorded 14/03/2007 by elpep
' Keyboard Shortcut: Ctrl+r

If Sheets("Sheet4").Visible = xlSheetVisible And _
Sheets("Sheet5") = xlSheetVisible And _
Sheets("Sheet4").Range("A1").Value + _
Sheets("Sheet5").Range("A1").Value <> 0 Then


mbRes = MsgBox(Prompt:="There seem to be" & _
" pricing errors -" & _
" do you wish to continue", _
Buttons:=vbYesNo, _
Title:="EOD Reval")

If mbRes = vbNo Then
Exit Sub

Else
Sheets(Array("Sheet4", "Sheet5")).PrintOut _
Copies:=1, Collate:=True
End If
ElseIf Sheets("Sheet4").Visible <> xlSheetVisible And _
Sheets("Sheet5") <> xlSheetVisible Then
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
ElseIf Sheets("Sheet4").Visible <> xlSheetVisible And _
Sheets("Sheet5").Visible = xlSheetVisible Then
' Sheet4 not visible, Sheet5 visible
' do what?
ElseIf Sheets("Sheet5").Visible <> xlSheetVisible And _
Sheets("Sheet4").Visble = xlSheetVisible Then
' Sheet5 not visible, sheet4 visible
' do what?
ElseIf Sheets("Sheet4").Range("A1").Value + _
Sheets("Sheet5").Range("A1").Value = 0 Then
' both sheets visible, but sums to zero
' do What?
End If

End Sub