Randen of Arcering aanbrengen in Excel, met VBA

[AbvStaalduinen]

Hallo allemaal,

Ik heb de volgende vraag: Ik wil in Excel op een blad alleen de cellen arceren waar een waarde in staat.

De werkmap waarin ik werk bestaat uit 2 bladen. Op het eerste blad wordt een formule uitgerekend waarbij de invoer 'x' een variabele is. Men kan een minimum, een maximum en de stapgrootte aan geven doormiddel van onderstaande formule:

    For j = [Blad1!A1] To [Blad1!A2] Step [Blad1!A3]
    [Blad1!A4] = j

De resultaten uit deze formule worden automatisch ingevuld op blad2. Voor elke stap worden 10 resultaten weergegeven (= 10 kolommen). De stapgrootte bepaald dus het aantal rijen. Ik wil nu alleen om de resultaten die op blad 2 worden ingevuld een rand aanbrengen en dat kan niet vooraf gedaan worden omdat je niet weet welke stapgrootte de gebruiker zal kiezen.

 

[snb]

Gebruik:

[Blad2!A1].currentregion.Borders

 

[AbvStaalduinen]

Als ik deze code in m'n programma plaats (boven End Sub) krijg ik de foutmelding:

"Fout 424 tjdens uitvoering: Object vereist"

Moet er niet nog niets aan toegevoegd worden waarin je aangeeft wat voor rand het is, wat de dikte is en wat de kleur is etc.?

 

[snb]

Zeker wel; ik gaf geen oplossing, maar een oplossingsrichting.
Het probleem leek het bepalen van het omvang van het gebied. De oplossing daarvoor is currentregion. Uit die opmaak kom je zelf wel vermoed ik.

 

[AbvStaalduinen]

Ik zit aan de volgende code te denken:

    With [Blad2!A1].CurrentRegion
        .BorderAround , Weight:=xlMedium
        .borders(xlInsideVertical).Weight = xlThin
        .borders(xlInsideHorizontal).Weight = xlThin
        .Interior.ColorIndex = xlNone
    End With

Maar dat wil nog niet echt lukken. Hoe bepaal je met CurrentRegion welk bereik je pakt, hij mag namelijk niet alle cellen met een waarde omringen . Moet dat met bijvoorbeeld een .Range("A2:Z100")?

 

[snb]

test hoe currentregion werkt met:

[A1].currentregion.select

 

[AbvStaalduinen]

Als ik dat doe, krijg ik weer foutmelding 424.

Ik start het programma met een Command Button op blad1. Als ik dus alleen [A1] gebruik, hoe weet m'n programma dan eigenlijk dat hij op blad 2 moet kijken?

 

[snb]

nee. Dus gebruik [Blad2!A1].currentregion

 

[AbvStaalduinen]

Ook die code geeft nog een foutmelding. Ik ben nog wat verder gaan zoeken op het net en ik kan nu in een gebied wat gevuld is met waardes randen aanbrengen. Alleen dit is nog niet helemaal wat ik wil. Zie bijlage.

Zoals eerder gezegd: Een x-aantal rijen wordt door een Command_Button ingevoegd op het blad. Vervolgens moet om deze cellen een rand komen te staan. Het zijn echter 3 series van 3 kolommen met een onbekend aantal rijen. Tussen deze secties komt een lege (smalle) kolom die niet omgeven mag worden met randen. Met CurrentRegion selecteer je dus maar een zo'n gebied (druk in de bijlage op de linkse knop "randen_aanbrengen"). De bijlage zal wellicht meer zeggen omdat aan de rechterzijde van het blad de situatie is weergegeven zoals de bedoeling is.

 

[snb]

Vermijd select en activate in VBA-code

Private Sub CommandButton1_Click()
  For j = 1 To 3
    With Sheets("Blad1").Cells(11, Choose(j, 1, 6, 10)).CurrentRegion.Borders
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .Weight = xlThin
    End With
  Next
End Sub

Bekijk mijn code, ga na wat die doet en waarom. Pas de layout van je werkblad aan zodat deze code precies gaat doen wat je wil.
Kijk in de hulpfunktie van de VBEditor wat currentregion betekent.

 

[AbvStaalduinen]

Snb bedankt voor je hulp hier kan ik aardig mee uit de voeten.

Ik probeerde nu alleen nog om die ene regel over te slaan en dan opnieuw een bereik in te zetten met de volgende code:

Private Sub CommandButton1_Click()
  
For j = 1 To 3
    With Sheets("Blad1").Cells(11, Choose(j, 1, 6, 10)).CurrentRegion.Borders
       .LineStyle = xlContinuous
       .ColorIndex = 0
       .Weight = xlThin
    End With
Next

For j = 1 To 3
    With Sheets("Overzicht schaarbenen")
        .Range("A65536").End(xlUp).Offset(2, 0) _
             = Choose(j, 1, 6, 10)
             .LineStyle = xlContinuous
             .ColorIndex = 0
             .Weight = xlThin
    End With
  Next

End Sub

Maar dan krijg ik foutmelding 438 en hij plaatst het getal 1 in de onderste cel.