VBA Issue when multiple workbooks open

[TheJay]

Hello there, I have multiple sheets containing "Private Sub Worksheet_Change(ByVal Target As Range)" and this is throwing an error when I have another workbook open due to "Private WithEvents oCbarEvents As CommandBars" within "ThisWorkbook". I don't want to remove any of the Worksheet_Change code as this will impact on the way my worksheets function.

Has anyone had similar experiences? Is there an easy way to resolve this?

 

[Larry Haydn]

In dealing with multiple workbooks, it is always a MUST to positively identify each workbook,
by assigning each workbook and its worksheets to variables.

For example, I always create a module to identify the workbook and its worksheets, and make
this module run in the Workbook_Open event.

First, declare the required variables in a separate module, like this:

Public myWB As Workbook
Public mySales As Worksheet
Public myInventory As Worksheet

Then create a module to identify the current workbook and worksheets within:

Sub modIdenWbWs()
     Set myWB = Activeworkbook
     Set mySales - myWB.Sheets("Sales")
     Set myInventory = myWB.Sheets("Inventory")
End Sub

Then create this module in the ThisWorkbook object:

Private Sub Workbook_Open()
     modIdenWbWs
End Sub

 

[Larry Haydn]

So everytime your workbook is opened, it will automatically identify the workbook and its worksheets.
You can use similar method to identify other workbooks that are opened by this program.

 

[TheJay]

Thank you for your advice. Could you please provide a sample worksheet set up like this? How would you deal with additional sheets? Always update the code?

 

[Larry Haydn]

Here is the sample workbook:

Google Drive: Sign-in

 

[TheJay]

Thanks Larry, that looks neat and logical. Based on what I am working on at the moment, I have gone too far to implement this and based on my experience, I think it would further confuse me. It's something for me to work on if I need to start a new project in the future. It might look easy in the stripped down version, but I have about 14 worksheets in total within the workbook in the full version.

I definitely still have a problem with my workbook, as once I've been onto the specific sheet that contains the TAB code, it brings up an error on EVERY workbook/worksheet I open afterwards unless I close all and then open up the others without the problematic one.

I've removed some of the problematic code but still getting errors:

https://drive.google.com/file/d/1yL0...ew?usp=sharing

Option Explicit

Public arr As Variant
Public strAddress As String


Public Sub ProcessTab()
Dim i As Integer
If Len(strAddress) <> 0 Then
    For i = 0 To UBound(arr)
        If arr(i) = Split(strAddress, ":")(0) Then
            If i = UBound(arr) Then
                i = 0
            Else
                i = i + 1
            End If
            Exit For
        End If
    Next
    ActiveSheet.Range(arr(i)).Select
Else
    strAddress = arr(0)
End If
End Sub


Public Sub ProcessBkTab()
Dim i As Integer
If Len(strAddress) <> 0 Then
    For i = 0 To UBound(arr)
        If arr(i) = Split(strAddress, ":")(0) Then
            If i = 0 Then
                i = UBound(arr)
            Else
                i = i - 1
            End If
            Exit For
        End If
    Next
    ActiveSheet.Range(arr(i)).Select
Else
    strAddress = arr(0)
End If
End Sub

In Sub ProcessTab, it's specifically this code that throws the error:

Run-time error '13': Type mismatch

ActiveSheet.Range(arr(i)).Select

strAddress = arr(0)

Run-time error '9': Subscript out of range

thisAddress = Split(strAddress, ":")(0)

I need to isolate the VBA to this worksheet and when the worksheet is not the active window, disable the TAB functionality and resume normal tabbing in other worksheets and workbooks.

 

[Larry Haydn]

Public Sub ProcessTab()
Dim i As Integer
'------------------------------------------------------------------------------------------------
'   Add this line to activate the appropriate worksheet
'   then your code ActiveSheet.Range(arr(i)).Select will work on the correct worksheet
'   Replace the myWorkksheetName with the actual worksheet name
' -----------------------------------------------------------------------------------------------
Sheets("myWorksheetName").Activate

If Len(strAddress) <> 0 Then
    For i = 0 To UBound(arr)
        If arr(i) = Split(strAddress, ":")(0) Then
            If i = UBound(arr) Then
                i = 0
            Else
                i = i + 1
            End If
            Exit For
        End If
    Next
    ActiveSheet.Range(arr(i)).Select
Else
    strAddress = arr(0)
End If
End Sub

 

[TheJay]

Thanks Larry, so to confirm, that will ensure the clipboard VBA will only operate within that worksheet if it's in focus? Preventing the problem I was encountering with TAB being used in other applications from trying to open the workbook to run the VBA?

If so, it's a good idea to include it in the other VBA too.

Option Explicit

Public arr As Variant
Public strAddress As String

Public Sub ProcessTab()
Dim i As Integer
Sheets("Clipboard").Activate 'Make sure this only runs when the sheet is open and in focus
If Len(strAddress) <> 0 Then
    For i = 0 To UBound(arr)
        If arr(i) = Split(strAddress, ":")(0) Then
            If i = UBound(arr) Then
                i = 0
            Else
                i = i + 1
            End If
            Exit For
        End If
    Next
    ActiveSheet.Range(arr(i)).Select
Else
    strAddress = arr(0)
End If
End Sub

Public Sub ProcessBkTab()
Sheets("Clipboard").Activate 'Make sure this only runs when the sheet is open and in focus
Dim i As Integer
If Len(strAddress) <> 0 Then
    For i = 0 To UBound(arr)
        If arr(i) = Split(strAddress, ":")(0) Then
            If i = 0 Then
                i = UBound(arr)
            Else
                i = i - 1
            End If
            Exit For
        End If
    Next
    ActiveSheet.Range(arr(i)).Select
