.OnAction command not taking a Variable

r0b3m4n · February 4th, 2009, 03:23 PM

HELP PLZ!

I am passing a variable through an .OnAction I get everything to come out ok EXCEPT I have extra quotation marks around the variable!

I expect to see the following title for the Sub Name: MyFile.xls!'Close200002'
But in stead I get: MyFile.xls!'Close"200002"'
I am unable to get rid of the extra quotation marks (which are an invalid Sub title, otherwise I wouldn't have a problem...)
When I try to remove a set of quotation marks I get: MyFile.xls!'Close" & WOrderNum & "'
Which is obviously not what I want since it isn't taking the variable for the file name.

I'm not a VB Pro just a hack, so plz excuse the code. I'm using excel 2007 and this is only a small portion of the code I believe to be applicable.

Code:

Sub Try0()

Dim WOrderNum As String

Range("P8").Select
ThisWorkbook.Names.Add Name:="WOrderNum", RefersTo:="=$P$8", Visible:=True         

WOrderNum = Range("WOrderNum")                                             ' In this case the WOrderNum is 200002 referenced from cell P8

Range("Q8").Select                                                         ' INSERT CHECKBOX SUB FOR CLOSING
                        ActiveSheet.CheckBoxes.Add(1850, 201, 112, 10).Select
                        With Selection
                        .Name = M8
                        .LinkedCell = "L8"
                        .Value = xlOff
                        .Characters.Text = "Check Box to CLOSE WO"
                        .OnAction = "'Close""" & WOrderNum & """'"        '<=---- This is where problem is believed to exist!
                        Range("Q8").Select
                        End With

'Down here later in the code I have the Macro record a new macro titled the same as the sub attempting to be labeled above.

End Sub

This part of the program is adding check boxes for Work Orders, which are then clicked to close the work order. The sub that is executed .OnAction closes the work order. (a very annoying sub to write since I couldn't find a great way to have a macro write another macro...)

Please tell me if you have any ideas as I feel like I've tried all the quotation mark combos that can possibly exist!

thanks.

Shasur · February 5th, 2009, 07:10 AM

Can you try

.OnAction = "'Close" & WOrderNum & "'"

Cheers
Shasur