Runtime error 1004 after 1st run

  • Thread starter Thread starter Arkansas Lady
  • Start date Start date
A

Arkansas Lady

I am creating an Excel spreadsheet from Micosoft Access. I am using the
following lines:

XLSheet.Protection.AllowEditRanges.Add Title:="Time", Range:=Range("H" &
Irow & ":I" & Mrow)
XLSheet.Protect Password:="cpw1959", DrawingObjects:=True, Contents:=True,
Scenarios:=True

The first time I run it, it works. The second it gives me a 1004 run-time
error. The third time works but the fourth time doesn't. Any ideas, please.
 
What version of Excel?

You will probably get a better answer by posting this question in an Excel
forum.

I suspect it has something to do with the fact that the sheet is already
protected by the first call, but I'm only guessing.
 
Graham,

I figured out that it runs the first time because I haven't created the
Title "Time" yet. I suspect that when I run it the second time, it detects
that it exists but maybe it's delete because the third time runs
successfully. I am running this through Access. Thanks
--
Arkansas Lady


Graham Mandeno said:
What version of Excel?

You will probably get a better answer by posting this question in an Excel
forum.

I suspect it has something to do with the fact that the sheet is already
protected by the first call, but I'm only guessing.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Arkansas Lady said:
I am creating an Excel spreadsheet from Micosoft Access. I am using the
following lines:

XLSheet.Protection.AllowEditRanges.Add Title:="Time", Range:=Range("H" &
Irow & ":I" & Mrow)
XLSheet.Protect Password:="cpw1959", DrawingObjects:=True, Contents:=True,
Scenarios:=True

The first time I run it, it works. The second it gives me a 1004 run-time
error. The third time works but the fourth time doesn't. Any ideas,
please.
Click to expand...
Click to expand...
 
I realise you are running it through Access, but you are in Excel object
space, using methods and properties from the Excel object model. That's why
I suggested an Excel forum.

However, I've had a play around with this and have discovered that the
enigmatic Error 1004 is raised in the following circumstances:

1. You try to Add an AllowEditRange using a Title that already exists
2. You try to Add or Delete an AllowEditRange when the sheet is protected.

I suggest you first execute XLSheet.Unprotect

Then check for an AllowEditRange named "Time" and delete it if found:

Dim aer as Object
For Each aer in XLSheet.Protection.AllowEditRanges
If aer.Title = "Time" Then
aer.Delete
Exit For
End If
Next

Then continue as before.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Arkansas Lady said:
Graham,

I figured out that it runs the first time because I haven't created the
Title "Time" yet. I suspect that when I run it the second time, it
detects
that it exists but maybe it's delete because the third time runs
successfully. I am running this through Access. Thanks
--
Arkansas Lady


Graham Mandeno said:
What version of Excel?

You will probably get a better answer by posting this question in an
Excel
forum.

I suspect it has something to do with the fact that the sheet is already
protected by the first call, but I'm only guessing.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Arkansas Lady said:
I am creating an Excel spreadsheet from Micosoft Access. I am using the
following lines:

XLSheet.Protection.AllowEditRanges.Add Title:="Time", Range:=Range("H"
&
Irow & ":I" & Mrow)
XLSheet.Protect Password:="cpw1959", DrawingObjects:=True,
Contents:=True,
Scenarios:=True

The first time I run it, it works. The second it gives me a 1004
run-time
error. The third time works but the fourth time doesn't. Any ideas,
please.
Click to expand...
Click to expand...
Click to expand...
 
Graham,

I just tried this and I'm getting the same results. I will move this to the
Excel Discussion group and see what happens. I did run it the first time
with the Title "Time" and it worked (as before). Before I ran it the second
time, I changed the title to another name and it failed. So the problem must
not be in the title. Again, thank you for your assistance.
--
Arkansas Lady


Graham Mandeno said:
I realise you are running it through Access, but you are in Excel object
space, using methods and properties from the Excel object model. That's why
I suggested an Excel forum.

However, I've had a play around with this and have discovered that the
enigmatic Error 1004 is raised in the following circumstances:

1. You try to Add an AllowEditRange using a Title that already exists
2. You try to Add or Delete an AllowEditRange when the sheet is protected.

I suggest you first execute XLSheet.Unprotect

Then check for an AllowEditRange named "Time" and delete it if found:

Dim aer as Object
For Each aer in XLSheet.Protection.AllowEditRanges
If aer.Title = "Time" Then
aer.Delete
Exit For
End If
Next

Then continue as before.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Arkansas Lady said:
Graham,

I figured out that it runs the first time because I haven't created the
Title "Time" yet. I suspect that when I run it the second time, it
detects
that it exists but maybe it's delete because the third time runs
successfully. I am running this through Access. Thanks
--
Arkansas Lady


Graham Mandeno said:
What version of Excel?

You will probably get a better answer by posting this question in an
Excel
forum.

I suspect it has something to do with the fact that the sheet is already
protected by the first call, but I'm only guessing.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am creating an Excel spreadsheet from Micosoft Access. I am using the
following lines:

XLSheet.Protection.AllowEditRanges.Add Title:="Time", Range:=Range("H"
&
Irow & ":I" & Mrow)
XLSheet.Protect Password:="cpw1959", DrawingObjects:=True,
Contents:=True,
Scenarios:=True

The first time I run it, it works. The second it gives me a 1004
run-time
error. The third time works but the fourth time doesn't. Any ideas,
please.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top