Auto Save Add-In - STILL not working

C

CharlesF

Dave, I am a novice! Please can you explain how I enter this into th
macro, how do I designate the macro as auto-open?

Thank
 
G

Guest

it would be very polite to the rest of us if you would
keep your reply posts to the original post so that the
rest of us would know what you are talking about. this is
a learning experiance for all of us.
and if all of your posts were grouped, maybe someone else
could help you also.
 
D

Dave Peterson

I'm guessing that you don't have a personal.xls workbook.

Personal.xls is a common name that lots of people use to store macros that they
want available whenever they start excel. It's stored in a folder called
XLStart.

So start a new workbook.
hit alt-f11 (to get to the VBE)
hit ctrl-r (to see the project explorer--much like windows explorer)

Find the project for your new workbook.
It should look something like:
VBAProject (book1)

Rightclick on that project and choose Insert|Module
Paste this in the code window that just opened up:

Option Explicit
Sub auto_open()

On Error Resume Next
Workbooks("autosave.xla").Excel4IntlMacroSheets("Loc Table") _
.Range("ud01b.Prompt").Value = False
If Err.Number <> 0 Then
MsgBox "Prompt not changed!"
Err.Clear
End If
On Error GoTo 0

'ThisWorkbook.Close SaveChanges:=False

End Sub

Now hit ctrl-g to see the immediate window.
type/copy this and hit enter:
?application.startuppath

For win98 and xl2002, I get:
C:\WINDOWS\Application Data\Microsoft\Excel\XLSTART

Copy your results to the clipboard.
(or write it down (yechh!))

hit alt-f11 to swap back to excel
File|SaveAs
Paste in your path
but add:
a nice workbook name--not personal.xls (save that for something important!)

I choose:
C:\WINDOWS\Application Data\Microsoft\Excel\XLSTART\autosavepromptfix.xls

====
This line:
'ThisWorkbook.Close SaveChanges:=False
is commented out.

The workbook will open, try to reset that prompt.

If you remove the leading apostrophe from that 'ThisWorkbook.Close line, then
the macro will close itself without saving. This is nice once the workbook is
working. But if you're testing, then it's a pain--the workbook closes without
saving!

So once you have it working (and if you want the workbook to close when it's
done--why not???), uncomment that line and resave the workbook (still using that
name in XLStart).

===
Now for the real test.

Close excel, and reopen it.

Did it work????
 
Top