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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Great idea, however the macro won't unprotect the sheet without manually
putting in the password. It will re-protect it though.
 
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.
 
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.
 
I got brave and went in and edited the macro using the code you gave me and
it works great. Thanks.
 
Congratulations! The only way to learn.

--

Vasant


dtg_denver said:
I got brave and went in and edited the macro using the code you gave me and
it works great. Thanks.
Click to expand...
 
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 said:
Can the protection be turned back on with the option to AutoFilter being
turned on?
Click to expand...
 
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.
 
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).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel 2007 Run-Time error 1
Run Macro If Cell have "x" value 0
1004 Run-Time Error 3
Run Time Error 1004 1
Copying in sheet with protection with the use of a macro 5
Run-time Error '1004' 1
runtime error code 1004 2
"Run-time error '1004' 5

Back
Top