| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run time error 1004 on protected worksheet when I try to run macro
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run time error 1004 on protected worksheet when I try to run m
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run time error 1004 on protected worksheet when I try to run m
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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run time error 1004 on protected worksheet when I try to run m
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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run time error 1004 on protected worksheet when I try to run m
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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run time error 1004 on protected worksheet when I try to run m
Congratulations! The only way to learn.
-- Vasant "dtg_denver" wrote in message ... 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" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run time error 1004 on protected worksheet when I try to run m
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" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run time error 1004 on protected worksheet when I try to run m
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" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run time error 1004 on protected worksheet when I try to run m
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" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run time error 1004 on protected worksheet when I try to run m
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" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run time error 1004 on protected worksheet when I try to run m
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" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run time error 1004 on protected worksheet when I try to run m
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" wrote in message ... 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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run time error 1004 on protected worksheet when I try to run m
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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
| I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
| MACRO Run Time Error 1004 | Excel Discussion (Misc queries) | |||
| Run-time error 1004: Cannot shift nonblank cells off the worksheet | Excel Discussion (Misc queries) | |||
| Macro Issue - Run time error 1004 | Excel Discussion (Misc queries) | |||
Linear Mode
