Worksheet tab won't update.
 

Hello, I have the following code:
Public Sub RMacro()
'This code puts the date in cell B22 formatted as 14-Dec-07.

Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("B22")
MyRange = Range("B22").Value
ActiveSheet.Name = MyRange

End Sub
In a module (Module3), when the user clicks a command button on the
worksheet a macro runs that calls the module.
So far so good, but, instead of changeing the worksheet tab to 14-Dec-07
I get a "runtime error 1004". Indicating I'm trying to rename the worksheet
with an invalid name.
Cell B22 is formatted to display the date as 14-Dec-07, but in the formula
bar the date is displayed as 12/14/2007. Is this a default format that Excel
uses? and how can I workaround that?
Thanks in advance for any help,

Worksheet tab won't update.
 

Try this perhaps...

ActiveSheet.Name = Worksheets("Sheet1").Range("B22").Text

--
HTH...

Jim Thomlinson


"Paul3rd" wrote:

Hello, I have the following code:
Public Sub RMacro()
'This code puts the date in cell B22 formatted as 14-Dec-07.

Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("B22")
MyRange = Range("B22").Value
ActiveSheet.Name = MyRange

End Sub
In a module (Module3), when the user clicks a command button on the
worksheet a macro runs that calls the module.
So far so good, but, instead of changeing the worksheet tab to 14-Dec-07
I get a "runtime error 1004". Indicating I'm trying to rename the worksheet
with an invalid name.
Cell B22 is formatted to display the date as 14-Dec-07, but in the formula
bar the date is displayed as 12/14/2007. Is this a default format that Excel
uses? and how can I workaround that?
Thanks in advance for any help,

Worksheet tab won't update.
 

BINGO! We have a winner!
Thanks for your help Jim

"Jim Thomlinson" wrote:

Try this perhaps...

ActiveSheet.Name = Worksheets("Sheet1").Range("B22").Text

--
HTH...

Jim Thomlinson


"Paul3rd" wrote:

Hello, I have the following code:
Public Sub RMacro()
'This code puts the date in cell B22 formatted as 14-Dec-07.

Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("B22")
MyRange = Range("B22").Value
ActiveSheet.Name = MyRange

End Sub
In a module (Module3), when the user clicks a command button on the
worksheet a macro runs that calls the module.
So far so good, but, instead of changeing the worksheet tab to 14-Dec-07
I get a "runtime error 1004". Indicating I'm trying to rename the worksheet
with an invalid name.
Cell B22 is formatted to display the date as 14-Dec-07, but in the formula
bar the date is displayed as 12/14/2007. Is this a default format that Excel
uses? and how can I workaround that?
Thanks in advance for any help,