Calling a sub with a variable name containing multiple underscores

Question

Currently I'm working on a sub that will activate one of the subs already written if a cell value matches a sheet's name and thus the sub's name.

So if I have selected "Index" in a cell with data validation, the sub will activate the sub name "Index" that will create a pdf file of the sheet "Index". The sub "Index" that creates the pdf file works. The sub that activates the sub "Index" also works IF there are no underscores in the subname. But I do want it to work with underscores since some sheets have spaces in their names. For example; sheet "Material inspection" is combined with the sub "Material_inspection" or sheet "Material test report" with "Material_test_report".

The code only works sometimes after I have reset a subname.

Code:

Sub CreateSomePDF()

  Dim iMyValue As String
  iMyValue = Worksheets("Invulformulier").Range("A96").Value
  Application.Run iMyValue

End Sub

Is it possible to make it work? I'm almost certain that the underscores are the problem since single names work.

Answer 1

If your issue is that sheets have spaces in their names and the subs have underscores why not just replace them in the string when calling the macro?

Sub CreateSomePDF()

  Dim iMyValue As String
  iMyValue = Replace(Worksheets("Invulformulier").Range("A96").Value, " ","_")
  Application.Run iMyValue

End Sub