code without select

[daniels012]

Can I write this without selecting the "Foreman" Sheet?



If Sheets("Foreman").Range("C1").Value = True Then
Sheets("Foreman").Select
' If CancelShrink And Range("C1").Value = True Then
Calculate
Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = True
Application.Dialogs(xlDialogPrint).Show

Thank You,
Michael

[GTO]

Greetings Michael,

Yes you can. Here is but one way:

With ThisWorkbook.Worksheets("Foreman")
If .Range("C1").Value = True Then
.Calculate
.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB") _
.EntireColumn.Hidden = True

Application.Dialogs(xlDialogPrint).Show
End If
End With


Please note that the line continuation in the .Range(... line is certainly not req'd, but rather I used optionally to keep the line width down for the display in the post (for those of us with 'skinnier' screens).

Hope that helps,

Mark

[daniels012]

I get an error 1004
??/
Any ideas why?

Michael

[GTO]

Greetings Michael,

I did guess (and should have asked) as to 'CancelShrink' being a Boolean; is it?

Still presuming yes, I just ran this on a new wb w/a sheet named "Foreman" with no problem:

Sub exa()
Dim CancelShrink As Boolean

With ThisWorkbook.Worksheets("Foreman")

CancelShrink = True

If CancelShrink And .Range("C1").Value = True Then
.Calculate
.Range("A:A,D:E,H:I,L:M,P:Q,T:U,X:Y,AB:AB").EntireColumn.Hidden = True
Application.Dialogs(xlDialogPrint).Show
End If
End With
End Sub


As you can see, the only thing different is that I shortened the arg for the range (which I should have spotted before).

If it still goes KABOOM!, maybe post the entire procedure, as I do not see anything to go wrong.

Mark

[daniels012]

Here is what I have ended up with:

Private Sub Workbook_BeforePrint(CancelSh As Boolean)
CancelSh = ActiveSheet.Name = "Worksheet"

If CancelSh Then
Sheets("ESTIMATING").Select
Calculate
Application.Dialogs(xlDialogPrint).Show
ans = MsgBox("Do you want to print FOREMAN SHEET? (Yes/No)", vbYesNo, "Print Foreman")
If ans = vbYes Then
Sheets("Foreman").Select
Calculate
If Range("C1").Value = True Then
Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = True
End If
Application.Dialogs(xlDialogPrint).Show
Else
Cancel = True
MsgBox "Not Printing the Foreman Sheet."

End If
End If

End Sub

I went without the WITH and it works fine till I get to the Range line.
The error says: "Unable to set the Hidden Property of the range Class"

Michael

[SamT]

I think I would use an array, populate it with the required column letters, A, D, E, H, Etc, then use a "For i=1 to Array.Count" to iterate thru Worksheet.Columns(Array(i)).EntireColumn.Hidden = True.

Since you can declare a fixed array at the beginning, this makes your code easier to maintain if you change the Worksheet.

You might also try using A1, D1, instead of A:A, D:D.

Don't forget, if I knew what I was doing, I wouldn't be studying here.
SamT

Edit: Stupid smilies. That was supposed to be 'D' ':' 'D'.

[daniels012]

Still don't know why this wont work the way i have it though?
Michael

[mdmackillop]

You mention 3 sheet names in your code. Can you explain in simple terms what you wish to happen?

[daniels012]

Sure i can!
There are 3 sheets: Foreman, Worksheet, Estimating
Estimating is where we estimate a job. It is never printed.
Worksheet and Foreman are a result of this that is printed front and back. many people forget to print the Foreman sheet (back page).
The Foreman sheet has columns that must be hidden so prices don't show.

If you are on the Estimating sheet, [code] moves you to the Worksheet and brings up the print dialog. then asks if you want to print back (Foreman sheet). Then hides columns and prints.

Thank You,
Michael

[mdmackillop]

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo Exits
Cancel = True
Application.EnableEvents = False
Calculate
ans = MsgBox("Do you want to print FOREMAN SHEET? (Yes/No)", vbYesNo, "Print Foreman")
With Sheets("Foreman")
If ans = vbYes Then
If .Range("C1").Value = True Then
.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = True
End If
Sheets(Array("Worksheet", "Foreman")).Select
Else
Sheets("Worksheet").Select
End If
Application.Dialogs(xlDialogPrint).Show
.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = False
End With
ws.Select
Exits:
Application.EnableEvents = True
End Sub

[daniels012]

I am going to try this. Quick question...
What if they try to print the Estimating sheet? I really don't want them printing this page. Can we add something that will move it to the Worksheet page? Or is it there and I missed it??

Thank You very much,
Michael

[mdmackillop]

Cancel = True stops Estimating from printing. The code selects either one or both of the other sheets only.

[daniels012]

Whoa! I am sorry. I meant, I don't want them to print the Worksheet page. The Estimating page is the one we want to print.
So "Estimating" and "Foreman" are the pages to print. Really any other tab should be printable. Just not the "Worksheet" sheet should not be allowed to be printed. Actually it would be fine if it moved to the Estimating page with a print dialog there!

Thank You and sorry if i confused you,
Michael

[mdmackillop]

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
Dim ans As Long
Set ws = ActiveSheet
On Error GoTo Exits
Cancel = True

'Don't print Worksheet
If ws.Name = "Worksheet" Then
MsgBox "You may not print this page", vbCritical
Exit Sub
End If


Application.EnableEvents = False
Calculate
'If Foreman selected, print it
If UCase(ws.Name) <> "FOREMAN" Then
ans = MsgBox("Do you want to print FOREMAN SHEET as well?", vbYesNo, "Print Foreman")
End If
'If other selected, give choice to print Foreman as well
With Sheets("Foreman")
If .Range("C1").Value = True Then
.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = True
End If
If ans = vbYes Then Sheets(Array(ws.Name, "Foreman")).Select
Application.Dialogs(xlDialogPrint).Show
.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = False
End With
ws.Select
Exits:
Application.EnableEvents = True
End Sub

[daniels012]

OK, This is good.
The reason I have the Dialog come up instead of just printing is I have to have time to turn the page over to print on the back. With your code it prints the 2 pages. Does that make sense?
I appreciate what you have helped me with so far.

Michael

[mdmackillop]

Can your printer do Duplex?

[daniels012]

I am not sure, but I can try to find it.
I don't think it does though...

[daniels012]

I tried changing what you gave me, but it will not print the "Foreman" Sheet?
Dim ws As Worksheet
Dim ans As Long
Set ws = ActiveSheet
On Error GoTo Exits
Cancel = True

'Don't print Worksheet
If ws.Name = "Worksheet" Then
MsgBox "You may not print this page", vbCritical

Exit Sub
End If


Application.EnableEvents = False
Calculate
'If Foreman selected, print it
If ws.Name = "Estimating" Then
Application.Dialogs(xlDialogPrint).Show
ans = MsgBox("Do you want to print FOREMAN SHEET as well?", vbYesNo, "Print Foreman")
End If
'If other selected, give choice to print Foreman as well
With Sheets("Foreman")
If .Range("C1").Value = True Then
.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = True
End If
If ans = vbYes Then Sheets("Foreman").Select
Application.Dialogs(xlDialogPrint).Show
' .Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = False
End With
ws.Select
Exits:
Application.EnableEvents = True
Michael

[mdmackillop]

Modify this bit

'If other selected, give choice to print Foreman as well
With Sheets("Foreman")
If .Range("C1").Value = True Then
.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = True
End If
If ans = vbYes Then
'Print first sheet with no dialog
Sheets(ws.Name).PrintOut
'Show dialog for Foreman
Sheets("Foreman").Select
Application.Dialogs(xlDialogPrint).Show
End If
.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = False
End With
ws.Select
Exits:

[daniels012]

This time it never gave me the messagebox??

Michael

[mdmackillop]

A bit better structured

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
Dim ans As Long
Set ws = ActiveSheet
On Error GoTo Exits
Cancel = True
Application.EnableEvents = False
Calculate
Select Case UCase(ws.Name)
Case "WORKSHEET"
MsgBox "You may not print this page", vbCritical
GoTo Exits
Case "FOREMAN"
If ws.Range("C1") = True Then
ws.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = True
ws.PrintOut
ws.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = False
End If
Case "ESTIMATING"
ws.PrintOut
ans = MsgBox("Do you want to print FOREMAN SHEET as well?", vbYesNo, "Print Foreman")
If ans = vbYes Then
With Sheets("Foreman")
.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = True
.Select
Application.Dialogs(xlDialogPrint).Show
.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = False
End With
End If
Case Else
ws.PrintOut
End Select

ws.Select
Exits:
Application.EnableEvents = True
End Sub

[daniels012]

Tried this. It brings up a print dialog on every sheet???

Michael

[mdmackillop]

I can't see why. Have you tried stepping through the code?

[daniels012]

How do I step through?
When I am in VB what can I do to step through?

Michael

[mdmackillop]

Add a break point (F9) to the first line of code, then try printing. Press F5 to step through a line at a time.

[daniels012]

WOW! I got it!! It was a protected sheet. I had to Unprotect and protect!!!

Wow, thank you for all you have done. I know I am really stupid when it comes to this code stuff, but you have been really helpful.
Thank you,
Michael

[daniels012]

After review, it does not exactly do correctly?????:banghead:

Here is what I have:

Dim ws As Worksheet
Dim ans As Long
Set ws = ActiveSheet
On Error GoTo Exits
Cancel = True
Application.EnableEvents = False
Calculate
Select Case UCase(ws.Name)
Case "WORKSHEET"
MsgBox "You may not print this page", vbCritical
GoTo Exits
Case "FOREMAN"
If ws.Range("C1") = True Then
ws.Unprotect
ws.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = True
ws.PrintOut
ws.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = False
ws.Protect
End If
Case "ESTIMATING"
ws.PrintOut
ans = MsgBox("Do you want to print FOREMAN SHEET as well?", vbYesNo, "Print Foreman")
If ans = vbYes Then
With Sheets("Foreman")
.Unprotect
.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = True
.Select
Application.Dialogs(xlDialogPrint).Show
.Range("A:A,D:D,E:E,H:H,I:I,L:L,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y,AB:AB").EntireColumn.Hidden = False
.Protect
End With
End If
Case Else
ws.PrintOut
End Select

ws.Select
Exits:
Application.EnableEvents = True

If I hit yes, I need the "FOREMAN" sheet to delay printing. This is a back page. So If I could have a message come up that says Hit OK when ready to print back(FOREMAN).

THank You,
Michael

[mdmackillop]

It Foreman is active, it will be printed, otherwise it will be printed off after the selected sheet. Try removing the Case "Foreman" code from the Select statement.