CODEWITHSUNDEEP
excelvba
Nik
Nik

Reputation: 21

How to pass a selection in a Form to a variable in VBA

I'm having some trouble with the following activity: I need to pass a selection of 5 different drivers (STIMA, STIMA_SEDE, LOTTO, STORICO, UNIFORME) done in a form to a variable in VBA and search the corresponding column in my worksheet.

So far I tried two ways, unfortunately without results:

First way:

    Private Sub UserForm_Initialize()

Dim rngDriver  As Range
Dim ws As Worksheet

Set ws = Worksheets("Data validation")

For Each rngDriver In ws.Range("Drivers")
    Me.cbDriver.AddItem rngDriver.Value
Next rngDriver

End Sub

Private Sub OKbtn_Click()
Dim destValues() As Variant, driverValues() As Variant, totalDriver As Double
    Dim totalValue As Double, offsetDriverCol As Integer, deltaPerc() As Double
    Dim i As Long, j As Long, k As Long, decimalPart As Double, driverRange As Range
    Dim driverCol As Integer, modCol As Integer, DestRange As Range
...
 driverCol = 2
    Do While Cells(23, driverCol).Value <> frmDistribuisci.cbDriver.Value
        driverCol = driverCol + 1
    Loop

Second way:

...
If frmDistribuisci.cbDriver.Value Is LOTTO Then
        driverCol = 2
        Do While Cells(23, driverCol).Value <> "Lotto"
            driverCol = driverCol + 1
        Loop
        End If
        'Identificazione colonna driver Stima
        If frmDistribuisci.cbDriver.Value Is STIMA Then
        driverCol = 2
        Do While Cells(23, driverCol).Value <> "Stima"
            driverCol = driverCol + 1
        Loop
        End If
        'Identificazione colonna driver Stima da sede
        If frmDistribuisci.cbDriver.Value Is STIMA_SEDE Then
        driverCol = 2
        Do While Cells(23, driverCol).Value <> "Stima da sede"
            driverCol = driverCol + 1
        Loop
        End If
        'Identificazione colonna driver Storico
        If frmDistribuisci.cbDriver.Value Is STORICO Then
        driverCol = 2
        Do While Cells(23, driverCol).Value <> "Storico"
            driverCol = driverCol + 1
        Loop
        End If

Debugging this code it seems to skip the If-steps as well as the first solution, so I'm stuck...

Has anyone an idea of how to do this? It would be highly appreciated, thanks in advance!

Upvotes: 0

Views: 74

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

This is no valid syntax for a value comparison:

If frmDistribuisci.cbDriver.Value Is LOTTO Then

It should be If … = … Then and LOTTO is considered as a type/class (because of the Is operator, which is used for something else entirely), not as the intended string. String literals must be delimited with double-quotes:

If frmDistribuisci.cbDriver.Value = "LOTTO" Then

That should work as intended.

Upvotes: 1

Related Questions