Tweaking existing VBA code...

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
...however, I don't know how to write VBA code. What I'm using, I got help with from here. :biggrin:

(I had all of this typed already, but apparently my login expired...grr...anyway...)

The code I'm currently using opens 2 separate workbooks and prompts for the others to open.

I usually start entering information in a certain workbook every time. How can I set it to switch to this one workbook after it opens the one I select?

Thanks!
 
Hi Kristy

RE: Judging by the tone

No no, you have me wrong, there was no tone intented. I was just be earnest.

As long as the Workbook is open, the code I posted should work if placed as the last line in your code. You should only have to change the name to suit.

If you could paste your code here myself, or many others will help.
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
I wasn't meaning anything bad by the "judging by the tone" remark, sorry :biggrin:.

As for the code, I posted it this morning (see the first page of this thread).

As the code is right now, it is set to close the workbook with the code in it after it is done running everything (I think that may be some of the problem). I tried putting it right before the thing for closing the workbook, at the very end, etc. I couldn't get it to go.

*sigh* Stupid vba. (Yep...must be the vba's fault :rolleyes:)
 
Upvote 0
Kristy I didn't realise you were doing this via the Workbook_open (guess I did assume :eek:)).

Ok, completely untested, but it should help

Option Explicit
Dim bActivateIt As Boolean
Private Sub Workbook_Deactivate()
On Error Resume Next
If bActivateIt = True Then
Workbooks("MyBook.xls").Activate
End Sub
End Sub

Private Sub Workbook_Open()
On Error GoTo myErr
bActivateIt = False
ChDir "ECR"
Workbooks.Open "UPG List Revised.xls"
Workbooks.Open "Main Directory.xls"
Which = Application.GetOpenFilename(MultiSelect:=True)
'Which now contains an array of file names which you can enumerate through
For I = 1 To UBound(Which)
Workbooks.Open Which(I)
bActivateIt = True

'Not sure why you close ThisWorkbook on the _
first loop? It means it will onl loop once.
ThisWorkbook.Close False

Next

End
myErr:

ThisWorkbook.Close False
End Sub
 
Upvote 0
Dave,

I couldn't get that to work either. Ugh.
It gives me the following: "Compile error: variable not defined" and highlights the line "Private_Sub Workbook_Open()" in yellow, and selects the "Which =" in the line that lets me select from the open dialog box.

Then, if I go to close excel, it brings up another error: "Comiple error: Block If without End If" and highlights "Private_Sub Workbook_Deactivate()" in yellow, and selects the first "End Sub" from what you added to the beginning of the code.

As for closing the workbook, I set a blank workbook to run the code, and I have it close after it opens all the necessary workbooks so that it will not be in the way when switching between workbooks.

All the code you see me post came from people here. So I have no clue about it running on the first loop or anything.

_________________
Kristy

"There are two means of refuge from the miseries of life: music and cats." - Albert Schweitzer
This message was edited by Von Pookie on 2002-03-25 12:43
 
Upvote 0
Hi Kristy

The code must be placed in the Private Module of the Workbook that is to run the code upon opening. The easiest way to get there is to open the Workbook, right click on the Excel icon, top left next to "File" and select "View Code". Now delete any old code and paste in this


Dim bActivateIt As Boolean
Private Sub Workbook_Deactivate()
On Error Resume Next

If bActivateIt = True Then
Workbooks("MyBook.xls").Activate
End If

End Sub

Private Sub Workbook_Open()
Dim Which

On Error GoTo myErr
bActivateIt = False
ChDir "ECR"

Workbooks.Open "UPG List Revised.xls"
Workbooks.Open "Main Directory.xls"

Which = Application.GetOpenFilename(MultiSelect:=True)
'Which now contains an array of file names which you can enumerate through
For I = 1 To UBound(Which)
Workbooks.Open Which(I)
Next

myErr:
bActivateIt = True
ThisWorkbook.Close False
End Sub

Change "MyBook.xls" to the name of the Workbook you want to be activated when this one closes.

If you have any other macros called "Private Sub Workbook_Open()" in any Standard modules you must also delete them.

Save and close your Workbook, then re-open it.
 
Upvote 0
Dave,

Thanks for your help, but it's not working. I used an entirely new workbok to test it on. It doesn't open any of the files, bring up the open box or anything. It does close the file, though.

Another thing is this: Just to try it, I copied my original code that I posted here, did the delete code thing from that like you mentioned, then pasted it and tried that again. It did the same thing.

Why would the original code start acting like that as well? :confused:
 
Upvote 0
Hi Kristy


The errpr most likely lies in your original code, remove or comment out the: On Error GoTo myErr line. This will force a run time error, click on Debug and it will take you straight there.
 
Upvote 0
Thanks Dave,

I found that error: a simple oversight on my part (I missed adding a full directory path back in :rolleyes:).

It is opening everything again like it was, however it is still not bringing the sheet to the front, and staying on the last one opened.

I'm beginning to think this just isn't going to work. *sigh*
 
Upvote 0
Hi Kristy

It will work sooner or later!

Remove the "On error resume next" from the Workbook_Deactivate code and see what the problem is.
 
Upvote 0
Good morning, Dave! (at least it is for you. It's about 6:30pm here...and still Tuesday).

I had this typed out all nice and neat once already, but apparently hit the wrong button somewhere.

Anyway, the line that seems to be disliked this time is: Workbooks("Main Directory.xls").Activate in the Workbook_Deactivate part of the code.

Thanks for all of your help. Who would have thought we would have to go through all of this trouble just to help me somewhat "automate" a stupid little task.

Thanks again,
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top