Create new window and navigate in that window to a particular cell

[sriche01]

Hello all,

I am trying to execute some code, part of which should be opening a new window and navigating to a particular location.

I was trying:

            ActiveWindow.NewWindow.Activate
            Application.Goto Reference:=ActiveWorkbook.Worksheets("Profile Path").Range("D3"), Scroll:=False

all it does is open a new window, then goes back to window 1 and navigates. The problem is window 1 contains all the view settings like freeze panes. The window I am trying to create is to a sheet that gives a snapshot - kind of a popup if you will - that the user can then close that window after viewing the snapshot and they would be where they originated. For some reason, the Goto method always wants to work in window 1. Can anyone help me?
Thank you!

 

[JLGWhiz]

Instead of this:

Application.Goto Reference:=ActiveWorkbook.Worksheets("Profile Path").Range("D3"), Scroll:=False

Try this:

ActiveWindow.Range("D3").Select

 

[sriche01]

ActiveWindow.Range(rng).Select

this produces an error "Object doesn't support this property or method." rng is a variable for

Set rng = ActiveWorkbook.Worksheets("Profile Path").Range("D3")

I also did your code with "D3" and it still produced the error.

 

[JLGWhiz]

ActiveWindow.Range(rng).Select

this produces an error "Object doesn't support this property or method." rng is a variable for

Set rng = ActiveWorkbook.Worksheets("Profile Path").Range("D3")

I also did your code with "D3" and it still produced the error.

If you Dim rng As Range (and apparently you have since you use Set to initialize it) Then

rng.Select

would be the correct syntax. To get the cell selected in the new window, vba has to specify which window is the active one. When you create a new window, it automatically becomes the activer one until you specifically activate another one. But your original code used a different syntax for the GoTo statement which ignores the Windows object entirely and refers directly to the sheet name and range. It would be better if you remained consistent in how you refer to the objects you want to work with.

 

[sriche01]

Unfortunately, this produced a different error.

"Select method of range class failed"

Thanks for your continued help. I'm still working on it.

 

[sriche01]

If it helps, this is the entire code in the sheet code where the range "Sales_Plan_Item" is a named range.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim itm As Range
Dim rng As Range
If Selection.Count = 1 Then
        If Not Intersect(Target, Range("Sales_Plan_Item")) Is Nothing Then
            'somewhere in here I'd like a command to create a new window in which to run the
            'code below
            Set itm = Application.Selection
            Set rng = ActiveWorkbook.Worksheets("Profile Path").Range("D3")
            rng.Value = itm.Value
            ActiveWindow.NewWindow.Activate
            rng.Select
        End If
    End If
End Sub

 

[JLGWhiz]

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim itm As Range
Dim rng As Range
If Selection.Count = 1 Then
        If Not Intersect(Target, Range("Sales_Plan_Item")) Is Nothing Then
            'somewhere in here I'd like a command to create a new window in which to run the
            'code below
            Set itm = Application.Selection
            Set rng = ActiveWorkbook.Worksheets("Profile Path").Range("D3")
            rng.Value = itm.Value
            ActiveWindow.NewWindow.Activate
            rng.Select
        End If
    End If
End Sub

I just ran the code above and it created a new window and selected Range("D3") in the new window, while leaving the original cell selected in the old window. So I don't know what is causing your system to produce the error you are getting. You can check the sheet name spelling, extra spaces, characters, etc. If the name used in the code is not an exact match to the name on the sheet name tab it would produce that error. But the code is valid otherwise.

 

[sriche01]

I think the problem must lie in navigating to a different sheet in the new window. It all starts with a sheet called "Sales Plan". When one of the cells in the named range "Sales_Plan_Item" is selected, the code activates and changes a value in rng to the value of the cell selected as per this part of the code:

            Set itm = Application.Selection
            Set rng = ActiveWorkbook.Worksheets("Profile Path").Range("D3")
            rng.Value = itm.Value

That part works great.
And it works to go to a cell in the new window if it is in the Sales Plan tab.

But I need it to navigate to a different worksheet called "Profile Path" in the new window.

I tried doing this part of a code in a new module and calling it from the sheet code where I had been working. Still not working to navigate from the original worksheet. Still trying different stuff...

 

[sriche01]

So I had a different approach...

I thought maybe I could use the workbook.followhyperlink method as in

Dim adrs As String
adrs = ActiveWorkbook.FullNameURLEncoded
ActiveWorkbook.FollowHyperlink Address:=adrs, SubAddress:=rng, NewWindow:=True

But that produces another error saying invalid call or argument. Anything obvious I'm doing wrong here?

 

[JLGWhiz]

Why are you using the NewWindow method? Why not just use the difrferent sheets to do what you want to do? I guess I don't understand the overall strategy of the objective. But creating a new window doesn't do anything for you navigation wise. In fact it can be a complication to navigation because you will have two sheets with the same name floating around. If you are trying to use worksheet events to trigger changes from one sheet to another, it can be a dangerous scenario, resulting in perpetural loops and possible corruption of a file. You should think this through carefully.