[Excel] Standaard waarde met latere toevoeging

[Ghizmo]

Hoi allemaal,

Ik zit hier met een lijst die gemaakt moet worden. Nou wil ik dus een soort van formule of iets dergelijks gebruiken om een paar 'standaard' dingen te laten weergeven, en het werk een beetje te vergemakkelijken, die ook niet gewist mogen/kunnen worden.

Voorbeeld:
In veld A1 moet de 'K' staan, en die moet daar altijd staan, als ik dan A1 aanklik en '1234' type zou er dus moeten komen te staan 'K1234'.

Ik ben zelf geen meester met Excell, weet er wel een beetje van, maar dat is meer voor rekenachtige dingen binnen excell.
Nu heb ik al GoT een beetje afgezocht, met mijn zoek criteria kom ik niet echt ver in dit vraagstuk. Google ook al beetje afgezocht, maar kom alleen maar sites tegen met basis informatie en met de simpelste formules als voorbeelden.

Zou heel fijn zijn als er iemand is die zou kunnen helpen want dit is iets wat maandelijks gedaan moet worden, en vaak gaat het wel over duizend regels!

Ghizzy

[dottie]

Zonder VBA zal dat in dezelfde cel niet lukken. Als het ook in een andere cel mag dan zet je in c1 =A1&B1. Vervolgens naar beneden slepen en alles kopieren>>plakken speciaal>>waarden en voila

[Ghizmo]

Nee het zal echt in dezelfde cel moeten, want als het in meerdere cellen gedaan zou moeten worden zou ik het zelf nog wel kunnen oplossen hehe

Maar hoe zit het dan met dat VBA gebeuren, want daar weet ik dus niks van .. En zou het dan ook kunnen voor een hele kollom in plaats van maar 1 cel ..?

[F_J_K]

offtopic:
Het heet Excel, met 1 l. Ik heb dus de titel wat aangepast.

Waarom moet het nu in 1 cel? Maar goed, als geen ervaring hebt met VBA wil je dat niet gaan gebruiken in documenten die je verplicht aan eindgebruikers.

[Clock]

• Rechtermuisklik op tabblad, klik op programmacode weergeven.
• Dubbelklik in het menu aan de linkerkant nu op de desbetreffende sheetnaam.
• Haal de paar regels code die er evt. instaan weg, en plak de volgende code:
  
  code:
  

  Private Sub Worksheet_Change(ByVal Target As Excel.Range)
      Application.EnableEvents = False
  
      If (Target.Address = "$A$1") Then
          Range("$A$1").Value = "K" + CStr((Range("$A$1").Value))
      End If
      
      If (Target.Address = "$A$2") Then
          Range("$A$2").Value = "Hoi " + CStr((Range("$A$2").Value))
      End If
      
      Application.EnableEvents = True
  End Sub

• De tekst ed kan je nu zelf wel aanpassen dacht ik. Je kan het If statement meerdere malen herhalen om dit toe te passen bij meerdere cellen (is nu voor 2 cellen gedaan (A1 en A2))

[Ghizmo]

Ok , gaaf!! Ik wist helemaal niet dat dat kon! is toevallig die eerste If statement dan ook uit tebreiden voor een hele range ofzo. Iets met euuh.

,"$A$20"

ofzo iets ..
En als dat niet kan, ook goed! Heel erg bedankt voor je hulp clock!!

Oh by the way, ik ben de eindgebruiker

ghizzy

[Clock]

Ghizmo schreef op maandag 07 mei 2007 @ 20:02:
Ok , gaaf!! Ik wist helemaal niet dat dat kon! is toevallig die eerste If statement dan ook uit tebreiden voor een hele range ofzo. Iets met euuh.

,"$A$20"

ofzo iets ..
En als dat niet kan, ook goed! Heel erg bedankt voor je hulp clock!!

Oh by the way, ik ben de eindgebruiker

ghizzy

Probeer dan iets als:

code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Application.EnableEvents = False

    Dim RangeKolom As String, Prefix As String
    Dim RangeBeginRij As Integer, RangeEindRij As Integer
    
    RangeKolom = "$A"
    RangeBeginRij = 5
    RangeEindRij = 15
    Prefix = "Hoi"
    
    If (Len(Target.Address) <= 5) Then
        If (Left(Target.Address, 2) = RangeKolom And Mid(Target.Address, 4) >= RangeBeginRij And Mid(Target.Address, 4) <= RangeEindRij) Then
            Range(Target.Address).Value = Prefix + CStr((Range(Target.Address).Value))
        End If
    End If
    Application.EnableEvents = True
