Changing Window Within Workbook

SinginWes

New Member
Joined
Aug 17, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Within a Workbook, I have a window (Sheet2) that needs to be active for entering raw data and a window (Sheet1) that will be up on a monitor, showing live, manipulated data. In order to keep the current line item on the screen I have it run Application.Goto Target.EntireRow on Sheet1, which selects and scrolls to the most recent entry row on my report sheet in its open window and does what I need it to do. The problem is, I need to get back to where I was in Sheet2 to continue making entries. My only interface is a scanner, so I can't alt-tab (it's ascii based so I can't scan an alt-tab either). I have tried SendKeys to alt-tab back, but it only works about 95% of the time, which won't work (I have tried pausing and DoEvents to assist the SendKeys, but it still consistently fails). Since it's within the same application and the same workbook, none of the options I have found apply. When I try to Activate a worksheet, it changes the worksheet in the active window, instead of going to the open window of that sheet like GoTo does in the first move. My current thought is using Application.GoTo to get back, targeting the first empty cell in the first column of Sheet2. I'm still very new to VBA, so I wouldn't know how to accomplish this. I appreciate any help you can provide in coding this idea or if you have any easier ways to accomplish this, that would be great too!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,670
Office Version
  1. 2010
Platform
  1. Windows
Have you tried just activating the sheet1 window momentarily and swapping straight back to the sheet 2 window using code like this:
VBA Code:
    Windows("Book1:1").Activate
    Windows("Book1:2").Activate
 

SinginWes

New Member
Joined
Aug 17, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Have you tried just activating the sheet1 window momentarily and swapping straight back to the sheet 2 window using code like this:
VBA Code:
    Windows("Book1:1").Activate
    Windows("Book1:2").Activate
Whenever I try this it gives me a subscript out of range error.
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,670
Office Version
  1. 2010
Platform
  1. Windows
you do need to need to edit the "Book1:1" to what ever your workbook is called, then 1 and 2 after to colon signifies which window they are
 

SinginWes

New Member
Joined
Aug 17, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

you do need to need to edit the "Book1:1" to what ever your workbook is called, then 1 and 2 after to colon signifies which window they are
Yes, I tried with the updated workbook name, even renamed it for simplicity sake, and I still get that error.
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,670
Office Version
  1. 2010
Platform
  1. Windows
Have you got two windows open one with sheet 1 in it and one with sheet 2 in it, because this is what this depends on.
 

SinginWes

New Member
Joined
Aug 17, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Have you got two windows open one with sheet 1 in it and one with sheet 2 in it, because this is what this depends on.
Yes, I have two windows open, one on sheet1 and the second on sheet2. Windows("Name:1").Activate Windows("Name.xlsm:1").Activate, etc. has not been successful yet. Does it matter where this text is? My existing code is in the worksheets.
 

SinginWes

New Member
Joined
Aug 17, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Have you got two windows open one with sheet 1 in it and one with sheet 2 in it, because this is what this depends on.
Any alternative ideas for scrolling in Sheet1 while keeping Sheet2 active? Then I wouldn't have to switch back and forth at all.
 

SinginWes

New Member
Joined
Aug 17, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Have you got two windows open one with sheet 1 in it and one with sheet 2 in it, because this is what this depends on.
Ended up using ActiveWindow.ActivatePrevious which is working like a charm! Thanks for the help.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,517
Messages
5,602,127
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top