[VBA / Excel 2010] Error handling met dropdown

[YellowOnline]

(Sorry, het titelveld was niet groot genoeg om het probleem goed te omschrijven)

Ik heb een dropdown waar x aantal items in staan. Als een item gekozen wordt, wordt er een actie uitgevoerd. Er zijn verschillende dropdowns met verschillende acties op verschillende pagina's, maar ze hebben allemaal hetzelfde probleem: default is de dropdown leeg en als je klikt op de box en een item kiest werkt alles perfect. Klik je echter meteen terug op het blanco veld, dan krijg je een error: "Unable to get the list property of the dropdown class".

De macro wordt dus wel degelijk uitgevoerd als een item gekozen wordt, maar hij verslikt zich als ik blanco kies. Nu is VBA niet mijn specialiteit: dit kadert in een groot Powershellscript van waaruit ik VBA injecteer in een Exceldocument. Ik moet dat ding wijsmaken dat wanneer de gekozen waarde blanco is hij niets moet doen. Ik zou niet meteen weten hoe, aangezien de gekozen waarde eigenlijk een zoekopdracht op een ander werkblad is. Ik probeerde een "On Error Resume Next", maar vanaf dan werkt geen enkele keuze in de dropdown nog

Het is dus geen vreselijk probleem: mijn macro werkt; maar ik heb niet graag dat er een handeling kan gebeuren die errors genereert. Als iemand mij daar vanaf kan helpen: bedankt voor je tips.

Voorbeeldcode van één van de dropdowns:

VBScript:

Sub AddUsers()
    'Declarations
    Dim Row As String
    Dim Application As String
    Dim arrUserData As Variant
    Dim objUserData As Variant
    Dim intLastRow As Integer
    Dim intEndCounter As Integer
    Dim intWriteCounter As Integer
    Dim intCounter As Integer
    Dim Range As String
            
    'Find application
    Row = CStr(ActiveSheet.Range("$S$1").Value)
    Application = ActiveSheet.DropDowns(1).List(Row)
    
    'Find last row
    If ActiveSheet.Name = "Packages Users" Then
        intLastRow = Worksheets(4).UsedRange.SpecialCells(11).Row
    Else
        If ActiveSheet.Name = "Free Users" Then
            intLastRow = Worksheets(7).UsedRange.SpecialCells(11).Row
        Else
            If ActiveSheet.Name = "Licenced Users" Then
                intLastRow = Worksheets(10).UsedRange.SpecialCells(11).Row
            Else
                intLastRow = Worksheets(13).UsedRange.SpecialCells(11).Row
            End If
        End If
    End If
    
    'Create array
    Range = "$K$1" & ":$R$" & intLastRow
    arrUserData = ActiveSheet.Range(Range).Value
    
    'Write data from array
    intWriteCounter = 1
    intEndCounter = intLastRow
    For intCounter = 2 To intEndCounter
        If arrUserData(intCounter, 1) = Application Then
            intWriteCounter = intWriteCounter + 1
            ActiveSheet.Cells(intWriteCounter, 4) = arrUserData(intCounter, 2)
            ActiveSheet.Cells(intWriteCounter, 5) = arrUserData(intCounter, 3)
            ActiveSheet.Cells(intWriteCounter, 6) = arrUserData(intCounter, 4)
            ActiveSheet.Cells(intWriteCounter, 7) = arrUserData(intCounter, 5)
            ActiveSheet.Cells(intWriteCounter, 8) = arrUserData(intCounter, 6)
            ActiveSheet.Cells(intWriteCounter, 9) = arrUserData(intCounter, 7)
            ActiveSheet.Cells(intWriteCounter, 10) = arrUserData(intCounter, 8)
        End If
    Next
End Sub

[Lustucru]

Zet eens een onderbrekingspunt op regel 14. Ik neem tenminste aan dat het daar mis gaat?
Het lijkt erop dat je een MSforms-combox plaatst op een excelblad en die bindt aan een cel S1.

Hoe je dan deze macro weer aanroept is me even niet duidelijk. Ik zou hem eerder verwachtne in een dropbox_change event? Hoe dan ook, het lijkt alsof je uit S1 een index leest, die omzet in een string, en die string weer voert aan list die eigenlijk een integer verwacht? Dat hele circus kun je overslaan door direct de waarde uit te lezen van combobox(1). Ben je ook van de fout af.

