Solved: Unwanted Connection to Macros in Another WB

[CaptRon]

I have a workbook that contains code that somehow creates a connection to other workbooks I have open at the time.

When I close this workbook, the remaining open workbook tries to open the now closed workbook with a Sheet_SelectionChange event in the open workbook.

The only way to "kill" the connection is to close Excel entirely. Its like the macro is loaded into the application memory and won't let go, just runs on whatever else is open when a triggering event occurs.

I've tried making the subs private, modules private, etc. and that doesn't work either.

Any ideas?

Ron

[JimmyTheHand]

I think the problem lies with those OnKey methods. You should revert the keys to normal functioning before closing the workbook.
The procedures linked to keypresses are probably stored in memory with full path and workbook name, so when they are due to run, Excel tries to open the workbook they are contained in.

Jimmy

[CaptRon]

Bingo! That did it. I placed the following in ThisWorkbook module:

[vba]
Private Sub Workbook_Deactivate()

Application.OnKey "{TAB}"
Application.OnKey "~"

End Sub
[/vba]

Now when I close the workbook or simply activate another, the TAB and ENTER key behavior returns to normal and other workbooks are not seeking the workbook with the OnKey code each time the TAB or ENTER key is pressed.

I have noticed that once I shift to another workbook then back to the workbook with the OnKey code, the TAB and ENTER keys do not run their assigned procedures. I must press both the TAB key and then the ENTER key. Then we are back on track and the keys pick up their assignments. A minor issue I hope to resolve, if possible.

Thanks so much for the suggestion.

Ron

[lucas]

I have noticed that once I shift to another workbook then back to the workbook with the OnKey code, the TAB and ENTER keys do not run their assigned procedures.

Ron, how are you initializing your Tab and Enter key behaviors? Is it with workbook open? You will need to run it on Workbook_activate too just as you did to get rid of it.

[CaptRon]

Ron, how are you initializing your Tab and Enter key behaviors?

I guess I'm not initializing them. Not sure how to go about that. I have the OnKey code in the sheet modules and associated with that sheet and a particular cell address. The procedure connected to the OnKeys just selects a specific cell.