Else
    strAddress = arr(0)
End If
End Sub

Public Function putToClipboard(ByVal theValue As Variant)
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText theValue & ""
        .PutInClipboard
    End With
    Sheet10.Range("$C$4") = theValue
End Function

Public Sub ClipOn()
Dim thisAddress As String
thisAddress = Split(strAddress, ":")(0)
With Sheet10
    .IsClipRunning = True
    ' unprotect and change the color of the "play" button to red (or you may use any color)
    .Unprotect
    .Shapes("Status").TextFrame.Characters.Text = "Copy Mode"
    .Shapes("Status").TextFrame.Characters.Font.Color = RGB(128, 134, 146)
    .Shapes("Status").Fill.ForeColor.RGB = RGB(242, 242, 242)
    .Shapes("Button 33").TextFrame.Characters.Font.Color = RGB(137, 153, 171)
    .Shapes("Button 34").TextFrame.Characters.Font.Color = vbBlack
    Sheet10.Range("C7,C8,C9,C10,C11,C13,C16,C19,C22,E7:I7,E10:I10,E13,I13,E16,E19:I19,G13,G16,I16").Interior.Color = RGB(242, 242, 242)
    Sheet10.Range("C6:C11,C12:C13,C15:C16,C18:C19,E6:I7,E9:I10,E12:E13,E15:E16,G12:G13,G15:G16,I12:I13,I15:I16,E18:I19,C21:I22").Font.Color = RGB(128, 134, 146)
    .Protect
    If Len(Trim$(.Range(thisAddress).Value & "")) Then
        Call putToClipboard(.Range(thisAddress).Value)
    End If
End With
End Sub

Public Sub ClipOff()
With Sheet10
    ' unprotect to re-instate the color of "play" button to black
    .Unprotect
    .Shapes("Status").TextFrame.Characters.Text = "Input Mode"
    .Shapes("Status").TextFrame.Characters.Font.Color = vbBlack
    .Shapes("Status").Fill.ForeColor.RGB = RGB(146, 208, 80)
    .Shapes("Button 33").TextFrame.Characters.Font.Color = vbBlack
    .Shapes("Button 34").TextFrame.Characters.Font.Color = RGB(146, 208, 80)
    Sheet10.Range("C7:C11,C13,C16,C19,C22:I22,E7:I7,E10:I10,E13,G13,I13,E16,G16,I16,E19:I19").Interior.Color = RGB(146, 208, 80)
    Sheet10.Range("C6:C11,C12:C13,C15:C16,C18:C19,E6:I7,E9:I10,E12:E13,E15:E16,G12:G13,G15:G16,I12:I13,I15:I16,E18:I19,C21:I22").Font.Color = vbBlack
    .IsClipRunning = False
    .Protect
End With
End Sub

Public Sub ClipClear()
Dim Answer As Integer
Answer = MsgBox("Are you sure you wish to clear the data and reset the form?", vbQuestion + vbYesNo + vbDefaultButton2, "Automatic Clipboard")
If Answer = vbYes Then
    Call ClipOff
'    MsgBox "Cleared"
    Sheet10.Range("C7:C11,C13,C16,C19,C22:I22,E7:I7,E10:I10,E13,I13,E16,G16,I16,E19:I19").ClearContents
    Sheet10.Range("C7:C11,C13,C16,C19,C22,E7:I7,E10:I10,E13,I13,E16,E19:I19,G13,G16,I16").Interior.Color = RGB(146, 208, 80)
    Sheet10.Range("C6:C11,C12:C13,C15:C16,C18:C19,E6:I7,E9:I10,E12:E13,E15:E16,G12:G13,G15:G16,I12:I13,I15:I16,E18:I19,C21:I22").Font.Color = vbBlack
    Sheet10.Range("A1").Select
    Sheet10.Range("C7").Select
    Sheet10.Range("$C$4") = Null
Else
    'Do nothing
End If
End Sub

Sub Help_Click()
    Dim Help As Integer
    Help = MsgBox("Software relies heavily on the Windows clipboard." & Chr(13) & Chr(13) & _
    "If you need to duplicate information to multiple accounts/properties, use this tool." & Chr(13) & Chr(13) & _
    "Type the information you need to copy, then within ""Clipboard Controls"" click """ & Chr(62) & """ and then any cell you click on will automatically be copied to the clipboard." & Chr(13) & Chr(13) & _
    "To input text, click ""||"" and when finished, click """ & Chr(62) & """ to continue copying.", _
    vbOKOnly + vbInformation, "About Automatic Clipboard")
        If Help = vbOK Then
    End If
End Sub


' In the code module named Clipboard:
'Sub TabOn()
'  MsgBox "The operator just hit <Tab>."
'  End Sub

'Sub TabBack()
'  MsgBox "The operator just hit <Tab Back>."
'End Sub

 

[Larry Haydn]

The use of the Sheets("SheetName").Activate is well and good.

However, if you have multiple workbooks open, and you inadvertantly click on another workbook,
the focus shifts to the the now "active" workbook and whichever worksheet that happens to be open,
thus causing the program to erroneously act on the (now active) worksheet.

Therefore, I always advocate assigning each workbook and their worksheets to variables as soon as they
are opened.

 

[TheJay]

I think I understand what you are saying. So, when I had that worksheet open and changed to another workbook and pressed TAB, it came up with "Run-time error '9': Subscript out of range".

I am interested in what yo are saying, I am not sure how to do it though. Your first response is clear enough, it's hard to understand how that gets implemented into the existing workbook and code.

Is it possible to demonstrate how that would work on the Clipboard workbook? Once I see a practical example on something I am familiar with, I might be able to pick it up and implement it properly.

Thanks Larry.