I have a model I am programming.  It dynamically opens any database the user chooses and after they click on what fields they want, they will get a chart in VB as MSChart or if they choose they will get one In excel.  

I am using Dynamic SQL code to retrieve the data for the
charts.  I have this problem solved, the problem is, i have to write the same exact code in about 7 spots, for every command button, is there a way I can write the code once using a public function or declaring public?  This is the code:

Dim SQLSTR As String, CHAR
CHAR = ""
If sellist.ListCount > 0 Then
For i = 0 To sellist.ListCount - 1
selstr = "[" & sellist.List(i) & "]"
thestr = thestr & "," & selstr

End If

If Mid(thestr, 1, 1) = "," Then
thestr = Mid(thestr, 2, Len(thestr))
End If

If sqlbox.Text <> "" Then
SQLSTR = "SELECT " & thestr & " FROM " & "[" & tablelist.Text & "]" & " WHERE " & sqlbox.Text
End If

If sqlbox.Text = "" Then
SQLSTR = "SELECT " & thestr & " FROM " & "[" & tablelist.Text & "]"
End If

If Len(File1.path) = 3 Then
path = Mid(File1.path, 1, 3)
path = File1.path & "\"
End If

for example, the user presses this button to show a database grid. I write the code in that button.  The user presses another button to generate a chart, I write the code in that button also, How do I write it one time?

Who is Participating?
PoddyConnect With a Mentor Commented:
just put

public sub TheCode()


end Sub

around the code and put

TheCode into the command buttons
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.