CelNietAfdrukken

Al meerdere keren is er in de MicroSoft Excel nieuwsgroep gevraagd naar een mogelijkheid om de waarde van individuele cellen te verbergen bij het afdrukken.

Het probleem.

Excel heeft geen optie "cel niet afdrukken" zoals dat wel voor objecten als figuren, besturingselementen, en zo voorts wél is in te stellen.

De waarde van een cel kan verborgen worden door via celeigenschappen > lettertype > kleur een kleur te kiezen die hetzelfde is als de kleur van de achtergrond onder andere bereikbaar via celeigenschappen > patronen > opvulkleur.
Echter die methode geniet niet de voorkeur omdat bij het selecteren van een gebied waarin de cel is opgenomen toch de waarde zichtbaar wordt en bij het wijzigen van de cel opvulkleur ook de kleur van het lettertype moet worden aangepast.

Daarom is het beter de cel waarde onzichtbaar te maken via celeigenschappen > getal > aangepast > type: ;;; [drie maal puntkomma].
zie de site van Ingrid Baplue waar uitleg en nog veel meer mogelijkheden zijn te vinden voor een celnotatie.
Het voordeel van deze methode is dat de overige instellingen van de celeigenschappen bewaard blijven.

Met één cel om te verbergen kan je dat met de hand doen, als je er elke keer voor het afdrukken er aan denkt tenminste....

Een mogelijke oplossing met Vba.

De macro's starten met [alt] + [f8] of verbindt ze aan knoppen of voeg ze toe aan een menu.
Helemaal onderaan deze pagina een code om via de gebeurtenis afdrukken de macro CelNietAfdrukkenUitvoeren te starten.

Option Explicit
'made by Kil & Co versie 20050605
'maximaal aantal aantal cellen voor niet afdrukken = 65535
'maximal aantal bladen = 255
Public Const CelNietAfdrukkenBlad As String = "CelNietAfdrukkenData"

Sub CelNietAfdrukkenUitvoeren()
Dim c As Range
Dim s As String
Dim i As Integer
Dim l As Long
Dim l2 As Long
Dim r As Long
Dim arrCelNietAfdrukken() As String

    i = ActiveSheet.Index
    CelNietAfdrukkenSnelheid True
    If Not CelNietAfdrukkenControle(i, True) Then GoTo Eind

    With Sheets(CelNietAfdrukkenBlad)
        r = .Cells(65536, i).End(xlUp).Row
        l2 = 1
        ReDim arrCelNietAfdrukken(r - 1)
        For l = 1 To r
            s = .Cells(l, i).Value
            If Worksheets(i).Range(s) <> "" Then
                ReDim Preserve arrCelNietAfdrukken(l2 - 1)
                arrCelNietAfdrukken(l2 - 1) = Worksheets(i).Range(s).NumberFormat
                Worksheets(i).Range(s).NumberFormat = ";;;"
                l2 = l2 + 1
            End If
        Next
        Worksheets(i).PrintPreview
        l2 = 1
        For l = 1 To r
            s = .Cells(l, i).Value
            If Worksheets(i).Range(s) <> "" Then
                Worksheets(i).Range(s).NumberFormat = arrCelNietAfdrukken(l2 - 1)
                l2 = l2 + 1
            End If
        Next
    End With
Eind:
    CelNietAfdrukkenSnelheid False
End Sub

Sub CelNietAfdrukkenVastleggen()
Dim i As Integer

    i = ActiveSheet.Index
    If Sheets(i).Type <> xlWorksheet Then
        MsgBox "routine kan alleen met een werkblad worden uitgevoerd"
        Exit Sub
    ElseIf Selection.Count > 65536 Then
        MsgBox "teveel cellen geselecteerd"
        Exit Sub
    ElseIf Not CelNietAfdrukkenBladBestaat Then
        If ActiveWorkbook.Sheets.Count > 255 Then
            MsgBox "er zijn teveel werkbladen"
            Exit Sub
        End If
        CelNietAfdrukkenBladMaken i
    End If
    Sheets(i).Activate
    CelNietAfdrukkenSelectieOpslaan i

End Sub

Sub CelNietAfdrukkenZien()
Dim r As Long
Dim l As Long
Dim s As String
Dim i As Integer

    i = ActiveSheet.Index
    CelNietAfdrukkenSnelheid True
    If Not CelNietAfdrukkenControle(i, True) Then GoTo Eind
    With Sheets(CelNietAfdrukkenBlad)
        r = .Cells(65536, i).End(xlUp).Row
        For l = 1 To r
            If l = r Then
                s = s & .Cells(l, i).Value
            Else
                s = s & .Cells(l, i).Value & ","
            End If
        Next
    End With
    Range(s).Select
Eind:
    CelNietAfdrukkenSnelheid False

End Sub

Private Sub CelNietAfdrukkenSelectieOpslaan(ByVal iSh As Integer)
Dim c As Range
Dim l As Long

    With Worksheets(CelNietAfdrukkenBlad)
        .Columns(iSh).ClearContents
        l = 0
        For Each c In Selection
            l = l + 1
            .Cells(l, iSh) = c.Address
        Next
    End With

End Sub

Private Function CelNietAfdrukkenBladBestaat() As Boolean
Dim Wsh As Worksheet

    For Each Wsh In Worksheets
        If Wsh.Name = CelNietAfdrukkenBlad Then
            CelNietAfdrukkenBladBestaat = True
            Exit Function
        End If
    Next
    CelNietAfdrukkenBladBestaat = False

End Function

Private Function CelNietAfdrukkenInstelling(ByVal iSh As Integer) As Boolean

    With Worksheets(CelNietAfdrukkenBlad)
        If .Cells(1, iSh) = "" Then
            CelNietAfdrukkenInstelling = False
        Else
            CelNietAfdrukkenInstelling = True
        End If
    End With

End Function

Private Sub CelNietAfdrukkenBladMaken(ByVal iSh As Integer)

    With ActiveWorkbook
        .Sheets.Add _
            After:=.Sheets(.Sheets.Count)
        .Sheets(.Sheets.Count).Name = CelNietAfdrukkenBlad
        .Sheets(CelNietAfdrukkenBlad).Visible = xlVeryHidden
    End With

End Sub

Private Function CelNietAfdrukkenControle(ByVal iSh As Integer, _
    ByVal MetMsgb As Boolean) As Boolean

    If Sheets(iSh).Type <> xlWorksheet Then
        If MetMsgb Then _
            MsgBox "routine kan alleen met een werkblad worden uitgevoerd"
        CelNietAfdrukkenControle = False
    ElseIf Not CelNietAfdrukkenBladBestaat Then
        If MetMsgb Then _
            MsgBox "u moet eerst de niet af te drukken cellen definiëren"
        CelNietAfdrukkenControle = False
    ElseIf Not CelNietAfdrukkenInstelling(iSh) Then
        If MetMsgb Then _
            MsgBox "geen instelling voor dit werkblad aangetroffen"
        CelNietAfdrukkenControle = False
    Else
        CelNietAfdrukkenControle = True
    End If

End Function

Private Sub CelNietAfdrukkenSnelheid(ByVal Aan As Boolean)
    With Application
        .EnableEvents = Not Aan
        .ScreenUpdating = Not Aan
    End With

End Sub

De volgende code regels zorgen ervoor dat bij het afdrukken automatisch wordt bepaald of er cellen in het actieve werkblad als niet afdrukken zijn gedefinieerd. Is dat het geval dan kan er een keuze worden gemaakt om wel of niet met de niet afdrukken cellen af te drukken.

De vraag.

Het probleem.

Een mogelijke oplossing met Vba.