?
Solved

How is an Excel 'custom view' generated using VBA?

Posted on 2011-05-04
6
Medium Priority
?
544 Views
Last Modified: 2012-08-14
What's the VBA code (Excel 2007) to generate a custom view "View1" that will display:
- two side-by-side windows that fill up the Excel panel
- left window shows "Sheet1"
- right window shows "Sheet2"

Thanks, --Andres
0
Comment
Question by:AndresHernando
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 35696473
Fast and easy?
- go to the macro menu and start recording
- go through all the steps you need to achieve your goal
- stop the recording of the macro
- view the code assotiated to macro you've just recorded
0
 

Author Comment

by:AndresHernando
ID: 35697828
I tried that. However, when I tried modifying the recorded code I ran into problems.
Macro recorder uses references to the two windows as    

   Windows("Filename.xlsm:1").Activate  

'// where Filename is the current name of the workbook.  I need to use code like

    Windows("vOldWindow").Activate   <==but this bugs out with 'subscript out of range error'


This is my code:

Sub Macro1()
   
    Dim vThisWorkbook As Workbook
    Dim vNewWindow As Window
    Dim vOldWindow As Window
   
    Set vThisWorkbook = ActiveWorkbook
    Set vOldWindow = ActiveWindow
   
    ActiveWindow.NewWindow
    ActiveWindow.WindowState = xlNormal
    Set vNewWindow = ActiveWindow
   
    With ActiveWindow
        .Top = 2.5
        .Left = 580
    End With
    With ActiveWindow
        .Width = 628.5
        .Height = 504
    End With
    ActiveWindow.WindowState = xlNormal
 
   Windows(vOldWindow).Activate
    ActiveWindow.WindowState = xlNormal
   
    Windows(vOldWindow).Activate
    With ActiveWindow
        .Top = 2.5
        .Left = 617.5
    End With
    With ActiveWindow
        .Width = 591
        .Height = 504
    End With

 
End Sub
0
 
LVL 18

Expert Comment

by:x-men
ID: 35698348
use references to the sheet numbers. On the left pane of the vb editor, you'll have some tips on how to get those references
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35729865
Sub splitwins()
    Sheets("Sheet2").Select
    ActiveWorkbook.NewWindow
    Sheets("Sheet1").Select
    ActiveWorkbook.Windows.Arrange xlArrangeStyleVertical
End Sub

Open in new window


should do it.
0
 

Author Closing Comment

by:AndresHernando
ID: 35730736
Good job.  Thanks Rorya.  --Andres
0
 

Author Comment

by:AndresHernando
ID: 35730751
Thanks for the suggestions X-men.  I ended up giving the pts to Rorya for his code.

Thanks to both!  --Andres
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question