[SOLVED] run time error 1004 on protected worksheet when I try to run macro

[dtg_denver]

I created a macro to import a csv data file. The macro works fine when the
worksheet in unprotected. When I protect the worksheet and click on the macro
button I get a run time erro 1004. I have tried protecting the worksheet with
all the options checked off, but I still get the error. Any idea would be
helpful.

[Dave Peterson]

Have your macro unprotect the worksheet, do the import, and reprotect the
workbook.



dtg_denver wrote:
>
> I created a macro to import a csv data file. The macro works fine when the
> worksheet in unprotected. When I protect the worksheet and click on the macro
> button I get a run time erro 1004. I have tried protecting the worksheet with
> all the options checked off, but I still get the error. Any idea would be
> helpful.

--

Dave Peterson

[dtg_denver]

Great idea, however the macro won't unprotect the sheet without manually
putting in the password. It will re-protect it though.

"Dave Peterson" wrote:

> Have your macro unprotect the worksheet, do the import, and reprotect the
> workbook.
>
>
>
> dtg_denver wrote:
> >
> > I created a macro to import a csv data file. The macro works fine when the
> > worksheet in unprotected. When I protect the worksheet and click on the macro
> > button I get a run time erro 1004. I have tried protecting the worksheet with
> > all the options checked off, but I still get the error. Any idea would be
> > helpful.
>
> --
>
> Dave Peterson
>

[Vasant Nanavati]

Yes, you have to provide the password.

Worksheets("MySheet").Unprotect Password:="PW"

You have to reprotect it the same way.

Alternatively, you may want to protect the worksheet (you can only do this
through code) with the UserInterfaceOnly parameter set to True. This will
prohibit manual changes but will allow a macro to make changes.

--

Vasant


"dtg_denver" <[email protected]> wrote in message
news:[email protected]...
> Great idea, however the macro won't unprotect the sheet without manually
> putting in the password. It will re-protect it though.
>
> "Dave Peterson" wrote:
>
>> Have your macro unprotect the worksheet, do the import, and reprotect the
>> workbook.
>>
>>
>>
>> dtg_denver wrote:
>> >
>> > I created a macro to import a csv data file. The macro works fine when
>> > the
>> > worksheet in unprotected. When I protect the worksheet and click on the
>> > macro
>> > button I get a run time erro 1004. I have tried protecting the
>> > worksheet with
>> > all the options checked off, but I still get the error. Any idea would
>> > be
>> > helpful.
>>
>> --
>>
>> Dave Peterson
>>

[dtg_denver]

Thanks for the input. However, it looks like I'm in way over my head. I
wouldn't know how to add the code you just suggested. I can record a simple
macro and that is about it.

"Vasant Nanavati" wrote:

> Yes, you have to provide the password.
>
> Worksheets("MySheet").Unprotect Password:="PW"
>
> You have to reprotect it the same way.
>
> Alternatively, you may want to protect the worksheet (you can only do this
> through code) with the UserInterfaceOnly parameter set to True. This will
> prohibit manual changes but will allow a macro to make changes.
>
> --
>
> Vasant
>
>
> "dtg_denver" <[email protected]> wrote in message
> news:[email protected]...
> > Great idea, however the macro won't unprotect the sheet without manually
> > putting in the password. It will re-protect it though.
> >
> > "Dave Peterson" wrote:
> >
> >> Have your macro unprotect the worksheet, do the import, and reprotect the
> >> workbook.
> >>
> >>
> >>
> >> dtg_denver wrote:
> >> >
> >> > I created a macro to import a csv data file. The macro works fine when
> >> > the
> >> > worksheet in unprotected. When I protect the worksheet and click on the
> >> > macro
> >> > button I get a run time erro 1004. I have tried protecting the
> >> > worksheet with
> >> > all the options checked off, but I still get the error. Any idea would
> >> > be
> >> > helpful.
> >>
> >> --
> >>
> >> Dave Peterson
> >>
>
>
>

[dtg_denver]

I got brave and went in and edited the macro using the code you gave me and
it works great. Thanks.

"Vasant Nanavati" wrote:

