Clearing data from a so called protected sheet???

[funkymonkUK]

Hi I have a button on a worksheet (menu). when the user clicks on it it must check they filled in some data which works. It then runs a macro called compile this macro asks them to select which workbooks they want to combine they select the workbooks it then opens the workbooks up and pastes in to the relevant sheet. It then emails the data off to me. NOW for the problem. once compile is finished it then needs to clear the sheets that figures are in. It then tells me that the worksheet is protected which I dont understand as I have unprotected the sheet to input the data which has worked it does not get put on it debugs at the spot where the is. I stop the code and check under tools/protection and it is not protected.

Can somebody help?

Below is my code followed by my error message


Private Sub CommandButton1_Click()

If Range("c2").Value = "" Or Range("c2").Value = "<Blank>" Then
MsgBox "Please enter in your area name"
Range("c2").Activate
Exit Sub
ElseIf Range("n2").Value = "" Then
MsgBox "Please enter in your the month you are compiling."
Range("n2").Activate
Exit Sub
ElseIf Range("p2").Value = "" Then
MsgBox "Please enter in your the year you are compiling."
Range("p2").Activate
Exit Sub
End If
Worksheets("1").Unprotect otherPassword
Worksheets("2").Unprotect otherPassword
Worksheets("3").Unprotect otherPassword

'run code to extract data from other workbooks & email data off
compile

'Clear the sheets that have data
Sheets("1").Select
Range("F9:k28").ClearContents
Sheets("2").Select
Range("F9:K28").Select
Selection.ClearContents
Sheets("LCIS").Select
Range("3").Select
Selection.ClearContents
Sheets("menu").Select
Range("a1").Activate

Worksheets("1").Protect otherPassword
Worksheets("2").Protect otherPassword
Worksheets("3").Protect otherPassword
Sheets("menu").Activate
ComboBox1.Value = "<Blank>"
ComboBox2.Value = "<Blank>"
ComboBox3.Value = "<Blank>"
End Sub


My Error Message: Runtime Error 1004: The cell or chart you are trying to change is portected and therefore read-only.
To modify a protected cell or chart, first remove protection using the Unprotect sheet command (Tools mennu, Protection Submennu) you may be prompted for a password

[Ivan Raiminius]

Hi,

are you sure you are unprotecting worksheets of the right one workbook?
I am asking, because in VBA you don't get any error message while
unprotecting worksheet that was not protected (this means that your
protected worksheet could remain protected, because you unprotected
different worksheet).

Regards,
Ivan

[funkymonkUK]

It is the only file I have open when it unprotects. I have checked by stepping through the code the page is protected before I start the code and as I step through each sheet is unprotected.

[Tom Ogilvy]

Since your code is in a sheet module, unqualified references to ranges refer
to that sheet - not the activesheet, so you are not refering to sheets "1",
"2", or "3", but to the sheet containing the code (which I assume remains
protected).

'Clear the sheets that have data
Sheets("1").Select
Range("F9:k28").ClearContents
Sheets("2").Select
Range("F9:K28").Select
Selection.ClearContents
Sheets("LCIS").Select
Range("3").Select
Selection.ClearContents
Sheets("menu").Select
Range("a1").Activate

should be

'Clear the sheets that have data
Sheets("1").Range("F9:k28").ClearContents
Sheets("2").Range("F9:K28").ClearContents
' the next line seems strange since you unprotected worksheets("3")
Sheets("LCIS").Range("3").ClearContents
Sheets("menu").select
Sheets("menu").Range("a1").Select

--
Regards,
Tom Ogilvy

"funkymonkUK" wrote:

>
> It is the only file I have open when it unprotects. I have checked by
> stepping through the code the page is protected before I start the code
> and as I step through each sheet is unprotected.
>
>
> --
> funkymonkUK
> ------------------------------------------------------------------------
> funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
> View this thread: http://www.excelforum.com/showthread...hreadid=542818
>
>

[funkymonkUK]

sorry tom was surpose to be sheet("3") not lcis. Anyway it works a treat. so in that case if I am coding on the Sheet then I must not use terms such as sheets("1").select or . activate?

[Tom Ogilvy]

Assume you code is in a sheet name LCIS

You can use Sheets("1").Select and that sheet will be selected, but if you
next do

Range("A1").Select

then you will get an error because it will try to select

Worksheets("LCIS").Range("A1")
which is not on the active sheet.

Likewise
Worksheets("1").Select
Range("A1").Value = 3

would be the equivalent of
Worksheets("1").Select
Worksheets("LCIS").Range("A1").Value = 3

to actually assign the value to A1 of "1", you would need
Worksheets("1").Select
Worksheets("1").Range("A1").Value = 3

where Worksheets("1").Select would be optional and only for visible feedback
to the user.

If you don't need to select a sheet, it is always better to work with it
through references since this is usually much faster. Note that an
overwhelming majority of actions can be accomplished without selecting, but
sometimes it can not be avoided.

--
Regards,
Tom Ogilvy



"funkymonkUK" wrote:

>
> sorry tom was surpose to be sheet("3") not lcis. Anyway it works a
> treat. so in that case if I am coding on the Sheet then I must not use
> terms such as sheets("1").select or . activate?
>
>
> --
> funkymonkUK
> ------------------------------------------------------------------------
> funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
> View this thread: http://www.excelforum.com/showthread...hreadid=542818
>
>