Solved

Write VBA code for one Excel workbook but avoid affecting other workbooks

Posted on 2014-01-02
7
1,350 Views
Last Modified: 2014-01-21
Hello,

When using VBA code written for one specific Excel 2013 spreadsheet/workbook, what can be done to prevent it from affecting any other concurrently-running Excel workbooks?

Thanks
0
Comment
Question by:Steve_Brady
  • 3
  • 2
  • 2
7 Comments
 
LVL 20

Expert Comment

by:dsacker
Comment Utility
Preface everything either with ThisWorkbook, or define a variable and set it to ThisWorkbook. Here is an example of a small subroutine that will only update the workbook of the code within which it is written, without affecting any other open workbooks.

Option Explicit
Public Sub RunThisOnly()
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set wb = ThisWorkbook
    Set ws = wb.Sheets(1)
    ws.Range("A2").Value = "I will only affect the workbook within which this code is running."
    ws.Range("A3").Value = Now()
    Set ws = Nothing
    Set wb = Nothing
End Sub

Open in new window

This example assumes you want to write to the first sheet in that workbook.
0
 

Author Comment

by:Steve_Brady
Comment Utility
>>dsacker
"Preface everything either with ThisWorkbook, or define a variable and set it to ThisWorkbook."
Thank you for the prompt reply. I should have mentioned this before but I have only very scanty knowledge of VBA. I did try inserting "ThisWorkbook" at the beginning of the code but got the following error:

Fig 1The specific code giving me problems was written quite some time ago by an EE expert (dlmille). By pressing a hotkey, it hides all menu, tool, & status bars in a specific Excel spreadsheet thereby enabling only the spreadsheet content to be displayed (a different hotkey 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 workbooks). 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.

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 again.

Fig 2Module1
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
 
LVL 20

Expert Comment

by:dsacker
Comment Utility
In your VBA Tools, Options, General Tab, change to "Break on All Errors", so that you can see which line is causing the error. My guess is  you may not be referencing the worksheet within the workbook or vice versa -  you must be using both ThisWorkbook, or a variable set to it, and ThisWorkbook.Sheets("SomeSheet"), or a variable set to that. Notice my example above, I set both wb and ws accordingly.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 80

Expert Comment

by:byundt
Comment Utility
Steve,
I suspect that you have a Workbook_Open sub that calls hideItAll and a Workbook_BeforeClose sub that calls ResetAllBack.

If you added a Workbook_Activate sub that did what the Workbook_Open sub does and a Workbook_Deactivate sub that mimics the Workbook_BeforeClose, I suspect that would solve your problem.

Could you post a workbook that includes all the code? That way I could added the needed bits and test the solution.

Brad
0
 

Author Comment

by:Steve_Brady
Comment Utility
Thanks Brad,

The file, which is attached, is titled "__Scripture pop-up…date.xlsm".

The objective is to have a small window which pops up at user-defined intervals to display a passage randomly selected from a predefined list of scriptural references. The functionality utilizes VBA, Excel formulas and 2 AutoHotkey (AHK) scripts.

The VBA is intended to remove all bars (title, tool, menu, sheet tabs, status, etc.) so the pop-up window appears as a simple box with no resemblance to Excel or any another app.

The Excel file contains the following named ranges & formulas:

• Source  (=$C$23:$E$1000)
• Refs  (=$C$23:$C$1000)
• F3  (=INDEX(Source,MATCH(C20,Refs,0),3))
• F16  (="~ "&INDEX(Source,MATCH(C20,Refs,0),2))
• C20  (=RANDBETWEEN(C23,C21))
• C21  (=MAX(C23:C1000))
• C24 copied down to C1000  (=IF(D24<>"",OFFSET(C24,-1,0)+1,"`"))

The first AHK script (which defines the repeat interval and window position) is as follows:
AppsKey & z::	; hotkey
Loop, 100	; arbitrary large number	
{	
  SetTitleMatchMode, 2	
  WinActivate, Scripture pop-up	
  WinWait, Scripture pop-up	
  WinMove, Scripture pop-up,, 610, 415, 685, 330	
  Send ^{Home}	
  Send {Right 12}	
  Send {Down 14}	; positions selected cell	
  Send {Delete}	; causes =RANDBETWEEN() to cycle	
  Sleep, 1800000	; waits 30 min	
}	
Return

Open in new window

The second AHK script minimizes the window:
AppsKey & x::	; hotkey
  SetTitleMatchMode, 2	
  WinMinimize, Scripture pop-up	
Return	

Open in new window

I assume you saw the link for the original thread which I posted above but in case not, here it is again:

  View nothing but cells in Excel spreadsheet

Thanks again
--Scripture-pop-up-2014-01-04.xlsm
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
I'm not familiar with the AHK script that you mentioned, but got the ribbon hiding working while the workbook was active (and only while it was active) using the following code in ThisWorkbook:
Private Sub Workbook_Activate()
hideItAll
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
resetAllBack
End Sub

Private Sub Workbook_Deactivate()
resetAllBack
End Sub

Private Sub Workbook_Open()
hideItAll
End Sub

Open in new window

Scripture-pop-up-2014-01-04--Q28.xlsm
0
 

Author Closing Comment

by:Steve_Brady
Comment Utility
Thanks
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now