Sheet names

[Johnlemons]

I have a checkbox and a button that needs to be named the same name as the sheet it is on. Is there a way to link its name to the name of the sheet. If the sheet name changes, so does the name for the checkbox and button.
Any ideas?

Thanks,
John.

[Justinlabenne]

What toolbar did the button and the checkbox come from?

Control Toolbox or Forms?

[Johnlemons]

Forms.

Someone asked why do I need that. FYI:

I have 18 sheets that I have to change between. I can't see all 18 when all are visible. I have a master sheet. I click on a checkbox to unhide/goto the sheet I'm referring to. When I return I hit a return button. Instead of having 17 macros for the checkboxes and 17 for the return buttons. If they are labled as the sheetnames they come from I can use application.caller to see which sheet needs to be unhidden and same for the return, which one being closed. I've manually changed the names to match. I want to automate it so it can be kept somewhat self maintained.

Thanks.
JohnnyAppleSeed.

[Justinlabenne]

I am not sure just how your setup is working, but something like this should get you pointed in the direction you want:


Option Explicit

Sub ChangeToSheetNames()
Dim s As Shape
For Each s In ActiveSheet.Shapes
If s.Type = msoFormControl Then
s.Select
On Error Resume Next
Selection.Characters.Text = ActiveSheet.Name
End If
Next s
End Sub

[Killian]

If you're aiming for self-maintaining code, then try this slightly different approach:
Put a listbox on the Master sheet and add the code below to the sheet's code.
This will hide and show worksheets whatever they are called.


Private Sub Worksheet_Activate()
Dim ws As Worksheet
ListBox1.Clear
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Master" Then
ListBox1.AddItem ws.Name
If ws.Visible = True Then
ListBox1.Selected(ListBox1.ListCount - 1) = True
End If
End If
Next
End Sub

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim i As Long
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Worksheets(ListBox1.List(i, 0)).Visible = True
Else
Worksheets(ListBox1.List(i, 0)).Visible = False
End If
Next I
End Sub

[Johnlemons]

Thanks guys that was most helpful.John.

[sheeeng]

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim i As Long
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Worksheets(ListBox1.List(i, 0)).Visible = True
Else
Worksheets(ListBox1.List(i, 0)).Visible = False
End If
Next i
End Sub


Wonderful code here. :thumb Glad for your help. :friends:

[geekgirlau]

Another approach to this problem is to create a floating toolbar listing all sheet names - have a look at http://www.vbaexpress.com/kb/getarticle.php?kb_id=152