When executing macro via hyperlink, macro doesn't re-scroll window back to A1

**rain4u** · 05-15-2011, 12:06 PM

Hi guys. I need slight help with one of my macros. I'm using following code on sheet called "analyse" to execute macro via hyperlink

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$E$7" Then
    Application.Run "hyperlink_backlog_monday"
End If
End Sub

This code is placed within the correct module of course (under analyze)

Now when clicking E7 on sheet "analyze" it triggers macro "hyperlink_backlog_monday" which essentially copies information from one sheet to another, filters in some unwanted rows and deletes them. As a part of the code in "hyperlink_backlog_monday" I have following line

Application.Goto Reference:=Range("a1"), Scroll:=True

This line simply scrolls window back to A1. Its a good feature to have. Otherwise when I go back and forth between the sheets and re run some macros, it then remembers the last window placement. This causes me to scroll back to A1 manually with a mouse. This gets annoying really fast.

Anyway, when I execute"hyperlink_backlog_monday" in normal way it performs all actions beautifully and line Application.Goto Reference:=Range("a1"), Scroll:=True does its job.
But as soon as macro "hyperlink_backlog_monday" is executed via clicking E7 on sheet "analyze" it performs everything within the macro except this scrolling part. After the macro run, the window scroll bars are where the were last time when used.

Can anyone help me to tackle this problem. I would be very grateful.

Cheers
Rain

EDIT: See attached xls on post 3

**111StepsAhead** · 05-15-2011, 12:11 PM

Try replacing this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$E$7" Then
    Application.Run "hyperlink_backlog_monday"
End If
End Sub

with this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$E$7" Then
    Application.Run "hyperlink_backlog_monday"
    Application.Goto Reference:=Range("a1"), Scroll:=True
End If
End Sub

**rain4u** · 05-15-2011, 12:19 PM

Thank you for the help. He he. I also tried that. Unfortunately it then performs re scroll on sheet "analyze" where the the macro is executed instead performing it on the sheet where the copy-paste, and removing some lines happens.

Any other suggestions?

PS! I have now attached demo xls. Click E7 on sheet analyse. On sheet Hyperlink I have purposely left the scroll bars to random place. Note if you run macro "hyperlink_backlog_monday" manually on module 11 you will achieve the result required.

Cheers
Rain

**111StepsAhead** · 05-15-2011, 12:31 PM

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$E$7" Then
    Application.Run "hyperlink_backlog_monday"
    Application.ScreenUpdating = True
    Worksheet("SHEET_YOU_WANT_TO_SCROLL").Activate
    Application.Goto Reference:=Range("a1"), Scroll:=True
End If
End Sub

Or

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$E$7" Then
    Application.Run "hyperlink_backlog_monday"
    Application.ScreenUpdating = True
    Worksheet("WHATEVER_NAME").Activate
    Range("A1").Select
    'Application.Goto Reference:=Range("a1"), Scroll:=True
End If
End Sub

If you are allowed post a workbook if neither of these works.

**111StepsAhead** · 05-15-2011, 12:42 PM

This code worked for me. Replaced the code on your Sheet4 (Analyze)


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$E$7" Then
    Application.Run "hyperlink_backlog_monday"
    Application.ScreenUpdating = True
    Worksheets("Hyperlink").Activate
    ActiveSheet.Range("A1").Select
    'Application.Goto Reference:=Range("a1"), Scroll:=True
End If
End Sub

**rain4u** · 05-15-2011, 01:04 PM

Thanks. Works like a charm. One more question though. What if I have more lines on the code like below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$E$7" Then
    Application.Run "hyperlink_backlog_monday"
End If
If Target.Address = "$E$8" Then
    Application.Run "hyperlink_backlog_DNB_monday"
End If
If Target.Address = "$E$9" Then
    Application.Run "hyperlink_backlog_Reject_monday"
End If
If Target.Address = "$E$10" Then
    Application.Run "hyperlink_backlog_Commercial_monday"
End If
If Target.Address = "$E$11" Then
    Application.Run "hyperlink_backlog_Genuine_monday"
End If
If Target.Address = "$E$12" Then
    Application.Run "hyperlink_backlog_Promised_monday"
End If
If Target.Address = "$E$13" Then
    Application.Run "hyperlink_backlog_Age_Profile_monday"
End If
etc
etc
etc
End Sub

Would it be possible to place that code somewhere in the end, or there is no way around and the code needs to be between ever "if" statement like below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$E$7" Then
    Application.Run "hyperlink_backlog_monday"
    Application.ScreenUpdating = True
    Worksheets("Hyperlink").Activate
    ActiveSheet.Range("A1").Select
End If
If Target.Address = "$E$8" Then
    Application.Run "hyperlink_backlog_DNB_monday"
    Application.ScreenUpdating = True
    Worksheets("Hyperlink").Activate
    ActiveSheet.Range("A1").Select
