Run macro from button

[Desu Nota from Columbus]

I have a macro that works when I use it by selecting from the menu (alt+F8) or by using the shortcut (ctrl+shift+O).

BUT, when I created a command button and had it run the macro, I keep getting the following error. Can someone explain why this is?


ERROR:

User defined type not defined!

Sub ATest()
Dim i As Integer
Dim lRow As Long
Dim iRow As Long
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim startDate As Date
Dim endDate As Date

Server_Name = "GGVORNE\SQLEXPRESS" ' Enter your server name here
Database_Name = "VorneDB" ' Enter your database name here
User_ID = "vorne" ' enter your user ID here
Password = "VData!!!" ' Enter your password here
startDate = Range("A1").Value
endDate = Range("A2").Value
SQLStr = ""
SQLStr = SQLStr & " SELECT * FROM [VorneDB].[dbo].[interval_stream] "
SQLStr = SQLStr & " WHERE [start_time] >= '" & Format(startDate, "yyyy-mm-dd hh:nn:ss.000") & "'"
SQLStr = SQLStr & " AND [end_time] <= '" & Format(endDate, "yyyy-mm-dd hh:nn:ss.000") & "'"
MsgBox SQLStr
Debug.Print SQLStr
Application.ScreenUpdating = False

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic
' Dump to spreadsheet
With Worksheets("sheet1").Range("a1:z50000") ' Enter your sheet name and range here
    .ClearContents
    .CopyFromRecordset rs
End With
'            Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing

With Sheets("Sheet1")
    Columns("A:BB").Select
    Selection.NumberFormat = "General"
    Range("A:A,J:J,AS:AS").Select
    Selection.NumberFormat = "m/d/yyyy h:mm"
End With

This is the command button code:

Private Sub CommandButton1_Click()
    ThisWorkbook.ATest
End Sub

 

[Hashiru]

Can you try this.


Private Sub CommandButton1_Click() call ATest End Sub</pre>

 

[Hashiru]

Can you try this.

Private Sub CommandButton1_Click()
Call ATest
End Sub

 

[Desu Nota from Columbus]

Didn't work, highlights the line "Call ATest"

Compile Error:

Sub or Function not defined!

 

[Hashiru]

if this one line code is highlighted in yellow it means it cannot access the sub procedure where the code you what the command to act on. Make sure it the same spelling.
Otherwise why not write the code on the click event handler.

 

[Desu Nota from Columbus]

.
Otherwise why not write the code on the click event handler.

I'm not familiar with this. Can you clarify?

 

[Desu Nota from Columbus]

I used:

Private Sub commandbutton1_click()
   Call ThisWorkBook.ATest
End Sub

To the same result as above. Highlights the Dim CN as ADOB.Connection.

But I run the macro without the CommandButton and it works.

Confused

 

[Hashiru]

Where does your code lies? Did you right click ThisWorkbook and then enter a code or did you right-click the command and then add the code. One problem with such a thing if the code lies in the wrong place nothing will happen.
Check on the above.
Hope this help

 

[Desu Nota from Columbus]

Where does your code lies? Did you right click ThisWorkbook and then enter a code or did you right-click the command and then add the code. One problem with such a thing if the code lies in the wrong place nothing will happen.
Check on the above.
Hope this help

Not sure I fully understand you. I originally created my ATest macro in personal.xls. I copy/pasted it into THISWORKBOOK. When I created the CommandButton1, I went to view code and then typed in "Call ThisWorkBook.ATest".

When I click on the command button, it calls up the error message on the macro (located in ThisWorkBook) and highlights Dim Cn as ADODB.Connection and gives the error code.

The commandbutton is connecting to the correct macro located where I believed it to be, if this is what you were asking me to check.

 

[RoryA]

That would imply that you have not set a reference in your project in the VBEditor to the Microsoft ActiveX Data Objects Library. I don't see how you could have been running the same macro with your attempts fro the Macros dialog or shortcut, since you should get the same error.