Question

Locked

How to Start a Excel Marco Automatically

By tallman_roy ·
Hi,

I would like to know how to program or set a Excel file to run a marco upon opening the file?

Normally we need to click an object that link to a macro or run it by selecting Alt + F8. I want a specific marco in the excel file to start when any user open this particular Excel file. How can I do it? Please help.

Roy

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Workbook_Open ()

by Cozy_Rocks In reply to How to Start a Excel Marc ...

Put your macro code in the Workbook_Open() sub of the VB editor. In the VB Editor projects window, Double Click "This Workbook" and from the drop down at the top of the code window select "workbook" and "open". You would need to set you macro security down to zero for it to run without prompt. This is not genarally accepted as good practice as it would allow malicious code to run without stopping.

Collapse -

Need more guide

by tallman_roy In reply to Workbook_Open ()

Hi Acameron,

I need to run the below macro automatically when the file is first open. I have tried the recommendation, but seems doesn't work. Might be due to the setting which I don't understand. Please guide me step-by-step if possible with screen-shot. You may contact me at tallman_roy@yahoo.com.sg

==============================
Sub test()
DES_SHT = "Sheet1"
DES_VAL = Worksheets(DES_SHT).Cells(1, 4).Value

DES_VAL = DES_VAL + 1
Worksheets(DES_SHT).Cells(1, 4).Value = DES_VAL

End Sub
==================================

Regards.


Roy

Collapse -

E-mail reply

by Cozy_Rocks In reply to Need more guide

Roy

I have sent an instruction document direct which I hope makes things a bit easier to understand and gets you the results you are after.

Regards

Collapse -

Are you working too hard?

by CharlieSpencer In reply to Workbook_Open ()

I've been able to get a macro to start automatically in Excel 2003 by naming the macro 'auto_open'. Naming it 'auto_close' will cause it to run when the spreadsheet is closed.

Regarding the security, you can go through the process of generating your own signature. The single time I did that it took almost 20 seconds for the file to open. I could leave the macro security set to "Prompt" and click the "Open" button in less than five, so I stopped creating the sigs.

Collapse -

Point taken

by Cozy_Rocks In reply to Are you working too hard?

You can have too much in the opening process which slows down the work. better to have controls to activate queries or updates etc.
I use the worksheet_open element to ensure all colums are set to autofit in a large spreadsheet with various sizes of numbers and different data types. Also to create an index sheet.

Collapse -

Clarification

by tallman_roy In reply to Point taken

Hi Andy,

What do you means by;
"use the worksheet_open element to ensure all colums are set to autofit in a large spreadsheet with various sizes of numbers and different data types. Also to create an index sheet. "

Please explain. Thanks.


Roy

Collapse -

Use the range object

by Cozy_Rocks In reply to Clarification

If you have different lengths of numbers e.g. 1,000,000 and 1000 and maybe dates in your cels excel may not set the width of the colum to show all the data so it might display ##### instead. The macro below selects the first sheet and colums a to Z and auto fits the colums to their contents so every time the sheet is opened the data is dislayed correctly.

Private Sub Workbook_Open()
Sheets(1).Activate
Range("A:Z").Columns.AutoFit
End Sub
Check this tech rep article for how to add an index sheet to your spreadsheet.
http://articles.techrepublic.com.com/5100-10877_11-5629983.html

Collapse -

Good ideas, Thanks.

by tallman_roy In reply to Use the range object

Hi Andy,

Thanks. I wrote you an email, did you got it?

I am still stuck on the reading and writing without open the excal data file using VB. Any solution?

Regards.


Roy

Collapse -

try this

by Jesus_C In reply to How to Start a Excel Marc ...

Press Alt+F11, or choose Macros from the Tools menu and then choose Visual Basic Editor from the resulting submenu. Excel displays the Visual Basic Editor.
Near the upper-left side of the editor is the Project Explorer. This contains a hierarchical tree that shows the different modules in your workbook. If the Project Explorer is not visible on your screen, press Ctrl+R to display it.
Within the Project Explorer should be a folder called Modules. If it is not already open, double-click on the Modules folder to display its contents.
Right-click on a module in the folder. A Context menu is displayed.
Choose the Remove option from the Context menu. You are asked if you want to export the module before removing it.

Collapse -

Thanks. 2 more questions

by tallman_roy In reply to try this

Hi J, thanks. I got it.
I have another 2 questions;
1) How to read a cell without opening the excel data file using VB?

2) How to write a data into a cell without opening the excel data file using VB?

Please advise.
Regards.


Roy

Back to Software Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Software Forums