change the name of a worksheet/update VBA code

[Dave F]

I have a bond pricing template designed by someone else which I'm
automating.

I have a number of subs, such as the following:

Sub EnterFaceParValueOfBond()
Dim MyString As String
Dim MyString2 As String
MyString = Application.InputBox("Enter face/par value of bond")
'Queries the user to enter the face/par value of the bond
Worksheets("PRICE OF BOND").Range("b2") = MyString
'Inserts the value entered by the user in cell B2
If Worksheets("PRICE OF BOND").Range("b2") = False Then
MsgBox ("Caution! Canceling Input sets the price of the bond
to $1,000.00!")
Else: Exit Sub
End If
MyString2 = 1000
Worksheets("PRICE OF BOND").Range("B2") = MyString2
End Sub

The worksheet name needs to be change to PriceOfBond (because Access
is pulling data from this worksheet, and it doesn't deal well with
spaces in names). If I leave the code as it appears above, I get a
"subscript out of range" error, presumably because PriceOfBond does
not agree with PRICE OF BOND in the above code.

So: is there a way to have Excel automatically update code if the name
of the worksheet changes? Or do I have to manually go into this code
and change the worksheet names?

Thanks....

 

[Guest]

I deal with that using worksheet code names. Take a look at the properties
window for the sheet that has the name change. You should see something
that looks like this:

(Name) Sheet1

Change Sheet1 to something that makes sense for that sheet. (Price_Of_Bond)

To reference the sheet, use something like this:

Price_Of_Bond.Range("b2") = MyString

What you have changed is the worksheet codename. As far as I know, the
codename can not be changed programmatically.

HTH,
Barb Reinhardt