Conditional grey-out and disabling of cells

**jman0707** · 10-03-2008, 10:01 AM

IT'S SO CLOSE! It works really great, just a couple more small things, and if I knew anything about programming I'd tweak them myself, but i don't!

1. When you select anything other than "DI," "LTC," or "Z" it unlocks the whole sheet and then all cells on the worksheet are un-protected. Then when it re-protects, it forgets which cells were locked and which weren't, and leaves all cells unlcoked. Is there any way to get it to immediately lock again when one of those 3 values is selected? The sheet needs to stay locked at all times.

2. And the other things is, let's say when you select a value in E17 such as "DI" and F17 get's greyed out and locked, then you move down and select "DI" for E18 and F18 gets locked, F17 re-opens. I need for all the greyed out cells to stay locked once their locked. It only locks the last cell that you work with. And i don't think I can manually lock column F because if the value IS NOT one of the 3 chosen, I need to be able to enter information still, and can't do that if the sheet is locked like it will be AND all the cells are locked. So I need all greyed out cells to stay locked, all non-greyed out cells to stay unlocked, and the sheet to always remain locked and password protected.

And a couple other wierd quirks.

3. Whenever I highlight at least one E cell and one F cell (even if they are blank) and hit "delete" it gives me an error message, "run-time error '13.'"
4. Also, when I manually type in the values instead of using the drop down list, the wrong cell gets affected (ex. if I typed "DI" in E17, F18 would turn grey and lock. And vice versa with typing in a value other than the 3 chosen, the wrong cell turns white.

5. And when I clear out a cell and it's blank, the corresponding cell stays gray. So maybe that goes along with the last error and when you type stuff in it gets messed up.

6. And one other thing (not too important) is when I hit Ctrl^C to copy a cell in column E to paste in multiple other cells, it only copies once, making you hit Ctrl^C again instead of doing multiple pastes. This is last b/c it's not super important. If these few quirks get worked out it will be PERFECT! Hopefully it's not too much tweaking. It just needs to look very professional while at the same time being user friendly for the people inputting info. Thanks so much for all your help!

**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