+ Reply to Thread
Results 1 to 11 of 11

Code to detect previous active workbook instead of current active workbook

  1. #1
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Code to detect previous active workbook instead of current active workbook

    Hello.

    I have two workbooks open. The first workbook will always have a different filename but the second workbook will always be called "Chart1". I have a code that inserts the first (unknown) workbook into "Chart1" but for some reason the code is hyperlinking "Charts1" into "Charts1" !

    Please Login or Register  to view this content.
    I think the code is detecting the active workbook as the hyperlinked file which is "Chart1" instead of the previously open workbook. So if the code can take the previous open workbook then I think this code would work properly.

    Thanks in advance

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: 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:
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Code to detect previous active workbook instead of current active workbook

    Hi Hahobe, nice to see you again.
    I plugged in your code but it did not insert any hyperlinks. Here is the file that I'm inserting into "Chart1.xlsm": "2014-02-20 no.430430 Product.xlsm"

    Thank you.

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Code to detect previous active workbook instead of current active workbook

    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:
    Please Login or Register  to view this content.
    If the code wonīt do what you want please change into the VBE, place the cursor into the code line
    Please Login or Register  to view this content.
    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

  5. #5
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Code to detect previous active workbook instead of current active workbook

    What description in other post?
    The code again did not work and the code highlighted the part of the script that you said I should put a break point in. I'm not sure what I should do next?

    Sorry to make you nuts and I do appreciate your help.

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Code to detect previous active workbook instead of current active workbook

    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

  7. #7
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Code to detect previous active workbook instead of current active workbook

    Maybe I am not doing it right. Can you send me the samples you are working with-with the codes already inserted in the VBA?

  8. #8
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Code to detect previous active workbook instead of current active workbook

    Hi, kosherboy,

    I thought it was you who was searching help, not me. So to me it would be a better way that you included your macro workbook but anyhow...

    Ciao,
    Holger
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Code to detect previous active workbook instead of current active workbook

    Quote Originally Posted by HaHoBe View Post
    Hi, kosherboy,

    I thought it was you who was searching help, not me. So to me it would be a better way that you included your macro workbook but anyhow...

    Ciao,
    Holger
    Once again you amazed me!
    Thanks!
    I modified the code to insert the hyperlink to the pre-selected cell.

    Please Login or Register  to view this content.
    Everything is working the way i want it to. Can you just tell me how i can get rid of the pop-up window that says "Privacy warning: This document contains macros, ActveX controls....."

    Many thanks and best of luck to you:-)

  10. #10
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Code to detect previous active workbook instead of current active workbook

    Hey HaHoBe,
    I ran into an unforeseen issue. At work I usually have more than one workbook open, the code does not seem to work with other workbooks open aside from the ones involved in your last post. So basically the code will only work if those two workbooks, alone, are open.
    You mentioned in this thread and a different thread http://www.excelforum.com/excel-prog...ml#post3592885 that there are more ways to solve this problem? I'm curious as to what they are?

    I appreciate all your help and I hope to hear from you soon

  11. #11
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Code to detect previous active workbook instead of current active workbook

    Hi, kosherboy,

    Quote Originally Posted by OP
    I have two workbooks open.
    Quote Originally Posted by #10
    At work I usually have more than one workbook open
    All the posting I made rely on the information from the opening post.

    I will try tonight to have a closer look what may cause the problem.

    Ciao,
    Holger
    Last edited by HaHoBe; 02-25-2014 at 02:03 AM.

  12. #12
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Code to detect previous active workbook instead of current active workbook

    You are right, apologies.
    Looking forward to hearing from you again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA macro for hyperlink to active workbook in the active workbook path
    By Scott Taylor in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-10-2013, 05:37 AM
  2. [SOLVED] Edit code to close all workbooks except active workbook and other specific workbook
    By rocksan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-04-2012, 09:29 PM
  3. Macro operates on 'closed' workbook & Current Active WB
    By BEEJAY in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-04-2011, 08:48 AM
  4. Saving only the current, active workbook
    By SinGin in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-16-2010, 11:28 AM
  5. Paste data from active workbook to another workbook using code.
    By 2funny4words in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-07-2009, 04:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1