Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Opening another workbook when one opens

Aug 18, 2004 8:15PM PDT

Hi there,

I was thinking if it was possible to prompt the user if he/she would like to open another workbook when he opens a workbook. This could preferably be by means of a message box.

If it is not possbile, could i create a button and insert the relevant macro commands so that when the user clicks on that button on the main page of one workbook, it will open another workbook.

Hope you can help!

Thanks=)

Discussion is locked

- Collapse -
Re: Opening another workbook when one opens
Aug 18, 2004 10:23PM PDT

Use the Open event of a workbook. Like in the following example from Visual Basic for Excel help:

Private Sub Workbook_Open()
Application.WindowState = xlMaximized
End Sub

You want to open another workbook, I understand. Help says this is the code to do so:
Workbooks.Open "ANALYSIS.XLS"
It's a method of the workbooks object.

You'll need a msgbox and an if, but that won't be very difficult.

Hope this helps.

Kees

- Collapse -
Re: Opening another workbook when one opens
Aug 19, 2004 5:43AM PDT

Proceed as follows to paste the code:

1. Open Excel.
2. Go to Tools.
3. Go to Macro.
4. Go to Visual Basic Editor.
5. Doubleclick ThisWorkbook and a window appears.
6. Paste this code.
7. Save.
8. Close Excel then open your saved file.


Here is the code. I tested it before posting it. It should do just as you want it. Just mark it and paste it.

Dim ip As String 'ip stands for input

Private Sub Workbook_Open()'opens automatically with your Excel file
AskMe 'Function asking to open workbooks
End Sub

Private Function AskMe()
ip = InputBox("Do you want to open another workbook?")
If ip = "y" Or ip = "Y" Then 'other than y/Y will exit function
OpenOthers 'Function to open the Open Dialog.
Else: Exit Function
End If
End Function

Private Sub OpenOthers()
Application.Dialogs(xlDialogOpen).Show ("C:\Documents and Settings\Toto\My Documents\")'put the directory here where your other Excel files are located
End Sub

- Collapse -
Re: Opening another workbook when one opens
Aug 19, 2004 7:49PM PDT

Swisse,

Quite nice.

Up till now, it hasn't been customary on this forum to publish code, just some hints to let the poster sort out things himself.

You might have set a new standard.

Kees

- Collapse -
Re: Opening another workbook when one opens
Aug 20, 2004 2:27AM PDT

I was trying to help him out. If what I did was wrong, I better keep my codes to myself. I thought I was being helpful, but being helpful can also be misunderstood. I thought the purpose of this forum is to share knowledge, not to keep it just to one's self.

- Collapse -
You misunderstood me.
Aug 20, 2004 7:17AM PDT

I didn't meant to be cross or sarcastic. Just note and applaud.
It's great help, and I'm sure the original poster appreciates it very much. If you happen to be able to continue like this, do so!
The only caveat: don't copy large pieces of code written by other people, but post a link to it. That's one of the forum rules. There's no rule at all about NOT posting (working) code written by yourself. On the contrary, I should say.

Kees

- Collapse -
Re: You misunderstood me.
Aug 21, 2004 7:08PM PDT

To Kees Bakker,
I am a programmer, and wrote those codes myself. Before posting code or giving out programs to others we programmers debug our codes/programs making sure it works. I don't provide links to other sites which I am not sure if what they offer works, or if they are just discussing something about a code and this code seems not to work. I would prefer to sit down churn out some codes, debug it, and post it after making sure it works.

- Collapse -
Re: You misunderstood me.
Aug 21, 2004 10:42PM PDT

I'm a programmer also. I fully agree. I don't hope I failed to express myself properly the second time in a row by having you read I thought you copied the code. That was just a general remark about the forum policies.
Hope to see more of your work here, if you've got solutions for people with problems.

Kees

- Collapse -
Re: You misunderstood me.
Aug 22, 2004 12:57AM PDT

OK, thanks for clearing things up. Hope to hear more from you soon.