List = hyperlink to view

[hro5e]

Dear All,

I have a validation that cell A15 is a list, the list contains, 1) Chart 1; 2) Chart 2 & 3) Chart 3.

When i click on chart 2 (for example) i want to view between columns P to Z.
Because chart 2 is located between those columns

When i click on chart 3 i want to view between columns AA to AI an so on.

Please advise.

 

[Domenic]

Instead of using a hyperlink, I think a Worksheet_Change event can be used. This way, every time a new chart is selected in the validation cell, the selected chart will be brought into view. And, instead of "going to" or "viewing" specific columns when a chart has been selected, it might be better to bring the selected chart into view so that the upper left corner of the chart appears in the upper left corner of the window? This way, if necessary, the charts can be moved around within the worksheet without having to modify the code. Or is there some reason why you want to specify the columns? Maybe you have data, in addition to the chart itself, which needs to be viewed as well?

 

[hro5e]

Instead of using a hyperlink, I think a Worksheet_Change event can be used. This way, every time a new chart is selected in the validation cell, the selected chart will be brought into view. And, instead of "going to" or "viewing" specific columns when a chart has been selected, it might be better to bring the selected chart into view so that the upper left corner of the chart appears in the upper left corner of the window? This way, if necessary, the charts can be moved around within the worksheet without having to modify the code. Or is there some reason why you want to specify the columns? Maybe you have data, in addition to the chart itself, which needs to be viewed as well?

Hi thank you, i think the way you suggested would work, how do i perform this worksheet change?

 

[Domenic]

Try the following macro, which needs to be placed in the sheet module for the sheet containing the validation cell and charts (right-click the sheet tab, and select 'View Code')...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]If[/color] Target.Cells.Count > 1 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]If[/color] Target.Address <> "$A$15" [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]Dim[/color] ChrtObj [color=darkblue]As[/color] ChartObject
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    [color=darkblue]Set[/color] ChrtObj = ActiveSheet.ChartObjects(Target.Value)
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
    [color=darkblue]If[/color] [color=darkblue]Not[/color] ChrtObj [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        Application.Goto reference:=ChrtObj.TopLeftCell, Scroll:=[color=darkblue]True[/color]
    [color=darkblue]Else[/color]
        MsgBox "'" & Target.Value & "' does not exist...", vbExclamation
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]
[/font]