• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trying to localise code in a module for tabbing exclusively in one worksheet and struggling

V

VBXL

Member
I have a workbook that contains code to enable the tabbing order to be set and also to enable copying of content from a worksheet. The problem I am having is that it's not isolated to the worksheet. It's not even isolated to the workbook. If any other worksheet or workbook is already open or opened whilst this one is still active, it disables the ability to TAB in all of them/generated errors.

I am not an expert and am stuck at this point trying to figure out how I restrict the tabbing/copying behaviour specifically to the one worksheet.

I have a example file I can provide for you to see. Switch to a blank sheet with no code and even then, you'll see TAB has been hijacked.

I hope someone can help me, even though I doubt this is straightforward. I really want to learn and it's thanks to you that I can, by seeing how things change and understanding what they do.

docs.google.com

Example Stripped (Buggy).xlsm

docs.google.com docs.google.com

Thank you so much.
 
I'm not sure what I'm looking at when I click on your attached. But just trying to figure out your description, let's start with one question and I'll go on from there: What do you mean by "tabbing order"? When I'm in an Excel worksheet, the <Tab> key moves the selection one cell to the right; I don't think you have a function that modifies that behavior. I can get from one worksheet to the next by hitting <Ctrl-Down> or <Ctrl-Up>, but there's no "Tab" key involved there so you probably aren't talking about worksheet order either. <Ctrl-Tab> moves me from one Excel workbook to the next; maybe you're talking about that? Or are you talking about the fields in a user form?
 
Thanks for your reply.

If you try tabbing through the "Clipboard" worksheet, you will see that the order of cell tabbing is not left to right, top to bottom. It is set by the VBA. The problem is, it should only be active on "Clipboard". If you try tabbing on "Sheet2", you will see that it doesn't work like normal because it's still calling some of the VBA.

Within ThisWorkbook:

Code: 
Option Explicit

Private Sub Workbook_Open()
'Initialise the array
    Call ClipOff
'Make selection on clipboard
    With Sheet10
'    Clipboard.strAddress = "$C$7"
'    Range(Clipboard.strAddress).Select
'Insert text into Clipboard
    .Unprotect
    .Shapes("Status").TextFrame.Characters.Text = "Input Mode"
    .Protect
    End With
Application.EnableEvents = True
'Control selection made using TAB
    Application.OnKey "{TAB}", "Clipboard.ProcessTab"
    Application.OnKey "+{TAB}", "Clipboard.ProcessBkTab"
'Cells included in array
    Clipboard.arr = Array("$C$7", "$C$8", "$C$9", "$C$10", "$C$11", "$C$13", "$E$7", "$E$10", "$E$13", "$G$13", "$I$13", "$E$16", "$G$16", "$I$16", "$C$16", "$C$19", "$E$19", "$C$22")
Application.EnableEvents = False
'Add words to cells in specified worksheets and set permissions
    Dim wks As Worksheet
    For Each wks In Worksheets
        If wks.Name = "Property Numbering" Then
            wks.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            wks.Range("C14,C8").ClearContents
            wks.Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"
            wks.Range("C14,C8").Value = "'Choose"
        ElseIf wks.Name = "VO Areas" Then
            wks.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            wks.Range("C4").ClearContents
            wks.Range("C4").Value = "'Choose"
        Else
            wks.Protect UserInterFaceOnly:=True
        End If
    Next
Application.EnableEvents = True
End Sub

Clipboard Sheet:

Code: 
Option Explicit

Public IsClipRunning As Boolean

Private Sub Worksheet_Activate()
   With ActiveWindow
        .DisplayFormulas = False
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = True
    End With
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim thisAddress As String
    thisAddress = Split(Target.Address, ":")(0)
    Clipboard.strAddress = Target.Address
    If IsClipRunning Then
        If Len(Trim$(Sheet10.Range(thisAddress).Value & "")) Then
            putToClipboard Sheet10.Range(thisAddress).Value
        End If
    End If
End Sub

Clipboard Module:

Code: 
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

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

Specifically:

Code: 
Application.EnableEvents = True
'Control selection made using TAB
    Application.OnKey "{TAB}", "Clipboard.ProcessTab"
    Application.OnKey "+{TAB}", "Clipboard.ProcessBkTab"
