automatisch rij bij creëren - hoe ?

[Luc Eukerdt]

Hallo
Ik vroeg mij af op het mogelijk is om een formule te maken zodat wanneer er
“iets” in een cel gevoegd wordt in een bepaalde rij, er automatisch een rij
onder die rij bijgemaakt wordt.
Voorbeeld : ik maak een simpele boekhouding van mijn bankrekening met
volgende rij :
datum |omschrijving|in|uit|uittrekselnummer

Ik zorg dan dat de “in” en “uit” automatisch opgeteld worden en van mekaar
afgetrokken zodat het actuele saldo berekend wordt in een rij daaronder.
Evenwel moet ik dan geregeld rijen bijmaken want de vrije rijen tussen de
laatste boeking en saldi raken vol.
Vandaar mijn vraag : kan er een formule gemaakt worden zodat automatisch rijen bijgemaakt worden ? Het probleem is mogelijk dat de gegevens die ik invoeg nooit
dezelfde zijn (andere datum, andere omschrijving, ander bedrag) zodat ik
hierboven “iets” schreef. Dus m.i. =ALS(A1=”iets”; rij hieronder bij creëren).

Indien de formule niet kan met “iets” (= om het even welk gegeven in de cel) zou er een kolom kunnen bijgemaakt worden waar dan een constante ingevoegd word (het cijfer 1 bijvoorbeeld) dan zou je kunnen maken =ALS(A1=1;rij hieronder bij creëren). Maar hoe omschrijf je “rij hieronder bij creëren” in formulevorm ?

Thx Luc

 

[Luc Eukerdt]

verduidelijking

Misschien is de uitleg hierboven wat overvloedig en daarom wat onduidelijk. Dus concreet komt het erop neer dat ik de opdracht “rijen invoegen” zou willen automatiseren in volgend rekenblad :

A B C D E F
Datum | i/u | Omschrijving |in | uit |

In kolom B komt ofwel “i” (voor in) of “u” (voor uit); nodig om later tabellen te maken (en mogelijk nuttig als constante waar ik over sprak in de oorspronkelijke vraag).

In F had ik dan graag de formule gehad : =ALS(B=i of u;rij invoegen/toevoegen onder deze rij).

 

[Mr_Magoo]

in een Excelsheet kan dit niet wel met VBA

 

[Luc Eukerdt]

Is dat simpel in VBA ? Ik ben een volledige VBA-leek (weet enkel dat het bestaat).

Kan dit eenvoudig aangeleerd worden of moet je echt al een informatica-opleiding genoten hebben ?

 

[Mr_Magoo]

Luc,

Ik heb een kleine VBA code voor je gemaakt

Om deze in je eigen Worksheet te plaatsen druk je op Alt+F11
je ziet links VBAProjectxxxxxxx (xxxxxx is je filenaam van je worksheet)
klik op het plusje -> klik ook op het plusje van Microsoft Excel -objecten
je ziet dan ThisWorkbook staan dubbelklik hierop
en plaats onderstaande code in het rechterscherm

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Application.Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
  If Target.Count > 1 Then Exit Sub
  If Target.Value = "i" Or Target.Value = "u" Then
    Sh.Rows(Target.Row + 1 & ":" & Target.Row + 1).Insert Shift:=xlDown
  End If
End Sub

Als er nu in kolom B een i of een u typt wordt er daarna in nieuwe regel ingevoegd
dit gebeurt nu in alle werkbladen
als je wilt dat het alleen maar in 1 blad werkzaam is laat het me weten dan pas ik het aan
geef dan wel even de bladnaam(en) op waarop dit van toepassing is

 

[Luc Eukerdt]

Hartelijk dank voor de moeite maar ik vrees dat ik nog wat vragen heb.

- Ik vermoed dat om de formule (ook) op een andere kolom toe te passen alleen de ("B:B") in je formule moet gewijzigd worden ?

- Liefst had ik het inderdaad in een spreadsheet gehad nl. "basisspreadsheet" (zonder aanhalingstekens).

