Gegevensvalidatie: waarde bijwerken bij wisselen blad

[Figaro75]

Ik heb een excelbestand met meerdere tabbladen.
Vanuit 1 van de bladen (rekenblad) wordt er een lijstje van bedragen opgesomd (koppeling) in een 2e blad (picklist).
Deze heb ik voorzien van een bereik (lijst).

Op een ander blad (offerte) kan ik middels gegevensvalidatie 1 van de bedragen selecteren (bv met of zonder korting).

De bedragen kunnen echter tijdens de offertefase veranderen, bv hoger of lager.
Als ik echter terug ga naar het blad met de offerte (brief) blijft hier het eerder gekozen bedrag staan.
Kan je (bv middels VBA) ervoor zorgen dat hij het bedrag bijwerkt.
Als je namelijk op het driehoekje drukt staan in het lijstje wel de nieuwe bedragen.

Elk 'totaalbedrag' heeft zijn eigen naam alsook de cel met gegevensvalidatie.
Dit omdat er binnen het bestand op meerdere plekken deze bedragen weer herhaald worden.

 

[edmoor]

Je kan op twee momenten je werkbladen aanpassen. Dat gaat om de volgende secties:
Private Sub Worksheet_Activate()

en:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Daarnaast kan je uiteraard vanuit iedere cel in ieder werkblad, iedere andere cel in een ander werkblad aanpassen.

 

[Figaro75]

Ik zat idd aan de 2e te denken maar hoe match je dan de eerder gekozen waarde met de nieuwe waarde op dezelfde plek.

Dus zonder dat je die middels gegevensvalidatie weer uit het 'lijstje' moet selecteren.

Het is met name om te voorkomen dat iemand de brief/offerte kan afdrukken waarbij het bedrag op de brief niet overeenkomt met de berekende waarde in de begroting.
Soms is het namelijk gewoon het begrootte bedrag maar soms het bedrag met korting of het bedrag met opties.

Zie bijgevoegde bestandje.

Bekijk bijlage Map1.xls

 

[HSV]

Waarom verwijder je de validatielijst niet in de brief en zet er onderstaande voor terug.

=Projectbedrag

 

[Figaro75]

@Harry
Dat kan niet want in dit geval is het projectbedrag een optelling van de offerte, de korting en de optionele onderdelen.
In de meeste gevallen is de keus hier namelijk de offertesom. De optionelesom is niet altijd van toepassing.
Als die wel van toepassing is wordt deze vaak apart eronder weergegeven (als in het voorbeeld bestand). Anders is deze verborgen.
En korting wordt vaak reeds in de offertesom verwerkt. Alleen bij prijsonderhandeling of bijzondere gevallen komt er een aparte korting.

Dus in de brief wordt een keuze gemaakt. Maar in de bespreking intern kan dit bedrag veranderen.
Dan zou het mooi zijn als het bedrag in de brief (net als bij een koppeling) mee verandert.

 

[Figaro75]

Kan me wel voorstellen dat ik de gegevensvalidatie op de benaming zet en dan met verticaal zoeken het bijbehorende bedrag erbij zoek.
Het 'nadeel' hiervan is dat ik dan ergens buiten de brief de gebruiker een keus moet laten maken.
Nu moet de keuze 'in' de brief zelf worden gemaakt, wat mijn voorkeur heeft.

De gebruiker heeft hier namelijk 4 bedragen om uit te kiezen.

 

[HSV]

Plaats onderstaande code eens in moduleblad 'Begroting'.
Ik kan het wel in de 'Sheet_Activate' gooien, maar dat is geen garantie of je van blad verwisseld voor het printen.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Columns(6)) Is Nothing Then Sheets("brief").Range("C33") = Range("F27").Value
End Sub

 

[Figaro75]

@HSV
Helaas werkt dit alleen als de keuze overeenkomt met de waarde uit F27.
Dus er wordt niet gekeken welke keuze er is gemaakt.

 

[HSV]