End Sub

De variabelen kan je instellen naar eigen behoefte.

[ Voor 2% gewijzigd door Clock op 07-05-2007 21:05 . Reden: Kleine verbetering mbt selectie deleten error ]

[denivan]

Kan je ook niet 1 kolom vullen met 'K' , in de 2e kolom alle waarden zetten en dan via een Excell functie deze kolommen samenvoegen ? Lijkt me toch makkelijker zonder code, of niet ?

[Ghizmo]

Ghizmo schreef op maandag 07 mei 2007 @ 16:57:
Nee het zal echt in dezelfde cel moeten ..

Dat is iets met de opmaak van één of ander bestand dat een collega heeft gemaakt, en iets met de printer uitvoering ervan.
Ik heb het maar zo gelaten, en ga ook niet tegen me collega in (tevens mijn baas).

Clock schreef op maandag 07 mei 2007 @ 20:17:

code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Application.EnableEvents = False

    Dim RangeKolom As String, Prefix As String
    Dim RangeBeginRij As Integer, RangeEindRij As Integer
    
    RangeKolom = "$A"
    RangeBeginRij = 5
    RangeEindRij = 15
    Prefix = "Hoi"
    
    If (Len(Target.Address) <= 5) Then
        If (Left(Target.Address, 2) = RangeKolom And Mid(Target.Address, 4) >= RangeBeginRij And Mid(Target.Address, 4) <= RangeEindRij) Then
            Range(Target.Address).Value = Prefix + CStr((Range(Target.Address).Value))
        End If
    End If
    Application.EnableEvents = True
End Sub

De variabelen kan je instellen naar eigen behoefte.

Is goed Clock, ik zal deze eens even testen!
(lache joh, ik leer zowaar nog een beetje codeer taal (Voornamelijk te lezen dan ))

[Ghizmo]

Heel erg bedankt clock, hij werkt uitstekend!
Alleen is het nu dat als ik dus als beginrij 1 invoer, ne bij eindrij 100, hij bij de 100 geen 'Hoi' er voorzet. Dus ben ik nog even verder gegaan naar 120, en voert hij dus bij alles na de honderd nu geen 'Hoi' meer in.

Nu geloof ik dat wat ik lees de code gewoon alles wat groter&gelijk aan beginrijd, en kleiner&gelijk aan eindrij, hij daar dus die 'Hoi' moet invullen. en ik zie geen iets ofzo dat ervoor zou kunnen zorgen dat hij niet verder als die honderd wilt gaan .. Zou het kunnen liggen aan excel ofzo dan dat hij dat niet wilt doen.

Edit: heb ff nog wat gesnuffelt Zou er dan als variabele voor , uhh, eindrij? dan Long gebruikt moeten worden ..? Of brabbel ik nu gewoon complete n00b-taal uit

[ Voor 12% gewijzigd door Ghizmo op 08-05-2007 09:25 ]

[Lustucru]

code:

If (Len(Target.Address) <= 5)

zorgt ervoor dat targets met een adres dat langer is dan vijf tekens niet verwerkt worden. $A$99 gaat dus nog net, $A$100 niet meer.


Sowieso is die manier om het target te parsen nogal omslachtig. Het volgende werkt ook, is duidelijker leesbaar en minder foutgevoelig:

code:

If Not (Intersect(Target, Range("A2:A5")) Is Nothing) Then
    'doe je ding
End If

Daarnaast is het misschien wel aan te raden om nog even met een if je te controleren of de prefix er niet al voorstaat

[ Voor 20% gewijzigd door Lustucru op 08-05-2007 09:43 ]

[Clock]

Lustucru schreef op dinsdag 08 mei 2007 @ 09:41:

code:

If (Len(Target.Address) <= 5)

zorgt ervoor dat targets met een adres dat langer is dan vijf tekens niet verwerkt worden. $A$99 gaat dus nog net, $A$100 niet meer.

Dat is idd de 'oorzaak'. Zoals ik zei: het is een beginnetje, geen compleet prefecte code Maar ik ben met je eens dat dit een beetje knullig is.

Lustucru schreef op dinsdag 08 mei 2007 @ 09:41:
Sowieso is die manier om het target te parsen nogal omslachtig. Het volgende werkt ook, is duidelijker leesbaar en minder foutgevoelig:

