transfer the name of combobox to module

[Shane Wierenga]

I am trying to transfer the name of a combobox to my sub procedure.
Here is what I have so far. Obviously this keeps passing the name of
the combobox in quotations. If someone could let me know what I'm
doing wrong it would make my life alot easier.

Private Sub Line1Type1_DropButtonClick()
Dim strlinenum1 As Variant


Dim strlinenum2 As String



strlinenum1 = line1type1.Name
strlinenum2 = line1type2

L1T1 strlinenum1


End Sub


Sub L1T1(strlinenum1 As Variant)

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strsql As String
Dim strcb2 As String


strsql = "SELECT DISTINCT 'Electrical', 'none' from materials"

cnn.Open strcnn
rst.Open strsql, cnn, adOpenStatic
rst.MoveFirst

ActiveSheet.strlinenum1.List = Application.Transpose(rst.GetRows)

ActiveSheet.strlinenum1.Clear
ActiveSheet.line1type2.Clear
ActiveSheet.Cells(87, 17).Value = 0

rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing


End Sub

 

[SteveM]

I am trying to transfer the name of a combobox to my sub procedure.
Here is what I have so far. Obviously this keeps passing the name of
the combobox in quotations.  If someone could let me know what I'm
doing wrong it would make my life alot easier.

Private Sub Line1Type1_DropButtonClick()
    Dim strlinenum1 As Variant

    Dim strlinenum2 As String

    strlinenum1 = line1type1.Name
    strlinenum2 = line1type2

   L1T1 strlinenum1

End Sub

Sub L1T1(strlinenum1 As Variant)

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strsql As String
    Dim strcb2 As String

    strsql = "SELECT DISTINCT 'Electrical', 'none' from materials"

    cnn.Open strcnn
    rst.Open strsql, cnn, adOpenStatic
    rst.MoveFirst

   ActiveSheet.strlinenum1.List = Application.Transpose(rst.GetRows)

    ActiveSheet.strlinenum1.Clear
    ActiveSheet.line1type2.Clear
    ActiveSheet.Cells(87, 17).Value = 0

    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

End Sub

I think methods and properties require that variables be explicitly
typed. Variants won't work. Try declaring strlinenum1 as String as
see what happens.

SteveM

 

[Shane Wierenga]

Steve,
I tried that and end up getting the same thing "line1type1". Thanks

 

[Tim Zych]

Pass the object itself (combobox), not the object.Name (string)

Private Sub Line1Type1_DropButtonClick()
'...
L1T1 Line1Type1
'...
End Sub


Sub L1T1(strlinenum1 As MSForms.Combobox) ' Or As Variant
'...
End Sub

 

[Shane Wierenga]

I have tried that also. When I do that it appears as though it is
just passing the value of the combobox and not the combobox itself.