Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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 :
    Worksheets("Sheet" & i).Unprotect Password:="pass"
    ....
    ....
    ....
    Worksheets("Sheet" & i).Protect Password:="pass"

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

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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).Unprotect Password:="pass"
    Worksheets("Sheet" & i).Range("C6:C40,D6,E6:F40,G6:M12,G14:M20,G22:M28, G30:M36,G38:M40").ClearContents
    Worksheets("Sheet" & i).Protect Password:="pass"

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    Next
    End Sub
    Regards,
    Rudi

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Regards,
    Rudi

Posting Permissions

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