[Excel/VBA] Userform met Worksheet_Change en ListFillRange

[Clock]

Goedenmiddag,

Ik ben al een tijdje aan de slag met een planningsysteem voor een supermarkt, en gebruik daarvoor Excel en (via VBA) een Access database. Nu loop ik echter tegen 2 problemen aan:

• Ik heb een lijst met namen (aanpasbaar door user)(variabele lengte) die als opties worden 'gebruikt' in 26 dropdownboxen (niet middels data validatie maar een 'echt' userform). Telkens als de lijst wordt aangepast door de gebruiker, schrijf ik de namen naar de database en wil ik de nieuwe lijst in de dropdownboxen laden. Hiervoor gebruik ik in VBA de volgende code:
  
  code:
  

  'Defineer nieuwe range
  strNewRange = "$L$3:$M$" & (i - 1)
      
  Set VullerRange = Sheets("Dag Instellingen").Range(strNewRange)
  ActiveWorkbook.Names.Add Name:="VullersRange", RefersTo:=VullerRange
      
  With Application.ActiveSheet
     .Select_1_1.ListFillRange = "VullersRange"
     .Select_1_2.ListFillRange = "VullersRange"
     etc
  End With

  
  Het probleem hiermee dat deze constructie nogal traag is, omdat er op deze manier 26*+-14 cellen moeten worden uitgelezen in en de dropdownboxen gezet moet worden. Dit duurt ongeveer 2 seconden, en is erg ongewenst. Ik hoopte de boel sneller te maken door een Named Range aan te maken, en alle dropdownboxes die range te voeren, maar ipv het één keer inlezen leest VBA/Excel nog steeds 26 keer de range in. Is er een manier om dit sneller te krijgen?
• Als de user een wijziging maakt in een van de checkbox, wil ik graag bepaalde code uitvoeren. Het allermooist zou zijn dat dit getriggerd wordt door worksheet_change (en dan filteren op de target cell). Ik hoopte dit te bereiken door een linked cell aan te geven per dropdownbox, waarin hij dus het Id van de geselecteerde persoon schrijft. Dat gebeurt ook wel (bij het veranderen van de geselecteerde optie schrijft hij keurig het Id van die persoon naar een cel), maar de worksheet_change wordt niet getriggerd. Vervolgens probeerde ik code te koppelen via Sub Select_1_1_Change(), echter wordt deze ook getriggered als de ListFillRange gebruikt wordt (en bij andere handeling die ik met VBA uitvoer op de dropdownbox). Het filteren hiervan is erg lastig, omdat ik het onderscheid niet kan maken tussen een verandering van de selectie door VBA of door een user (Application.EnableEvents = False werkt niet voor userforms, helaas). Is er een manier om worksheet_change te triggeren bij het wijzigen van een selectie in de dropdownbox?

Zo, een heel verhaal, maar ik hoop dat het duidelijk is. Niet ongebelangrijk: ik gebruik Office 97 (wegens config op locatie).

Alvast bedankt!

[Clock]

Overigens is op deze manier de triggering van de Dropdownbox_Change tijdelijk af te vangen (net gevonden):
http://www.cpearson.com/excel/SuppressChangeInForms.htm

Ik zal vanmiddag kijken hoever ik hiermee kom, maar toch is het voor mijn 'programma' mooier als gewoon de worksheet_change wordt getriggerd bij het wijzigen van de dropboxbox selectie.

Wat ik niet snap: ik heb als linked cell bijv B1 gekozen. Dus bij het wijzigen van de dropdownbox selectie wordt het persoonId in de cel B1 geschreven. Dit triggered Worksheet_Change niet. Vervolgens heb ik als formule in cel C1: "=B1". Nu veranderd cel C1 ook als er een er in de dropdownbox een verandering van de selectie plaatsvind. Maar gek genoeg triggerd dit nog steeds geen Worksheet_Change! Erg raar vind ik persoonlijk.

[Verwijderd]

moet het aanpassen realtime? maak anders een bevestigingsknop die de gebruiker moet aanklikken nadat er wijzigingen zijn aangebracht in de namenlijst.
het change event wordt niet getriggerd door een programmatorische wijziging, noch een herberekening.

[Clock]

Verwijderd schreef op maandag 07 mei 2007 @ 15:41:
moet het aanpassen realtime? maak anders een bevestigingsknop die de gebruiker moet aanklikken nadat er wijzigingen zijn aangebracht in de namenlijst.
het change event wordt niet getriggerd door een programmatorische wijziging, noch een herberekening.

Nee, dit moet helaas realtime. Er kan namelijk ook een naam verwijderd worden, en die moet dan ook direct uit de database (en de uur-gegevens moeten allemaal geupdate worden). Daarnaast zie ik het dan al wel wer gebeuren dat die knop een keer vergeten wordt, met leuke (maar redelijk vergaande) gevolgen van dien.

Hmm, lastig qua het change event. Een mogelijkheid is om dan het calculate event te gebruiken, maar die geeft toch geen target als parameter?

[Clock]

Ondertussen heb ik het gedoe met de change_event ed opgelost. (Zie link 3 post hoger).
Niet de mooiste oplossing, maar hij werkt wel

Weet iemand wellicht nog iets om het aanpassen van de dropdownbox sneller te kunnen krijgen? Zou echt niet weten wat ik nog zou kunnen proberen. Alle tips zijn meer dan welkom!

[Lustucru]

Je kunt je afvragen waarom het zo traag is.

Op een stokoude PIII heeft excel2003 minder dan 0,02 seconde nodig om 26 listboxjes van een nieuwe range te voorzien en vooralsnog verwacht ik niet dat 97 zo dramatisch slechter zou moeten performen. En trouwens, waarom neem je ook nog eens kolommen B:M op?

[ Voor 15% gewijzigd door Lustucru op 08-05-2007 14:01 ]

[Clock]

Lustucru schreef op dinsdag 08 mei 2007 @ 14:00:
Je kunt je afvragen waarom het zo traag is.

Op een stokoude PIII heeft excel2003 minder dan 0,02 seconde nodig om 26 listboxjes van een nieuwe range te voorzien en vooralsnog verwacht ik niet dat 97 zo dramatisch slechter zou moeten performen. En trouwens, waarom neem je ook nog eens kolommen B:M op?

Ik neem nergens een kolom B, die diende alleen ter voorbeeld in eerdere post. Ik gebruik als range L3:Mi, waarin in de L kolom de namen van de personen staan, en in de M kolom de betreffende persoon Id's.

Verder denk ik dat het grotendeels aan excel ligt, omdat dezelfde actie (zelfde document) in excel 2007 maar een fractie van een seconde nodig heeft.

[Lustucru]

Excel97 gaat wat anders met ActiveX elementen om, maar ik had niet verwacht dat dat zo'n performance dip opleverde. Ik zou eerder toch eens kijken of er niet andere acties de boel zo vertragen. Eventueel kun je nog proberen of een loopje met .additem() of .list sneller werkt?

[CoRrRan]

Persoonlijk zou ik een dynamische name-range gebruiken als input van je forms-combobox (niet een VBA userform combobox) en er een macro aan hangen.

Zie http://www.cpearson.com/excel/named.htm

Grootste probleem is, is dat ik niet helemaal meer weet welke functies Excel97 allemaal mist t.o.v. 2003, dus mogelijk dat er iets mijn methode in de weg zou staan. Ik heb het echter geprobeerd in Excel2003 en zonder probleem worden alle 26 comboboxen direct ge-update als de gebruiker een entry toevoegt in de dynamic name range.