> Yes, you have to provide the password.
>
> Worksheets("MySheet").Unprotect Password:="PW"
>
> You have to reprotect it the same way.
>
> Alternatively, you may want to protect the worksheet (you can only do this
> through code) with the UserInterfaceOnly parameter set to True. This will
> prohibit manual changes but will allow a macro to make changes.
>
> --
>
> Vasant
>
>
> "dtg_denver" <[email protected]> wrote in message
> news:[email protected]...
> > Great idea, however the macro won't unprotect the sheet without manually
> > putting in the password. It will re-protect it though.
> >
> > "Dave Peterson" wrote:
> >
> >> Have your macro unprotect the worksheet, do the import, and reprotect the
> >> workbook.
> >>
> >>
> >>
> >> dtg_denver wrote:
> >> >
> >> > I created a macro to import a csv data file. The macro works fine when
> >> > the
> >> > worksheet in unprotected. When I protect the worksheet and click on the
> >> > macro
> >> > button I get a run time erro 1004. I have tried protecting the
> >> > worksheet with
> >> > all the options checked off, but I still get the error. Any idea would
> >> > be
> >> > helpful.
> >>
> >> --
> >>
> >> Dave Peterson
> >>
>
>
>

[Vasant Nanavati]

Congratulations! The only way to learn.

--

Vasant


"dtg_denver" <[email protected]> wrote in message
news:[email protected]...
>I got brave and went in and edited the macro using the code you gave me and
> it works great. Thanks.
>
> "Vasant Nanavati" wrote:
>
>> Yes, you have to provide the password.
>>
>> Worksheets("MySheet").Unprotect Password:="PW"
>>
>> You have to reprotect it the same way.
>>
>> Alternatively, you may want to protect the worksheet (you can only do
>> this
>> through code) with the UserInterfaceOnly parameter set to True. This will
>> prohibit manual changes but will allow a macro to make changes.
>>
>> --
>>
>> Vasant
>>
>>
>> "dtg_denver" <[email protected]> wrote in message
>> news:[email protected]...
>> > Great idea, however the macro won't unprotect the sheet without
>> > manually
>> > putting in the password. It will re-protect it though.
>> >
>> > "Dave Peterson" wrote:
>> >
>> >> Have your macro unprotect the worksheet, do the import, and reprotect
>> >> the
>> >> workbook.
>> >>
>> >>
>> >>
>> >> dtg_denver wrote:
>> >> >
>> >> > I created a macro to import a csv data file. The macro works fine
>> >> > when
>> >> > the
>> >> > worksheet in unprotected. When I protect the worksheet and click on
>> >> > the
>> >> > macro
>> >> > button I get a run time erro 1004. I have tried protecting the
>> >> > worksheet with
>> >> > all the options checked off, but I still get the error. Any idea
>> >> > would
>> >> > be
>> >> > helpful.
>> >>
>> >> --
>> >>
>> >> Dave Peterson
>> >>
>>
>>
>>

[dtg_denver]

Can the protection be turned back on with the option to AutoFilter being
turned on?

"Vasant Nanavati" wrote:

> Yes, you have to provide the password.
>
> Worksheets("MySheet").Unprotect Password:="PW"
>
> You have to reprotect it the same way.
>
> Alternatively, you may want to protect the worksheet (you can only do this
> through code) with the UserInterfaceOnly parameter set to True. This will
> prohibit manual changes but will allow a macro to make changes.
>
> --
>
> Vasant
>
>
> "dtg_denver" <[email protected]> wrote in message
> news:[email protected]...
> > Great idea, however the macro won't unprotect the sheet without manually
> > putting in the password. It will re-protect it though.
> >
> > "Dave Peterson" wrote:
> >
> >> Have your macro unprotect the worksheet, do the import, and reprotect the
> >> workbook.
> >>
> >>
> >>
> >> dtg_denver wrote:
> >> >
> >> > I created a macro to import a csv data file. The macro works fine when
> >> > the
> >> > worksheet in unprotected. When I protect the worksheet and click on the
> >> > macro
> >> > button I get a run time erro 1004. I have tried protecting the
> >> > worksheet with
> >> > all the options checked off, but I still get the error. Any idea would
> >> > be
> >> > helpful.
> >>
> >> --
> >>
> >> Dave Peterson
> >>
>
>
>

[Dave Peterson]

I would think that you would want the autofilter enabled all the time. If
that's the case...