Zoals alles is gekoppeld in jouw bestand zal het altijd de prijs weergeven van F27.
De gegevens voor je validatielijst wordt al bijgewerkt in tab 'picklist'.
De validatielijst wordt daarop weer bijgewerkt.

Als er nog iets aan schort ligt dat misschien aan de omschrijving met wat de bedoeling is.

 

[VenA]

Maar eens zien of dit de bedoeling is.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Columns(6)) Is Nothing Then
    With Application
     .EnableEvents = False
     n = Target.Value
     .Undo
     Sheets("Picklist").Range("AP2:AP6") = Sheets("Picklist").Range("AO2:AO6").Value
     Target.Value = n
     Sheets("Brief").Range("C33") = .Index(Sheets("Picklist").Range("AO2:AO6"), .Match(Sheets("Brief").Range("C33"), Sheets("Picklist").Range("AP2:AP6"), 0))
     .EnableEvents = True
    End With
  End If
End Sub

Dynamischer en beter kan altijd nog.

 

[Figaro75]

@VenA Prachtig, dit werkt.
Dus hij kopieert eerst de oude waarde van de picklist in de kolom ernaast en vergelijkt dan de nieuwe waarde op basis van de gekopieerde oude waarde.
Snap ik dat dan zo goed?

 

[VenA]

Dat snap je goed.:d

 

[HSV]

Zonder extra kolom.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Columns(6)) Is Nothing Then
  With Application
     .EnableEvents = False
     n = Target.Value
     .Undo
       sn = [transpose(picklist!AO2:AO6)]
       sp = Application.Match(Sheets("brief").[c33], sn, 0)
       Target.Value = n
   Sheets("Brief").Range("c33") = .Index(Sheets("Picklist").Range("AO2:AO6"), sp, 0)
     .EnableEvents = True
    End With
  End If
End Sub

 

[Figaro75]

@HSV, jouw oplossing ga ik ook uitproberen.

Iedereen heel erg bedankt voor het meedenken en het komen tot een werkende oplossing.

 

[Figaro75]

Ik krijg in ons echte bestand nu soms een foutmelding.
Dit lijkt te komen door beveiliging van de drie betreffende bladen.

Als ik alle beveiliging ophef heb ik het probleem namelijk niet.
Hoe kom ik erachter welk beveiligd blad de problemen veroorzaakt?
De cellen die wijzigen zijn niet geblokkeerd (celeigenschappen).
Ik kan niet het gehele bestand plaatsen i.v.m. gevoelige bedrijfsinformatie.

 

[VenA]

Dat kan je toch uitproberen?

 

[Figaro75]

Ik kan idd elk blad 1 voor 1 van de beveiliging afhalen om te testen hoe of wat.
Maar hoe kan ik dan in VBA bij dat ene blad tijdelijk de beveiliging opheffen en daarna weer inschakelen?

Dus moet dit tussen With Application en End With staan?

 

[VenA]

Gewoon proberen er komt vast geen rook uit de pc als je het niet goed doet

Sheets(boosdoener).unprotect om de beveiliging eraf te halen .protect om de beveiliging er weer op te zetten. Kijk anders ook even hier https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-protect-method-excel

 

[Figaro75]

Als ik alle bladen bewerkbaar maak (middels een macro haal ik bij 27 bladen de beveiliging eraf) dan kan ik de drie afzonderlijke bladen prima los beveiligen zonder dat het fout gaat.
Zodra ik het bestand weer beveilig middels mijn macro gaat het weer fout...aaarrcchhhh.

Vanavond maar kijken of ik er wat verder mee kom.

 

[Figaro75]

Ik ben er achter dat een tweede stukje VBA stoort met de bovenstaande VBA programmering.

In het begrotingsblad markeer ik namelijk met voorwaardelijke opmaak de rij waarop ik sta.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[GesRij] = Target.Row
End Sub

Hoe kan ik beide codes op het blad laten uitvoeren zonder foutmelding?