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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Put the following code in the workbook that you don't want active:

Private Sub Workbook_Open()
Workbooks("BookToActivate.xls").Activate
End Sub
 
Upvote 0
Thanks, Al, but I have a little question about that.

After the macro runs, the workbook that I selected to open is the one that stays on top. This is not always the same workbook. Would I have to put that in every workbook that I might have to open?

I honestly was assuming there would be some kind of command somewhere to look at what files are open and bring this certain one to the front, as it were.

Hitting ctrl+tab to flip through them isn't really a big pain or anything, I was just hoping there was a way around it.
 
Upvote 0
How about the following code:

For Each w In Workbooks
If w.Name = "NameOfBookToActivate.xls" Then
w.Activate
End If
Next w
 
Upvote 0
Would that one go into the existing code?

(Just checking, but I can't try it now until Monday)
 
Upvote 0
"There are two means of adding to the miseries of life: music and cats." - C. O. Jones
 
Upvote 0
Al,

This is what I currently have:

Private Sub Workbook_Open()
On Error GoTo myErr
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)
ThisWorkbook.Close False

Next

End
myErr:
ThisWorkbook.Close False
End Sub

I would like to have the Main Directory on top instead of the one I select.

Thanks for all of your help,

_________________
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 06:43
 
Upvote 0
On 2002-03-25 07:05, Dave Hawley wrote:
Hi Kristy

Can't you just put:

Workbooks("BookIWant.xls").Activate

as the last line of your "code" ?

There are only 2 problems with that:

1) Judging by the tone, you seem to assume that I know how to use VBA...which I don't (unfortunately). That's why I'm posting for help.

2) I went ahead and tried your suggestion in several different places of the code...I couldn't get it to work. However, I'm assuming that it's just me that's the problem.

Thanks,
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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