Macro to locate Worksheet based on cell value or defined name

[natekris8183]

I think I can likely figure out how to write the VB language so that it locates a worksheet based on a cell value (although if anyone has some suggestions I'll listen), however I am putting some controls on a worksheet to select to redirect you to the appropriate worksheet. Writing that is simple, however there will be 20 controls on the front page (there's 20 locations), so I would prefer not having to rewrite the sub code 20 times. Can you use a defined name of an object to identify the worksheet with the same name:

Object Control is an image (picture) with an assigned macro. For instance the defined name for the object control would be "Sheet1" so the macro would select the Sheets("Sheet1") based upon the defined name "Sheet1". Is this possible?

 

[tlowry]

1. Name the pictures "Sheet Name"<sheet name="" to="" call="">Caller

ie. Sheet4Caller
OrangeQuotasCaller

Do not use the word Caller in a sheet name.

2. Copy the code below to a module
3. Assign all the pictures macro to ImageAllClick

Test

Sub ImageAllClick()
     Sheets(Replace(Application.Caller, "Caller", "")).Activate
End Sub

</sheet>

 

[natekris8183]

When you refer to naming, you are meaning "defined" names or variables in the VBA?

 

[tlowry]

There are two things (objects) that are named here:

1. The sheet names, it is what is displayed on the tab of each sheet (generally starts out as Sheet1, but can change)

2. The name of the object (picture) you want to click to activate a sheet. To name an object do a Ctrl left mouse click on the object and in the upper left hand side type in the name and press enter. The name should be like "Sheet2Caller".

 

[daverunt]

Hi,

Mr Lowry has posted what you asked for but I got this far so I will add it anyway. Object names can be changed if required.
You assign the same macro to all pictures.

Sub Image_Click()

Nme = ActiveSheet.Shapes(Application.Caller).Name

Select Case Nme

Case Is = "Picture 2"
Worksheets("Sheet2").Activate

Case Is = "Picture 3"
Worksheets("Sheet3").Activate

End Select

End Sub

 

[natekris8183]

Both methods worked PERFECTLY by the way, and THANK so much. I was curious, if there a way to store the same name of the image as a variable? I'd like for the name to carry forward to a cell value on the worksheet it references. Thank you both SO much by the way!

 

[daverunt]

Hi

you mean just add the object name at the same time as you click?
For my method I have used A1 on the target worksheet.

Sub Image_Click()

Nme = ActiveSheet.Shapes(Application.Caller).Name

Select Case Nme

Case Is = "Picture 2"
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range("A1").Value = Nme

Case Is = "Picture 4"
Worksheets("Sheet3").Activate
Worksheets("Sheet3").Range("A1").Value = Nme

End Select

End Sub

 

[natekris8183]

Sorry to be a pest, but I still needed to use the "Caller" tag on the shape names because the images reference the individual branch order sheets. So, I am trying to introduce place to your

Nme = ActiveSheet.Shapes(Application.Caller).Name

This is what I am assuming, but it's giving me a compiling error

Nme = ActiveSheet.Replace(Shapes(Application.Caller, "Caller", "")).Name

You've been a tremendous help thus far on this. I've not tried integrating "shapes" (actually in this cal I am filling the shapes with images of the individual locations... hoping to tie in some unity and connection within the organization since we are spread pretty far out).

Sorry I answered my own question

Nme = ActiveSheet.Shapes(Replace(Application.Caller, "Caller", "")).Name

 

[natekris8183]

Ok, so I was wrong. For some odd reason it only works on the very FIRST image.

 

[natekris8183]

Again my apologies... instead of handling it at the variable, I just need to add the value deconstructed less the "caller".

ActiveSheet.Range("D4").Value = Replace(Nme, "Caller", "")