Print if populated

[Stuart Evans]

Hi All
I have a workbook with a number of sheets one of which is hidden and
called "continuation sheet" I have a command button on a frontend sheet with
a macro attached. What i need to do is look at the sheet named "continuation
sheet" and if cell B5 is populated print the sheet.

Stuart

 

[Tom Ogilvy]

if len(trim(Worksheets("continutation sheet").Range("B5").Text)) > 0 then
Application.ScreenUpdating = False
Worksheets("continuation sheet").Visible = xlSheetVisible
Worksheets("continuation sheet").Printout
Worksheets("continuation sheet").Visible = xlSheetHidden
Application.ScreenUpdating = True
End if

 

[Stuart Evans]

Tom
Thanks for the reply however I cant seem to get this working can you
explain where I put this code please.

Stuart

 

[Tom Ogilvy]

Private Sub Commandbutton1_Click()

if len(trim(Worksheets("continutation sheet").Range("B5").Text)) > 0 then
Application.ScreenUpdating = False
Worksheets("continuation sheet").Visible = xlSheetVisible
Worksheets("continuation sheet").Printout
Worksheets("continuation sheet").Visible = xlSheetHidden
Application.ScreenUpdating = True
End if

End Sub

 

[Stuart Evans]

Tom
Nope cant get this working I get a Subscript out of range error when I
click the button? Am I missing something here?

Stuart

 

[Tom Ogilvy]

if len(trim(Worksheets("continutation sheet").Range("B5").Text)) > 0 then

has continuation mispelled.

if len(trim(Worksheets("continuation sheet").Range("B5").Text)) > 0 then

 

[Stuart Evans]

Tom
Thats Sorted it thank you.
Just one more what code do I need to check a range of cells say A8:K20
in the same sheet?

Stuart

 

[Tom Ogilvy]

Private Sub Commandbutton1_Click()

if Application.CountA(Worksheets("continuation sheet").Range("A8:K20")) > 0
then
Application.ScreenUpdating = False
Worksheets("continuation sheet").Visible = xlSheetVisible
Worksheets("continuation sheet").Printout
Worksheets("continuation sheet").Visible = xlSheetHidden
Application.ScreenUpdating = True
End if

End Sub

 

[Stuart Evans]

Tom
The code now prints the sheet even if there is no data in any of the
cells?

Stuart

 

[Tom Ogilvy]

counta gives the count of cells that are not empty.

do you have formulas in the cells that are returning empty strings? - then
those are not empty.

I don't see any reason it wouldn't work if the cells are actually empty.

demo'd from the immediate window:

Worksheets("Continuation sheet").Range("A8:K20").ClearContents
? Application.CountA(Worksheets("continuation sheet").Range("A8:K20"))
0
Worksheets("Continuation sheet").Range("B10").Value = 6
? Application.CountA(Worksheets("continuation sheet").Range("A8:K20"))
1

 

[Stuart Evans]

Tom
Yes there is formula in the cells having removed these formula the code
works as you say. Is there anyway to check these cells with formula in as
the first code for a single cell works with formula in the cell.

Stuart

 

[Tom Ogilvy]

Assuming you want a printout if any cell in the range is displaying a value:

Private Sub Commandbutton1_Click()
Dim cell as Range
For each cell in Worksheets("continuation sheet").Range("A8:K20")
if len(trim(cell.Text)) > 0 then
Application.ScreenUpdating = False
Worksheets("continuation sheet").Visible = xlSheetVisible
Worksheets("continuation sheet").Printout
Worksheets("continuation sheet").Visible = xlSheetHidden
Application.ScreenUpdating = True
Exit for
End if
Next

End Sub

 

[Stuart Evans]

Tom
Thanks very much for your help thats sorted the problem.

Stuart