Placement of Code
 

The following code was kindly provided to me - to allow users to click on merged cell range C9 in the €œScorecard€� worksheet, whereupon they are moved to the €œCustomer€� worksheet, cell A1, and moved to the upper left corner of the screen. I eventually want to make this idea work for 12 merged cell ranges on the €œScorecard€� worksheet, to send users to various other worksheets and target cells

Can someone tell me where/how to insert the code? Tried the €œScorecard€� worksheet code module and a general module but it doesnt respond

Any help would be appreciated
Thanks, Phi

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range
if Target.Address = "$C$9" the
Application.ScreenUpdating = Fals
Sheets("Customer").Selec
Sheets("Customer").Range("A1").Selec
ActiveWindow.Zoom = 6
ActiveWindow.ScrollRow =
ActiveWindow.ScrollColumn =
Application.ScreenUpdating = Tru
End I
End Su

Placement of Code
 

The code has to go in the macro module associated with the worksheet
Right click the worksheet name tab. View Code. Copy/paste the cod
there.

Another way is to View/Project Explorer in VB Editor and doubleclic
the sheet

--
Message posted from http://www.ExcelForum.com

Placement of Code
 

Thanks for your reply, Brian. I put it there but it doesn't activate when I click cell E20. The object he When the user clicks the merged cell range in the "Scorecard" worksheet (populated by a formula in the cell, =IF(G20<"","1.2:",""), when they make a text entry in cell G20), they are moved to the "Customer" worksheet, cell A33 is selected, window zoomed to 62%, and cell A33 is placed in the upper left corner of the screen. The author of this code tested it and said it worked for him. Any idea of what I am doing wrong?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$E$20" Then
Application.ScreenUpdating = False
Sheets("Customer").Select
Sheets("Customer").Range("A33").Select
ActiveWindow.Zoom = 62
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Application.ScreenUpdating = True
End If
End Sub

Placement of Code
 

Missed the merged cells in the original message.


right click on the sheet tab of the scorecard sheet and select view code.
Paste in the resulting module.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = Range("E20").MergeArea.Address Then
Application.ScreenUpdating = False
Sheets("Customer").Select
Sheets("Customer").Range("A33").Select
ActiveWindow.Zoom = 62
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollColumn = 1
Application.ScreenUpdating = True
End If
End Sub


And it does work fine for me using a merged cell area.

--
Regards,
Tom Ogilvy



"Phil Hageman" wrote in message
...
Thanks for your reply, Brian. I put it there but it doesn't activate when
I click cell E20. The object he When the user clicks the merged cell
range in the "Scorecard" worksheet (populated by a formula in the cell,
=IF(G20<"","1.2:",""), when they make a text entry in cell G20), they are
moved to the "Customer" worksheet, cell A33 is selected, window zoomed to
62%, and cell A33 is placed in the upper left corner of the screen. The
author of this code tested it and said it worked for him. Any idea of what
I am doing wrong?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$E$20" Then
Application.ScreenUpdating = False
Sheets("Customer").SelectPrivate
Sheets("Customer").Range("A33").Select
ActiveWindow.Zoom = 62
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Application.ScreenUpdating = True
End If
End Sub

Placement of Code
 

Thanks, Tom €“ it works perfectly. I want to expand this to include other €œclick cells€� (the merged cell range being clicked on ) and worksheet target cells. Should I create this sub for each click cell, or could the existing code include all the addresses, as follows:

Click Cell Target W/S and Cell
E17 Customer A1
E20 Customer A33 existing code does this case
E23 Customer A65
E35 Financial A1
E38 Financial A33
E41 Financial A65
AE17 Learning A1
AE20 Learning A33
AE23 Learning A65
AE35 Process A1
AE38 Process A33
AE41 Process A65

Thanks, Phil

Placement of Code
 

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim v(1 To 12, 1 To 3) As String
Dim rng1 As Range
Dim i As Long
v(1, 1) = "E17": v(1, 2) = "Customer": v(1, 3) = "A1"
v(2, 1) = "E20": v(2, 2) = "Customer": v(2, 3) = "A33"
v(3, 1) = "E23": v(3, 2) = "Customer": v(3, 3) = "A65"
v(4, 1) = "E35": v(4, 2) = "Financial": v(4, 3) = "A1"
v(5, 1) = "E38": v(5, 2) = "Financial": v(5, 3) = "A33"
v(6, 1) = "E41": v(6, 2) = "Financial": v(6, 3) = "A65"
v(7, 1) = "AE17": v(7, 2) = "Learning": v(7, 3) = "A1"
v(8, 1) = "AE20": v(8, 2) = "Learning": v(8, 3) = "A33"
v(9, 1) = "AE23": v(9, 2) = "Learning": v(9, 3) = "A65"
v(10, 1) = "AE35": v(10, 2) = "Process": v(10, 3) = "A1"
v(11, 1) = "AE38": v(11, 2) = "Process": v(11, 2) = "A33"
v(12, 1) = "AE41": v(12, 2) = "Process": v(12, 3) = "A65"


For i = 1 To 12
If Target.Address = Range(v(i, 1)).MergeArea.Address Then
Application.ScreenUpdating = False
Set rng1 = Sheets(v(i, 2)).Range(v(i, 3))
Sheets(v(i, 2)).Select
rng1.Select
ActiveWindow.Zoom = 62
ActiveWindow.ScrollRow = rng1.Row
ActiveWindow.ScrollColumn = rng1.Column
Application.ScreenUpdating = True
Exit For
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
Thanks, Tom - it works perfectly. I want to expand this to include other
"click cells" (the merged cell range being clicked on ) and worksheet target
cells. Should I create this sub for each click cell, or could the existing
code include all the addresses, as follows:

Click Cell Target W/S and Cell
E17 Customer A1
E20 Customer A33 existing code does this case
E23 Customer A65
E35 Financial A1
E38 Financial A33
E41 Financial A65
AE17 Learning A1
AE20 Learning A33
AE23 Learning A65
AE35 Process A1
AE38 Process A33
AE41 Process A65

Thanks, Phil

Placement of Code
 

Restrict to mouse click - not that I am aware of. If I think of something,
I will post back.

--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
Tom, I dug into the code instruction book and found the ElseIf - and did
the following (typical). It works okay. A lot of code - is there a cleaner
way? Also, if the user arrows to the click cell, the code activates - can
we add something restricting activation to a mouse click?

Thanks, Phil

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = Range("E17").MergeArea.Address Then
Application.ScreenUpdating = False
Sheets("Customer").Select
Sheets("Customer").Range("A1").Select
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
ActiveWindow.Zoom = 62
Application.ScreenUpdating = True
ElseIf Target.Address = Range("E20").MergeArea.Address Then
Application.ScreenUpdating = False
Sheets("Customer").Select
Sheets("Customer").Range("A33").Select
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollColumn = 1
ActiveWindow.Zoom = 62
Application.ScreenUpdating = True
ElseIf Target.Address = Range("E23").MergeArea.Address Then
.......Etc,......

Placement of Code
 

Tom, did you think of anything about triggering the code with the mouse only? Should I post this separately?

Thanks,
Phil