Index functie & macro doet niet wat ik wil.

[grietjeb]

Goedemiddag,

Voor mijn werk probeer ik een excel te maken waarin bepaalde codes opgezocht kunnen worden. Ik krijg het echter niet werkend. Om een goed beeld van mijn Excel te krijgen:

Ik heb 2 werkbladen: "Invoer" en "data"

Op Invoer staan 2 keuzelijsten, waarbij de 2de gevuld word aan de hand van wat er in de eerste ingevuld word.
De waarde van de eerste keuzelijst komen uit "Groepslijst" en is simpel ongeveer 20 cellen onder elkaar ergens op de "data" worksheet.

Onder het eerste zoekveld heb ik de naam van de gekozen groep staan met de formule
=INDEX(Groeplijst;Invoer!F7) (note, F7 zet het keuzeveld het nummer van de keuze neer)
De cel waar dit instaat heb ik "selectie" genoemd zodat ik er naar kan refereren

De tweede keuzelijst word gevuld door kleine matrixjes verspreid over de worksheet "data" worksheet. Elke kleine matrixje draagt zijn groepsnaam. Matrixjes zijn altijd 2 kolommen groot (een met omschrijving, een met code) en varierend van 2 tot 15 rijen lang.
Het vullen van de keuzelijst werkt naar behoren met een macro.

Nu wil ik, dat als er in de onderste ook een keuze is gemaakt, Excel onder de keuzemenus laat zien wat er is gekozen en welke code daarbij hoort

Daar gaat het mis.

Als ik handmatig invul in een van de cellen eronder:
=INDEX(Koeling;F16;1) (note, F16 zet de keuzelijst zijn output neer, Koeling is 1 van de kleine matrixjes)
Dan doet ie het. Hij returned de waarde van het kruispunt F16 met 1 in matrixje Koeling.

Maar ja, dan heb ik alleen dat van Matrixje Koeling en die moet juist variabel zijn, afhankelijk van de groep die in het eerste keuzeveld word gekozen.

Dus ik dacht, ik heb de cel die de naam van de groep draag en variabel is "selectie" genoemd, dus dacht ik

=INDEX(selectie;F16;1)

Maar dan krijg ik #VERW!

Dus dacht ik, misschien moet ik het met een macro oplossen.
Maar ook daar. Statisch werkt het wel:

Sub Groepkeuzelijst_BijWijzigen()

Dim IRange As Range, Mycell As Range

ActiveSheet.Shapes("PechKeuzelijst").Select
 With Selection
  .ListFillRange = Range("D14").Text
  .LinkedCell = "F16"
  .Display3DShading = False
 End With
 
Set IRange = Sheets("data").Range("M2:M10")
Set Mycell = Sheets("Invoer").Range("C24")

Mycell.Offset(0, 2).Value = Application.WorksheetFunction.Index _
(IRange, 3, 1)


End Sub

Maar hoe word het variabel?

 

[zapatr]

Zonder een voorbeeldbestand valt er naar mijn mening geen zinnig antwoord te geven.

 

[grietjeb]

Toch fijn als je iets post en je dan ineens uitgelogd bent

Ik heb het opgelost!

Ik moest indirect gebruiken in mijn verwijzing ;-)

 

[zapatr]

grietjeb,
ik neem aan dat je opmerking voor mij bedoeld is, en gelet op de negatieve emoticon erbij, wil je kennelijk mij een sneer geven. Daar is op zich niets op tegen als daar aanleiding voor zou zijn, maar die aanleiding zie ik hier niet, want:
ten eerste: je kunt niet zien of ik op dit forum wel of niet ben ingelogd;
en ten tweede: als je dat wel zou kunnen, mag je uit het feit dat iemand al dan niet is ingelogd, geen conclusies trekken m.b.t. de beantwoording van je vraag. De vragen waarop ik reageer, volg ik doorgaans goed op. Als je daaraan mocht twijfelen, bekijk alle voorgaande vragen waar ik ooit op gereageerd heb.
En verder:
1. je vraag zal niet alleen voor mij onduidelijk geweest zijn, anders zouden er wel meer mensen gereageerd hebben.
2. Fijn voor je dat je je probleem zelf hebt kunnen oplossen. De beheerders van dit forum verwachten echter dat, als een vraag is opgelost, de vragensteller die ook als zodanig markeert.

 

