Hi All,

I have run into a problem that I thought didn't exist. WIll appreciate
if anyone can give pointers.

Here is what I am doing. I have three combo boxes which the user
selects. I concatenate all of them togather to form a string. I now
want to call a procedure that is the same value as the concatenated
string. For example if the the combo boxes are box1, box2 and box3 and
there values are Australia, AP, BIZ the the final string is
Australia_AP_BIZ and there exists a procedure by that name. Then I
call the procedure with call to_proc where the to_proc variable
contains the above string. I expected xl to call the above procedure,
however guess what I got an error stating that it expected procedure
not variable.

Is there anyway I can make xl understand that I want to call a
procedure by the name that is stored in the variable?

Will appreciate any pointers as I have developed this since I need to
call more than 300 functions which are formed by the combination of the
combo boxes, and it is not possible (rather cumbersome) to write so
many select clauses.


Regards

Basu,

Try using application.run.

Sub TryNow()
Str1 = "Procedure"
Str2 = "V2"
Application.Run Str1 & "_" & Str2
End Sub

Sub Procedure_V2()
MsgBox "Hello World"
End Sub

HTH,
Bernie
MS Excel MVP


"Basu" wrote in message
oups.com...
Hi All,

I have run into a problem that I thought didn't exist. WIll appreciate
if anyone can give pointers.

Here is what I am doing. I have three combo boxes which the user
selects. I concatenate all of them togather to form a string. I now
want to call a procedure that is the same value as the concatenated
string. For example if the the combo boxes are box1, box2 and box3 and
there values are Australia, AP, BIZ the the final string is
Australia_AP_BIZ and there exists a procedure by that name. Then I
call the procedure with call to_proc where the to_proc variable
contains the above string. I expected xl to call the above procedure,
however guess what I got an error stating that it expected procedure
not variable.

Is there anyway I can make xl understand that I want to call a
procedure by the name that is stored in the variable?

Will appreciate any pointers as I have developed this since I need to
call more than 300 functions which are formed by the combination of the
combo boxes, and it is not possible (rather cumbersome) to write so
many select clauses.


Regards

Hi Bernie,

Works like a charm.... Thanks for the timely help. Much appreciated.

Regards
Shuvro


Bernie Deitrick wrote:
Basu,

Try using application.run.

Sub TryNow()
Str1 = "Procedure"
Str2 = "V2"
Application.Run Str1 & "_" & Str2
End Sub

Sub Procedure_V2()
MsgBox "Hello World"
End Sub

HTH,
Bernie
MS Excel MVP


"Basu" wrote in message
oups.com...
Hi All,

I have run into a problem that I thought didn't exist. WIll appreciate
if anyone can give pointers.

Here is what I am doing. I have three combo boxes which the user
selects. I concatenate all of them togather to form a string. I now
want to call a procedure that is the same value as the concatenated
string. For example if the the combo boxes are box1, box2 and box3 and
there values are Australia, AP, BIZ the the final string is
Australia_AP_BIZ and there exists a procedure by that name. Then I
call the procedure with call to_proc where the to_proc variable
contains the above string. I expected xl to call the above procedure,
however guess what I got an error stating that it expected procedure
not variable.

Is there anyway I can make xl understand that I want to call a
procedure by the name that is stored in the variable?

Will appreciate any pointers as I have developed this since I need to
call more than 300 functions which are formed by the combination of the
combo boxes, and it is not possible (rather cumbersome) to write so
many select clauses.


Regards