Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cells (Excel 2K)

    I have a cell (C10) with a list that allows the user to select either "Yes" or "No". I want to unlock cell C11 and change its background color on the fly if the user selects "Yes". I want C11 to remain locked and unchanged if the user selects "No". I give up. All my attempts have gone nowhere. Any help is appreciated. Thanks!!!

  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: Cells (Excel 2K)

    Try this. It assumes the password is "drowssap"
    If you enter in yes in C10, the sheet is unprotected, c11 is unlocked and background changed to red, then the sheet is again protected.

    If you enter anything else into c10, the sheet is unprotected, c11 is locked then the sheet is again protected
    Change the password in the macro as appropriate and the color desired for C11.

    Note: C10 must be unlocked so you can change the value!

    Steve
    <pre>Private Sub Worksheet_change(ByVal Target As Excel.Range)
    Dim PW As String
    PW = "drowssap"
    If Not Intersect(Target, Range("C10")) Is Nothing Then
    If UCase(Range("c10").Value) = "YES" Then
    ActiveSheet.Unprotect password:=PW
    Range("c11").Locked = False
    Range("c11").Interior.Color = vbRed
    ActiveSheet.Protect password:=PW
    Else
    ActiveSheet.Unprotect password:=PW
    Range("c11").Locked = True
    ActiveSheet.Protect password:=PW
    End If
    End If
    End Sub
    </pre>


  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells (Excel 2K)

    First of all, thanks for your comments! I modified the code as follows and pasted it in the VB editor. However, couldn't get it to work. The "Yes" and "No" options are picked from a list. Don't know if this changes anything. Thanks in advance for your help!!!!

    Private Sub Worksheet_change(ByVal Target As Excel.Range)

    Dim PW As String
    PW = "drowssap"

    If Not Intersect(Target, Range("C19")) Is Nothing Then
    If UCase(Range("c19").Value) = "YES" Then
    ActiveSheet.Unprotect password:=PW
    Range("c20").Locked = False
    Range("c21").Locked = False
    Range("c20").Interior.Color = vbCyan
    Range("c21").Interior.Color = vbCyan
    Range("b20").Value = "Gross AP"
    Range("b21").Value = "AP effective date"
    ActiveSheet.Protect password:=PW
    Else
    ActiveSheet.Unprotect password:=PW
    Range("c20").Value = 0
    Range("c21").Value = ""
    Range("c20").Locked = True
    Range("c21").Locked = True
    Range("b20").Value = ""
    Range("b21").Value = ""
    ActiveSheet.Protect password:=PW
    End If
    End If

    End Sub

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cells (Excel 2K)

    <hr>pasted it in the VB editor<hr>
    Pasted it where? This code needs to be in the worksheet, not in a code module. With your Excel worksheet active, right-click on the sheet tab at the bottom and choose View Code. This is where the routine should be placed. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells (Excel 2K)

    Told you I was dense!!!

    Works as advertised. Thanks!!!!

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cells (Excel 2K)

    You're not dense, I didn't even know there was any other place but a code module when I started posting here. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells (Excel 2K)

    Now you want to write a COM Add-In <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cells (Excel 2K)

    Scary, isn't it? <img src=/S/flee.gif border=0 alt=flee width=25 height=25>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells (Excel 2K)

    Naw, not scary, just what the Lounge can give you the confidence to try. <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

Posting Permissions

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