[grietjeb]

:shocked:

Ik doelde op mezelf ;-) (ik vind trouwens ook geen negatieve emoticon.. maar goed)

Ik had zelf helemaal het hele verhaal getypt, drukte op 'plaatsen' en toen was ik uitgelogd, was mijn hele verhaal weg...... toen was ik te moe om alles nog een keer te typen....


Mijn vraag was inderdaad niet zo duidelijk, sorry hoor. Als Dyslect soms nogal moeilijk om dat wat je vraag is ook zo op papier te krijgen......

 

[grietjeb]

Ik zal nog een keer proberen te beschrijven wat het probleem was, misschien heeft een volgende gebruiker er profijt van:

- Ik heb een cel waarin de naam van een tabel staat. Deze cel draagt de naam 'selectie'.

- Via een Index functie wil ik verwijzen naar de tabel, welke zijn naam geschreven staat in die cel selectie

Ik typte dus:

=INDEX(selectie;5;1)

Dat deed ie dus niet. Je krijgt #VERW

De oplossing:

=INDEX(INDIRECT(selectie);5;1)

Omdat de cel selectie natuurlijk indirect een verwijzing is naar de tabel wie zn naam in die cel staat ;-)

Zo simpel, eigenlijk, maar het heeft me gister lang bezig gehouden

 

[zapatr]

Geen probleem, na je uitleg

En je in eerste instantie gebruikte fuctie: =INDEX(selectie;5;1) is WEL juist, MAAR:
dan moet de TABEL waaruit je via de indexfunctie gegevens wil halen wel die naam hebben. Die naam geven aan de tabel doe je als volgt:
Selecteer de tabel (bv: selecteer E1:E20, of E1:F20, dit zijn willekeurige voorbeelden).
- Plaats - terwijl de tabel geselecteerd staat - de cursor in het naamvak (dat is het vak boven de letter A van de A-kolom waar normaliter de eerste cel van de selectie staat aangeduid).
- Wis wat daar staat, en schrijf ervoor in de plaats: Selectie , en druk op Enter.
Als je nu =index(selectie;5;1) gebruikt, zal de waarde in de vijfde rij van de eerste kolom van je tabel worden weergegeven.

 

[grietjeb]

Klopt, nu had ik alleen het issue dat dus de tabel waarnaar verwezen moest worden dus variabel was ;-)
De cel genaamd 'selectie' is dus een cel met variabele waarde. en deze waarde zijn gelijk aan de namen van de verschillende tabellen waarin gezocht moet worden.

Het bestand (nu werkend):
http://blog.smartiechick.nl/wp-content/uploads/2011/11/Pechcodelijst_onbeveiligd.xlsm


Nu heb ik nog een probleem, wat nu zo op komt duiken.
Dit bestand moet verspreid worden maar de gebruikers mogen helemaal niets aanpassen. Ze mogen alleen iets selecteren in de 2 keuzevelden. Meer niet.

Het keuzeveld moet echter schrijfrechten hebben op een paar cellen.

Dus ik had blad 'data' compleet vergrendeld en verborgen;
blad invoer had ik zoals in dit excelletje en dan (met wachtwoord) beveiligen, maaaaar

Dan crasht mijn excel als ik hem opnieuw (als test) probeer te openen!?

 

[grietjeb]

Pff dat meen je niet, hij doet het nu helemaal niet meer?

Als ik hem open zegt ie dat de macro's uit staan, ik druk op 'macro toestaan' en dan crasht Excel. Dan open ik hem opnieuw, zijn mijn keuzelijsten ineens plaatjes geworden? Dus ik sluit af, zonder op te slaan.

Open opnieuw Excel, is ineens mijn onderste keuzelijst leeg en weigerd te vullen terwijl er bij input zegmaar wel degelijke een goede referentie staat..


