Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Prevent VBA written for one Excel workbook from affecting other workbooks

Posted on 2013-12-29
5
Medium Priority
?
3,952 Views
Last Modified: 2014-01-18
Hello,

With multiple workbooks open in Excel 2013, is there a way to prevent VBA code which applies to only one of the workbooks from acting on the other open workbooks?

For example, quite some time ago, an EE expert (dlmille) wrote some VBA code for me which (by pressing Ctrl+h) hides all menu, tool, & status bars in an Excel spreadsheet thereby enabling only the spreadsheet content to be displayed (Ctrl+r reverses the process).* The tree configuration and code itself are included below.

The problem I'm experiencing is that whenever the Excel file containing the code is running, it produces unwanted effects elsewhere (ie in other open spreadsheets). For example, it often causes changes in which bars are displayed in other open Excel files. Also, having other files open sometimes prevents the Formula Bar from being hidden in the coded file. Another problem when the coded file is running is that I cannot use Ctrl+h as a shortcut to open the Replace Box in non-coded spreadsheets.

The code was written back during the time when multiple workbooks could be opened within a single instance of Excel. At that time (ie with those earlier versions of Excel), none of the unwanted effects were present. My hope is that there is a way to modify the code so that it functions in Excel 2013 as it did originally (ie not be affected by other running Excel files nor have any effect on them).

Thanks

FigModule1:
Option Explicit

Sub hideItAll()

    With ActiveWindow
        .DisplayHorizontalScrollBar = False 'hide horizontal scroll bar
        .DisplayVerticalScrollBar = False 'hide vertical scroll bar
        .DisplayWorkbookTabs = False 'hide sheet tabs
        .DisplayHeadings = False 'hides row and column headings
    End With
    With Application
        'or you could do this for both scrollbars - currently commented
        .DisplayScrollBars = False
        
        .DisplayStatusBar = False 'hide status bar
        .DisplayFullScreen = False 'makes QAT, formula bar and status bar disappear while in full screen mode
        .DisplayFormulaBar = False 'hide formula bar
    End With
        
    Call Title_Hide 'Hides quite a bit more than just the title bar, so use at your option, and comment out the above that are not necessary, depending on what you're trying to do.
End Sub
Sub resetAllBack()

    With ActiveWindow
        .DisplayHorizontalScrollBar = True 'hide horizontal scroll bar
        .DisplayVerticalScrollBar = True 'hide vertical scroll bar
        .DisplayWorkbookTabs = True 'hide sheet tabs
        .DisplayHeadings = True 'hides row and column headings
    End With
    With Application
        'or you could do this for both scrollbars - currently commented
        .DisplayScrollBars = True
        
        .DisplayStatusBar = True 'hide status bar
        .DisplayFullScreen = False 'makes QAT, formula bar and status bar disappear while in full screen mode
        .DisplayFormulaBar = True 'hide formula bar
    End With
    
    Call Title_Show 'Shows quite a bit more than just the title bar, so use at your option, and comment out the above that are not necessary, depending on what you're trying to do.
End Sub

Open in new window

