-
Aug 16th, 2018, 07:05 AM
#1
Thread Starter
New Member
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.
-
Aug 16th, 2018, 07:34 AM
#2
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.
-
Aug 16th, 2018, 08:19 AM
#3
Thread Starter
New Member
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.
-
Aug 16th, 2018, 09:24 AM
#4
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.
-
Aug 16th, 2018, 10:22 AM
#5
Re: How to use Macro in Personal workbook with two different workbook
-
Aug 16th, 2018, 12:26 PM
#6
Re: How to use Macro in Personal workbook with two different workbook
Can you show us the code you're currently trying to use?
-
Aug 16th, 2018, 04:42 PM
#7
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
-
Aug 18th, 2018, 09:47 AM
#8
Thread Starter
New Member
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.
-
Aug 18th, 2018, 07:02 PM
#9
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|