Automatisch invullen van meerdere cellen na validatie

[Tjoptjop333]

Goededag,

Ik ben bezig om een tekeningenlijst op te zetten; Nou wil ik de tekening nummers (Disciplines) automatisch laten genereren.
Validatie middels 3 verschillende keuzes is gelukt, te weten: Discipline, Techniek en verdieping. De gegevens van deze validatie's staan in het tabblad "gegevens lijst".

Als ik in de sheet "Tekeningen lijst" Bij discipline b.v. "Bouwkundig" kies en vervolgens bij Techniek "500 - Bouwplan" verschijnt er automatisch in kolom H enkel het getal "500"; Dit werkt prima echter:
Nu wil ik dat dit voor de volgende keuze, te weten "Verdieping", tevens gebeurt, en dat deze validatie in kolom "G" terecht komt...
Dit zodat het tekening nummer automatisch verschijnt in: Objectnummer-Verdieping-techniek-001 (001 is een handmatige invulling)

Ik heb al flink wat uurtjes nu zitten stoeien in de VBA-code en gesnord op internet, maar geen enkele oplossing kunnen vinden.

Hopelijk dat iemand mij een zetje kan geven in de goede richting, of zelfs de juiste code kan aangeven.

Bijgaand het bestand.
Bekijk bijlage 20170802 - Tekeningenlijst.xlsm
Groet Michel Vermeulen

 

[mart37]

Heb je al eens gekeken naar de functie LINKS (LEFT)?

 

[Tjoptjop333]

Heb je al eens gekeken naar de functie LINKS (LEFT)?

De functie "left" neemt de gehele tekst over, dit is niet de bedoeling. Tevens als ik deze functie in de cel invoer, zal ik dit handmatig voor elke cel moeten kopiëren, dit is niet mijn bedoeling. Er zal toch een VBA code aanvulling moeten zijn denk ik op wat ik reeds heb staan... Of begrijp ik jouw verwijzing niet goed??

 

[mart37]

Voor de drie cijfers aan de linkerkant bij techniek is dit voldoende: Target.Offset(, 5) = Left(Target.Value, 3)
Het is jammer dat je andere lijst (verdieping) niet met een vast aantal cijfers werkt.

 

[Tjoptjop333]

De cijfers voor techniek is alleen t.b.v. de juiste keuze door de gebruiker, in de "gegevens lijst" staat het getal dat dient te worden toegepast in het tekening nummer.

In principe zijn cijfers van de verdiepingen lijst ook "vaste" cijfers, even als die van de techniek.

Maar begrijp ik het nou goed dat de functie Target.Offset(, 5) = Left(Target.Value, 3) in de cel dient te worden toegepast, of dient dit verwoord te worden in de VBA code??

 

[mart37]

Dat kan je plaatsen in de VBA-code.
Maar omdat je liever de tabel gegevenslijst als basis wilt het volgende toevoegen:

    If Not Intersect(Target, Columns([COLOR="#FF0000"]4[/COLOR])) Is Nothing Then
        With Sheets("gegevens lijst")
            On Error GoTo Err_Handler
            CustRow = .Columns(2).Find(Target.Value, , xlValues, xlWhole).Row
            Target.Offset(,[COLOR="#FF0000"] 6[/COLOR]) = .Cells(CustRow, 3).Value
        End With
    End If

 

[VenA]

Mij lijkt zoiets wel voldoende

Private Sub Worksheet_Change(ByVal Target As Range)
Dim f As Range
  If Intersect(Target, Range("C:D")) Is Nothing Then Exit Sub
  Set f = Sheets("gegevens lijst").Columns(2).Find(Target.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then Target.Offset(, IIf(Target.Column = 3, 5, 3)) = f.Offset(, 1).Value
End Sub

 

[Tjoptjop333]

Helemaal geweldig, dit was exact wat ik zocht. Dank je wel!!

Nog een andere vraag, als dat is toegestaan hoor . Ik zou ook graag de lijst automatisch willen laten sorteren:

Bouwkundig - Verdieping (laag naar hoog) - Numeriek (400/410/420 enz.) - nummer (001/002/003 enz.)
Elektrotechnisch - Verdieping (laag naar hoog) - Numeriek (010/011/012 enz.) - nummer (001/002/003 enz.)
Werktuigkundig - Verdieping (laag naar hoog) - Numeriek (200/210/220 enz.) - nummer (001/002/003 enz.).

Dus:
Bouwkundig / 400-Interieur / -2-kelder / 001
Bouwkundig / 400-Interieur / -1-kelder / 002
Enz.
Elektrotechnisch / 010-Elicht-kracht / -2-kelder / 001
Elektrotechnisch / 010-Elicht-kracht / -1-kelder / 002
Enz.
Werktuigkundig / 200-Ventilatie en luchtbehandeling / -2-kelder / 001
Werktuigkundig / 200-Ventilatie en luchtbehandeling / -1-kelder / 002
Enz.

Weet jij ook hiervoor een mooie oplossing?

 

[VenA]

Range("B8:J" & Cells(Rows.Count, 2).End(xlUp).Row).Sort Range("B8"), , Range("C8"), , , Range("D8"), , xlNo

 

[Tjoptjop333]

De code werkt perfect hoor, echter ik krijg een foutmelding als ik een regel wil tussenvoegen..

Fout 1004 tijdens uitvoering:
Door de toepassing of door object gedefinieerde fout

Is dit tevens mogelijk zonder een foutmelding te krijgen?

 

[Tjoptjop333]

Range("B8:J" & Cells(Rows.Count, 2).End(xlUp).Row).Sort Range("B8"), , Range("C8"), , , Range("D8"), , xlNo

Thanx voor de code, echter waar zal ik deze moeten ingeven?

 

[VenA]

Volgens mij zij er niet zo heel veel opties.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim f As Range
  If Intersect(Target, Range("C:D")) Is Nothing Then Exit Sub
  Set f = Sheets("gegevens lijst").Columns(2).Find(Target.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then Target.Offset(, IIf(Target.Column = 3, 5, 3)) = f.Offset(, 1).Value
  Range("B8:J" & Cells(Rows.Count, 2).End(xlUp).Row).Sort Range("B8"), , Range("C8"), , , Range("D8"), , xlNo
End Sub

 

[Tjoptjop333]

Beste VenA,

Excuus hiervoor, bij het programmeren als vanouds (in commodore tijdperk :S) ging dit (als ik het nog goed herinner) vaak met commando, actie en end... en weer opnieuw .

Maar top voor de oplossing! Hij schikt het heel mooi Vervelend is alleen dat de sortering direct al wordt uitgevoerd, hierdoor verspringt de regel.. Mooiste zou zijn als de regel geheel is ingevuld en dan pas gaat sorteren..

 

[VenA]

Zo dan

Private Sub Worksheet_Change(ByVal Target As Range)
Dim f As Range
  If Intersect(Target, Range("C:D")) Is Nothing Then Exit Sub
  Set f = Sheets("gegevens lijst").Columns(2).Find(Target.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    Target.Offset(, IIf(Target.Column = 3, 5, 3)) = f.Offset(, 1).Value
    If Target.Column = 4 Then Range("B8:J" & Cells(Rows.Count, 2).End(xlUp).Row).Sort Range("B8"), , Range("C8"), , , Range("D8"), , xlNo
  End If
End Sub

 

[Tjoptjop333]

Geweldig!!! Dit werkt top! Ik ga me in de code verdiepen om zelf ook nog meer handigheid er in te krijgen!

Die foutcode bij het invoegen van een rij zal waarschijnlijk niet te voorkomen zijn?

En als ik dan helemaal een klein beetje brutaal mag zijn...
Als ik in de lijst een rij wil toevoegen die het objectnummer (K303) en de - tekens direct overneemt en invult vanuit de bovenliggende regels is dit ook realiseerbaar om te schrijven voor jou?

 

[VenA]

Zonder foutmelding bij het invoegen van een nieuwe rij. Met een dubbelklik worden de gegevens van de rij gekopieerd naar een nieuwe rij.

 

[Tjoptjop333]

Helemaal geweldig! Enorm bedankt, ik ga hier verder mee stoeien!

 

[Tjoptjop333]

Zonder foutmelding bij het invoegen van een nieuwe rij. Met een dubbelklik worden de gegevens van de rij gekopieerd naar een nieuwe rij.

Beste VenA,

Ik ben weer wezen stuntelen met het bestand en heb de opzet iets aangepast. In het VBA sheet, dacht ik dan ook wel te kunnen aanpassen naar de nieuwe situatie :S... Echter de voorwaarde's komen nu niet meer overeen.... Zou jij eens kunnen kijken waar of dat ik nou de fout maak m.b.t. het overnemen van de gegevens, alsmede het sorteren van de regels...

Is het ook mogelijk om de Validatielijsten/Cellen te beveiligen dat de kolommen H, I en J niet handmatig gegevens in te voeren zijn en alleen keuzes gemaakt kunnen worden d.m.v. de Validatielijsten?

Bekijk bijlage 20170803 - Tekeningenlijst.xlsm

Groet Michèl

 

[VenA]

In je validatielijsten kan je toch niets anders invoeren?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim f As Range
  If Intersect(Target, Range("I:J")) Is Nothing Or Target.Count > 1 Then Exit Sub
  Set f = Sheets("gegevens lijst").Columns([COLOR="#FF0000"]3[/COLOR]).Find(Target.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    Target.Offset(, IIf(Target.Column = 9, -4, -7)) = f.Offset(, 1).Value
    If Target.Column = 10 Then Range([COLOR="#FF0000"]"A8:J" & Cells(Rows.Count, 8[/COLOR]).End(xlUp).Row).Sort Range("H8"), , Range("I8"), , , Range("J8"), , xlNo
  End If
End Sub

 

[Tjoptjop333]

Top weer!! Ik zat dus wel aardig in de goede richting, beredeneringsfoutje gemaakt.. Al doende leer ik

Ik had dit niet direct getest na het verkregen van je voorgaande versie. Maar inderdaad, dit kan helemaal niet! Is geweldig zo.

Ga ik nog weer even verder met vragen:
In cel I en J staan momenteel cijfers vb 500 - Bouwplan, 51 - 2e tussenverdieping. Is het mogelijk om de teksten in de keuzelijst "500 - Bouwplan" wel te laten zien, maar eenmaal aangeklikt alleen de tekst "Bouwplan" in de cel te tonen? Dit vraag ik daar er collega's zijn die de linken niet geheel snappen, maar wel beide dienen te zien :S.. In de lijst volstaat alleen de naam om overzicht te houden..