Run time Error '1004

[Jacob]

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
ActiveSheet.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]

With ActiveSheet
If .FilterMode Then
.ShowAllData
End If
End With

Will avoid the problem--as will:

on error resume next
activesheet.showalldata
on error goto 0

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

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

just in case the wrong sheet 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]

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 worksheet 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

 

[Nick Hodge]

If you just want to not show the dialog there are a few ways you can do it

Application.DisplayAlerts=False

(True before your code exits)

Activeworkbook.Saved=True

Activeworkbook.Save

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

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 worksheet 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]

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]

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]

Once you make a change (filtering included), you've made excel think that there
was a change.

If you really, really want to avoid this (I wouldn't want to), you could use:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub

Remember that you'll have to save the workbook (file|Save) to save any changes
you make. Else you'll be making those changes again.

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

 

[Jacob]

Ok, I did some playing around and here is what I came up with you can
tell me if it is right or not. I hadn't a second macro for the close
procedure so now I have two macros.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub

Private Sub Workbook_Open()
With Me.Worksheets("Current Revised")
.Select
.Range("a1").Select
If .FilterMode Then
.ShowAllData
End If
End With
Me.Saved = True
End Sub

This seems to work alright to me. Let me know what you think.

Thanks,

Jacob

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]

It looks ok to me.

Ok, I did some playing around and here is what I came up with you can
tell me if it is right or not. I hadn't a second macro for the close
procedure so now I have two macros.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub

Private Sub Workbook_Open()
With Me.Worksheets("Current Revised")
.Select
.Range("a1").Select
If .FilterMode Then
.ShowAllData
End If
End With
Me.Saved = True
End Sub

This seems to work alright to me. Let me know what you think.

Thanks,

Jacob