'Cells included in array
    Clipboard.arr = Array("$C$7", "$C$8", "$C$9", "$C$10", "$C$11", "$C$13", "$E$7", "$E$10", "$E$13", "$G$13", "$I$13", "$E$16", "$G$16", "$I$16", "$C$16", "$C$19", "$E$19", "$C$22")
Application.EnableEvents = False

I think perhaps part of the solution is here: https://bettersolutions.com/excel/macros/application-onkey.htm but not sure how to enable it on "Clipboard" and disable it on all other workbooks and worksheets.
 
Last edited:
Well, lookee here! I wasn't aware of the Application.OnKey method; thanks for pointing it out to me!

Without looking at the ProcessTab and ProcessBkTab procedures, what I see is that you're setting OnKey when the workbook is opened, and since it is a method of the Application class it applies to all worksheets. From what you say, you want that feature to be active only on the Clipboard worksheet—to put it another way, only when Clipboard is the active worksheet. I've never fooled with OnKey before, but it seems to me what you need is this: 1) Move the statements invoke OnKey from Workbook_Open to Clipboard.Worksheet_Activate; this will cause the behavior to begin when you activate Clipboard. 2) Create a new procedure in the code module for the Clipboard worksheet named Worksheet_Deactivate; this procedure would automatically run, as you no doubt already see, whenever you move away from the Clipboard worksheet. There you'd turn off the OnKey feature for <Tab>.
 
That's exactly what I think I need to do! I tried moving the code as suggested and it doesn't seem to work.

Clipboard worksheet:

Code: 
Option Explicit

Public IsClipRunning As Boolean

Private Sub Worksheet_Activate()
   With ActiveWindow
        .DisplayFormulas = False
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = True
    End With
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim thisAddress As String
    thisAddress = Split(Target.Address, ":")(0)
    Clipboard.strAddress = Target.Address
    If IsClipRunning Then
        If Len(Trim$(Sheet10.Range(thisAddress).Value & "")) Then
            putToClipboard Sheet10.Range(thisAddress).Value
        End If
    End If

Call ClipOff
If ActiveSheet = "Clipboard" Then
'Control selection made using TAB
    Application.OnKey "{TAB}", "Clipboard.ProcessTab"
    Application.OnKey "+{TAB}", "Clipboard.ProcessBkTab"
'Cells included in array
    Clipboard.arr = Array("$C$7", "$C$8", "$C$9", "$C$10", "$C$11", "$C$13", "$E$7", "$E$10", "$E$13", "$G$13", "$I$13", "$E$16", "$G$16", "$I$16", "$C$16", "$C$19", "$E$19", "$C$22")
Else
    Application.OnKey "{TAB}", ""
    Application.OnKey "+{TAB}", ""
End If
End Sub

ThisWorkbook:

Code: 
Option Explicit

Private Sub Workbook_Open()
'Initialise the array
    Call ClipOff
'Make selection on clipboard
    With Sheet10
'Insert text into Clipboard
    .Unprotect
    .Shapes("Status").TextFrame.Characters.Text = "Input Mode"
    .Protect
    End With
Application.EnableEvents = False
'Add words to cells in specified worksheets and set permissions
    Dim wks As Worksheet
    For Each wks In Worksheets
        If wks.Name = "Property Numbering" Then
            wks.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            wks.Range("C14,C8").ClearContents
            wks.Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"
            wks.Range("C14,C8").Value = "'Choose"
        ElseIf wks.Name = "VO Areas" Then
            wks.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            wks.Range("C4").ClearContents
            wks.Range("C4").Value = "'Choose"
        Else
            wks.Protect UserInterFaceOnly:=True
        End If
    Next
Application.EnableEvents = True
End Sub
 
Last edited:
If that code is the latest version, I don't think you did what I had in mind. I may have missed something, but what I see is this:

1) You removed the OnKey invocation from ThisWorkbook.Workbook_Open, alright, but you moved it to Clipboard.Worksheet_SelectionChange. I'm not sure I've ever had cause to use that event, but I'm pretty sure it runs only when you move the cursor, right? And it runs every time you move the cursor, invoking OnKey again and again every time the focus move from one cell to another. For my money, you want it in Worksheet_Activate of the same module; there it'll run as soon as you move to the Clipboard sheet (instead of only after you move the cursor inside that sheet), and it'll run only once each time the Clipboard sheet gets the focus.

(And by the way, even in the Worksheet_SelectionChange procedure there's no need for that If statement checking to be sure that Clipboard is the active sheet; this procedure runs only when the operator is looking at Clipboard, because, after all, it's in the Clipboard.Worksheet_SelectionChange event.)

2) You didn't do the other part: You need to create a Clipboard.Worksheet_Deactivate procedure (which runs each time the operator moves away from the Clipboard sheet) containing statements that turn off the OnKey assignment. Otherwise the OnKey assignments remain in effect all the time, which no doubt is what you're seeing.
 
Apologies, I hadn't updated as described. I have now but behaviour seems to be the same? I've updated the Excel file in the link.

ThisWorkbook:
Code: 
Option Explicit

Private Sub Workbook_Open()
'Initialise the array
'    Call ClipOff
'Make selection on clipboard
    With Sheet10
'Insert text into Clipboard
    .Unprotect
    .Shapes("Status").TextFrame.Characters.Text = "Input Mode"
    .Protect
    End With
Application.EnableEvents = False
'Add words to cells in specified worksheets and set permissions
    Dim wks As Worksheet
    For Each wks In Worksheets
        If wks.Name = "Property Numbering" Then
            wks.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            wks.Range("C14,C8").ClearContents
            wks.Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"
            wks.Range("C14,C8").Value = "'Choose"
        ElseIf wks.Name = "VO Areas" Then
            wks.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            wks.Range("C4").ClearContents
            wks.Range("C4").Value = "'Choose"
        Else
            wks.Protect UserInterFaceOnly:=True
        End If
    Next
Application.EnableEvents = True
End Sub

Clipboard Workbook:
Code: 
Option Explicit

Public IsClipRunning As Boolean

Private Sub Worksheet_Activate()
   With ActiveWindow
        .DisplayFormulas = False
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = True
    End With
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
Call ClipOff
'Control selection made using TAB
    Application.OnKey "{TAB}", "Clipboard.ProcessTab"
    Application.OnKey "+{TAB}", "Clipboard.ProcessBkTab"
'Cells included in array
    Clipboard.arr = Array("$C$7", "$C$8", "$C$9", "$C$10", "$C$11", "$C$13", "$E$7", "$E$10", "$E$13", "$G$13", "$I$13", "$E$16", "$G$16", "$I$16", "$C$16", "$C$19", "$E$19", "$C$22")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim thisAddress As String
    thisAddress = Split(Target.Address, ":")(0)
    Clipboard.strAddress = Target.Address
    If IsClipRunning Then
        If Len(Trim$(Sheet10.Range(thisAddress).Value & "")) Then
            putToClipboard Sheet10.Range(thisAddress).Value
        End If
    End If
End Sub

Private Sub Worksheet_Deactivate()
    Call ClipOff
    Application.OnKey "{TAB}", ""
    Application.OnKey "+{TAB}", ""
End Sub

Not sure why it's not working.
 
Last edited:
"Same behavior", I take it, means the special <Tab> behavior is in effect no matter which worksheet is active? Well, you seem to be doing what I would have said is needed. As I said before, I've never fooled with OnKey, so I'd have to experiment to see whether there are any strange on undocumented (or merely misunderstood) things to know about it.

