Re: Run time Error '1004

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Dave,

The me.saved = true worked fine if I didn't try to filter anything.
Once I did it ask me to save. Any other ideas?

Thanks,

Jacob


Dave Peterson wrote:
At the bottom of the workbook_open code, you can add a line that "lies" to
excel.

me.saved = true

This tells excel to treat the workbook like it's just been saved/never updated.



Jacob wrote:

All of these solutions worked great. Thanks!! I have thought about
putting the code when the workbook opens, but then I want to get around
not having to click the "Do you want to save Changes" when I go to
close out the workbook. Is there a way to surpass the dialog box? All
that the work*** contain is like a contact list, plus I am the only
one currently using it. If I were to share the workbook I guess I give
read only access to it. Any suggestion???

Thanks,

Jacob

Dave Peterson wrote:
With Active***
If .FilterMode Then
.ShowAllData
End If
End With

Will avoid the problem--as will:

on error resume next
active***.showalldata
on error goto 0

And I think I'd be more specific than using Active***. Maybe:

With me.worksheets("sheet9999")
.select
.range("a1").select
If .FilterMode Then
.ShowAllData
End If
End With

just in case the wrong *** was active.

And I'd use:
Me.Save
instead of:
ActiveWorkbook.Save

Me refers to the thing holding the code--in this case, it's ThisWorkbook.

========
But what happens if a user opens the workbook, messes it up beyond belief and
wants to close without saving?

Your code will save his/her changes and that may not be good.

Have you thought of putting the equivalent code in the Workbook_open event so
that things are set up when the workbook opens instead?

Jacob wrote:

I have a macro to run right before the workbook closes. It is as
follows:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Range("A1").Select
Active***.ShowAllData
ActiveWorkbook.Save
End Sub

The top row has the autofilter on. If the data is not filtered then
when the macro runs it throws a Run Time Error '1004'. If it is
filtered the Macro runs fine.

I can't seems to find the right code to get the macro to end if it
errors out. I think it would be an if then statement but my coding
skills aren't quite up the par.

Any help would be great. Thanks in advance.

Jacob

--

Dave Peterson

--

Dave Peterson

.

Quantcast