Excel 2013>VBA>PageSetup>User Defined Formula>Interference

[aworthey]

Hello,

I have code that saves a PDF of my spreadsheet to my desktop with custom print settings. It works perfectly except when I enable the PageSetup settings. When these settings are enabled within the code, my user-defined formula generates error messages within the cell.

What is it about the PageSetup settings that is interfering with my user-defined formula?

Here's the PDF print code:


Sub Button2()


Application.ScreenUpdating = False


Dim strFilename As String
Dim rngRange As Range
Dim konumber As String


konumber = Environ$("UserName")
Set rngRange = ThisWorkbook.Sheets("CostWorksheet").Range("D4")
strFilename = rngRange.Value & Format(Now(), "mmddyyyy hhmm")


ThisWorkbook.Sheets("CostWorksheet").Activate


'With ActiveSheet.PageSetup
'.Orientation = xlLandscape
'.Zoom = False
'.FitToPagesWide = 1
'.FitToPagesTall = 1
'End With


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\" & konumber & "\Desktop\" & strFilename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


ActiveCell.Offset(1, 100).Activate

Application.ScreenUpdating = True


End Sub


Thanks!

[aworthey]

Here's the fix I found:


Sub Button2()


Application.ScreenUpdating = False


Dim strFilename As String
Dim rngRange As Range
Dim konumber As String


konumber = Environ$("UserName")
Set rngRange = ThisWorkbook.Sheets("CostWorksheet").Range("D4")
strFilename = rngRange.Value & Format(Now(), "mmddyyyy hhmm")


ThisWorkbook.Sheets("CostWorksheet").Activate


With ThisWorkbook.Sheets("CostWorksheet").PageSetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With


ThisWorkbook.Sheets("CostWorksheet").EnableCalculation = False
ThisWorkbook.Sheets("CostWorksheet").EnableCalculation = True


ThisWorkbook.Sheets("CostWorksheet").Calculate


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\" & konumber & "\Desktop\" & strFilename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


ActiveCell.Offset(1000, 1000).Activate

Application.ScreenUpdating = True


End Sub

[mdmackillop]

I can't see what would cause an issue. Can you post your workbook?

[aworthey]

Here's 2 user-defined formulas that I have on 2 general Modules for summing and taking an average horizontally on visible cells:



Function Sum_Visible_Cells(Cells_To_Sum As Object)
Dim cell As Object
Dim Total As Double


Application.Volatile


For Each cell In Cells_To_Sum
If (cell.Rows.Hidden = False) And (cell.Columns.Hidden = False) Then
Total = Total + cell.Value
End If


Next
Sum_Visible_Cells = Total


End Function



Function AverageVisible(rng As Object)
Dim rCell As Object
Dim iCount As Integer
Dim dTtl As Double

Application.Volatile

iCount = 0
dTtl = 0

For Each rCell In rng
If (rCell.Rows.Hidden = False) And (rCell.Columns.Hidden = False) Then
dTtl = dTtl + rCell
iCount = iCount + 1
End If

Next
AverageVisible = dTtl / iCount

End Function






Somehow the PageSetup settings were preventing the calculations in the sheet using those formulas from functioning.

[aworthey]

But the disable and enable then calculate commands corrected the problem.

[mdmackillop]

You could tidy up a bit for clarity, and I suspect omit the EnableCalculation lines.

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("CostWorksheet")


With ws
With .PageSetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
.EnableCalculation = False
.EnableCalculation = True
.Calculate
.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\" & konumber & "\Desktop\" & strFilename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Application.Goto .Cells(1000, 1000)
End With

[aworthey]

Thanks for those suggestions!