[VBA] Excel --> kleuren printen

[argi]

Hallo iedereen,

Ik heb een Excel bestand met daarin een rapport dat allerlei kleuren bevat. Bij het printen van het bestand wil ik dat de kleuren van cellen wordt veranderd. Hiervoor heb ik een knop gemaakt met daarachter een stukje VBA code. Deze code is verdeeld in de volgende delen:

1. Kopieren van het huidige werkblad naar een tijdelijk werkblad (gemaand 'temporary print sheet')
2. Vervangen van kleuren op basis van huidige kleuren
3. Printen
4. Tijdelijke werkblad verwijderen


1. Kopieren van het huidige werkblad naar een tijdelijk werkblad

Visual Basic:

Private Sub PrintButton_Click()
Dim PercRange As Range, Thing


'Inform user about copying on the statusbar
Application.StatusBar = "Removing colors from report and prepare for a print-preview..."

'Copy active worksheet
Worksheets(ActiveSheet.name).Copy After:=Worksheets(Worksheets.Count)

'empty clipboard
Application.CutCopyMode = False

'Rename worksheet to "temporary print sheet"
ActiveWorkbook.ActiveSheet.name = "temporary print sheet"

2. Vervangen van kleuren op basis van huidige kleuren

Visual Basic:

'<= Replace colors =>
'Set percRange to whole active worksheet
Set PercRange = Range("A1", ActiveWorkbook.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
'Begin loop
For Each Thing In PercRange
    'Select case on colorinder of cell color
    Select Case Thing.Interior.ColorIndex
    Case 55 'Dark blue
        With Thing.Interior
            .ColorIndex = 48            '48=dark grey
            .Pattern = xlSolid          'Solid color
        End With
    Case 47                             'Light blue
        With Thing.Interior
            .ColorIndex = 15            '15=light grey
            .Pattern = xlSolid          'Solid color
        End With
    Case 34                             'Very Light blue
        With Thing
            .Interior.ColorIndex = 2    '2=white
            .Borders.ColorIndex = 1
            .Interior.Pattern = xlSolid 'Solid color
        End With
    Case 44                             'Light yellow
        With Thing.Interior
            .ColorIndex = 2             '2=white
            .Pattern = xlSolid          'Solid color
        End With
    End Select
'Continue loop
Next

3. Printen

Visual Basic:

'Print preview
ActiveWorkbook.ActiveSheet.PrintPreview

4. Tijdelijke werkblad verwijderen

Visual Basic:

'<= Delete temporary worksheet =>
'Turn off delete alert
Application.DisplayAlerts = False
'Delete worksheet
ActiveWorkbook.ActiveSheet.Delete
'Turn on delete alert
Application.DisplayAlerts = True

'you give control of the statusbar back to Excel
Application.StatusBar = False

End Sub

Als ik bovenstaande code uitvoer, dan krijg ik de volgende foutcode:

Fout 1004 tijdens uitvoering:

Methode Range van het object _Worksheet is mislukt

En blijft de uitvoering 'hangen' op de volgende regel:

Visual Basic:

Set PercRange = Range("A1", ActiveWorkbook.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

Maar als ik 'deel 2' van de code kopieer en onder een nieuwe knop zet en vervolgens uitvoer in het actieve werkblad, werkt alles perfect.

Hoe kan ik mijn code aanpassen, zodat het werkt zoals ik wil?

[YoupY]

Visual Basic:

MsgBox(ActiveWorkbook.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

Ik het het bovenstaande stukje van even in een MessageBox gegooid, (altijd handig om te debuggen) en het resultaat daarvan is helemaal leeg. Wat je het beste met je range kan doen is het volgende

Visual Basic:

Set PercRagen = ActiveWorkbook.ActiveSheet.Range("A1:??")

waarbij je de vraagtekens van tevoren even berekent, of de range zo groot maakt, dat in ieder geval alle cellen worden veranderd die veranderd moetenworden.

Nog een tip verder, wanneer je met verschillende werkbladen werkt, vindt ik het zelf aftijd handig om deze even een naam te geven. ActiveSheet kan wel eens wat andere resultaten opleveren dan je verwacht.

Visual Basic:

Dim printBlad as Worksheet
Set printBlad = ThisWorkbook.Worksheets(i)

Waarbij i natuurlijk het nummer van heb werkblad is.

[ Voor 42% gewijzigd door YoupY op 21-07-2003 08:24 . Reden: Nog even een tip toegevoegd ]

[OZ-Gump]

Een paar tips:

• debuggen
• breakpoints zetten
• kijk naar de definitite van SpecialCells

Uit de help van Excel blijkt ook dat de methode SpecialCells een type en een value verwacht, en cellen van het type A1 kent Excel volgens mij niet

Voorbeeld
In het volgende voorbeeld wordt de laatste cel in het gebruikte bereik van Sheet1 geselecteerd.

Worksheets("Sheet1").Activate
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate

Verder vind ik dit een redelijk debug-topic, omdat je ons eigenlijk vraagt wat je moet doen om je code werkend te krijgen en je neit echt aangeeft wat je allemaal al geprobeerd hebt.

[ Voor 31% gewijzigd door OZ-Gump op 21-07-2003 09:05 ]

[KingRichard]

@YoupY: .SpecialCells(xlCellTypeLastCell) retourneert een lege waarde omdat het hier een range betreft. Als je ActiveWorkbook.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate doet, wordt gewoon de laatste cel geactiveerd.

@OZ-Gump: A1 hoort in de code van TS niet bij SpecialCells, maar bij Range. Bovendien, (uit Excel-help:)

Value Variant, optioneel. Als Type gelijk is aan xlCellTypeConstants of xlCellTypeFormulas, wordt dit argument gebruikt om te bepalen welke celtypen moeten worden opgenomen in het resultaat.

Ik wilde net opschrijven dat ik de oplossing ook niet wist, maar toen zag ik dit:

Visual Basic:

Dim PercRange As Range, Thing

Dit moet natuurlijk zijn:

Visual Basic:

Dim PercRange, Thing As Range

[ Voor 1% gewijzigd door KingRichard op 21-07-2003 15:14 . Reden: typo ("geactiveert" ... :r) ]