Calling text from a Button

Looking at setting up a suite of buttons for navigating to various places in a workbook. Buttons will be drawn Bevel shapes.

I am looking to write one macro that will be assigned to all buttons with various Case Select statements dependent on which button is clicked.

How do I pull the Text value from the button to determine which one has been clicked?

I am fairly sure this can be done as I have seen and used it before but don't recall how.

Thanks
Rob H
LVL 36
Rob HensonFinance AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi Rob,

This may depend on which type of Command Button you are using (ActiveX, or Form Control), but I usually use this method...

Public Sub Command_Button_Click()

  Select Case (Application.Caller)

      Case ("Text on First Button")
          ' Statements relating to First Button Click

      Case ("Text on Second Button")
          ' Statements relating to Second Button Click

      Case ("Text on Third Button")
      Case ("Text on etc...")
      Case Else

  End Select

End Sub

Open in new window

0
Rob HensonFinance AnalystAuthor Commented:
Neither, as I said in the question I am just using drawn shapes to which I am assigning the macro.

Thanks
Rob H
0
[ fanpages ]IT Services ConsultantCommented:
A Command Button is a "Shape" object.

Have you tried my suggestion?

If that does not suit, then you will probably need to be more precise with which "Shape(s)" you are using.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Rob HensonFinance AnalystAuthor Commented:
I was also just looking at using Hyperlinks rather than buttons but one of the sheets to which I will be navigating will effectively have a filter in place.

I have a Dynamic named Range for to select a specific area on a list of Retail units across various sites. The Units sheet contains all units for all sites. The site in question will already be selected and I want the navigation button that goes to the Units sheet to go to the first entry for that site.

Thanks
Rob H
0
[ fanpages ]IT Services ConsultantCommented:
If, for example, you were using a Rectangle Shape, you could use...

  Select Case (ActiveSheet.Shapes(Application.Caller).TextFrame2.TextRange.Text)

      Case ("Text within Rectangle1")
          ' Statements relating to Rectangle1 Click

      Case ("Text within Rectangle2")
          ' Statements relating to Rectangle2 Click

      Case ("Text within Rectangle3")
      Case ("Text within etc...")
      Case Else

  End Select

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystAuthor Commented:
That worked, thank you.
1
[ fanpages ]IT Services ConsultantCommented:
I was also just looking at using Hyperlinks rather than buttons but one of the sheets to which I will be navigating will effectively have a filter in place.

What do you mean by 'effectively'?  Will it have a filter in place, or not?

Or did you then go on to explain this?...

I have a Dynamic named Range for to select a specific area on a list of Retail units across various sites. The Units sheet contains all units for all sites. The site in question will already be selected and I want the navigation button that goes to the Units sheet to go to the first entry for that site.

I think I will need to see an example of what you have now, & what you are trying to achieve, please.

It does not sound difficult, but I just want to make sure I understand what you are doing without making assumptions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.