code:

If Not (Intersect(Target, Range("A2:A5")) Is Nothing) Then
    'doe je ding
End If

Daarnaast is het misschien wel aan te raden om nog even met een if je te controleren of de prefix er niet al voorstaat

Zo is ie nog weer wat beter. We komen er wel

code:

Private Sub AttachPrefix(Prefix As String, RangeBeginCel As String, RangeEindCel As String, CheckPrefix As Boolean, Target As Range)
    If Not (Intersect(Target, Range(RangeBeginCel + ":" + RangeEindCel)) Is Nothing) Then
        If (CheckPrefix = True) Then
            If Not (Left(CStr(Range(Target.Address).Value), Len(Prefix)) = Prefix) Then
                Range(Target.Address).Value = Prefix + CStr(Range(Target.Address).Value)
            End If
        Else
            Range(Target.Address).Value = Prefix + CStr(Range(Target.Address).Value)
        End If
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Application.EnableEvents = False

    'AttachPrefix (Prefixwaarde, Begincel, Eindcel, Checkprefix, Targetvar)
    AttachPrefix "K", "A1", "B2", True, Target
    AttachPrefix "B", "A3", "B4", False, Target

    Application.EnableEvents = True
End Sub

Met dank aan Lustucru

[Ghizmo]

haha, van deze laatste snap ik helemaal niks meer.
Hij voegt een K toe aan a2 en a3 en bij b2 en b3 voegt hij de b toe. Terwijl er juist a1 en B2 voor prefix-K staan en A3 en B4 voor prefix-B ..


edit: OK ik zie het al, hij selecteert een range van boven naar rechts onder in dit geval.

[ Voor 19% gewijzigd door Ghizmo op 08-05-2007 13:24 ]

[Clock]

Ghizmo schreef op dinsdag 08 mei 2007 @ 13:05:
haha, van deze laatste snap ik helemaal niks meer.
Hij voegt een K toe aan a2 en a3 en bij b2 en b3 voegt hij de b toe. Terwijl er juist a1 en B2 voor prefix-K staan en A3 en B4 voor prefix-B ..

Dat hij een K toevoegd aan A3 lijkt me sterk. Ik zal even uitleggen hoe het scriptje gebruikt moet worden.

Je kan een range (bereik) aangeven waarin een bepaalde prefix voor de cellen gezet wordt. Een nieuw prefixbereik instellen doe je dmv de volgende regel:

code:

AttachPrefix "K", "A1", "B2", True, Target

Deze funtie heeft 5 parameters. De eerste (hier "K") is de waarde van de prefix. De tweede (hier "A1") is de begincel van het bereik (de linkerbovencel dus). De derde is de eindcel van het bereik (de rechterondercel dus). Alle cellen die binnen die rechthoek (bereik) vallen worden voorzien van de prefix. De 4e parameter geeft aan of er ook gecheckt moet worden of de gebruiker de prefix zelf al geplaatst heeft (Dus als de user intikt K123, dat er dan geen KK123 komt te staan). Deze parameter kan True of False zijn. De 5e en laatste parameter geeft door welke cel op het moment van aanroepen gewijzigd is. Niks mee doen, gewoon laten staan.

In het voorbeeld heb ik 2 prefixbereiken gemaakt. In het bereik A1:B2 worden de cellen voorzien van een K, en wordt er gekeken of de user zelf de prefix heeft al neergezet (Dus K123 blijft K123 en wordt geen KK123). In het bereik A3:B4 worden de cellen voorzien van een B, en wordt er niet gekeken naar een evt bestaande prefix. (Dus B123 wordt BB123).

Hoop dat het zo wat duidelijker is

[Ghizmo]

Ja ik schreef het een beetje heel erg verkeerd op En ik snap het nu ook een stuk beter , in elk geval de uitvoering van dit stukje code.
Kan het bereik ook verkleind worden naar een range voor maar 1 kolom. Dus alleen A in plaats van B hier ook nog bij? (Waar deze controle ook nog inzit, zodat er geen KK123 komt te staan)

Of moet ik daarvoor terug stappen naar het eerste stukje code dat je hier had neergezet? (en dan zonder controle gebruiken)

[Clock]

