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?

 

[TheJay]

Here's an updated exported version with your previous revision included:

ClipboardEMRE.xlsm

drive.google.com

 

[Larry Haydn]

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

1. First, this is how I organize my codes.
   I put each subroutine into it's own module.
   Numbering the modules lets you organize the modules in their running order.
   
   
2. I always start my modules with the GlobalVar module.
   This is used to define the global variables in an organized manner:
   
   

 

[Larry Haydn]

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.

I apologize for the late reply. I believe we are in different timezones (I am GMT +8).

1. First, this is how I organize my codes.
   I put each subroutine into it's own module.
   Numbering the modules lets you organize the modules in their running order.
   
   
2. I always start my modules with the GlobalVar module.
   This is used to define the global variables in an organized manner.
   Notice that I created the variables for the workbook and its worksheets here.
   
   
3. Next, I create a module to identify this workbook and its worksheets.
   I like to precede my module names with modXXXX - this helps me (and other programmers) to identify this as a module
   and not other type of variables, but this is strictly a personal preference.
   
   
4. Once the modIDEN module has been created, I create a Worksheet_Open module to call the modIDEN module whenever this workbook is opened.
   This module is to be placed inside the ThisWorkbook object.
   
   
5. You are then able to refer, positively, to each worksheet, such as:
   WB is this workbook (and not any other open workbook)
   wsClip is the Clipboard worksheet in this workbook
   ws10 is the Sheet10 worksheet in this workbook
   
   
   Hope this helps.