For now, the only suggestion I have is this: When Excel is behaving in a way I don't understand, I sometimes (if I'm desperate) simplify my code, step by step cutting out extraneous parts and retesting, until it starts behaving as I think it should. Then I know that something is wrong with the last part I cut out. (You will of course keep the original code intact somewhere; you don't want to have to write it all again from scratch.) Or, starting from the other direction, make the simplest possible program, maybe something like this:
Code: 
' In ThisWorkbook; nothing

' In the code module for the worksheet Clipboard:
Sub Worksheet_Activate()
  MsgBox "Entering Clipboard." 'this message confirms that Worksheet_Activate is running
  Application.OnKey "<TAB>", "TabOn"
  End Sub

Sub Worksheet_Deactivate()
  MsgBox "Leaving Clipboard." 'confirms this routine is running
  Application.OnKey "<TAB>"
  End Sub

' In the code module named Clipboard:
Sub TabOn()
  MsgBox "The operator just hit <Tab>."
  End Sub
Hey, wait a minute, is there any possibility that somehow EnableEvents is turned off somewhere?

And one other discrepancy I see: Your code for turning off the special <Tab> behavior looks like this:
Code: 
Application.OnKey "{TAB}", ""
But the documentation says just omit the second argument, rather than feed it an empty string. You might try that to see whether it's the difference.
 
Thank you, that's helpful and confirms that the deactivate sub is running.

Code: 
Option Explicit

Public IsClipRunning As Boolean

Private Sub Worksheet_Activate()
   With ActiveWindow
        .DisplayFormulas = False
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = True
    End With
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
Call ClipOff
'Control selection made using TAB
    Application.OnKey "{TAB}", "Clipboard.ProcessTab"
    Application.OnKey "+{TAB}", "Clipboard.ProcessBkTab"
'Cells included in array
    Clipboard.arr = Array("$C$7", "$C$8", "$C$9", "$C$10", "$C$11", "$C$13", "$E$7", "$E$10", "$E$13", "$G$13", "$I$13", "$E$16", "$G$16", "$I$16", "$C$16", "$C$19", "$E$19", "$C$22")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim thisAddress As String
    thisAddress = Split(Target.Address, ":")(0)
    Clipboard.strAddress = Target.Address
    If IsClipRunning Then
        If Len(Trim$(Sheet10.Range(thisAddress).Value & "")) Then
            putToClipboard Sheet10.Range(thisAddress).Value
        End If
    End If
End Sub

Private Sub Worksheet_Deactivate()
Application.EnableEvents = True
'    Call ClipOff
    Application.OnKey "{TAB}"
    Application.OnKey "+{TAB}"
    MsgBox "Left Clipboard."
End Sub

Yet tab seems to be disabled still on anything else?
 
I finally gave up and created that minimum code I suggested above:
Code: 
' In the code module for the worksheet Clipboard:
Sub Worksheet_Activate()
  MsgBox "Entering Clipboard." 'this message confirms that Worksheet_Activate is running
  Application.OnKey "{TAB}", "TabOn"
  End Sub

Sub Worksheet_Deactivate()
  MsgBox "Leaving Clipboard." 'confirms this routine is running
  Application.OnKey "{TAB}"
  End Sub

' In a separate code module:
Sub TabOn()
  MsgBox "The operator just hit <Tab>."
  End Sub
At first it didn't work because I used angle brackets in the OnKey assignments, purely out of thoughtless habit. Once I corrected them to curly braces, it works as you might expect: The MsgBox appears whenever I change worksheets—the <Tab> key displays a MsgBox (and does nothing else) in the target worksheet—in the other worksheet it moves the selection right one column in the normal way.

The only thing I know how to suggest now is to start with a minimum program, either mine or something like it, make sure it works as advertised, and then gradually build it up with the other statements in your modules until it stops working. At that point you'll have some idea of which statement(s) are causing the problem, and you can go on from there. Sorry, until I wake up in the middle of the night with the answer, I'm out of ideas.
 
Interesting, in it's most basic form, without assigning the TAB order, it seems to work in the text workbook:

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).
drive.google.com
 
Okay, I've stripped it down and it seems to work. The only issue at the moment is that the tab array doesn't start to work until switch to another sheet and then switch back, which is why some of the code was in the Worksheet Open event.

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).
drive.google.com

It doesn't look as though Worksheet Activate triggers unless switching worksheets, and that's a problem in this instance. So, I need to figure out a way of making it work.
 
I think there is a bug with Worksheet Activate when the workbook opens. Does anyone know about this/workaround?
 
Ah, found this code:

Code: 
Private Sub Workbook_Open()
    ' Bug in Excel:
    ' The Worksheet_Activate event does not fire for the sheet that is active
    ' when the workbook is opened, so call it explicitely. Make sure that
    ' Worksheet_Activate() is declared as Public.
    ' Ignore ActiveSheets without (Public) Worksheet_Activate()
    On Error Resume Next
    Call ActiveSheet.Worksheet_Activate
    On Error GoTo 0
End Sub
 
VBXL said:
It doesn't look as though Worksheet Activate triggers unless switching worksheets, and that's a problem in this instance. So, I need to figure out a way of making it work.
Click to expand...
Ah, I didn't know that. Seems to me the easiest way to fix that is to include a statement in Workbook_Open to activate the Clipboard worksheet. I'll bet that'll do the trick, even if the workbook would have opened to that worksheet anyway. If not, have Workbook_Open activate a different sheet, then go back to Clipboard; that should do the trick.
 
You must log in or register to reply here.
Back
Top