Check Box to toggle Button Text which can Select Different Sheets

Bawb

New Member
Joined
Nov 27, 2021
Messages
9
Office Version
  1. 2010
Platform
  1. Windows
I'm such a dabbler at code writing but, as everyone does I have a "big Idea". Mine is to build a Job Log for work that will help with tracking projects and save time due to the redundancy of the required paperwork. I have been working on it when time permits and have been actually using it in the field for the last couple years. It suffices, but is somewhat inadequate and is not user friendly for others. Therefore, I am now re-writing it to clean it up, fix issues and add items and functions that will be helpful.
Any help anyone can give is quite appreciated as my code writing skills are very beginner level.

I have (1) workbook with (1) Master sheet called "Time Cards" which selects (10) individual sheets called "Job logs". They are referred to as Job Log 1 through Job Log 10. I have a "T & M" check box on each Job Log which toggles the text in a button on each Job Log sheet to read "Labor Form" or "Job Invoice". Our Time & Material projects require different paperwork.
The check box works to toggle the text, but I'm stuck as to how to build the code for the button to change the sheet selection. The below is the working code I currently have in place.
There are 10 buttons, on 10 job logs, toggling between 10 each Labor Forms/ Job Invoices. All working separately. IE Job Log 1, Labor Form 1, Job Invoice 1, Job Log 2, Labor Form 2 etc.
All sheets are hidden until called upon by its corresponding button. They do not need to re-hide themselves as I have a single macro attached to a return to Time Card Button on every sheet that hides all but the master time cards sheet.

Sub JumpToLaborForm1()
' JumpToSheet13 Macro
Sheets("Labor Form 1").Visible = True
Sheets("Labor Form 1").Select
End Sub

I look forward to any help provided and thank you very much ahead of time.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
IE Job Log 1, Labor Form 1, Job Invoice 1, Job Log 2, Labor Form 2 etc.
Assuming you are using Form Control buttons and not ActiveX
try something along the lines of this
the message box is just to see the sheet name that was built, if necessary
VBA Code:
Sub Button1_Click()
    Dim i As Integer
    Dim Shtname As String
    
i = Split(ActiveSheet.Name, " ")(2)
Shtname = ActiveSheet.Buttons("Button 1").Caption & " " & i
'MsgBox Shtname
    
With Sheets(Shtname)
    .Visible = True
    .Select
End With

End Sub
 
Upvote 0
Solution
Hi NoSparks, You have helped me before. Thank you for your response. My buttons are shapes. Or so I think. I may have a mixture of the two, even though they are identical, other than the text. My other button names say Rectangle Rounded corners with a number attached. I tried changing

"Shtname = ActiveSheet.Buttons("Button 1").Caption & " " & i" line to .Shapes and checked the button name. It is Button 1 in the name window. I played around with the later part of the line, but so far to no avail?

Also there are 4 other buttons on this page that appear as Button 1 in the Name box, because I copy pasted them. I'm sure these names should change, but I can't find how to change them. None of my alt f #'s do anything on the surface pro I am using. Maybe it doesn't matter since they have different macros attached? Would this cause an issue in the code you recommended?
 
Upvote 0
Afraid if you don't know what you're working with... I don't either.
I have a "T & M" check box on each Job Log which toggles the text in a button on each Job Log sheet to read "Labor Form" or "Job Invoice".
I suspect the code associated with your "T & M" check box would indicate how to refer to the 'button' and its text.
 
Upvote 0
Great thinking! the check box refers to it as "ActiveSheet.Shapes("Button 1").TextFrame.Characters".
I entered that into your code above and it's working. SO AWESOME! THANK YOU!!!
I have been struggling with this one for a while, but I don't like to ask for help until I'm at my wits end, which as you can tell doesn't run far in coding. Lol!
But the challenge is fun when I get the chance. Below is the modified "working" code. I'll mark #2 as the answer.
Thanks so much again!

Sub Button1_Click()
Dim i As Integer
Dim Shtname As String

i = Split(ActiveSheet.Name, " ")(2)
Shtname = ActiveSheet.Shapes("Button 1").TextFrame.Characters.Caption & " " & i
'MsgBox Shtname

With Sheets(Shtname)
.Visible = True
.Select
End With

End Sub
 
Upvote 0
Good for you, glad you got it sorted and thanks for reporting back.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top