| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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, |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Update one worksheet tab to automatically update all other tabs? | Excel Worksheet Functions | |||
| Update worksheet | Excel Discussion (Misc queries) | |||
| How to update worksheet colours based on worksheet name | Excel Discussion (Misc queries) | |||
| How do I update worksheet 1 from data on worksheet 2 | Excel Worksheet Functions | |||
| Update second worksheet with changes in original worksheet | Excel Worksheet Functions | |||
Linear Mode
