Code to detect previous active workbook instead of current active workbook

Hi, kosherboy,

one way to solve the issue might be to loop through the orkbooks in the instance and get the name of the first workbook which isn´t the destination workbook like:

Dim ws As Workbook
Dim strText As String
Dim var
Dim strName As String

Const cstrWB_NAME As String = "Chart1.xlsm"

If ActiveWorkbook.Name <> cstrWB_NAME Then Exit Sub
For Each ws In Workbooks
  If ws.Name <> cstrWB_NAME Then
    strName = ws.FullName & "|" & ws.Name
    Exit For
  End If
Next ws

strText = Mid(Split(strName, "|")(1), 15)
strText = Left(strText, Len(strText) - 5)
var = Split(strText, " ")

With Workbooks(cstrWB_NAME).Sheets("Sheet1")
    .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
    .Hyperlinks.Add Anchor:=Selection, _
    Address:=Split(strName, "|")(0), TextToDisplay:=var(0) & " " & var(1)
End With
End Sub

Ciao,
Holger

Hi, kosherboy,

you would need to start the macro from witin Chart1.xlsm, I don´t know it maybe you were irritated with my description in the other post:

Sub kosherboy()
'http://www.excelforum.com/excel-formulas-and-functions/991000-code-to-detect-previous-active-workbook-instead-of-current-active-workbook.html
Dim wb            As Workbook
Dim strText       As String
Dim var
Dim strName       As String

Const cstrWB_NAME As String = "Chart1.xlsm"
Const cstr_PERS   As String = "PERSONAL.XLSB"

If ActiveWorkbook.Name <> cstrWB_NAME Then Exit Sub
For Each wb In Workbooks
  Select Case wb.Name
    Case cstrWB_NAME, cstr_PERS
    Case Else
      strName = wb.FullName & "|" & wb.Name
      Exit For
  End Select
Next wb

strText = Mid(Split(strName, "|")(1), 15)
strText = Left(strText, Len(strText) - 5)
var = Split(strText, " ")

With Workbooks(cstrWB_NAME).Sheets("Sheet1")
    .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
    .Hyperlinks.Add Anchor:=Selection, _
    Address:=Split(strName, "|")(0), TextToDisplay:=var(0) & " " & var(1)
End With
End Sub

If the code won´t do what you want please change into the VBE, place the cursor into the code line

If ActiveWorkbook.Name <> cstrWB_NAME Then Exit Sub

and press F9 to set a breakpoint. Start the code again and use F8 to singlestep through the code and see what the code does or where it breaks.

Ciao,
Holger

Hi, kosherboy,

a breakpoint is set to get further information about the code and the variables. What you reckognized was exactly what was planned: after setting the breakpoint and starting the procedure the code will stop and allow you to continue to go through the code by pressing F8 thus being able to pass more information than just code doesn´t work (which I can´t find as the code worked fine in the samples I build to test).

Ciao,
Holger