Ghizmo schreef op dinsdag 08 mei 2007 @ 13:43:
Ja ik schreef het een beetje heel erg verkeerd op En ik snap het nu ook een stuk beter , in elk geval de uitvoering van dit stukje code.
Kan het bereik ook verkleind worden naar een range voor maar 1 kolom. Dus alleen A in plaats van B hier ook nog bij? (Waar deze controle ook nog inzit, zodat er geen KK123 komt te staan)

Of moet ik daarvoor terug stappen naar het eerste stukje code dat je hier had neergezet? (en dan zonder controle gebruiken)

Jawel, gebruik dan:

code:

AttachPrefix "K", "A1", "A120", True, Target

[Ghizmo]

hoe zit het dan met die tweede AttachPrefix lijn? moet die dna ook blijven staan? Lijkt mij juist van niet ... Maar goed, zal wel ff testen, en zien wat ik krijg

[Ghizmo]

Ok dit is dus wat ik heb staan:

code:

Private Sub AttachPrefix(Prefix As String, RangeBeginCel As String, RangeEindCel As String, CheckPrefix As Boolean, Target As Range)
    If Not (Intersect(Target, Range(RangeBeginCel + ":" + RangeEindCel)) Is Nothing) Then
        If (CheckPrefix = True) Then
            If Not (Left(CStr(Range(Target.Address).Value), Len(Prefix)) = Prefix) Then
                Range(Target.Address).Value = Prefix + CStr(Range(Target.Address).Value)
            End If
        Else
            Range(Target.Address).Value = Prefix + CStr(Range(Target.Address).Value)
        End If
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Application.EnableEvents = False

    'AttachPrefix (Prefixwaarde, Begincel, Eindcel, Checkprefix, Targetvar)
    AttachPrefix "K", "A1", "A120", True, Target

    Application.EnableEvents = True
End Sub

Maar hij haalt die K dus niet tevoorschijn. Ik kreeg eerst iedere keer een foutopsoring ofzo en dan werd er een lijn geel, maar dat is nu ineens weg.
De lijn die steeds geel werd is deze :

code:

If Not (Intersect(Target, Range(RangeBeginCel + ":" + RangeEindCel)) Is Nothing) Then

Dat is lijn 2 van de eerste sub.

[Clock]

Ik denk dat je per ongeluk een typfoutje in de functieaanroep hebt gemaakt waardoor die 'crashte' en de EnableEvents nog uit stond waardoor ie nu niks meer doet

Start Excel even helemaal opnieuw op, en gebruik de volgende code:

code:

Private Sub AttachPrefix(Prefix As String, RangeBeginCel As String, RangeEindCel As String, CheckPrefix As Boolean, Target As Range)
    On Error Resume Next
    If Not (Intersect(Target, Range(RangeBeginCel + ":" + RangeEindCel)) Is Nothing) Then
        If (CheckPrefix = True) Then
            If Not (Left(CStr(Range(Target.Address).Value), Len(Prefix)) = Prefix) Then
                Range(Target.Address).Value = Prefix + CStr(Range(Target.Address).Value)
            End If
        Else
            Range(Target.Address).Value = Prefix + CStr(Range(Target.Address).Value)
        End If
    End If
    If Err.Number <> 0 Then
        MsgBox "Foutcode: " & Err.Number
        Application.EnableEvents = True
        Stop
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Application.EnableEvents = False

    'AttachPrefix (Prefixwaarde, Begincel, Eindcel, Checkprefix, Targetvar)
    AttachPrefix "K", "A1", "A120", True, Target

    Application.EnableEvents = True
End Sub

[Ghizmo]

Ik weet nog dat ik die attachprefix (lijn 2) weghaalde, en dat hij toen niets meer deed, heb toen maar even bij macros de beveiliging ook op laag gezet voor het geval dat.

Nu snap ik alleen niet helemaal wat er in jou code anders is dan wat ik had
Heh, ben nogal een newbie met codering ..

Hej clock, ik heb die laatste code van je nog even getest! Echt super joh, wou dat ik dat kon.
Kun je ook uitleggen hoe dat alles precies in mekaar zit, bijvoorbeeld wat die boolean precies inhoudt en waar het voor dient en zo. En dan specifiek gericht tot dit stukje script. dus euh, soort van aantekeningen hiervan/over? Als dat niet teveel gevraagd is tenminste, of teveel van je tijd in beslag neemt.

(Of is dit niet de geschikte plek en zou ik dat net zo goed op google kunnen opzoeken)

