Ik heb de macro gemaakt die hieronder staat. Ik weet dat hij korter kan, maar ik wil eerst zorgen dat het werkt. Aan het begin zet ik de beveiliging uit en geef ik aan dat hij de screenupdates niet moet laten zien. Toch laat hij de updates zien en geeft hij op een gegeven moment een foutmelding dat hij iets niet uit kan voeren omdat het workbook beveiligd is (bij het stukje: zet schermen terug). Hoe kan dit?
Code:
Sub CreatePDFPricelist()
ActiveWorkbook.Unprotect
Application.ScreenUpdating = False
Application.DisplayAlerts = False
' Check Correct ingevulde select
If Range("C8") = "x" Then
MsgBox "Error"
Exit Sub
Else
' Creëer Pricelist
Sheets("Select").Select
Sheets("Pricelist").Visible = True
Sheets("Calc").Visible = True
Sheets("Calc").Select
ActiveSheet.Range("$J$8:$J$156").AutoFilter Field:=1, Criteria1:="<>"
Range("A9:I156").Select
Selection.Copy
Sheets("Pricelist").Select
Range("A4").Select
' Pas opmaak aan van Pricelist
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 16180952
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Color = -8897280
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Color = -8897280
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Color = -8897280
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Color = -8897280
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Color = -8897280
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Color = -8897280
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Application.ReplaceFormat.Clear
With Application.ReplaceFormat.Font
.Size = 1
.Subscript = False
.TintAndShade = 0
End With
With Application.ReplaceFormat.Interior
.PatternColorIndex = xlAutomatic
.Color = 7879936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Replace What:="'", Replacement:="'", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True
Selection.Replace What:=":", Replacement:=":", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True
'Save as pdf
Range("A1").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("Instructions!C3").Value, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
'Zet schermen terug
Range("A157:I517").Select
Selection.Copy
Range("A4:A156").Select
ActiveSheet.Paste
Sheets("Pricelist").Select
Range("A1").Select
Sheets("Pricelist").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Calc").Select
ActiveSheet.Range("$J$8:$J$156").AutoFilter Field:=1
Sheets("Calc").Select
ActiveWindow.SelectedSheets.Visible = False
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub
Laatst bewerkt: