Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel 2010 vs 365 split screens/multiple workbook views

0

Hello,

My home computer is Excel 2010.  My work computer is Excel 365.

My question is about viewing multiple worksheets in Excel 365.

I had written a bunch of VBA text manipulation/looping/extraction programs for use in my department, and had organized the view to show 2 separate  (Workbook:1 & Workbook:2)  views of the same workbook.  (see image attached).  (Separate workbooks would be fine too.) 

Similar to this : https://www.teachexcel.com/excel-tutorial/1929/view-separate-worksheets-at-once-in-excel        View >  New Window > Arrange All >  Vertical

The left half is for vba buttons and short text input fields to control where/how actions are taken.  The right half is for the raw text data being acted upon.  Single column input but can be thousands of rows long, and then the other columns for extraction output purposes.

It worked great in Excel 2010....would save and reopen exactly as intended with 2 separate sheet views, and 2 separate vertical scroll bars.  (So the raw text data at the top right of the sheet could be viewed even if selecting a command from the bottom left sheet).

...but then I brought it to work.  :(

Excel 365 doesn't appear to support this functionality.

I could use the program myself as 2 separate workbooks (the closest thing I've found in 365)....but that view can't be saved within the workbook for those in the department that aren't as tech savy. (It's intended for use by those that only know the basics of excel, so "press of a button" simplicity with easy open/set-up)

Does anyone know of a fix for this view in 365?

Or...alternately...are there some other solutions I can look into?  (to put the vba buttons and text input cells elsewhere)

Originally I was thinking user forms as an alternate,  but I've only ever seen simple ones...so I'm not sure they could handle a branching command menu with vba buttons and input areas.

I'm still newer to VBA, but looking for suggestions/alternatives on what I should learn next to solve this issue.

Thank you for your suggesstions.

Kaitlyn

excel
MS Office Version: 2010
posted by: Kaitlyn_VBA (rep: 6) on Thu Jun 29, 2023 at 8:48 pm
Answer
Discuss

Discussion

Hi Kaitlyn

You tried to attach an image to your question but that isn't possible in the Forum. You could embed one in sn Excel file and upload that but it would be easier for me to see your file with code (i have both 2010 and 365).

I'll try to reply later today anyway or over the weekend
John_Ru (rep: 6142) Jun 30, '23 at 1:46 am
Thank you.  I added the file. 
It's still in the beginning stages.  It's not necessary to look at the code. 
(There's still a lot of work to be done on this, but the basic format is there)
This is more about the user interface.
What do you think the best option is for viewing the commands & input areas on the left, while still being able to scroll freely through the data portion on the right when using in 365? 

This structure works in Excel 2010,  but this set-up simply won't work in 365...so I need to find another way to accomplish it.  

Thanks for your help.

Kaitlyn
Kaitlyn_VBA (rep: 6) Jun 30, '23 at 11:25 am
Kaitlyn

Please see my Answer. It seems solid on 365 but might be a bit temperamental on 365. At worse you'll need to pick a data tab on the righthand screen.

Kindly respond to the Answer.
John_Ru (rep: 6142) Jun 30, '23 at 1:29 pm
Did that work, Kaitlyn?
John_Ru (rep: 6142) Jul 6, '23 at 6:03 am
Add to Discussion

Answers

0
Selected Answer

Kaitlyn

You can do a similar thing in 365 (via the ribbon or VBA)...

In the attached revised file, I've added the event macro below (under This Workbook) which - for both Excel 2010 and 365 - will open your workbook with the Data sheet (including control buttons) on the left and TestData1 (on the right). I've commented it for your benefit:

Private Sub Workbook_Open()
    
    Dim Scrn1 As Window, Scrn2 As Window ' left and right screens
    
    ' set second screen first (as active window)
    Worksheets("TestData1").Activate
    Set Scrn2 = ThisWorkbook.Windows(1)
    ' check if there's another window open
    If ThisWorkbook.Windows.Count = 1 Then
        ' if not, make new
        Set Scrn1 = ThisWorkbook.NewWindow
        Else
        ' if so, pick second
        Set Scrn1 = ThisWorkbook.Windows(2)
    End If
    ' swap tabs
    Scrn1.Activate
    Worksheets("Data").Activate
    ' set up side by side
    Windows.CompareSideBySideWith Scrn1.Caption
    Windows.SyncScrollingSideBySide = False
    ' split vertically,only this workbook
    Windows.Arrange ActiveWorkbook:=True, ArrangeStyle:=xlVertical

End Sub

It assumes you last saved the file with no more than 2 windows open.

Hope this works for you- if so, please remember to mark this Answer as Selected (and comment if you like or if it doesn't work for you).

posted by: John_Ru (rep: 6142) on Fri Jun 30, 2023 at 1:21 pm
Discuss

Discussion

Hi Kaitlyn- did you see my Answer? (Many people seem to miss alert emails sent over the weekend) 
John_Ru (rep: 6142) Jul 4, '23 at 1:29 am
Thank you for your help.   Sorry about the delay.  I wasn't able to check my work computer until now.  
Kaitlyn_VBA (rep: 6) Jul 6, '23 at 9:47 pm
Glad that worked for you- thanks for selecting my Answer Kaitlyn.
John_Ru (rep: 6142) Jul 7, '23 at 1:36 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login