If you already have the outline applied, you can protect the worksheet in code
(auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

If I protected the worksheet this way (in xl2003), I could unprotect it and
reprotect it in code and this setting was remembered. (I didn't need to put
that .enableautofilter in the reprotection code.)

But you could add it to each .protect section of code if you need to.


dtg_denver wrote:
>
> Can the protection be turned back on with the option to AutoFilter being
> turned on?
>
> "Vasant Nanavati" wrote:
>
> > Yes, you have to provide the password.
> >
> > Worksheets("MySheet").Unprotect Password:="PW"
> >
> > You have to reprotect it the same way.
> >
> > Alternatively, you may want to protect the worksheet (you can only do this
> > through code) with the UserInterfaceOnly parameter set to True. This will
> > prohibit manual changes but will allow a macro to make changes.
> >
> > --
> >
> > Vasant
> >
> >
> > "dtg_denver" <[email protected]> wrote in message
> > news:[email protected]...
> > > Great idea, however the macro won't unprotect the sheet without manually
> > > putting in the password. It will re-protect it though.
> > >
> > > "Dave Peterson" wrote:
> > >
> > >> Have your macro unprotect the worksheet, do the import, and reprotect the
> > >> workbook.
> > >>
> > >>
> > >>
> > >> dtg_denver wrote:
> > >> >
> > >> > I created a macro to import a csv data file. The macro works fine when
> > >> > the
> > >> > worksheet in unprotected. When I protect the worksheet and click on the
> > >> > macro
> > >> > button I get a run time erro 1004. I have tried protecting the
> > >> > worksheet with
> > >> > all the options checked off, but I still get the error. Any idea would
> > >> > be
> > >> > helpful.
> > >>
> > >> --
> > >>
> > >> Dave Peterson
> > >>
> >
> >
> >

--

Dave Peterson

[dtg_denver]

I'm using Excel 2003 and I'm currently unprotecting and protecting the sheet
using the code suggested earlier in this thread.

Worksheets("Master").Unprotect Password:="pwd"
ActiveWorkbook.RefreshAll
Worksheets("Master").Protect Password:="pwd"

I'm very new to this whole programming stuff and not real sure what you are
talking about with auto open.

"Dave Peterson" wrote:

> I would think that you would want the autofilter enabled all the time. If
> that's the case...
>
> If you already have the outline applied, you can protect the worksheet in code
> (auto_open/workbook_open??).
>
> Option Explicit
> Sub auto_open()
> With Worksheets("sheet1")
> .Protect Password:="hi", userinterfaceonly:=True
> .EnableAutoFilter = True
> End With
> End Sub
>
> It needs to be reset each time you open the workbook. (excel doesn't remember
> it after closing the workbook.)
>
> If I protected the worksheet this way (in xl2003), I could unprotect it and
> reprotect it in code and this setting was remembered. (I didn't need to put
> that .enableautofilter in the reprotection code.)
>
> But you could add it to each .protect section of code if you need to.
>
>
> dtg_denver wrote:
> >
> > Can the protection be turned back on with the option to AutoFilter being
> > turned on?
> >
> > "Vasant Nanavati" wrote:
> >
> > > Yes, you have to provide the password.
> > >
> > > Worksheets("MySheet").Unprotect Password:="PW"
> > >
> > > You have to reprotect it the same way.
> > >
> > > Alternatively, you may want to protect the worksheet (you can only do this
> > > through code) with the UserInterfaceOnly parameter set to True. This will
> > > prohibit manual changes but will allow a macro to make changes.
> > >
> > > --
> > >
> > > Vasant
> > >
> > >
> > > "dtg_denver" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Great idea, however the macro won't unprotect the sheet without manually
> > > > putting in the password. It will re-protect it though.
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > >> Have your macro unprotect the worksheet, do the import, and reprotect the
> > > >> workbook.
> > > >>
> > > >>
> > > >>
> > > >> dtg_denver wrote:
> > > >> >
> > > >> > I created a macro to import a csv data file. The macro works fine when
> > > >> > the
> > > >> > worksheet in unprotected. When I protect the worksheet and click on the
> > > >> > macro
> > > >> > button I get a run time erro 1004. I have tried protecting the
> > > >> > worksheet with
> > > >> > all the options checked off, but I still get the error. Any idea would
> > > >> > be
> > > >> > helpful.
> > > >>
> > > >> --
> > > >>
> > > >> Dave Peterson
> > > >>
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Auto_open is a subroutine that is placed into a general module (not behind
ThisWorkbook and not behind a worksheet).

It runs each time the workbook is opened (with macros enabled).



dtg_denver wrote:
>
> I'm using Excel 2003 and I'm currently unprotecting and protecting the sheet
> using the code suggested earlier in this thread.
>
> Worksheets("Master").Unprotect Password:="pwd"
> ActiveWorkbook.RefreshAll
> Worksheets("Master").Protect Password:="pwd"
>
> I'm very new to this whole programming stuff and not real sure what you are
> talking about with auto open.
>
> "Dave Peterson" wrote:
>
> > I would think that you would want the autofilter enabled all the time. If
> > that's the case...
> >
> > If you already have the outline applied, you can protect the worksheet in code
> > (auto_open/workbook_open??).
> >
> > Option Explicit
> > Sub auto_open()
> > With Worksheets("sheet1")
> > .Protect Password:="hi", userinterfaceonly:=True
> > .EnableAutoFilter = True
> > End With
> > End Sub
> >
> > It needs to be reset each time you open the workbook. (excel doesn't remember
> > it after closing the workbook.)
> >
> > If I protected the worksheet this way (in xl2003), I could unprotect it and
> > reprotect it in code and this setting was remembered. (I didn't need to put
> > that .enableautofilter in the reprotection code.)
> >
> > But you could add it to each .protect section of code if you need to.
> >
> >
> > dtg_denver wrote:
> > >
> > > Can the protection be turned back on with the option to AutoFilter being
> > > turned on?
> > >
> > > "Vasant Nanavati" wrote:
> > >
> > > > Yes, you have to provide the password.
> > > >
> > > > Worksheets("MySheet").Unprotect Password:="PW"
> > > >
> > > > You have to reprotect it the same way.
> > > >
> > > > Alternatively, you may want to protect the worksheet (you can only do this
> > > > through code) with the UserInterfaceOnly parameter set to True. This will
> > > > prohibit manual changes but will allow a macro to make changes.
> > > >
> > > > --
> > > >
> > > > Vasant
> > > >
> > > >
> > > > "dtg_denver" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Great idea, however the macro won't unprotect the sheet without manually
> > > > > putting in the password. It will re-protect it though.
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > >> Have your macro unprotect the worksheet, do the import, and reprotect the
> > > > >> workbook.
> > > > >>
> > > > >>
> > > > >>
> > > > >> dtg_denver wrote:
> > > > >> >
> > > > >> > I created a macro to import a csv data file. The macro works fine when
> > > > >> > the
> > > > >> > worksheet in unprotected. When I protect the worksheet and click on the
> > > > >> > macro
> > > > >> > button I get a run time erro 1004. I have tried protecting the
> > > > >> > worksheet with
> > > > >> > all the options checked off, but I still get the error. Any idea would
> > > > >> > be
> > > > >> > helpful.
> > > > >>
> > > > >> --
> > > > >>
> > > > >> Dave Peterson
> > > > >>
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[dtg_denver]

I don't have a clue as to what I just did, but it worked. Thanks a million.

"Dave Peterson" wrote:

> Auto_open is a subroutine that is placed into a general module (not behind
> ThisWorkbook and not behind a worksheet).
>
> It runs each time the workbook is opened (with macros enabled).
>
>
>
> dtg_denver wrote:
> >
> > I'm using Excel 2003 and I'm currently unprotecting and protecting the sheet
> > using the code suggested earlier in this thread.
> >
> > Worksheets("Master").Unprotect Password:="pwd"
> > ActiveWorkbook.RefreshAll
> > Worksheets("Master").Protect Password:="pwd"
> >
> > I'm very new to this whole programming stuff and not real sure what you are
> > talking about with auto open.
> >
> > "Dave Peterson" wrote:
> >
> > > I would think that you would want the autofilter enabled all the time. If
> > > that's the case...
> > >
> > > If you already have the outline applied, you can protect the worksheet in code
> > > (auto_open/workbook_open??).
> > >
> > > Option Explicit
> > > Sub auto_open()
> > > With Worksheets("sheet1")
> > > .Protect Password:="hi", userinterfaceonly:=True
> > > .EnableAutoFilter = True
> > > End With
> > > End Sub
> > >
> > > It needs to be reset each time you open the workbook. (excel doesn't remember
> > > it after closing the workbook.)
> > >
> > > If I protected the worksheet this way (in xl2003), I could unprotect it and
> > > reprotect it in code and this setting was remembered. (I didn't need to put
> > > that .enableautofilter in the reprotection code.)
> > >
> > > But you could add it to each .protect section of code if you need to.
> > >
> > >
> > > dtg_denver wrote:
> > > >
> > > > Can the protection be turned back on with the option to AutoFilter being
> > > > turned on?
> > > >
> > > > "Vasant Nanavati" wrote:
> > > >
> > > > > Yes, you have to provide the password.
> > > > >
> > > > > Worksheets("MySheet").Unprotect Password:="PW"
> > > > >
> > > > > You have to reprotect it the same way.
> > > > >
> > > > > Alternatively, you may want to protect the worksheet (you can only do this
> > > > > through code) with the UserInterfaceOnly parameter set to True. This will
> > > > > prohibit manual changes but will allow a macro to make changes.
> > > > >
> > > > > --
> > > > >
> > > > > Vasant
> > > > >
> > > > >
> > > > > "dtg_denver" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Great idea, however the macro won't unprotect the sheet without manually
> > > > > > putting in the password. It will re-protect it though.
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > >> Have your macro unprotect the worksheet, do the import, and reprotect the
> > > > > >> workbook.
> > > > > >>
> > > > > >>
> > > > > >>
> > > > > >> dtg_denver wrote:
> > > > > >> >
> > > > > >> > I created a macro to import a csv data file. The macro works fine when
> > > > > >> > the
> > > > > >> > worksheet in unprotected. When I protect the worksheet and click on the
> > > > > >> > macro
> > > > > >> > button I get a run time erro 1004. I have tried protecting the
> > > > > >> > worksheet with
> > > > > >> > all the options checked off, but I still get the error. Any idea would
> > > > > >> > be
> > > > > >> > helpful.
> > > > > >>
> > > > > >> --
> > > > > >>
> > > > > >> Dave Peterson
> > > > > >>
> > > > >
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

When magic occurs, make sure you keep a backup! <vbg>



dtg_denver wrote:
>
> I don't have a clue as to what I just did, but it worked. Thanks a million.
>
> "Dave Peterson" wrote:
>
> > Auto_open is a subroutine that is placed into a general module (not behind
> > ThisWorkbook and not behind a worksheet).
> >
> > It runs each time the workbook is opened (with macros enabled).
> >
> >
> >
> > dtg_denver wrote:
> > >
> > > I'm using Excel 2003 and I'm currently unprotecting and protecting the sheet
> > > using the code suggested earlier in this thread.
> > >
> > > Worksheets("Master").Unprotect Password:="pwd"
> > > ActiveWorkbook.RefreshAll
> > > Worksheets("Master").Protect Password:="pwd"
> > >
> > > I'm very new to this whole programming stuff and not real sure what you are
> > > talking about with auto open.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > I would think that you would want the autofilter enabled all the time. If
> > > > that's the case...
> > > >
> > > > If you already have the outline applied, you can protect the worksheet in code
> > > > (auto_open/workbook_open??).
> > > >
> > > > Option Explicit
> > > > Sub auto_open()
> > > > With Worksheets("sheet1")
> > > > .Protect Password:="hi", userinterfaceonly:=True
> > > > .EnableAutoFilter = True
> > > > End With
> > > > End Sub
> > > >
> > > > It needs to be reset each time you open the workbook. (excel doesn't remember
> > > > it after closing the workbook.)
> > > >
> > > > If I protected the worksheet this way (in xl2003), I could unprotect it and
> > > > reprotect it in code and this setting was remembered. (I didn't need to put
> > > > that .enableautofilter in the reprotection code.)
> > > >
> > > > But you could add it to each .protect section of code if you need to.
> > > >
> > > >
> > > > dtg_denver wrote:
> > > > >
> > > > > Can the protection be turned back on with the option to AutoFilter being
> > > > > turned on?
> > > > >
> > > > > "Vasant Nanavati" wrote:
> > > > >
> > > > > > Yes, you have to provide the password.
> > > > > >
> > > > > > Worksheets("MySheet").Unprotect Password:="PW"
> > > > > >
> > > > > > You have to reprotect it the same way.
> > > > > >
> > > > > > Alternatively, you may want to protect the worksheet (you can only do this
> > > > > > through code) with the UserInterfaceOnly parameter set to True. This will
> > > > > > prohibit manual changes but will allow a macro to make changes.
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Vasant
> > > > > >
> > > > > >
> > > > > > "dtg_denver" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Great idea, however the macro won't unprotect the sheet without manually
> > > > > > > putting in the password. It will re-protect it though.
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > >> Have your macro unprotect the worksheet, do the import, and reprotect the
> > > > > > >> workbook.
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > > >> dtg_denver wrote:
> > > > > > >> >
> > > > > > >> > I created a macro to import a csv data file. The macro works fine when
> > > > > > >> > the
> > > > > > >> > worksheet in unprotected. When I protect the worksheet and click on the
> > > > > > >> > macro
> > > > > > >> > button I get a run time erro 1004. I have tried protecting the
> > > > > > >> > worksheet with
> > > > > > >> > all the options checked off, but I still get the error. Any idea would
> > > > > > >> > be
> > > > > > >> > helpful.
> > > > > > >>
> > > > > > >> --
> > > > > > >>
> > > > > > >> Dave Peterson
> > > > > > >>
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Dave Peterson]

