Results 1 to 9 of 9

Thread: How to use Macro in Personal workbook with two different workbook

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    3

    How to use Macro in Personal workbook with two different workbook

    Dear members,

    I am a new person to VBA and trying to write a Macro code in personal workbook. I am applying this code in one workbook "Book1". After completing certain activities in Book1, I am trying to go to another workbook "Book2" using workbook.open ("c:\desktop\Book2") in the same Macro. It open the workbook but failed to execute the further commands given in Marco in Book2.

    In Book2, it highlight Design mode and stops.

    Please help to execute further commands given in Marco in Book2.

    Thanks in advance.

  2. #2
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,852

    Re: How to use Macro in Personal workbook with two different workbook

    Hi Avdhesh,

    This really belongs over in the VBA forum section. However, welcome to VBForums. It's been a while since I've messed with them, but I've got several macros I often use in my Personal.XLSB workbook. That's a file I keep in my "C:\Users\Elroy\AppData\Roaming\Microsoft\Excel\XLSTART" folder. And that folder is set down deep in the Excel options: Trust-Center/Trusted Locations/Excel default location: User Startup.

    Once I'm in that Personal.XLSB workbook, any macros I run from there will be applied to whatever workbook I have open. Here's just an example of one macro I've got in that file:

    Code:
    
    Sub CalculateModeToggle()
        If Application.Calculation <> xlAutomatic Then
            Application.Calculation = xlAutomatic
            MsgBox "Calculation Mode Set To Automatic"
        Else
            Application.Calculation = xlManual
            MsgBox "Calculation Mode Set To Manual"
        End If
    End Sub
    
    

    You can even put new buttons on your toolbars/ribbons and assign those macros to them if you want.

    Best of Luck,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    3

    Re: How to use Macro in Personal workbook with two different workbook

    Thanks Elroy for your inputs.

    I have saved the Marco in the same location but it is not working.

    Using Macro in Book1, I executed certain task. In the same Macro, while executing task in Book1, I have given reference of Book2 and wanted to execute certain task in Book2 as well. As written in previous post, it open the Book2 but do not execute any further command.

    I have tried defining through DIM two workbooks and activating the workbook but it also does not work. Please guide.

  4. #4
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,852

    Re: How to use Macro in Personal workbook with two different workbook

    avdhesh,

    There are people here who work with these macros more frequently than I do. I'm more of a straight-up VB6 guy, with substantial experience in VB6-to-Office automation. But I tend to stay out of the VBA.

    You really belong over in the VBA forum, found here.

    I'm going to recommend to the moderators that this thread be moved over there.

    Best Of Luck To You,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  5. #5
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: How to use Macro in Personal workbook with two different workbook

    Can you show us the code you're currently trying to use?

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to use Macro in Personal workbook with two different workbook

    i believe xlstart is just that, when excel starts, you need code to work on workbook_open or workbook_new events, normally you would use an addin workbook to do that, but i would think that you could use the personal workbook, but you would need to create an application object with events, then put your code in the appropriate events for when each workbook opens
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    3

    Re: How to use Macro in Personal workbook with two different workbook

    Dear Members,

    I am using following codes:

    Dim wbk As Variant

    Set wbk = Workbooks. Open("c:\Desktop\Book1.xlsx”)

    it open the Book1 but does not proceed further and ignore all other further command.

    Please suggest, if there is alternate way to it.

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to use Macro in Personal workbook with two different workbook

    [QUOTE]Please suggest, if there is alternate way to it. [/QUOTEi am sure there would be, but as we do not really know what you are trying to do it is hard to make suggestions

    post the full code, show where the code stops and what error messages you get
    you stated previously that the code worked for book1, but now you say the code stops for that after book1 is open
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width