# Thread: Clear form button (2003 SP2)

1. ## Clear form button (2003 SP2)

Good afternoon

Can you, and if so some ponters please, add a button to one sheet to clear information on other sheets, for example

on click()
Clear Sheet1-5 \$c\$6:\$N\$12
Clear Sheet1-5 \$c\$14:\$N\$20
Clear Sheet1-5 \$c\$22:\$N\$28
Clear Sheet1-5 \$c\$30:\$N\$36
Clear Sheet1-5 \$c\$38:\$N\$40

If that is not possible could I add a button to each of the sheets, 1 through 5 to delete the information in those cells

If the sheet is protected would information in the locked cells remain undeleted, if so iy would be easier to do \$C\$6:\$N\$40

Cheers

Steve

2. ## Re: Clear form button (2003 SP2)

<P ID="edit" class=small>(Edited by sdckapr on 21-Dec-07 10:34. Added PS )</P>Use lines like:
Worksheets ("Sheet1-5").range("c6:N12").clearcontents

or if the "Sheet1-5 is meant to be sheet1, sheet2, ... sheet5 you could use:

dim i as integer
for i = 1 to 5
Worksheets ("Sheet" & i).range("c6:N12").clearcontents
'etc
next

If the sheet is protected, you should unprotect it in code, clear the contents, then reprotect. If you try to clear the contents of locked cells in a protected sheet you will get an error.

Steve
PS You could even add a second loop for the ranges:
dim i as integer
dim x as integer
for i = 1 to 5
for x = 6 to 38 step 8
Worksheets ("Sheet" & i).range("C" & x & ":N" & x+6).clearcontents
next x
next i

3. ## Re: Clear form button (2003 SP2)

Thanks Steve

I will try the second option because I need to retain information in locked cells

Cheers

Steve

4. ## Re: Clear form button (2003 SP2)

If you don't want to delete the contents, don't include them in the range or loop through the ranges clearing only the contents of the unlocked cells

Steve

5. ## Re: Clear form button (2003 SP2)

Good morning

I think I have got my knickers in a twist, any help would be appreciated

To summarise the above post

At the end of each month I want to be able to clear certain cells in 5 worksheets of a protected workbook , when unprotected the macro (thanks Steve) runs fine and clears the specified cells but will not work when the cells are protected. My help book 'Make yourself look like a dummy' offered a suggestion that I have tried to adapt with little (make that none) effect

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 5

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = No

objSheet.Unprotect
Next objSheet

Worksheets("Sheet" & i).Range("C6:C40").ClearContents
Worksheets("Sheet" & i).Range("D6").ClearContents
Worksheets("Sheet" & i).Range("E6:F40").ClearContents
Worksheets("Sheet" & i).Range("G6:M12").ClearContents
Worksheets("Sheet" & i).Range("G14:M20").ClearContents
Worksheets("Sheet" & i).Range("G22:M28").ClearContents
Worksheets("Sheet" & i).Range("G30:M36").ClearContents
Worksheets("Sheet" & i).Range("G38:M40").ClearContents

objSheet.Protect
Next objSheet

Application.ScreenUpdating = Yes
Application.Calculation = xlCalculationAutomatic

End Sub

Next

Cheers

Steve

6. ## Re: Clear form button (2003 SP2)

I seem to have worked it out

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 5

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = No

Worksheets("Sheet" & i).Unprotect

Worksheets("Sheet" & i).Range("C6:C40").ClearContents
Worksheets("Sheet" & i).Range("D6").ClearContents
Worksheets("Sheet" & i).Range("E6:F40").ClearContents
Worksheets("Sheet" & i).Range("G6:M12").ClearContents
Worksheets("Sheet" & i).Range("G14:M20").ClearContents
Worksheets("Sheet" & i).Range("G22:M28").ClearContents
Worksheets("Sheet" & i).Range("G30:M36").ClearContents
Worksheets("Sheet" & i).Range("G38:M40").ClearContents

Worksheets("Sheet" & i).Protect

Application.ScreenUpdating = Yes
Application.Calculation = xlCalculationAutomatic

Next
End Sub

Cheers

Steve

7. ## Re: Clear form button (2003 SP2)

Spoke to soon

When the worksheet is locked and I now click my button it opens (one at a time) a password entry form for each of the 5 sheets, thats OK for me because I know the passwords but the supervisors who will use the form do not, any ideas please how I can adjust the code to not ask for the password but just to unprotect, run the code, re-protect

Thanks

Steve

8. ## Re: Clear form button (2003 SP2)

The protect method has arguments where you can tell the macro what the password is. If you add the password to the macro, it will quietly unprotect, do the job and reprotect.

Try this :
....
....
....

(where "pass" is the password that you supply!)

9. ## Re: Clear form button (2003 SP2)

BTW: You can shorten the code to this...

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 5

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Worksheets("Sheet" & i).Range("C6:C40,D6,E6:F40,G6:M12,G14:M20,G22:M28, G30:M36,G38:M40").ClearContents

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Next
End Sub

10. ## Re: Clear form button (2003 SP2)

Cheers Rudi

That worked like a charm

Cheers

Steve

BTW: reference snow, have you not taken the cable up Table Mountain, I have stood in the snow and looked down over the city, stunning sight, on one of my visits, perhaps 2003 or 2004.

11. ## Re: Clear form button (2003 SP2)

I have been up many times, but never in winter. Way to chilly for my liking. It is a rare thing to have snow on Table Mountain anyways. We have snow on the mountain ranges to the east of Cape Town nearly every winter, but since it is about 100km from where I stay, and since there are crowds that go to see the phenominon, I never go. (Hate crowds).
Cheers

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•