How to get Excel 2003 to open to specific cell refernece!

A

a24t42

I would like for my Excel 2003 worksheet to open at a specific cell
reference. Is this possible.? My knowledge of Excel is very limited so
please be explicit.

Thanks in advance.
 
D

Dave Peterson

You can have a macro run each time the workbook opens.

Option Explicit
Sub Auto_Open()
application.goto worksheets("sheet9999").range("x777"), scroll:=true
end sub

Change the worksheet name and cell address to what you want.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
A

a24t42

Dave,

Thanks for the quick reply but I must be doing something wrong. I have
two problems.

First - I followed the link you provided and did as they suggested for
using someone's elses macros. I only have one worksheet in my Excel
file and want it to open to Cell D5. So I used -

Option Explicit
Sub Auto_Open()
Application.Goto Worksheets("sheet1").Range("D5"), scroll:=True
End Sub

When I returned to my spreadsheet and tried to run the macro with ALT +
F8. I get the error " subscript out of range."

Second, so I thought I would save the file and go back to it later.
When I tried to save it, I got the following message -

"The following features from you workbook will not be saved in the XML
spreadsheet - VB project. Do you want to continue saving as XML
spreadsheet? Yes or no?"

I have my macro security set to medium.

Thanks in advance for any help you can give me?
 
D

Dave Peterson

Is Sheet1 the name of the worksheet?

Subscript out of range usually means that there is a typo--either in the code or
in the name of the worksheet.

And I don't use xl2007, so I'm not sure what the solution would be.

Are you sure you're saving as a normal workbook (with macros???).
 
A

a24t42

Problem #1 -fixed. My worksheet is not named sheet1. As soon as I
changed it, it worked.

Problem #2 - fixed. Captain Dum Dum here. I did not notice that it was
not saving as Excel workbook. As soon as I changed that, it worked.

Now, one more question. Now when I open the file, I get the security
warning about macros. Is there a way to turn this off or is it
something I must live with.

Thanks for all your help.

24t42
 
A

a24t42

I spoke to soon. It worked once and now it isn't again.

Let me make sure I have this correctly. My one sheet is named Master. I
want it to open on cell D5. Here is the code I used -

Option Explicit
Sub Auto_Open()
Application.Goto Worksheets("Master").Range("D5"), scroll:=True
End Sub

Now I noticed that there are several places where I can put the code -
either attach to Sheet1(Master) or ThisWorkbook. I put it in
Sheet1(Master). Is this correct?

Oh, by the way the macro works fine if I use ALT +F8. It just doesn't
open on D5. It opens on whatever I was on when I closed and saved.

24t42
 
G

Gord Dibben

If just for your own computer...........................

Check out Help on Digital Signing and the the SelfCert utility which allows you
to sign your projects on your own computer only.

Self-cert digital signatures are not exportable as are paid-for ones from Thawte
and Versisign.

Go to Start>Programs>Microsoft Office>Office Tools>Digital Certificates and
build a selfcert from there.

When complete and your workbook is open go to VBEditor and Tools>Digital
Signature>Choose.

Select the one you just created and Ok your way out.

Save the workbook and re-open. Click "Yes" when asked about "trusted whatever".

You won't see the macro message again for that workbook.

You can use the signature for all your workbooks with macros.


Gord Dibben MS Excel MVP
 
G

Gord Dibben

The Auto_open code must be stored in a general module.

Alt + F11 to open VBE.

Select your workbook/project and Insert>Module

Paste the code into that module.


Gord Dibben MS Excel MVP

I spoke to soon. It worked once and now it isn't again.

Let me make sure I have this correctly. My one sheet is named Master. I
want it to open on cell D5. Here is the code I used -

Option Explicit
Sub Auto_Open()
Application.Goto Worksheets("Master").Range("D5"), scroll:=True
End Sub

Now I noticed that there are several places where I can put the code -
either attach to Sheet1(Master) or ThisWorkbook. I put it in
Sheet1(Master). Is this correct?

Oh, by the way the macro works fine if I use ALT +F8. It just doesn't
open on D5. It opens on whatever I was on when I closed and saved.

24t42
Click to expand...
 
A

a24t42

I want to thank both of you for your help. Excel and I don't get along
too well. I don't use it often enough to be familiar with it's inner
workings.

I am glad to report that it is now working each time I open the file.
It helps if you put the code in the correct spot.

No, this file is not for my personal use. It is a score sheet that will
be used by several people on differnet computers.

Thanks, again!

Judy
 
A

a24t42

It is working fine on my computer. If I give the file to someone else
to use on their computer, will the macro transfer with the file so that
they can use it also?

24t42
 
G

Gord Dibben

If the macro is in a module in your workbook/file it will go with the file and
other users can use it.


Gord
 
Top