[Clock]

Ghizmo schreef op dinsdag 08 mei 2007 @ 16:22:
Ik weet nog dat ik die attachprefix (lijn 2) weghaalde, en dat hij toen niets meer deed, heb toen maar even bij macros de beveiliging ook op laag gezet voor het geval dat.

Nu snap ik alleen niet helemaal wat er in jou code anders is dan wat ik had
Heh, ben nogal een newbie met codering ..

Hej clock, ik heb die laatste code van je nog even getest! Echt super joh, wou dat ik dat kon.
Kun je ook uitleggen hoe dat alles precies in mekaar zit, bijvoorbeeld wat die boolean precies inhoudt en waar het voor dient en zo. En dan specifiek gericht tot dit stukje script. dus euh, soort van aantekeningen hiervan/over? Als dat niet teveel gevraagd is tenminste, of teveel van je tijd in beslag neemt.

(Of is dit niet de geschikte plek en zou ik dat net zo goed op google kunnen opzoeken)

Ik wil het wel voor je toelichten, maar ik denk dat het makkelijker en beter begrijpbaar is als ik je een curcus VBA stuur. Deze is in het engels, maar legt helder en duidelijk de grondbeginselen van VBA uit en gaat dan wat verder de diepte in.

[Chris03]

Als het je alleen om getallen te doen is heb je VBA niet eens nodig. Bij de opmaak van je cellen kies je "aangepast" en als type vul je "K"# in.

[F_J_K]

Aaaargh. Inderdaad.

^^ Wat Chris03 zegt. Soms kan het leven zo makkelijk zijn

[Ghizmo]

Ja dat had i kal geprobeerd, maar hij veradnert de hele Cel in K en valt niet meer aan te passen (kan aan iets liggen) maar het werkte in elk geval gewoon niet.

[F_J_K]

Dan ben je de aanhalingstekens vergeten denk ik. Dit werkt namelijk prima - zeker in combinatie met Data->Valideren->geheel getal.

Vul dan standaard overal een 0 in en je hebt 100% de vraag in de topicstart.

[Clock]

Hehe, dat is idd een heel stuk makkelijker. Wist niet dat dat mogelijk was

[Lustucru]

Chris03 schreef op dinsdag 08 mei 2007 @ 16:45:
Als het je alleen om getallen te doen is heb je VBA niet eens nodig. Bij de opmaak van je cellen kies je "aangepast" en als type vul je "K"# in.

Aaargh Dat had ik dus ook bedacht maar dat vond ik zó voor de hand liggen dat ik ervanuit ging dat wel niet de bedoeling zou zijn.

[Ghizmo]

Haha, jah zoals ik al zei had ik het al geprobeerd, heb er zelfs speciaal nog een andere office voor uit de kast gehaald, om te kijken of het misschien lag aan de versie. Maar in 2007 en in 2003 doet hij het bij mij niet, niet met de () en niet met de "", ook niet met een andere waarde, bijv: &
(Begin ik nou steeds meer op een leek te lijken, of komt het omdat het zo vroeg is dat ik gewoon nog half slaap .. )

edit: typefouten

[ Voor 3% gewijzigd door Ghizmo op 09-05-2007 08:42 ]

[F_J_K]

()? Welke haakjes?

Je moet precies intypen wat Chris03 deed:

code:

"K"#

Dus aanhalingsteken-Kaa-aanhalingsteken-hekje enter

[Ghizmo]

haha, ik kan wel lezen hoor, zo ver reikt mijn kennis nog net wel.
En met aanhalingsteken-Kaa-aanhalingsteken-hekje lukt het ook niet.

Maar goed twee oplossingen dus, als er 1 niet werkt zal de ander het wel doen. Heel erg bedankt allemaal!

[hufkes]

Om het netjes te doen moet je dat stukje "K"# dus invullen bij cell-format/custom over alle cellen waar je het wilt hebben. Dan vul je overal in al die cellen zelf alvast een 0 in -> er komt dan alleen een K te staan.

Om nu te zorgen dat er alleen gehele getallen ingevuld mogen worden pak je weer de range, dan data/validation. Selecteer "whole number", "greater then or equal to", "0".
In het tweede tabblad van de validationrule vul je eventueel een klein berichtje in dat de gebruiker alleen een geheel getal in dient te vullen en op het derde tabblad idem maar dan als errormessage.

En klaar is Klara Ghizmo