How to: button with cell value from another sheet

[CAPtain232]

Hello everyone.... it has been a while since I have posted. important note: CONSIDER ME A BEGINNER

I have created a workbook that I am going to use as a template. The first worksheet has 30 buttons on it and I have already set them up to go to each of the 30 worksheets. What I want to do for each button is have the text reference cell A1 of the corresponding worksheet.

The buttons I have drawn from SHAPES and customized them with different effects and then recorded a macro for each to jump to the correct page.

What I don't know: 1) The code to make the button text equal the A1 cell on the appropriate worksheet. 2) Where to write the code..... it has been a couple of years since I have done any code.

Thank you everyone

 

[Logit]

.
If you use ActiveX buttons, instead of Form Command Buttons, you can use this macro in the First Sheet Module :

Option Explicit


Private Sub Worksheet_Activate()
    CommandButton1.Caption = Sheets("Sheet2").Range("A1").Value
    CommandButton2.Caption = Sheets("Sheet3").Range("A1").Value
End Sub

Or - it may be better to paste this macro (slight change) in the ThisWorkbook module. This way, the captions will be assigned from
the very start without depending on Sheet 1 being activated :

Option Explicit


Private Sub Workbook_Open()
    Sheet1.CommandButton1.Caption = Sheets("Sheet2").Range("A1").Value
    Sheet1.CommandButton2.Caption = Sheets("Sheet3").Range("A1").Value
End Sub

 

[CAPtain232]

Thank You.... I will give this a try

 

[My Aswer Is This]

I know you may already like your new plan using 30 button on one sheet which when you click on will take you to a particular sheet.

But if you would like to look at another way here is a script I have.

Name your Sheet where you plan to have all your buttons "Master"

Or look at the script and modify "Master" to what you want.

Now when you run this script the script will put all your sheet names in column "A" of sheet named "Master"

And the script will also put a Hyperlink in those cells which when clicked on will take you to that sheet.
And in each of those sheets in Range("A1") there will be a Hyperlink which will take you back to the Master sheet.

Ever time you run the script it will add any new sheets you have added since last time you ran the script.

You may like this or maybe not.

Try this on a New Workbook with several sheets and see how it works. You may like it.

This script assumes sheet named "Master" is Sheet(1)
which is in the far left position on the tab bar.

Sub AddHyperLinks()
'Modified 12-16-17 9:45 AM EST
Dim C As Range
Dim i As Long
Dim ans As String
ans = "Master" 'Modify this name if needed all sheet names will be put in Column "A" of this sheet
Sheets(ans).Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Clear
    For i = 2 To Sheets.Count
    Sheets(ans).Cells(i, 1).Value = Sheets(i).Name
    Sheets(i).Cells(1, 1).Value = Sheets(ans).Name
    Sheets(i).Cells(1, 1).Hyperlinks.Add Anchor:=Sheets(i).Cells(1, 1), Address:="", SubAddress:="'" & Sheets(i).Cells(1, 1).Value & "'!A1"
    Next

With Sheets(ans)
    For Each C In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & C.Value & "'!A1"
    Next C
End With
End Sub