transfer the name of combobox to module
 

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

transfer the name of combobox to module
 

On Jun 19, 12:44*pm, Shane Wierenga wrote:
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

transfer the name of combobox to module
 

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

transfer the name of combobox to module
 

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


--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility


"Shane Wierenga" wrote in message
...
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

transfer the name of combobox to module
 

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.