Software

Ask something
Question Locked

How to Start a Excel Marco Automatically

0 helpful
11 replies

by tallman_roy 2 views

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

11 replies

by cozy_rocks

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.

by tallman_roy

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

by cozy_rocks

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

by charliespencer

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.

by cozy_rocks

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.

by jesus_c

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.

by tallman_roy

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

by tallman_roy

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

by cozy_rocks

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

by tallman_roy

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

This discussion is closed to new replies.