Userforms to VBA code

Question

is there a way to use UserForms to insert data not into a cell or spreadsheet, BUT into the VBA code directly.

The code is very simple, it goes on a SQLserver and Performs a query. I'd like to give the possibility to the user to modifiy the query (which is built into the VBA code) and change every day the date.

Set conn = New ADODB.Connection
conn.Open "Driver=Teradata;DBCName=dsnname;Databasename=dbname;Uid=Userid;Pwd=****;"

thisSql = "sel * from customer_db.customer where **Date = '2014-12-31'** "

Set rec1 = New ADODB.Recordset
rec1.Open thisSql, conn
Destination:=Sheet2.Range("A1")

I'd like to change just the Date with a UserForm or something similar, is it possible? thanks in advance

Answer 1

In order to have a userform return a value to your macro, the simplest option is to hide it once a user has selected a date, instead of unloading it. You can then retrieve all the values which can be found on the userform from your code.

For instance if you have a simple form with just a datepicker called dtpDate, and two command-buttons; "OK" and "Cancel", what you'd put in the userforms code would be something like this:

Private Sub cmdCancel_Click() 
    Unload Me 
End Sub 

Private Sub cmdOK_Click() 
    Me.Hide 
End Sub 

Private Sub UserForm_Initialize() 
    dtpDato = Date
End Sub 

And in your macro you'd put something like this to have the user "modify" the query string:

Set conn = New ADODB.Connection
conn.Open "Driver=Teradata;DBCName=dsnname;Databasename=dbname;Uid=Userid;Pwd=****;"

ufUserFormName.Show
thisSql = "sel * from customer_db.customer where **Date = '" _ 
          & Format(ufUserFormName.dtpDato, "yyyy-mm-dd") & "'** "
Unload ufUserFormName

Set rec1 = New ADODB.Recordset
rec1.Open thisSql, conn
Destination:=Sheet2.Range("A1")

The code will fail if the userform isn't loaded when the macro tries to read a value from it, but it shouldn't be too hard to check for this if you decide to go for this option. You can for instance find a function to check for that here.