Button Text and Select sheet query

[kriten]

Hello
I have a workbook comprising of 20 or so sheets.
On one of the sheets I have buttons each with the title of one of the other sheets.

I am trying to work out some code to read the text from the button that is
clicked and then use it to select the sheet named in the button.

I can do each button individually but I would like to use the same bit of code for each button, thus cutting down on the amount of code needed to be written.

Hope someone has some ideas as my VB knowlledge is just at the infant stage.

Thanks in advance.
Kriten

 

[Ron Morris]

Hi,

Couldn't you just record a macro for one button and then copy the code for the rest of them, changing the button click name and sheet selected name?

Ron

 

[erik.van.geit]

Kriten,

WELCOME to the Board!

This works for buttons created with formstoolbar as well as toolbox.

assign this macro to a button
You will understand what happens and then use it within your code.
At first sight I would use the buttons name instead of the text on it, but perhaps you've got a good reason to do so?

Sub button_info()
MsgBox Application.Caller
MsgBox Sheets("Sheet1").Shapes(Application.Caller).OLEFormat.Object.Characters.Text
End Sub

Could be used as
Sheets(Application.Caller).Activate

Do you know what hyperlinks are?
Perhaps you could use that technic instead of all those buttons?

You could also do a search here for "TOC" = Table Of Contents

kind regards and good luck on the Board!
Erik

 

[kriten]

Heya Ron

Yes, that is what I did originally, but I thought there must be a more efficient (in terms of coding) way, with only 1 macro needed. I am thinking that it should read the text on the button that is clicked and then use that to open the sheet.

Something like:
Dim buttontext as string
buttontext = Some code that i don't know to read the buttons text
Sheets(buttontext) = select

But as a novice VB user I dunno if this is possible or not.

Thnkas for the reply Ron

Cheers
Keith

 

[Ron Morris]

Hey Keith,

New at VBA myself. Try the example Erik posted, maybe that will do the trick.

Isn't it Saturday for you, go enjoy your weekend!!!

Ron

 

[kriten]

Greetings Erik

Thanks for the code. I learn more already!!
I see that it creates a message window with the button's name in.

Your idea of using the button name instead of the text on the button was one I also had but I can't find a way to change the name of the button to the name of the sheet I would like to open. This would be easier because then I could read the name and use it to open the sheet.

I have seen/heard of hyperlinks but they are greyed out as an option when I try to use it with my buttons.

Ah ha!!! Just had a thought and tried it out, get rid of the buttons and just use the cell!!! And it works!!! Brilliant.

Thanks for the wonderful help.

And yes Ron, tis Saturday morning here and I will go enjoy the rest of it now!!


Many grateful thanks.
Kriten

 

[erik.van.geit]

OK, Kriten,

hyperlinks with cells is the easiest in this case

changing button name ==> rightclick and see at the topleft of your screen
(can't remember at the moment how you call the white area where the names are: A1, G4, ...)

kind regards,
and succes with your spreadsheet!
Erik
(here it's about Saturday 2:30 am time to sleep )