Placement of Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 doesn’t 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
 
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
 
Thanks for your reply, Brian. I put it there but it doesn't activate when I click cell E20. The object here: 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
 
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 said:
Thanks for your reply, Brian. I put it there but it doesn't activate when
Click to expand...
I click cell E20. The object here: 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?
 
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
 
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 said:
Thanks, Tom - it works perfectly. I want to expand this to include other
Click to expand...
"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:
 
Restrict to mouse click - not that I am aware of. If I think of something,
I will post back.

--
Regards,
Tom Ogilvy

Phil Hageman said:
Tom, I dug into the code instruction book and found the ElseIf - and did
Click to expand...
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?
 
Tom, did you think of anything about triggering the code with the mouse only? Should I post this separately?

Thanks,
Phil
 
Back
Top