Module2:
Option Explicit
'Source:http://www.eileenslounge.com/viewtopic.php?f=27&t=1986
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
  (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
  (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
  (ByVal hwnd As Long, ByVal nIndex As Long) As Long

Private Const GWL_STYLE = (-16)
Private Const WS_CAPTION = &HC00000
Private Const WS_MAXIMIZEBOX = &H10000
Private Const WS_MINIMIZEBOX = &H20000
Private Const WS_SYSMENU = &H80000

Private Declare Function SetWindowPos Lib "user32" _
  (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
  ByVal x As Long, ByVal y As Long, ByVal cx As Long, _
  ByVal cy As Long, ByVal wFlags As Long) As Long

Private Enum ESetWindowPosStyles
  SWP_SHOWWINDOW = &H40
  SWP_HIDEWINDOW = &H80
  SWP_FRAMECHANGED = &H20
  SWP_NOACTIVATE = &H10
  SWP_NOCOPYBITS = &H100
  SWP_NOMOVE = &H2
  SWP_NOOWNERZORDER = &H200
  SWP_NOREDRAW = &H8
  SWP_NOREPOSITION = SWP_NOOWNERZORDER
  SWP_NOSIZE = &H1
  SWP_NOZORDER = &H4
  SWP_DRAWFRAME = SWP_FRAMECHANGED
  HWND_NOTOPMOST = -2
End Enum

Private Declare Function GetWindowRect Lib "user32" _
  (ByVal hwnd As Long, lpRect As RECT) As Long

Private Type RECT
  Left As Long
  Top As Long
  Right As Long
  Bottom As Long
End Type

Sub Title_Show()
  ShowTitleBar True
End Sub

Sub Title_Hide()
  ShowTitleBar False
End Sub

Sub ShowTitleBar(bShow As Boolean)
  Dim lStyle As Long
  Dim tRect As RECT
  Dim xlHnd As Long

  xlHnd = Application.hwnd

  '// Get the window's position:
  GetWindowRect xlHnd, tRect

  '// Show the Title bar ?
  If Not bShow Then
    lStyle = GetWindowLong(xlHnd, GWL_STYLE)
    lStyle = lStyle And Not WS_SYSMENU
    lStyle = lStyle And Not WS_MAXIMIZEBOX
    lStyle = lStyle And Not WS_MINIMIZEBOX
    lStyle = lStyle And Not WS_CAPTION
  Else
    lStyle = GetWindowLong(xlHnd, GWL_STYLE)
    lStyle = lStyle Or WS_SYSMENU
    lStyle = lStyle Or WS_MAXIMIZEBOX
    lStyle = lStyle Or WS_MINIMIZEBOX
    lStyle = lStyle Or WS_CAPTION
  End If

  SetWindowLong xlHnd, GWL_STYLE, lStyle

  Application.DisplayFullScreen = Not bShow

  '// Ensure the style is set and makes the xlwindow the
  '// same size, regardless of the title bar.
  SetWindowPos xlHnd, 0, tRect.Left, tRect.Top, tRect.Right - tRect.Left, _
    tRect.Bottom - tRect.Top, SWP_NOREPOSITION Or SWP_NOZORDER Or SWP_FRAMECHANGED
End Sub

Open in new window

* Original thread is here: View nothing but cells in Excel spreadsheet
0
Comment
Question by:Steve_Brady
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 total points
ID: 39745230
Insert this as the first line in the 2 subs hideItAll and resetAllBack

If ActiveWorkbook.Name <> ThisWorkbook.Name Then End

Then the hide and reset code will only run when the workbook with the code is active.

Change your shortcuts to Ctrl+Shift+h and Ctrl+Shift+r, then you can use the normal Ctrl+h to display the replace box.
It is generally better to use Ctrl+Shift+Letter, than Ctrl+Letter.
That avoids conflict with the buildin shortcuts.
0
 

Author Comment

by:Steve_Brady
ID: 39748646
Thanks for the response.

After inserting the line you included in the way you described, pressing Ctrl+h while in non-coded spreadsheets did nothing at all. I tried it a couple of times in different spreadsheets but it just seems to eliminate Ctrl+h as a shortcut. By the way, Ctrl+f continued to work just fine.
0
 
LVL 23

Expert Comment

by:Ejgil Hedegaard
ID: 39750292
Ctrl+h still runs for every workbook but due to the line the macro is aborted, and do nothing (End stops the sub), when activated when another workbook is active.
But when Ctrl+h is set to activate the macro, it overrides the normal build in use of Ctrl+h, ie. display the replace box.
That is why I recommend to change macro activation to Ctrl+Shift+h.
Then Ctrl+h, display the replace box, will also work in the workbook with the hide sub.
0
 

Author Comment

by:Steve_Brady
ID: 39752954
OK, thank you for that tip.

Do you know of any solutions for solving the problems I described with the menu/tools/status bars?
0
 
LVL 23

Expert Comment

by:Ejgil Hedegaard
ID: 39756524
Some of the properties refers to Application and that affects all workbooks.
I don't have a solution to prevent that, sorry.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

661 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