Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Cells depending on data entered in other cells
i'm trying to create a spreadsheet where depending on what data is entered in one cell thats how the other cells get changed, that is, gray out and disabled. i have the following vbscript coded so far but this could be totally incorrect, it's just not working......any help will be appreciated.
Private Sub Worksheet_Change(ByVal Target As Range) If ActiveCell.Column = 5 And ActiveCell.Row = 7 Then If Target.Value = "X" Then ActiveSheet.Unprotect Password:="MyPassword" With ActiveCell.Offset(0, 5) .ClearContents .Interior.Color = RGB(192, 192, 192) .Locked = True End With ActiveSheet.Protect Password:="MyPassword" Else If Target.Value = "" Then ActiveSheet.Unprotect Password:="MyPassword" With ActiveCell.Offset(0, 5) .ClearContents .Interior.Color = RGB(255, 255, 255) .Locked = False End With ActiveSheet.Protect Password:="MyPassword" End If End If ElseIf ActiveCell.Column = 7 And ActiveCell.Row = 7 Then If Target.Value = "X" Then ActiveSheet.Unprotect Password:="MyPassword" With ActiveCell.Offset(0, 3) .ClearContents .Interior.Color = RGB(192, 192, 192) .Locked = True End With ActiveSheet.Protect Password:="MyPassword" Else If Target.Value = "" Then ActiveSheet.Unprotect Password:="MyPassword" With ActiveCell.Offset(0, 3) .ClearContents .Interior.Color = RGB(255, 255, 255) .Locked = False End With ActiveSheet.Protect Password:="MyPassword" End If End If End If End Sub Thanking you in advance, Farida |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Cells depending on data entered in other cells
Some comments:
1. Since your code updates the sheet, you should turn off events before doing the update since that will again fire off your worksheet_change event 2. "Target" can be multiple cells, so you need to check each of them in a loop 3. Make sure you unlock all the cells in columns you want people to be able to edit: since the default state is Locked as soon as you protect the sheet they will also be protected from edits Some code ideas: Private Sub Worksheet_Change(ByVal Target As Range) Dim theCell As Range, theCol As Long, c As Range For Each c In Target.Cells 'can be more than one cell theCol = c.Column If c.Row = 7 Then If theCol = 5 Or theCol = 7 Then 'what offset are we using? Set theCell = c.Offset(0, IIf(theCol = 5, 5, 3)) If UCase(c.Value) = "X" Then ProcessCell theCell, True ElseIf c.Value = "" Then ProcessCell theCell, False End If End If 'col check End If 'row check Next c 'next cell in Target if 1 End Sub 'Set the color and Locked property of cell "rng" Private Sub ProcessCell(rng As Range, IsLocked As Boolean) On Error GoTo haveError Application.EnableEvents = False rng.Parent.Unprotect Password:="MyPassword" With rng .ClearContents .Locked = IsLocked .Interior.Color = IIf(IsLocked, RGB(192, 192, 192), _ RGB(255, 255, 255)) End With rng.Parent.Protect Password:="MyPassword" haveError: 'make sure this is always reset to True Application.EnableEvents = True End Sub Tim On Oct 20, 6:48*am, wrote: i'm trying to create a spreadsheet where depending on what data is entered in one cell thats how the other cells get changed, that is, gray out and disabled. i have the following vbscript coded so far but this could be totally incorrect, it's just not working......any help will be appreciated. Private Sub Worksheet_Change(ByVal Target As Range) * * If ActiveCell.Column = 5 And ActiveCell.Row = 7 Then * * * * If Target.Value = "X" Then * * * * * * ActiveSheet.Unprotect Password:="MyPassword" * * * * * * With ActiveCell.Offset(0, 5) * * * * * * .ClearContents * * * * * * .Interior.Color = RGB(192, 192, 192) * * * * * * .Locked = True * * * * * * End With * * * * * * ActiveSheet.Protect Password:="MyPassword" * * * * Else * * * * * * If Target.Value = "" Then * * * * * * * * ActiveSheet.Unprotect Password:="MyPassword" * * * * * * * * With ActiveCell.Offset(0, 5) * * * * * * * * .ClearContents * * * * * * * * .Interior.Color = RGB(255, 255, 255) * * * * * * * * .Locked = False * * * * * * * * End With * * * * * * * * ActiveSheet.Protect Password:="MyPassword" * * * * * * End If * * * * End If * * ElseIf ActiveCell.Column = 7 And ActiveCell.Row = 7 Then * * * * If Target.Value = "X" Then * * * * * * ActiveSheet.Unprotect Password:="MyPassword" * * * * * * With ActiveCell.Offset(0, 3) * * * * * * .ClearContents * * * * * * .Interior.Color = RGB(192, 192, 192) * * * * * * .Locked = True * * * * * * End With * * * * * * ActiveSheet.Protect Password:="MyPassword" * * * * Else * * * * * * If Target.Value = "" Then * * * * * * * * ActiveSheet.Unprotect Password:="MyPassword" * * * * * * * * With ActiveCell.Offset(0, 3) * * * * * * * * .ClearContents * * * * * * * * .Interior.Color = RGB(255, 255, 255) * * * * * * * * .Locked = False * * * * * * * * End With * * * * * * * * ActiveSheet.Protect Password:="MyPassword" * * * * * * End If * * * * End If * * End If End Sub Thanking you in advance, Farida |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing the colour of cells depending on the content. | Excel Discussion (Misc queries) | |||
Changing cell colour depending on another cells value... | Excel Discussion (Misc queries) | |||
Colouring a row depending on data entered in a column | Excel Worksheet Functions | |||
Protect data in cells after entered | Excel Discussion (Misc queries) | |||
How do I automatically shade cells depending on data entered? | Excel Worksheet Functions |