PDA

View Full Version : Solved: Running a sub based on the value in a cell



flyfisher15
04-12-2012, 06:40 AM
I'm trying to determine the value in a cell, and pull that value which in this case would be "ENV 4246". I clean out the space and then try to call a sub which has the same name as the text in the cell. Whenever I try to call that sub though it tells me that "ENV4246" cannot run because it's not available.

Code is below

Any help would be greatly appreciated!

Option Explicit
Option Compare Binary
Option Base 0


Public Sub main_comments()
Dim Z
Dim Cell As Range
Dim strsubname As String
Dim testy As Range
Dim cellval


cellval = 3

Z = 3
While Range("B" & Z) <> ""
Z = Z + 1
Wend
Z = Z - 1

For Each Cell In Range("B3:B" & Z)


strsubname = Cell.Value
strsubname = Replace(strsubname, " ", "")

cellval = cellval + 1

Run strsubname
Next
End Sub
__________________________________________________________________

'Setting this to True will turn off all error handling:
#Const m_blnErrorHandlersOff_c = True

Public Sub ENV4246()
If Range("B" & cellval).Value = "ENV 4246" Then
If InsertPictureComment(ActiveSheet.Range("B3"), "M:\Fulfillment_Ops\INVENTORY\Statement Images - 4-10\ENV 4246_20100101_0.jpg", _
overWrite:=True) Then
Else
MsgBox "Could not insert picture."
End If
End If
End Sub

Bob Phillips
04-12-2012, 07:57 AM
Try


Application.Run strsubname

flyfisher15
04-12-2012, 08:10 AM
Just tried it it still comes back as "Cannot run ENV4246 The macro may not be available"

Bob Phillips
04-12-2012, 08:23 AM
That suggests there is no such macro.

flyfisher15
04-12-2012, 08:53 AM
In the code I included in my first post the macro ENV2426 is there. When I run the "main comments" sub I've checked the "strsubname" value before having it call the sub. It has the value of "ENV2426" but it still says that it can't find the macro with the same name.

Bob Phillips
04-12-2012, 10:25 AM
Post the workbook, let us look deeper.

flyfisher15
04-12-2012, 10:42 AM
I've attached the workbook. The module I'm concerned about is named PicInsert.

Thanks for your help!!

Bob Phillips
04-12-2012, 11:23 AM
Enclose it in single quotes


Application.Run "'" & strsubname & "'"

flyfisher15
04-12-2012, 11:41 AM
I think that I've found a better way of doing this. Simply setting up the item names as variables within one main sub that pulls the images. Instead of having individual subs for each item type.

Public Sub main_comments()
Dim z
Dim cell As Range
Dim strsubname As String
Dim testy As Range



cellval = 2

z = 3
While Range("B" & z) <> ""
z = z + 1
Wend
z = z - 1

For Each cell In Range("B3:B" & z)


cellval = cellval + 1
strsubname = cell.Value

itemidstr = strsubname
Call imagesub
strsubname = Replace(strsubname, " ", "")


Next
End Sub
'Setting this to True will turn off all error handling:
#Const m_blnErrorHandlersOff_c = True

Public Sub imagesub()

If InsertPictureComment(ActiveSheet.Range("B" & cellval), "M:\Fulfillment_Ops\INVENTORY\Statement Images - 4-10\" & itemidstr & ".jpg", _
overWrite:=True) Then
Else
MsgBox "Could not insert picture."
End If

End Sub

flyfisher15
04-12-2012, 11:49 AM
XLD Thanks so much for your help!