Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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,...... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Placement of Code
Tom, did you think of anything about triggering the code with the mouse only? Should I post this separately?
Thanks, Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Placement of a chart | Charts and Charting in Excel | |||
beforeclose placement | Excel Discussion (Misc queries) | |||
Name Placement | Excel Discussion (Misc queries) | |||
Tab placement | Excel Discussion (Misc queries) | |||
Bar placement | Excel Programming |