typical_me
09-12-2007, 10:43 PM
I need your help guys...
I'm trying to create a 2 events Worksheet_Change macro in one sheet
It consist of 2 ranges of conditional events
First conditional range would allow some cell access or no access at all
and the Second conditional range would fill some cells with Text or no text
The problem is, for the first condition I need to fill in more than 1 text reference (more than just "ABCD" as the below macro; e.g. "BCDE", "CDEF", etc. )
And in the second condition, I need the result to be still editable after execution, so that users can still change the text or delete cell contents
here's what I've done so far:
Public BCell As Range
Public CCell As Range
Public vcELL As Boolean
Public Sub Worksheet_Change(ByVal target As Range)
Application.EnableEvents = False
Set BCell = Range("J24")
Set CCell = Range("Y30")
If Not BCell Is Nothing Then
Call isGreat(target)
Else: BCell = False
End If
If Not CCell Is Nothing Then
Call isGood(target)
Else: CCell = False
End If
Application.EnableEvents = True
End Sub
Private Sub isGood(ByVal target As Range)
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="123456789"
If Range("$Y$30").Value = "ABCD" Then
Range("$z$30:$aa$38").Locked = False
Range("$z$30:$aa$38").Interior.ColorIndex = 19
ActiveSheet.Protect Password:="123456789"
Else
Range("$z$30:$aa$38").Locked = True
Range("$z$30:$aa$38").Value = " "
Range("$z$30:$aa$38").Interior.ColorIndex = 15
ActiveSheet.Protect Password:="123456789"
End If
Application.EnableEvents = True
End Sub
Private Sub isGreat(ByVal target As Range)
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="123456789"
If Range("$J$24").Value = "WXYZ" Then
Range("$AA$47").Value = ("ANY TEXT")
Range("$AA$48").Value = "ANY OTHER TEXT"
Range("$AA$47:$AA$48").Locked = False
ActiveSheet.Protect Password:="123456789"
Else
Range("$AA$47").ClearContents
Range("$AA$48").ClearContents
Range("$AA$47:$AA$48").Locked = False
ActiveSheet.Protect Password:="123456789"
End If
Application.EnableEvents = False
ActiveSheet.Protect Password:="123456789"
End Sub
Private Sub Worksheet_SelectionChange(ByVal target As Excel.Range)
'Do nothing if more than one cell is changed or content deleted
If target.Cells.Count > 1 Then
vcELL = False
' Only activate if in area with our special values
ElseIf Intersect(target, Range("J24")) Is Nothing Then
vcELL = False
ElseIf Intersect(target, Range("y30")) Is Nothing Then
vcELL = False
Else
vcELL = True
Set CCell = target
End If
End Sub
I'm trying to create a 2 events Worksheet_Change macro in one sheet
It consist of 2 ranges of conditional events
First conditional range would allow some cell access or no access at all
and the Second conditional range would fill some cells with Text or no text
The problem is, for the first condition I need to fill in more than 1 text reference (more than just "ABCD" as the below macro; e.g. "BCDE", "CDEF", etc. )
And in the second condition, I need the result to be still editable after execution, so that users can still change the text or delete cell contents
here's what I've done so far:
Public BCell As Range
Public CCell As Range
Public vcELL As Boolean
Public Sub Worksheet_Change(ByVal target As Range)
Application.EnableEvents = False
Set BCell = Range("J24")
Set CCell = Range("Y30")
If Not BCell Is Nothing Then
Call isGreat(target)
Else: BCell = False
End If
If Not CCell Is Nothing Then
Call isGood(target)
Else: CCell = False
End If
Application.EnableEvents = True
End Sub
Private Sub isGood(ByVal target As Range)
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="123456789"
If Range("$Y$30").Value = "ABCD" Then
Range("$z$30:$aa$38").Locked = False
Range("$z$30:$aa$38").Interior.ColorIndex = 19
ActiveSheet.Protect Password:="123456789"
Else
Range("$z$30:$aa$38").Locked = True
Range("$z$30:$aa$38").Value = " "
Range("$z$30:$aa$38").Interior.ColorIndex = 15
ActiveSheet.Protect Password:="123456789"
End If
Application.EnableEvents = True
End Sub
Private Sub isGreat(ByVal target As Range)
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="123456789"
If Range("$J$24").Value = "WXYZ" Then
Range("$AA$47").Value = ("ANY TEXT")
Range("$AA$48").Value = "ANY OTHER TEXT"
Range("$AA$47:$AA$48").Locked = False
ActiveSheet.Protect Password:="123456789"
Else
Range("$AA$47").ClearContents
Range("$AA$48").ClearContents
Range("$AA$47:$AA$48").Locked = False
ActiveSheet.Protect Password:="123456789"
End If
Application.EnableEvents = False
ActiveSheet.Protect Password:="123456789"
End Sub
Private Sub Worksheet_SelectionChange(ByVal target As Excel.Range)
'Do nothing if more than one cell is changed or content deleted
If target.Cells.Count > 1 Then
vcELL = False
' Only activate if in area with our special values
ElseIf Intersect(target, Range("J24")) Is Nothing Then
vcELL = False
ElseIf Intersect(target, Range("y30")) Is Nothing Then
vcELL = False
Else
vcELL = True
Set CCell = target
End If
End Sub