Event trigger to open file after Ctrl+C keyboard action.

I'm looking for a code snippet to insert into an existing routine that will Open a workbook on my PC when:

1. a specific range is selected on a specific sheet, and
2. I do a Copy command with keyboard Ctrl+C.

The selected range to test if already selected would be B3:B15 on sheet "Specs" of the workbook.

Let me know if you need to see the existing code, but I think I can figure out where/how to insert it.

The code section that opens the file by selecting a menu of the macro is:

Dim str_xlsFile As String
Dim str_xlsFolder As String

str_xlsFolder = "C:\Users\myname\Documents\Litigation\reports\report templates\U&E Summary templates\"
str_xlsFile = str_xlsFolder & "Generic Summary Report.xlt"

Workbooks.Open Filename:=str_xlsFile, Editable:=True

Also - Why does the inserted Code section seem to be unclear? Previously, it was much easier to read.
Is there a setting to change or am I messing it up when pasting between the tags?

Thank you,
Delain

Hi there,

Try copying the following code into the VBA CodeModule of the worksheet(s) for which you want to provide this facility:

Option Explicit


'=========================================================================================
'=========================================================================================


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Const sTRIGGER  As String = "tblTriggerRange"   '   <<  Named range on worksheet

    If Target.Cells.Count = 1 Then

        If Not Intersect(Target, Me.Range(sTRIGGER)) Is Nothing Then
              Application.OnKey Key:="^c", Procedure:=Me.Name & ".OpenWorkbook"
        Else: Application.OnKey Key:="^c"
        End If

    End If

End Sub


'=========================================================================================
'=========================================================================================


Private Sub OpenWorkbook()
    MsgBox "Opening workbook . . ."
End Sub

The range referred to by the Named Range can be different for each of the various worksheets, but obviously the name of the Named Range (tblTriggerRange) must be the same on each worksheet.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Greg,

Thanks so much for offering up your time on this.

What would need to change in order for me to insert this in my personal.xlsm sheet and not captive or Private to a specific worksheet?

I use a macro, activated from a Ribbon button, that runs a printing routine, then selects what would be the "tblTriggerRange", waits for me to Ctrl+C the range cells so that I can open up another workbook to paste the cell data into.

If I keep this Private, then it seems I would have to copy this code into every workbook (template) which I want to have this facility.
Make sense?

Thank you,
Delain

Hi again,

Take a look at the attached workbook and see if it gets you going in the right direction.

Store the workbook in your XlStart folder so that it is opened each time you start Excel. The workbook itself is hidden, so it will not appear on the screen. When the workbook opens, it creates Application-Level events which trigger each time ANY workbook is activated, each time ANY worksheet is activated, and each time the selection is changed on ANY worksheet.

If the currently-active sheet does not contain a "tblTriggerRange" Named Range, no action is taken, but if it does contain such a named range, the Ctrl+C key combination is reassigned to the "OpenWorkbook" macro if the selected cell lies within the range defined by "tblTriggerRange".

If any of your own routines crash while they're running, the Module-Level variable "mclsExcelEvent" will lose its value, and the key reassignment will no longer work. For this reason, it's probably a good idea to provide a small routine in your Personal workbook to reopen the attached workbook so that "mclsExcelEvent" will be recreated.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Greg,

I've got some time now to start working with the workbook/vba you sent.

What is the reason or advantage to keeping the code in a workbook separate from my personal.xlsm file?

What is your naming scheme for the workbook "183 -" ..... "-2"?

Wouldn't an event triggered every time I make a selection change, on any workbook, cause extra work for Excel when my selection changes far out number the times I'm going to want to test for "tblTriggerRange" within a workbook?

I like to understand not all of it, but as much of the VBA code that I have incorporated from other users as I have time to decipher. My knowledge base is geared toward individual, self-contained macros that don't rely on application events and setups, so this is why you see these questions. Yes, I want to expand my knowledge and expertise.

Thank you,
Delain

Greg,

Problem: It does not appear that the routine is allowing for the actual Copy (Ctrl+C) of the range which is triggering the sequence of events.
After the new workbook opens (from OpenWorkbook) I need to be able to Paste the copied range data into the new workbook.

Can a variable be setup to capture the clipboard of the Copy action, then continue with the remainder of the actions?

Thoughts?

Thank you,
Delain

Hi again,

Sorry - I mustn't have read your posts closely enough, I didn't realise that you wanted to retain the original functionality of the Ctrl+C key.

Add the highlighted line to the routine in the last version of the workbook I sent you - this will copy the contents of the current range to the ClipBoard before the new workbook is opened:

'=========================================================================================
'=========================================================================================


Private Sub OpenWorkbook()

    Selection.Copy

    MsgBox "This is the routine which opens the new workbook"

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M

Greg,

Thanks for the solution.
Could you take a look at my post #5; should've waited before posting the other so you would've seen it easier.

Thank you,
Delain

Hi again Delain,

What is the reason or advantage to keeping the code in a workbook separate from my personal.xlsm file?

No special reason at all - I keep very little code in my own Personal.xlsm workbook, and tend to keep routines like the current one in their own workbooks. When necessary/appropriate, they can be easily removed from the XLStart folder without needing to interfere with the Personal.xlsm workbook. You can certainly incorporate the routine into your own Personal.xlsm workbook if that is what you prefer.

What is your naming scheme for the workbook "183 -" ..... "-2"?

Quite simple - your question was the 183rd I'd replied to since December last. The " - 2" suffix indicates the version number of the workbook

Wouldn't an event triggered every time I make a selection change, on any workbook, cause extra work for Excel when my selection changes far out number the times I'm going to want to test for "tblTriggerRange" within a workbook?

Yes indeed, but Excel has no way of "knowing in advance" whether or not the selection change you're about to make will involve the tblTriggerRange - it must therefore test every Selection_Change event. It might be possible to disable the Application-Level event if you need the routine to trigger only when certain worksheets are active.

Hope this helps.

Regards,

Greg M

Greg,

Thanks for the latest info post; very informative.

Wanted to see if we could modify the routine a bit. Here is the code, as it stands; haven't modified any of the other supporting modules not shown herein.

Public Sub SelectionChange(rTarget As Range)

    Const sTRIGGER      As String = "tblTriggerRange"   '   <<  Named range on worksheet

    Dim rTriggerRange   As Range
    Dim wks             As Worksheet

    Set wks = rTarget.Parent
    On Error Resume Next
        Set rTriggerRange = wks.Range(sTRIGGER)
    On Error GoTo 0

    If Not rTriggerRange Is Nothing Then

          If rTarget.Cells.Count = 1 Then

              If Not Intersect(rTarget, rTriggerRange) Is Nothing Then
                    Application.OnKey Key:="^c", Procedure:="OpenWorkbook"
              Else: Application.OnKey Key:="^c"
              End If

          End If

    Else: Application.OnKey Key:="^c"

    End If

End Sub

Private Sub OpenWorkbook()

 '   MsgBox "This is the routine which opens the new workbook"
    Dim str_xlsFile As String
    Dim str_xlsFolder As String
    
    Selection.Copy
    str_xlsFolder = "C:\Users\myname\Documents\Litigation\reports\report templates\U&E Summary templates\"
    str_xlsFile = str_xlsFolder & "Generic Summary Report.xlt"
    
    Workbooks.Open Filename:=str_xlsFile, Editable:=False
    
End Sub

1) I need to have the flexibility to Copy (Ctrl-C) a cell within the tblTriggerRange range. Currently, it seems that the OpenWorkbook code executes if I simply have one cell selected that happens to lie within tblTriggerRange and then do Ctrl-C. If we set it up so that the OpenWorkbook is called only when the entire tblTriggerRange is selected, that would be much better.

2) As you mentioned, how would we set this up to restrict the Application-Level event (selection change) to trigger only when certain worksheets are active? The "required" workbooks would have a common string within their file names (U&E by DG) or go by a specific worksheet/tab name (Specs) that would be active when the OpenWorkbook routine is needed to execute.

Thank you,
Delain

Bump . . . Greg M, could you take a look at #10?

shg, Thank you for your response. That modification worked as needed.

Thank you,
Delain

Huh?
When I said that it was functioning correctly, it sure seemed to.
Not sure what happened, but it is now not working as needed. It is triggering OpenWorkbook regardless of which worksheet is open/active and regardless of having a single cell selected in or out of "tblTriggerRange". It actually did it with a workbook that does not have tblTriggerRange in it at all.

Here is where I inserted your line of code; wasn't sure where to put it exactly, so I'm sure this is on me.

Public Sub SelectionChange(rTarget As Range)

    Const sTRIGGER      As String = "tblTriggerRange"   '   <<  Named range on worksheet

    Dim rTriggerRange   As Range
    Dim wks             As Worksheet

    Set wks = rTarget.Parent
    On Error Resume Next
        Set rTriggerRange = wks.Range(sTRIGGER)
    On Error GoTo 0

    If Not rTriggerRange Is Nothing Then

          'If rTarget.Cells.Count = 1 Then
          If rTarget.Address = rTriggerRange.Address Then
      
              If Not Intersect(rTarget, rTriggerRange) Is Nothing Then
                    Application.OnKey Key:="^c", Procedure:="OpenWorkbook"
              Else: Application.OnKey Key:="^c"
              End If

          End If

    Else: Application.OnKey Key:="^c"

    End If

End Sub

shg, your code - by itself - is not working.

I ran the code standalone(?) since you didn't mention anything about how to plug it in with the Class Module setup in place, from Greg.

Then, I tried stabbing it in to Greg's code that he sent previously, in a workbook xlsm, and it error'd because it couldn't find "rTarget" within his Class Module code.
Attached is his full code (sans your addition) along with the Class Module section, which I know nothing about the why's & what-for's. The only mod I made was to accommodate the correct workbook info in the Openworkbook section.

Greg's code was working as needed, except like I mentioned:
1) I need to have the flexibility to Copy (Ctrl-C) a cell within the tblTriggerRange range. Currently, it seems that the OpenWorkbook code executes if I simply have one cell selected that happens to lie within tblTriggerRange and then do Ctrl-C. If we set it up so that the OpenWorkbook is called only when the entire tblTriggerRange is selected, that would be much better.

The other option or mod I was hoping for, but Greg has not responded to at this point, is:

2) As you mentioned, how would we set this up to restrict the Application-Level event (selection change) to trigger only when certain worksheets are active? The "required" workbooks would have a common string within their file names (U&E by DG) or go by a specific worksheet/tab name (Specs) that would be active when the OpenWorkbook routine is needed to execute.

Let me know what your suggestion is at least for the 1) fix.
I appreciate your time with this.

Thank you,
Delain