PFFFFFFFFFFFF ***** excel.

 

[zapatr]

Ik kan je bestand niet controleren, want ik werk met Excel 2002.
Als je je bestand opslaat als Excel 97-2003-bestand en het hier plaatst, dan kan ik er naar kijken
(maar dat wordt dan wel vanavond, want ik moet nu weg).

 

[grietjeb]

dit is een iets ander bestand, maar volgens mij werkt deze wel (deze heb ik thuis op mn macbook gemaakt met office 2010 voor mac)

http://blog.smartiechick.nl/wp-content/uploads/2011/11/Pechcodes_onbeveiligd.xls


En beveiligd
http://blog.smartiechick.nl/wp-content/uploads/2011/11/Pechcodes_beveiligd.xls


(dat uploaden van het forum hier werkt bij mij niet? )

 

[oeldere]

Als je vraag nog niet is opgelost, haal dan het vinkje bij de vraag weg.

Dit kun je linksboven bij je eerste vraag doen.

 

[grietjeb]

Als je vraag nog niet is opgelost, haal dan het vinkje bij de vraag weg.

Dit kun je linksboven bij je eerste vraag doen.

Gedaan, dat gaat dus niet op een telefoon

Maar goed, eigenlijk is de vraag opgelost, want het werkt. Alleen waarom de ene XLSM niet werkt en de XLS wel, dat is mij een raadsel!

 

[zapatr]

grietjeb,
als ik het goed begrepen heb, heeft (of had) je probleem te maken met het feit dat je een blad beveiligde en je daarna geen macro's kon uitvoeren. Dat laatste is normaal, beveiliging heeft immers tot doel dat er in het blad geen wijzigingen kunnen worden aangebracht. Het verbaast me dat je nu - kennelijkzonder aanwijsbare oorzaak - de macro's wél kunt gebruiken, ook als het blad beveiligd is.

 

[grietjeb]

grietjeb,
als ik het goed begrepen heb, heeft (of had) je probleem te maken met het feit dat je een blad beveiligde en je daarna geen macro's kon uitvoeren. Dat laatste is normaal, beveiliging heeft immers tot doel dat er in het blad geen wijzigingen kunnen worden aangebracht. Het verbaast me dat je nu - kennelijkzonder aanwijsbare oorzaak - de macro's wél kunt gebruiken zonder dat het blad beveiligd is.

Ik had alle cellen waar de macro toegang tot moest hebben niet vergrendeld.
Maar het stomme is:

Als ik het beveilig, werkt het. onbeveiligd werkt het.

Maar op het moment dat ik de XLSM opsla en afsluit, en later opnieuw open, crasht Excel (windows uhh 7; office 2010). Of ik dat nu met of zonder beveiliging doe, resultaat is hetzelfde.

Toen heb ik thuis (macbook, office 2010 for mac) hetzelfde gemaakt, maar dan als XLS opgeslagen, daar lijkt/leek het te werken met én zonder beveiliging, maar nu mail ik hem naar een collega (als test) en nu flipt die excel ook :S

 

[zapatr]

Welke foutmelding krijg je bij de crash?
Waarschijnlijk een foutmelding in een vba-regel.
Hoe luidt de foutmelding en wát staat er in de vba-regel waar de foutmelding naar verwijst?

 

[grietjeb]

De eerste keer dat ik hem na opslaan open:

Hij geeft bovenaan de gele regel dat de macro's zijn uitgeschakeld. Ik druk op toestaan, en dan is mijn tweede keuzelijst ineens leeg. Terwijl er bij eigenschappen etc alles wel goed staat.

Sluit ik hem af zónder op te slaan, open hem opnieuw, en dan crasht excel:

" Microsoft Excel werkt niet meer. Er kan geprobeerd worden om uw informatie op te halen. -> het programma sluiten "

Ik druk op sluiten; open je excel daarna heeft hij het bestand 'gerecovered' maar zijn de keuze lijsten ineens plaatjes. Dus ik sluit af zonder op te slaan.

