Validatie dmv lijst met gegevens uit ander tabblad (VBA)

[robert123321]

Hoi,

ik wil graag wil graag cellen valideren dmv van een dropdown list met daarin gegevens uit een ander tabblad.
Als ik het via het menu Data -> Valideren probeer krijg ik de melding dat gegevensinvoer van een ander tabblad hiervoor niet mogelijk is. Met VBA krijg ik het ook niet voor elkaar.
Weet iemand hiervoor een trucje?

Ik gebruik de code:

Dim ValFormule as String
Dim i as Integer

i = ThisWorkbook.Sheets("Medewerkers").Columns(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count - 1

[B]ValFormule = "=Medewerkers!$A$2:$A$" & i[/B]


With ActiveSheet.Range("A5:A65536").Validation
         .Delete
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:=ValFormule
         .IgnoreBlank = True
         .InCellDropdown = True
         .InputTitle = ""
         .ErrorTitle = "Foutieve invoer!"
         .InputMessage = ""
         .ErrorMessage = "Kies enkel een medewerker uit de lijst!"
         .ShowInput = True
         .ShowError = True
     End With

 

[Thankyou]

Via VBA zou ik het niet durven zeggen maar misschien wel net als de gewone validatielijst via bereiknamen in plaats van A5:A65536? (bereiknaam wel variabel maken)

Succes,

 

[Efsix]

Volgens mij kun je de lijst gewoon een naam geven b.v. "Naam".
Dan kun je via validatie = "Naam"
Bij mij werkt het hoor. zie bijlage

groeten, Romé

 

[robert123321]

Hallo Romé,

Dat werkt inderdaad, maar als de lijst met namen wordt uitgebreid moet het bereik van de lijst worden aangepast/vergroot.
Dat kan je doen door de verwijzing aan te passen.
Aangezien ik zelf niet met het document aan de slag moet , maar enkele dames die weinig tot geen verstand hebben van excel, zie ik dat niet goed gaan. Ik heb geen zin/behoefte om wanneer er een naam wordt toegevoegd of verwijderd uit de lijst de verwijzing aan te gaan passen.

Om die reden wil ik het graag met vba voor elkaar krijgen. Je kan vast mbv VBA ook een naam bepalen welke verwijst naar een bepaald bereik. Met VBA kan ik dat bereik in ieder geval wel automatisch laten aanpassen met de functie Count.

Dus met andere woorden: Als iemand mij kan vertellen of het mogelijk een naam te bepalen welke verwijst naar een bepaald bereik mbv VBA hoor ik het graag.

ps. en ook graag hoe...

 

[gelens]

Robert,

Dit kan ook zonder VBA.

Met de lijst van medewerkers in kolom gebruik je de ovlgende formule :

=Verschuiving($a$1;1;0;aantalarg($a:$a);1)

 

[robert123321]

Opgelost!

Ik ben er inmiddels zelf ook uit dankzij de optie om macro´s op te nemen

Zie hier mijn oplossing

Dim MederwerkersLijst as string

i = ThisWorkbook.Sheets("Medewerkers").Columns(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count - 1


MederwerkersLijst = "=Medewerkers!$A$2:$A$" & 2 + i

ActiveWorkbook.Names.Add Name:="Aanvragers", RefersTo:=MederwerkersLijst 

    With ActiveSheet.Range("A5:A65536").Validation
         .Delete
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=Aanvragers"
         .IgnoreBlank = True
         .InCellDropdown = True
         .InputTitle = ""
         .ErrorTitle = "Foutieve invoer!"
         .InputMessage = ""
         .ErrorMessage = "Kies enkel een aanvragend arts uit de lijst!"
         .ShowInput = True
         .ShowError = True
    End With

Iedereen uiteraard bedankt voor de tijd en moeite!

 

[robert123321]

toch 1 probleempje

Het werkt prima, behalve dan dat er geen melding komt wanneer er iets wordt ingetypt wat niet in de validatielijst voorkomt.

Deze snap ik even niet. Als ik gegevens in de lijst zet afkomstig van hetzeflde tabblad dan wel, maar komen ze van een ander tabblad dan geen melding... vreemd...

 

[Warme bakkertje]

Selecteer Invoegen >> Naam >> Definieëren en maak hier een dynamisch bereik met de naam Aanvragers
Onderstaande code volstaat dan om alle validaties in kolom A aan te passen

Sub Validatie()
For x = 1 To 65536
    With ActiveSheet.Cells(x, 1).Validation
         .Delete
         .Add xlValidateList, xlValidAlertStop, xlBetween, "=Aanvragers"
         .IgnoreBlank = True
         .InCellDropdown = True
         .InputTitle = ""
         .ErrorTitle = "Foutieve invoer!"
         .InputMessage = ""
         .ErrorMessage = "Kies enkel een aanvragend arts uit de lijst!"
         .ShowInput = True
         .ShowError = True
    End With
Next
End Sub

 

[robinvdveeken]

Kan je de gehele file nog even uploaden?
Dank je

 

[Warme bakkertje]

@ robinvdveeken
Aangezien dit niet jouw vraag is, is mijn vraag>> Welk bestand uploaden ??

 

[robinvdveeken]

"Aangezien ik zelf niet met het document aan de slag moet , maar enkele dames die weinig tot geen verstand hebben van excel, zie ik dat niet goed gaan. Ik heb geen zin/behoefte om wanneer er een naam wordt toegevoegd of verwijderd uit de lijst de verwijzing aan te gaan passen."

Dit probleem heb ik ook.. Maar met de oplossingen zo los opgesomt kom ik er niet uit.
Het bestand met het uiteindelijke resultaat zou me vast helpen.
Lijkt me uberhaupt een mooi streven om als laatste bericht van elk topic het uiteinelijke resultaat te posten.
Bedankt!

 

[Ad Becude]

@robinvdveeken,
Als je de forum regels eerst eens leest.....weet je wat "Warme bakkertje" bedoeld.
Je kaapt andermans topic.

 

[robinvdveeken]

@ Ad Becude
Oneens. De vraag is beantwoord en er is een oplossing uitgekomen. Voor alle volledigheid zou het mooi zijn als deze word gepost. Hierdoor kan het topic voor meer mensen tot een oplossing leiden zonder dat deze berichten posten en ontstaan er dus minder overlappende topics.

 

[Warme bakkertje]

Dit is een Helpmij-forum, niet een MaakHetVoorMij-forum.Echter omdat het morgen mijn verjaardag is ben ik in een goede bui dus daarom bij deze. Ik heb de macro in een Activate-event gestoken dus telkens het werkblad geactiveerd wordt (na het aanvullen v/d lijst bv) worden alle validaties aangepast. Ook moet je nog het laatste cijfer aanpassen in x= 1 to 10 naar het juiste aantal regels waarvoor je een validatie wenst.

 

[Ad Becude]

@Rudi,

Mag ik je dan alvast feliciteren met je ?e verjaardag, je bent een echt 'warme bakkertje' met een warm hart.

 

[robinvdveeken]

Dan hebben we dat gemeen warm bakkertje!
Ik ben morgen ook jarig Bedankt voor je post!

 

[robert123321]

Oeps!

Vergeten dit topic op opgelost te zetten


Speciaal voor Robin:

ik maak een lijst en een naam aan in de code, zie hieronder

i = ThisWorkbook.Sheets("Medewerkers").Columns(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count - 1

Aanvragerslijst = "=Medewerkers!$A$2:$A$" & 2 + i

ActiveWorkbook.Names.Add Name:="Aanvragers", RefersTo:=Aanvragerslijst

    With Sheets("DNA versturen").Range("A5:A65536").Validation
         .Delete
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=Aanvragers"
         .IgnoreBlank = True
         .InCellDropdown = True
         .InputTitle = ""
         .ErrorTitle = "Foutieve invoer!"
         .InputMessage = ""
         .ErrorMessage = "Kies enkel een aanvragend arts uit de lijst!"
         .ShowInput = False
         .ShowError = True
    End With

Overigens, mijn felicitaties aan alle jarigen hier