Worksheet tab won't update.

P

Paul3rd

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,
 
J

Jim Thomlinson

Try this perhaps...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

trying to create sheets and tab names from first colum of data 2
How to copy worksheet template into multiple worksheets 1
Automatically Adding a Worksheet 14
How to temp disable Worksheet_Change Event while code in same completes? 4
Help ammendin some code 3
Find a value and color that cell 2
Code to delete names works in one WB, but not another WB? 2
copying cells from one worksheet to another 1

Top