Hierna blijft excel crashen..

Als ik de eerste keer (als de tweede lijst leeg is) het keuzevak delete, nieuwe toevoeg, alles weer goed zet, en opnieuw opsla (onder een andere naam). Blijf je precies hetzelfde houden .



Bedenk me wel; degene waarmee het fout gaat heeft activeX keuzelijsten omdat ik daarvan het lettertype kan aanpassen. De momenteel werkende heeft 'gewone' besturingselementen.


Nu die met activeX opgeslagen als XLS (Dus geen 2010 maar 97-2003) nu werkt ie gewoon

Ik zweer XLSM gewoon af

 

[zapatr]

Als listboxen zou ik toch die van 'werkset besturingselementen' aanbevelen, omdat die flexibeler in het gebruik zijn (vind ik) in vergelijking met die van de 'werkbalk formulieren'.
De Excel 97-2003-bestanden die je hier plaatste, kon ik wel openen, maar ik kon er niets mee aanvangen omdat de listboxen veranderd waren in afbeeldingen en er dus niets aanklikbaar was. Ik heb dan aan de hand van je gegevens een nieuw bestand gemaakt en heb toen gemerkt dat in de omschrijvingen die je als naam van een bereik gebruikt, nogal wat spaties en tekens zoals - en / voorkomen. Die zijn bij namen niet toegestaan en dat zal ook de oorzaak zijn dat er in listbox2 geen gegevens verschijnen. In het bestand dat ik hier bijvoeg, zijn die fouten gecorrigeerd, beveiligingen zijn nog niet aangebracht. Bekijk het eens, wellicht heb je er wat aan.

 

[grietjeb]

Als listboxen zou ik toch die van 'werkset besturingselementen' aanbevelen, omdat die flexibeler in het gebruik zijn (vind ik) in vergelijking met die van de 'werkbalk formulieren'.
De Excel 97-2003-bestanden die je hier plaatste, kon ik wel openen, maar ik kon er niets mee aanvangen omdat de listboxen veranderd waren in afbeeldingen en er dus niets aanklikbaar was. Ik heb dan aan de hand van je gegevens een nieuw bestand gemaakt en heb toen gemerkt dat in de omschrijvingen die je als naam van een bereik gebruikt, nogal wat spaties en tekens zoals - en / voorkomen. Die zijn bij namen niet toegestaan en dat zal ook de oorzaak zijn dat er in listbox2 geen gegevens verschijnen. In het bestand dat ik hier bijvoeg, zijn die fouten gecorrigeerd, beveiligingen zijn nog niet aangebracht. Bekijk het eens, wellicht heb je er wat aan.

Thanx!! Ik ga even kijken.

Dat met die plaatjes heb ik dus ook steeds. Na crashen maakt hij een herstel bestand aan met plaatjes :S

Het stomme is dat het met die spaties etc dus wel gewoon werkt in XLS, maar niet in XLSM. Maar ik zal het inderdaad weghalen. Ik had de meeste spaties al vervangen door '_' en inderdaad geen - en / gebruikt.

Ik had die activeXdingen gebruikt in de tweede omdat je daarvan het lettertype in de box kan aanpassen. De letters bij die gewone besturingselementen zijn zo ontzettend klein.
Maar nu had ik het werkend met activeX dingen en nu elke keer als ik hem opnieuw open veranderd ie de grote van die keuzelijsten Dus toch maar weer alles vervangen voor 'gewone' besturingselementen.

Zucht, kan microsoft niet iets maken wat gewoon werkt

 

[grietjeb]

Ik zie trouwens nu wat jij bedoeld met spaties en dingen in de namen, maar dat had ik niet zo.
De namen die de lijst vullen hadden inderdaad spaties, maar dit was puur voor de weergave.

Achter de schermen werkte hij met die 2de lijst (de loopuplijst) en die had verkortte namen (zonder spaties en andere leestekens).

Ik zie dat jij het direct met elkaar vult, ik had daar een stap tussen. Maar dan moet je idd wel met _ werken.