code:

Dim Row As String 
    Dim Application As String 
    Dim Range As String

Slechte gewoonte imo. Als ik row, range of application lees verwacht ik iets heel anders dan een of andere tekstvariabele. Het zijn gereserveerde woorden. Ik had eigenlijk niet eens verwacht dat dat zou compileren.

[YellowOnline]

Ik heb m'n code niet meer bij de hand (is op m'n werk en de VPN enz. opstarten... heb ik begrijpelijkerwijze weinig zin in op een vrijdagavond. Uit m'n hoofd denk ik wel nog te weten hoe het functioneert (dit schreef ik in juni 2012 of zo).

Lustucru schreef op vrijdag 18 januari 2013 @ 17:32:
Zet eens een onderbrekingspunt op regel 14. Ik neem tenminste aan dat het daar mis gaat?
Het lijkt erop dat je een MSforms-combox plaatst op een excelblad en die bindt aan een cel S1.

Sorry, had inderdaad even moeten vermelden waar de debugger me heenstuurt. Naar de volgende lijn om precies te zijn:

VBScript:

Application = ActiveSheet.DropDowns(1).List(Row) 

Hoe je dan deze macro weer aanroept is me even niet duidelijk. Ik zou hem eerder verwachten in een dropbox_change event? Hoe dan ook, het lijkt alsof je uit S1 een index leest, die omzet in een string, en die string weer voert aan list die eigenlijk een integer verwacht? Dat hele circus kun je overslaan door direct de waarde uit te lezen van combobox(1). Ben je ook van de fout af.

S1 is een hidden cell waar het indexnummer van de dropdown terecht komt. Dat indexnummer wordt aangepast (letter + (index + integer)) om te verwijzen naar een waarde op een ander werkblad waar een hele rij met informatie staat.

Concreet (maar vereenvoudigd): in deze dropdown op sheet2 staat een lijst met applicatie die uit sheet1 gehaald wordt. Als men klikt op de applicatie dan verschijnen de gebruikers van die applicatie, hun computer, de installatiedatum, hun OU (uit AD), hun emailadres, ...

code:

Dim Row As String 
    Dim Application As String 
    Dim Range As String

Slechte gewoonte imo. Als ik row, range of application lees verwacht ik iets heel anders dan een of andere tekstvariabele. Het zijn gereserveerde woorden. Ik had eigenlijk niet eens verwacht dat dat zou compileren.

De combobox heb ik via de GUI gemaakt en dan de VBA bekeken en daar verder mee gewerkt. Dat is hoe ik kom aan die generische namen.

---

Misschien is het begrijpelijker als ik het outputfile van mijn script (zijnde een Excel file met macro's) geanonimiseerd beschikbaar stel zodat je de code en werking kan zien.

Klik hier.

De waarden op het eerste blad kloppen overigens niet omdat die vanuit Powershell ingevuld worden (niet vanuit Excel) en ik tienduizenden entry's verwijderd heb. Maar dat doet er niet toe.

Klik in een 'Users' worksheet op een dropdown en kies blanco en je ziet wat er gebeurt. In het "Free Users" tabblad gaat dat niet meer, want bij wijze van test heb ik iets aangeklikt en dan is er geen blanco meer.

[Lustucru]

Grappig: ik hier nog een Excel 2003 liggen en die is zo slim die macro _niet_ uit te voeren als je geen keuze maakt. Anyway, als je geen keuze maakt, is cel S1 leeg, is row dus een lege string, en krijg je iets als:
combobox.list(""). En een foutmelding.

Anyway: row zou numeriek moeten zijn, maar VBA is vergevingsgezind en slikt je string ook. Fout afvragen als volgt:

Visual Basic:

If len(row) then   
       'doe je ding
else
       msgbox "Joh, kies wat"
end if

[YellowOnline]

Ik zal me daar maandag mee bezig houden - alvast bedankt!

Misschien moet ik het hele systeem eens opnieuw overdenken als ik tijd teveel heb, want de mooiste oplossing zou zijn wat jij krijgt in Excel 2003: nl. dat er niets gebeurt.