You may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

dtg_denver wrote:
>
> I don't have a clue as to what I just did, but it worked. Thanks a million.
>
> "Dave Peterson" wrote:
>
> > Auto_open is a subroutine that is placed into a general module (not behind
> > ThisWorkbook and not behind a worksheet).
> >
> > It runs each time the workbook is opened (with macros enabled).
> >
> >
> >
> > dtg_denver wrote:
> > >
> > > I'm using Excel 2003 and I'm currently unprotecting and protecting the sheet
> > > using the code suggested earlier in this thread.
> > >
> > > Worksheets("Master").Unprotect Password:="pwd"
> > > ActiveWorkbook.RefreshAll
> > > Worksheets("Master").Protect Password:="pwd"
> > >
> > > I'm very new to this whole programming stuff and not real sure what you are
> > > talking about with auto open.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > I would think that you would want the autofilter enabled all the time. If
> > > > that's the case...
> > > >
> > > > If you already have the outline applied, you can protect the worksheet in code
> > > > (auto_open/workbook_open??).
> > > >
> > > > Option Explicit
> > > > Sub auto_open()
> > > > With Worksheets("sheet1")
> > > > .Protect Password:="hi", userinterfaceonly:=True
> > > > .EnableAutoFilter = True
> > > > End With
> > > > End Sub
> > > >
> > > > It needs to be reset each time you open the workbook. (excel doesn't remember
> > > > it after closing the workbook.)
> > > >
> > > > If I protected the worksheet this way (in xl2003), I could unprotect it and
> > > > reprotect it in code and this setting was remembered. (I didn't need to put
> > > > that .enableautofilter in the reprotection code.)
> > > >
> > > > But you could add it to each .protect section of code if you need to.
> > > >
> > > >
> > > > dtg_denver wrote:
> > > > >
> > > > > Can the protection be turned back on with the option to AutoFilter being
> > > > > turned on?
> > > > >
> > > > > "Vasant Nanavati" wrote:
> > > > >
> > > > > > Yes, you have to provide the password.
> > > > > >
> > > > > > Worksheets("MySheet").Unprotect Password:="PW"
> > > > > >
> > > > > > You have to reprotect it the same way.
> > > > > >
> > > > > > Alternatively, you may want to protect the worksheet (you can only do this
> > > > > > through code) with the UserInterfaceOnly parameter set to True. This will
> > > > > > prohibit manual changes but will allow a macro to make changes.
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Vasant
> > > > > >
> > > > > >
> > > > > > "dtg_denver" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Great idea, however the macro won't unprotect the sheet without manually
> > > > > > > putting in the password. It will re-protect it though.
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > >> Have your macro unprotect the worksheet, do the import, and reprotect the
> > > > > > >> workbook.
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > > >> dtg_denver wrote:
> > > > > > >> >
> > > > > > >> > I created a macro to import a csv data file. The macro works fine when
> > > > > > >> > the
> > > > > > >> > worksheet in unprotected. When I protect the worksheet and click on the
> > > > > > >> > macro
> > > > > > >> > button I get a run time erro 1004. I have tried protecting the
> > > > > > >> > worksheet with
> > > > > > >> > all the options checked off, but I still get the error. Any idea would
> > > > > > >> > be
> > > > > > >> > helpful.
> > > > > > >>
> > > > > > >> --
> > > > > > >>
> > > > > > >> Dave Peterson
> > > > > > >>
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson