Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-10-2008, 04:15 AM
Novice
 
Join Date: Feb 2008
Posts: 1
deegz
Default Automate Excel tabname

Hi I want to automate my tabname (sheet3) based on cell c53.
A member called Bigwheel advised this

"Private Sub Worksheet_Activate()
Sheets(1).Name = Range("H2").Value


End Sub

Enter the code above on the required worksheet i.e. double click the sheet in the VB editor which you see listed under Microsoft Excel Objects"

Which I tried, but not being an Excel expert got lost....
Could somebody please advise in laymans terms for me.

Thanks in advance,
Deegz
Reply With Quote
  #2  
Old 12-05-2008, 10:37 AM
Novice
 
Join Date: Dec 2008
Location: Hong Kong
Posts: 7
Emily is on a distinguished road
Default

Code:
 
' Module    : Sheet3

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = "" Then Exit Sub
    If Target.Address = "$C$53" Then ActiveSheet.Name = Target
End Sub
Reply With Quote
  #3  
Old 12-08-2008, 02:57 AM
caholmes caholmes is offline Windows Vista Office 2007
Advanced Beginner
 
Join Date: Dec 2008
Location: Sydney, Australia
Posts: 54
caholmes is on a distinguished road
Thumbs up

Follow these instructions Deegz.
  1. Go into the Visual Basic Editor (Alt + F11)
  2. Ensure the Projects Window is Visible: View >> Project Explorer or Ctrl + R
  3. Double click on the worksheet that you are trying to rename
  4. Select worksheet from the dropdown box where it currently says (General)
  5. In the second dropdown list (to the right of the first one) select 'SelectionChange'
  6. Paste in the following code (exclude the link at the bottom). I hope this helps you.
On Error GoTo EH

If Range("C53") = "" Then Exit Sub

If ActiveSheet.Name <> Range("C53") Then ActiveSheet.Name = Range("C53")
Exit Sub
EH:
MsgBox "The value is cell C53 has created an error" & vbCr & vbCr & "Description: " & Err.Description
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Opening multiple Excel files within the same Excel window. lost9471 Excel 2 05-01-2010 01:57 PM
How do I use an Excel form to populate and Excel spreadsheet apostht Excel 0 05-21-2006 11:17 AM


All times are GMT -7. The time now is 11:12 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft