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

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.

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

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
>

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" <dtgdenver@discussions.microsoft.com> wrote in message
news:DC3156F5-2E61-4561-ABA1-C81829F74D08@microsoft.com...
> 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
>>

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" <dtgdenver@discussions.microsoft.com> wrote in message
> news:DC3156F5-2E61-4561-ABA1-C81829F74D08@microsoft.com...
> > 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
> >>
>
>
>

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" <dtgdenver@discussions.microsoft.com> wrote in message
> news:DC3156F5-2E61-4561-ABA1-C81829F74D08@microsoft.com...
> > 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
> >>
>
>
>

Congratulations! The only way to learn.

--

Vasant


"dtg_denver" <dtgdenver@discussions.microsoft.com> wrote in message
news:079D3D1D-9F3E-448C-A819-C1244F332785@microsoft.com...
>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" <dtgdenver@discussions.microsoft.com> wrote in message
>> news:DC3156F5-2E61-4561-ABA1-C81829F74D08@microsoft.com...
>> > 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
>> >>
>>
>>
>>

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" <dtgdenver@discussions.microsoft.com> wrote in message
> news:DC3156F5-2E61-4561-ABA1-C81829F74D08@microsoft.com...
> > 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
> >>
>
>
>

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" <dtgdenver@discussions.microsoft.com> wrote in message
> > news:DC3156F5-2E61-4561-ABA1-C81829F74D08@microsoft.com...
> > > 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