- Maar, ik hernoem deze spreadsheet per rekening en per persoon.

Als ik het goed begrijp zal de formule dan niet meer van toepassing zijn op bijvoorbeeld "basisspreadsheet 750".

Of wordt de formule voor "basisspreadsheet" meegenomen naar het hernoemde (opslaan als) rekenblad (zoals formules die je in cellen creëert ?).

Tenzij je dan de formule steeds kan aanpassen en de nieuwe namen waarop de formule moet van toepassing kan toevoegen.

Als dit allemaal te ver zou leiden en te ingewikkeld worden dan denk ik dat er eigenlijk geen probleem is dat die VBA-formule op alle rekenbladen van toepassing is.

- Wat als je de spreadsheet overzet op een andere computer. Moet je daar dan ook de formule creëren vooraleer het werkt ? Het komt mij voor dat de VBA-formules niet gekoppeld zijn aan een bepaalde spreadsheet maar wel aan Excel (op die bepaalde computer).

Ik ga mij in elk geval eens een handboek kopen want dit is wel zeer interessant.

Thx

 

[Mr_Magoo]

Hartelijk dank voor de moeite maar ik vrees dat ik nog wat vragen heb.

- Ik vermoed dat om de formule (ook) op een andere kolom toe te passen alleen de ("B:B") in je formule moet gewijzigd worden ?
Ja en nee, je kan dan een Union gebruiken. Daarmee kun je meerdere gebieden mee opgeven

- Liefst had ik het inderdaad in een spreadsheet gehad nl. "basisspreadsheet" (zonder aanhalingstekens).
Gebruik je voor iedereen een nieuwe worksheet (file) of maak je een nieuw tabblad aan per persoon?

- Maar, ik hernoem deze spreadsheet per rekening en per persoon.
Wacht wel even je antwoord af van de vorige vraag - of kijk even een paar antwoorden verder

Als ik het goed begrijp zal de formule dan niet meer van toepassing zijn op bijvoorbeeld "basisspreadsheet 750".
Tot nu toe geld het voor elke sheet in je worksheet, maakt niet uit hoe de sheetnaam is

Of wordt de formule voor "basisspreadsheet" meegenomen naar het hernoemde (opslaan als) rekenblad (zoals formules die je in cellen creëert ?).

Tenzij je dan de formule steeds kan aanpassen en de nieuwe namen waarop de formule moet van toepassing kan toevoegen.
Ik denk dat, dat niet nodig is tenzij je andere kolommen wilt afvragen

Als dit allemaal te ver zou leiden en te ingewikkeld worden dan denk ik dat er eigenlijk geen probleem is dat die VBA-formule op alle rekenbladen van toepassing is.
Ik kan het ook zo maken, als er bv een bepaald teken in de sheetnaam voorkomt dat de routine wordt uitgevoerd. Denk dat dat het gemakkelijkst is.

- Wat als je de spreadsheet overzet op een andere computer. Moet je daar dan ook de formule creëren vooraleer het werkt ? Het komt mij voor dat de VBA-formules niet gekoppeld zijn aan een bepaalde spreadsheet maar wel aan Excel (op die bepaalde computer).

De Routine die ik heb gemaakt is alleen werkzaam op de worksheet waar deze routine in staat. Dus is deze computer onafhankelijk. Je kan ook een een invoeg module maken, maar die moet je dan eerst installeren voordat je die kunt gebruiken.
Nog wel een kanttekening, om VBA Routines (macro's) te kunnen te gebruiken moet je nog wel een instelling maken in Excel.
Ga in het menu naar Extra -> Macro -> beveiliging
en kies daar beveiligingsniveau -> gemiddeld of laag
Bij gemiddeld krijg je elke keer als je worksheet start de vraag of de macro's geactiveerd moeten worden. en bij laag krijg je geen vragen. Dan worden de macro's altijd automatisch geactiveerd/uitgevoerd


Ik ga mij in elk geval eens een handboek kopen want dit is wel zeer interessant.

Thx

Mr. Magoo

 

[Luc Eukerdt]

[/B]

- Ik vermoed dat om de formule (ook) op een andere kolom toe te passen alleen de ("B:B") in je formule moet gewijzigd worden ?
Ja en nee, je kan dan een Union gebruiken. Daarmee kun je meerdere gebieden mee opgeven
>Ik bedoel eigenlijk : als de "i" of "u" ipv in de B-kolom staat in de C-kolom staat, is het dan voldoende om de "B:B" uit je formule te wijzigen naar "C:C"

- Liefst had ik het inderdaad in een spreadsheet gehad nl. "basisspreadsheet" (zonder aanhalingstekens).
Gebruik je voor iedereen een nieuwe worksheet (file) of maak je een nieuw tabblad aan per persoon?
>Een nieuwe worksheet per rekening/persoon (ik werk niet met meerdere bladen binnen een worksheet omdat ik dan het automatisch aanvullen [omschrijving van de verrichting] niet meer kan gebruiken).

- Maar, ik hernoem deze spreadsheet per rekening en per persoon.
Wacht wel even je antwoord af van de vorige vraag - of kijk even een paar antwoorden verder

Als ik het goed begrijp zal de formule dan niet meer van toepassing zijn op bijvoorbeeld "basisspreadsheet 750".
Tot nu toe geld het voor elke sheet in je worksheet, maakt niet uit hoe de sheetnaam is

Of wordt de formule voor "basisspreadsheet" meegenomen naar het hernoemde (opslaan als) rekenblad (zoals formules die je in cellen creëert ?).
> Zie boven.

Tenzij je dan de formule steeds kan aanpassen en de nieuwe namen waarop de formule moet van toepassing kan toevoegen.
Ik denk dat, dat niet nodig is tenzij je andere kolommen wilt afvragen

Als dit allemaal te ver zou leiden en te ingewikkeld worden dan denk ik dat er eigenlijk geen probleem is dat die VBA-formule op alle rekenbladen van toepassing is.
Ik kan het ook zo maken, als er bv een bepaald teken in de sheetnaam voorkomt dat de routine wordt uitgevoerd. Denk dat dat het gemakkelijkst is.
> Dat lijkt inderdaad wel het gemakkelijkst (bijvoorbeeld een * in L of N 1 of het woord "codes"; dat staat er toch al in).

- Wat als je de spreadsheet overzet op een andere computer. Moet je daar dan ook de formule creëren vooraleer het werkt ? Het komt mij voor dat de VBA-formules niet gekoppeld zijn aan een bepaalde spreadsheet maar wel aan Excel (op die bepaalde computer).

De Routine die ik heb gemaakt is alleen werkzaam op de worksheet waar deze routine in staat. Dus is deze computer onafhankelijk. Je kan ook een een invoeg module maken, maar die moet je dan eerst installeren voordat je die kunt gebruiken.
Nog wel een kanttekening, om VBA Routines (macro's) te kunnen te gebruiken moet je nog wel een instelling maken in Excel.
Ga in het menu naar Extra -> Macro -> beveiliging
en kies daar beveiligingsniveau -> gemiddeld of laag
Bij gemiddeld krijg je elke keer als je worksheet start de vraag of de macro's geactiveerd moeten worden. en bij laag krijg je geen vragen. Dan worden de macro's altijd automatisch geactiveerd/uitgevoerd

 

[Mr_Magoo]

als je een basis sheet maakt en deze opslaat als sjabloon xxxx.xlt
dan maakt excel een copie van je basis elke keer als je deze opent en daaruit kan je dan werken

Ik heb de routine aangepast
deze werkt pas als er in het bladnaam het woord "Codes" voorkomt. Maakt niet uit of het in kleine letters is of hoofdletters of mix.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If InStr(UCase(Sh.Name), "CODES") > 0 Then
If Application.Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = "i" Or Target.Value = "u" Then
Sh.Rows(Target.Row + 1 & ":" & Target.Row + 1).Insert Shift:=xlDown
End If
End If
End Sub

Kijk maar of dit dan voldoet