End If
If Target.Address = "$E$9" Then
    Application.Run "hyperlink_backlog_Reject_monday"
    Application.ScreenUpdating = True
    Worksheets("Hyperlink").Activate
    ActiveSheet.Range("A1").Select
End If
If Target.Address = "$E$10" Then
    Application.Run "hyperlink_backlog_Commercial_monday"
    Application.ScreenUpdating = True
    Worksheets("Hyperlink").Activate
    ActiveSheet.Range("A1").Select
End If
If Target.Address = "$E$11" Then
    Application.Run "hyperlink_backlog_Genuine_monday"
    Application.ScreenUpdating = True
    Worksheets("Hyperlink").Activate
    ActiveSheet.Range("A1").Select
End If
If Target.Address = "$E$12" Then
    Application.Run "hyperlink_backlog_Promised_monday"
    Application.ScreenUpdating = True
    Worksheets("Hyperlink").Activate
    ActiveSheet.Range("A1").Select
End If
If Target.Address = "$E$13" Then
    Application.Run "hyperlink_backlog_Age_Profile_monday"
    Application.ScreenUpdating = True
    Worksheets("Hyperlink").Activate
    ActiveSheet.Range("A1").Select
End If
etc
etc
etc
End Sub

Thanx for helping me
Rain

**111StepsAhead** · 05-15-2011, 01:21 PM

This is a first effort. I don't have all the sheets in mine which I am guessing each name will be a different sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
    Select Case Target.Address

          Case "$E$7"
              Application.Run "hyperlink_backlog_Reject_monday"
          Case "$E$10"
              Application.Run "hyperlink_backlog_Commercial_monday"
          Case "$E$11"
              Application.Run "hyperlink_backlog_Genuine_monday"
          Case "$E$12"
              Application.Run "hyperlink_backlog_Promised_monday"
          Case "$E$13"
              Application.Run "hyperlink_backlog_Age_Profile_monday"
    End Select
Scroll_Up
End Sub

Function Scroll_Up()
    Application.ScreenUpdating = True
    Worksheets("Hyperlink").Activate
    ActiveSheet.Range("A1").Select
End Function

**rain4u** · 05-15-2011, 01:42 PM

Oh sorry. Demo xls was just shortened version of the actual file to make it a lot smaller. Sheets will always remain the same i.e. Master YM26, Analyze and Hyperlink. Macros just filter in and out different information and delete unnecessary rows.

With your latest offered code something is messed up. Now regardless where you click on spreadsheet (even if its something random as cell T311) it runs a macro that deletes all rows from row 2 downwards. I updated the spreadsheet posted on post number 3 with your the latest code. Can you help me bit further and look into it once more? I think we are almost there.

Cheers
Rain

**111StepsAhead** · 05-15-2011, 02:15 PM

I don't know how well this would work. I get an error that the names are macros but can't be run because they are disabled when in fact all macros in my excel sheet are enabled. Run this code and tell me what happens,.

Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
    Select Case Target.Address

          Case Range("E7").Address
              Application.Run "hyperlink_backlog_Reject_monday"
              
          Case Range("E10").Address
              Application.Run "hyperlink_backlog_Commercial_monday"
              
          Case Range("E11").Address
              Application.Run "hyperlink_backlog_Genuine_monday"
              
          Case Range("E12").Address
              Application.Run "hyperlink_backlog_Promised_monday"
              
          Case Range("E13").Address
              Application.Run "hyperlink_backlog_Age_Profile_monday"
              
          Case Else
              Exit Sub
    End Select
Scroll_Up
End Sub

Function Scroll_Up()
    Application.ScreenUpdating = True
    Worksheets("Hyperlink").Activate
    ActiveSheet.Range("A1").Select
End Function

**rain4u** · 05-15-2011, 02:30 PM

Awesome. Looks to be working as I imagined. Thank you very much. Finally no more scrolling with mouse

Cheers
Rain

When executing macro via hyperlink, macro doesn't re-scroll window back to A1

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

When executing macro via hyperlink, macro doesn't re-scroll window back to A1

Re: When executing macro via hyperlink, macro doesn't re-scroll window back to A1

Re: When executing macro via hyperlink, macro doesn't re-scroll window back to A1

Re: When executing macro via hyperlink, macro doesn't re-scroll window back to A1

Re: When executing macro via hyperlink, macro doesn't re-scroll window back to A1

Re: When executing macro via hyperlink, macro doesn't re-scroll window back to A1

Re: When executing macro via hyperlink, macro doesn't re-scroll window back to A1

Re: When executing macro via hyperlink, macro doesn't re-scroll window back to A1

Re: When executing macro via hyperlink, macro doesn't re-scroll window back to A1

Re: When executing macro via hyperlink, macro doesn't re-scroll window back to A1

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions