Prompting a User

[DGB]

I am not even sure if what I would like to have is possible.....

I have a workbook with multiple pages, two of which are labled "Summary" and "Questionnaire" On the Summary sheet, I would like to have a button or permanent dialouge box that asks the user if he wants to print the "Questionnaire" sheet. The button should have 2 options YES and NO. If they say Yes, change the contents of cell A2 on the Questionnaire page to "1". If they selct NO, make contents of A2 on the Questionnaire sheet to "0"

I have a macro that reads the contents of A2 on all sheets and prints if >o

Thanks

 

[VoG]

Try assigning this to a button on the Summary sheet:

Sub SetPrint()
Dim Ans As VbMsgBoxResult
Ans = MsgBox("Print the Questionnaire sheet", vbYesNo + vbQuestion)
With Sheets("Questionnaire").Range("A2")
    If Ans = vbYes Then
        .Value = 1
    Else
        .Value = 0
    End If
End With
End Sub

 

[DGB]

Worked Like a charm.

Thanks

 

[DGB]

VoG,

Further to my question on a customer promt......

As I mentioned, I have a macro that evaluates the content of "A2" on each tab and if it is greater than "0", it colors the tab green and prints it. It then hides the tabs where the value is not greater than "0".

The ideal situation would be to do as described above, BUT istead of printing the "Sales_Questionnaire" tab along with all of the others, throw up the prompt asking if it should be printed with the rest. In either case, the "Sales Questionnaire" tab should be colored Green and remain un-Hidden.

Any suggestions? I have included the macro as it stands below.

Thanks so much for your help.

Sub ColorPrintUsed_HideUnused()
'
' Select_Summary Macro
' Macro recorded 3/9/2009 by Dan Bedard
'

'
Dim ws As Worksheet

For Each ws In Worksheets
With ws
If .Range("a2").Value > 0 Then
.Tab.ColorIndex = 10
.PrintOut
Else: ws.Visible = xlSheetVeryHidden
End If
End With
Next ws
Sheets("Quote_Summary").Select
Application.ScreenUpdating = True
End Sub

 

[VoG]

Try this

Sub ColorPrintUsed_HideUnused()
'
' Select_Summary Macro
' Macro recorded 3/9/2009 by Dan Bedard
'

'
Dim ws As Worksheet
Dim Ans As VbMsgBoxResult

For Each ws In Worksheets
    With ws
        If .Range("A2").Value > 0 Then
            .Tab.ColorIndex = 10
            If .Name = "Questionnaire" Then
                Ans = MsgBox("Print the Questionnaire sheet", vbYesNo + vbQuestion)
                If Ans = vbYes Then .PrintOut
            End If
        Else
            If .Name <> "Questionnaire" Then ws.Visible = xlSheetVeryHidden
        End If
    End With
Next ws
Sheets("Quote_Summary").Select
Application.ScreenUpdating = True
End Sub

 

[DGB]

Hi VoG

The macro works well if you select yes to the "Print Questionnaire" prompt but if you select no, nothing prints. It should print all other tabs where A2 was >0

Any ideas?

 

[VoG]

Woops! Try

Sub ColorPrintUsed_HideUnused()
'
' Select_Summary Macro
' Macro recorded 3/9/2009 by Dan Bedard
'

'
Dim ws As Worksheet
Dim Ans As VbMsgBoxResult

For Each ws In Worksheets
    With ws
        If .Range("A2").Value > 0 Then
            .Tab.ColorIndex = 10
            If .Name = "Questionnaire" Then
                Ans = MsgBox("Print the Questionnaire sheet", vbYesNo + vbQuestion)
                If Ans = vbYes Then .PrintOut
            Else
                .PrintOut
            End If
        Else
            If .Name <> "Questionnaire" Then ws.Visible = xlSheetVeryHidden
        End If
    End With
Next ws
Sheets("Quote_Summary").Select
Application.ScreenUpdating = True
End Sub

 

[DGB]

Actually, I take that back.

If you select YES to the promt, only the Questionnaire tab prints. It should print all tabs where A2>0 including the Questionnaire tab.

Hmmph?

 

[DGB]

VoG

Thank you so much, That fixed it all.

If your up to it.....

That macro which hides the sheets....How could I unhide them? I hear that sheets hidden with the VB command very hidden can not be unhidden. Is that true?

 

[VoG]

This should unhide every sheet:

Sub UnhideEm()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Visible = xlSheetVisible
Next ws
End Sub