MrExcel Publishing
Your One Stop for Excel Tips & Solutions

worksheet tabs


Posted by Howar on March 17, 2001 5:17 PM

Can I cahange my worksheet tabs (Multiple sheets), if I Put any "Name" on "A1" & vice a versa.


Posted by Dave Hawley on March 18, 2001 6:03 PM


Hi Howard, your question is a bit vague, but try
placing this code in the Woksheet module.

Private Sub Worksheet_Activate()
If Me.Name <> Range("A1") Then Range("A1") = Me.Name
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If Target.Address = "$A$1" Then Me.Name = Target
End Sub

Any good ?

Dave


OzGrid Business Applications

Posted by Hower on March 19, 2001 8:18 AM

Dave: First of all thank you very much for your reply. I could not get my result.
I repeat my problem. Suppose I change my sheets Tabs like Sheet1 (KMB), Sheet2 (SMB), Sheet3 (LMB). My problem is that I have to write down these names KMB, SMB, LMB... again on Cell "A1" of each sheet respectively. Some time I have 80 Sheets. I want to create a Macro Button.
I will sincerely, appreciate your help.

Posted by Dave Hawley on March 19, 2001 5:15 PM


Ok, try this one in your macro button.

Sub TryThis()
Dim Sht As Worksheet
Application.ScreenUpdating = False
For Each Sht In ThisWorkbook.Worksheets
Sht.Cells(1, 1) = Sht.Name
Next
Application.ScreenUpdating = True
End Sub

Dave

OzGrid Business Applications

Posted by Hower on March 20, 2001 10:26 AM

It is working perfectly, just for curiosity,is it possible if I put KMB on cell A1 it pops up on sheet Tab. Any how Dave I am very thankful to you***

Posted by Hower on March 20, 2001 10:27 AM

It is working perfectly, just for curiosity,is it possible if I put KMB on cell A1 it pops up on sheet Tab. Any how Dave I am very thankful to you***

Hower

Posted by Dave Hawley on March 20, 2001 2:18 PM

Hi Hower

Right click on the sheet picture top left next to file and select "View Code", paste in this code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If Target.Address = "$A$1" Then Me.Name = Target
End Sub
End Sub

This will change the active sheet name to the contents of cell A1.


Dave


OzGrid Business Applications

Posted by Dave Hawley on March 20, 2001 2:26 PM

Use this one instead!

Small typo in the other one.


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If Target.Address = "$A$1" Then Sh.Name = Target
End Sub
OzGrid Business Applications