[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveSheet.Name = "Front" Then
If ActiveCell.Address = "$B$15" Then
Application.OnKey "{TAB}", "ToF16"
Application.OnKey "~", "ToF16"
End If
End if
End Sub
[/vba]
I'm open for suggestions. Thanks.

Ron

[lucas]

Ron, can I ask exactly what you are doing with the on key events in the selection change of the sheet?

It looks like: If target cell is B15 then automatically take the cursor to F16 instead........what I am getting at is if we knew what you are trying to do that there may be a way to do it without the on key events....

another question: If this is selectionchange code that resides within a specific sheet(Front I assume) then why would you need to check to see if that sheet is active....a selection change cannot be made unless it is active.....am I missing something?

[CaptRon]

This is all part of my feeble attempt to create a custom tab order to direct the cursor where I want it to go and not where Excel takes by default. It is clumsy and clunky, but has worked very well except that it wants to work in everything I have open. I attached one of the workbooks that utilizes this method in my first post.

As for the the sheet assignment in conjunction with the activesheet specification, it seems I had a problem with it when I didn't include the sheet assignment (Front or Back). I don't really remember now. Might have just been my tendency toward overkill, especially when I don't know what I'm doing.

Anyway, I have tried a couple of different, much more abbreviated methods in the past but they did not work quite as well. mdmackiilop suggested another method that I have not yet tried.

If you have a suggestion for a better approach, I am very receptive. I am learning more all the time and I find this VBA business very interesting and challenging.

Thanks for taking the time to help me.

Ron

[lucas]

See if this helps Ron:
http://vbaexpress.com/kb/getarticle.php?kb_id=364

[CaptRon]

Steve,

That was one of the methods I explored about a year ago when I first came on board here. It does allow you to select a particular tab order, but it comes with one major problem. All of the cells in the array are selected and if you need to delete one of the entries, they all go away.

I communicated that to the author so he could revised to resolve that issue. I tried this KB solution again and it still deletes everything if one selected cell value is deleted. Backspace works OK, but the delete key will cause you to lose every entry.

Now this evening, I tried mdmackillop's code that he sent to me and it works very well with some minor modifications. This is the original code he sent to me:

Worksheet code
[vba]
PrivateSub Worksheet_Activate()
Application.OnKey "{TAB}", "TabList"
End Sub

PrivateSub Worksheet_Deactivate()
Application.OnKey "{TAB}"
End Sub
[/vba]
Module code
[vba]
Sub TabList()
Select Case ActiveCell.Address(0, 0)
Case "A5"
DoTab "B8"
Case "B8"
DoTab "A8"
Case "A8"
DoTab "B5"
Case "B5"
DoTab "A5"
End Select
End Sub

Sub DoTab(Addr AsString)
Range(Addr).Select
End Sub
[/vba]
I had to figure our a way to "deactivate" it when leaving the workbook, else it wants to run in the other workbook, too (that's why I started this thread). Also, when returning to the original workbook and Sheet1 is the active sheet, the code is not "initialized" (I suppose that is the proper term) by the Worksheet_Activate event. You have to tab to another sheet and back again for it to take effect.

I sometimes need different tab orders for each sheet. For Sheet2, I added the TabList2 that has its own tab sequence arranged differently than TabList1 for Sheet1.

I wanted the ability to return to Excel's default tab behavior unless and until I needed to redirect by code. Sometimes it involves redirecting the tab sequence for a few cells, then return to default, the redirect a few more as needed, etc. That way I don't have to assign the route for every single unlocked cell on the sheet. So I added a macro that returns flow to the next active cell.

Here's what I ended up with based on mdmackillop's original code.

In the Workbook module:
[vba]
Private Sub Workbook_Activate()

'For some reason, TabList2 must be listed first here
'or TabList1 won't work

Application.OnKey "{TAB}", "TabList2"
Application.OnKey "~", "TabList2"

Application.OnKey "{TAB}", "TabList1"
Application.OnKey "~", "TabList1"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "{TAB}"
Application.OnKey "~"
End Sub
[/vba]
In the Sheet1 module:
[vba]
Private Sub Worksheet_Activate()
Application.OnKey "{TAB}", "TabList1"
Application.OnKey "~", "TabList1"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{TAB}"
Application.OnKey "~"
End Sub
[/vba]
In the Sheet2 module:
[vba]
Private Sub Worksheet_Activate()
Application.OnKey "{TAB}", "TabList2"
Application.OnKey "~", "TabList2"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{TAB}"
Application.OnKey "~"
End Sub
[/vba]
In the standard module:
[vba]
Sub DoTab(Addr As String)
Range(Addr).Select
End Sub

Sub TabList1()
Select Case ActiveCell.Address(0, 0)
Case "A5"
DoTab "A7"
Case "A7"
DoTab "B5"
Case "B5"
DoTab "B7"
Case "B7"
DoTab "A10"
Case "A10:B11"
NextCell
Case "B12"
DoTab "B15"
Case "B15"
DoTab "A15"
Case "A15"
DoTab "B17"
Case "B17"
DoTab "A17"
Case "A17"
DoTab "A5"
Case Else
NextCell
End Select
End Sub

Sub NextCell()
ActiveCell.Next.Select
End Sub

Sub TabList2()
Select Case ActiveCell.Address(0, 0)
Case "A6"
DoTab "B8"
Case "B8"
DoTab "A8"
Case "A8"
DoTab "B6"
Case "B6"
DoTab "A6"
End Select
End Sub
[/vba]
Sheet3 does not require a special tab order. I left the Worksheet_Deactivate event in place so the TabLists would not be applied to sheets that don't need them.

This code works just as well as what I had been using and it is so much more concise and managable. Someone should post this as a KB entry. It really works well with no ill side effects that I can see. Thanks for your assistance.

And mdmackillop....sincere thanks to you for the code.

Ron

[lucas]

I barely remember that problem Ron. Glad you got it worked out....