1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Tidy VB?

Discussion in 'Scripting & Programming' started by quuuaid, Mar 4, 2011.

  1. quuuaid

    quuuaid Nibble Poster

    65
    2
    34
    Hi all. I recently needed to create an form in Excel with a button to clear the user input cells when the user was done with it.

    I used the following VB with the button:

    Code:
    Private Sub CommandButton1_Click()
    
    Range("C5,B9:B12,B18,H18,C29,C33,C36,D36,E36,F36,G36,H36,I36,J36,K36").Select
    Selection.ClearContents
    Range("C5").Select
    
    End Sub
    Now, I protected the sheet to prevent users from amending cells other than those in the Range. I cleared the "Protect" checkbox in the user-input cells to accomplish this. However, when I pressed the button I got an error message that the sheet was protected and therefore read-only (double-checked Protect checkboxes - all cleared). I got around this like so (the secrecy of the password is unimportant):

    Code:
    Private Sub CommandButton1_Click()
    
    ActiveSheet.Unprotect (Password = "password")
    Range("C5,B9:B12,B18,H18,C29,C33,C36,D36,E36,F36,G36,H36,I36,J36,K36").Select
    Selection.ClearContents
    ActiveSheet.Protect (Password = "password")
    Range("C5").Select
    
    End Sub
    This works but I'm sure there's a better way of going about it. Any comments\suggestions?
     
    Certifications: A+, N+, MCDST
    WIP: Drupal, PHP

Share This Page

Loading...