Conditional grey-out and disabling of cells

**Trebor76** · 10-06-2008, 08:12 PM

Sorry but I think this is beyond my limited capabilities

Hopefully there's a more learned person here who'll be able to provide you a working solution.

**jman0707** · 10-07-2008, 09:26 AM

OK, thanks a lot for your help Trebor76! I'll keep the code and hopefully continue to work on it. The protection is very vital to the sheet and that's the only reason I'm being so specific. But I really appreciate your help, have a good one!

**Trebor76** · 10-07-2008, 08:09 PM

Hi there,

One last try - let me know how the following goes (note my comments as well).

Regards,

Robert

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strMyPassword, strColumnChg As String
Dim lngLastRow, lngRowChg As Long

'Set your desired password to this variable.
strMyPassword = "YourPasswordHere"

ActiveSheet.Unprotect Password:=strMyPassword

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

strColumnChg = Left(Target.Address(False, False), 1)
lngRowChg = Val(Mid(Target.Address(False, False), 2, Len(Target.Address(False, False)) - 1))

    'Code will only work if the change has occurred in Column E and from rows _
    17 to the last row used in Column A (inclusive).
    If strColumnChg = "E" And _
    lngRowChg >= 17 Or _
    lngRowChg <= lngLastRow Then
    
        If Target.Value = "DI" Or _
        Target.Value = "LTC" Or _
        Target.Value = "Z" Then
                       
            With Range("F" & lngRowChg)
                    If ActiveSheet.ProtectContents = True Then
                        ActiveSheet.Unprotect Password:=strMyPassword
                    End If
                .Locked = False
                    If ActiveSheet.ProtectContents = True Then
                        ActiveSheet.Unprotect Password:=strMyPassword
                    End If
                .ClearContents
                    If ActiveSheet.ProtectContents = True Then
                        ActiveSheet.Unprotect Password:=strMyPassword
                    End If
                .Interior.Color = RGB(192, 192, 192)
                    If ActiveSheet.ProtectContents = True Then
                        ActiveSheet.Unprotect Password:=strMyPassword
                    End If
                .Locked = True
            End With
                
        Else
        
            With Range("F" & lngRowChg)
                    If ActiveSheet.ProtectContents = True Then
                        ActiveSheet.Unprotect Password:=strMyPassword
                    End If
                .Locked = False
                    If ActiveSheet.ProtectContents = True Then
                        ActiveSheet.Unprotect Password:=strMyPassword
                    End If
                .Interior.ColorIndex = xlNone
            End With
                
        End If
    
    End If
    
ActiveSheet.Protect Password:=strMyPassword
   
End Sub

**jman0707** · 10-08-2008, 09:42 AM

YES!!! That is awesome! This is totally not above your skill level! It works great!

The hard part is over. There are 2 little quirks that hopefully you can help me iron out.

1. When I select more than one cell in the whole worksheet, I get the "runtime error 13; type mismatch" error. Even if there is nothing in the cell to begin with. And this goes for any 2 cells in the whole workbook. I need to at least be able to highlight across a row and press delete since all the data for each individual case is on the same row.

2. As soon as I type anything else in a row where the cells been greyed and locked, it becomes unlocked. And when I double click on another cell in the row of a cell that's been disabled, it also becomes unlocked and turns white. If I type something in any other row, it's still fine.

So that's it, I know it looks like a lot of fixes, but this really works awesome right now and I'm excited! So please let me know if you know how to fix these couple quirks and then it will be ready for users! Thanks for all yoru hard work Trebor76!

**Trebor76** · 10-08-2008, 05:55 PM

Hi jman0707,

I'm excited!

- me too as I think the following is it (I hope):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strMyPassword, strColumnChg As String
Dim lngLastRow, lngRowChg As Long

'Set your desired password to this variable.
strMyPassword = "YourPasswordHere"

ActiveSheet.Unprotect Password:=strMyPassword

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

strColumnChg = Left(Target.Address(False, False), 1)
lngRowChg = Val(Mid(Target.Address(False, False), 2, Len(Target.Address(False, False)) - 1))

    'Code will only work if the change has occurred in Column E and from rows _
    17 to the last row used in Column A (inclusive).
    If strColumnChg = "E" And _
    lngRowChg >= 17 And _
    lngRowChg <= lngLastRow Then
    
        If Target.Value = "DI" Or _
        Target.Value = "LTC" Or _
        Target.Value = "Z" Then
                       
            With Range("F" & lngRowChg)
                    If ActiveSheet.ProtectContents = True Then
                        ActiveSheet.Unprotect Password:=strMyPassword
                    End If
                .Locked = False
                    If ActiveSheet.ProtectContents = True Then
                        ActiveSheet.Unprotect Password:=strMyPassword
                    End If
                .ClearContents
                    If ActiveSheet.ProtectContents = True Then
                        ActiveSheet.Unprotect Password:=strMyPassword
                    End If
                .Interior.Color = RGB(192, 192, 192)
                    If ActiveSheet.ProtectContents = True Then
                        ActiveSheet.Unprotect Password:=strMyPassword
                    End If
                .Locked = True
            End With
                
        Else
        
            With Range("F" & lngRowChg)
                    If ActiveSheet.ProtectContents = True Then
                        ActiveSheet.Unprotect Password:=strMyPassword
                    End If
                .Locked = False
                    If ActiveSheet.ProtectContents = True Then
                        ActiveSheet.Unprotect Password:=strMyPassword
                    End If
                .Interior.ColorIndex = xlNone
            End With
                
        End If
        
    End If
    
ActiveSheet.Protect Password:=strMyPassword
  
End Sub

Kind regards,

Robert

Conditional grey-out and disabling of cells

LinkBack

Thread Tools

Display

Hybrid View

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions