use string to call code

[Jaye7]

I am trying to use a textbox value to call vba code and can not get it to work.
I keep getting an error on the call str1 line.

Sub formscript()
Dim str1 As String
str1 = [Forms]![fscripts2]![t3]
'MsgBox str1

If str1 = "" Then
str1 = "err1"
Exit Sub
Else
Call str1 ' this is where it call the script based on value in textbox
End If
End Sub

 

[MarkK]

It's a more common pattern that you pass a value to a subroutine, and use that value to determine how to proceed. Consider that you have three distinctly named operations, which is your problem. How do you call a different routine based on the value of data?

Sub Op1
   Debug.Print "First"
End Sub

Sub Op2
   Debug.Print "Second"
End Sub

Sub Op3
   Debug.Print "Third"
End Sub

The best solution looks like this . . .

Sub Op(Number as Integer)
   Select Case Number
      Case 1
         Debug.Print "First"
      Case 2
         Debug.Print "Second"
      Case 3
         Debug.Print "Third"
   End Select
End Sub

See how in this case we pass a value to a generic routine, and that routine knows how to use our value to take the correct action.

Hope that helps,

 

[Jaye7]

Hi,

even with just one option it does not call the script, I thought that it was because my str was text so it was acting like

call "mycode" ' my str1 value

instead of like

 call mycode

with just one option it is not working

Sub formscript() Dim str1 As String 
str1 = [Forms]![fscripts2]![t3] 
'MsgBox str1  
Call str1 ' this is where it calls the script based on value in textbox  
End Sub

 

[vbaInet]

Show us the code you have in the textbox. Although I don't see the point of it.

 

[Galaxiom]

I have not followed the thread closely but maybe you need Eval().

 

[vbaInet]

I thought of that but I think the poster has written a block of code in a field hoping it will run by just calling it.

 

[Jaye7]

I have the names of the scripts in a field of a database, so in my form it shows the code name for the specific script so when that name of the script is in the textbox it will be the string.

ie. a macro named sub macro1() the name macro1 is the value in the textbox, so it would then call that macro.

 

[vbaInet]

In the first place, why do you need to do this?

 

[Jaye7]

I have a large list of scripts to do various tasks and I want to be able to go directly to that script in my vba editor, I edit the scripts frequently and therefore I do not want to have to copy the edited scripts constantly back into my table if I can just call them and go directly to them, the scripts have stop commands at the start if specific criteria are not met so it will break when I try to call it so I can then copy or edit it further.

 

[vbaInet]

ie. a macro named sub macro1()

Ok, is it a macro or VBA code? If it's a macro the word "Sub" won't be there.

 

[Jaye7]

it's vba code.

 

[vbaInet]

Ok, Eval() will work, but only for Functions.

You also have search functions in the VBA editor, which allows you to enter part of or the full name of the function, and from there you can find the function. It even does pattern matching searches.

 

[Jaye7]

sorry, I haven't used eval, how would that work with my string.

Sub formscript()
Dim str1 As String  
str1 = [Forms]![fscripts2]![t3] 
 'MsgBox str1
call str1 ' this is where it calls the script based on value in textbox   
End Sub

 

[vbaInet]

Have a look at the help files:

http://msdn.microsoft.com/en-us/library/office/aa172212(v=office.11).aspx

 

[vbaInet]

And I still think you're re-inventing the wheel. At the end of the day, what you want to do is find a function, the search function in the VBA editor is sufficient.

 

[Jaye7]

if I can click on a field and it automatically goes to the script it is a lot faster than having to hit ctrl+ f11 to get to the editor and then having to click ctrl+F for the find box, type in the value and then change from current module to within the project, one click event instead of numerous processes each time, isn't that what vba is for, to stop tedious processes.

 

[Jaye7]

OK, so I got it to work for a sub, where there is a will there usually is a way.
Thanks for your help.

Private Sub Command5_Click()

Dim str1 As String
str1 = [Forms]![form1]![Text3]
str1 = Mid(str1, 1, 50)
 'MsgBox str1
Application.Run str1

End Sub

the sub is in a module

Sub test1()
MsgBox "hello"
End Sub