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.
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.
What toolbar did the button and the checkbox come from?
Control Toolbox or Forms?
Justin Labenne
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.
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
Justin Labenne
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
K :-)
Thanks guys that was most helpful.John.
Wonderful code here. Glad for your help.Originally Posted by Killian
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