Update: I have changed the case expression list by breaking out the "L"
column address expressions before the "$L$39" expression and placing them
below the "$L$39" expression (see commented/bolded section in the code
extract below) and the code executes successfully. I can see no reason why
it didn't work initially and this "work-around" makes the code slightly
messier.
Any comments on this would be appreciated.
--A
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False
' Check if the specified cells have been changed and
' call the corresponding routine
'If Target.Address = "$L$39" Then
' Call ValidationType
' Call EnclosureAdd(Target.Address)
'End If
'Select Case Target.Address
' Case "$L$39"
' Call ValidationType
' Call EnclosureAdd(Target.Address)
'End Select
Select Case Target.Address
Case "$C$3"
Call DisplayCabinetLayout
Call ValidationType
Case "$C$4"
Call ACDCLocked
Call ValidationType
Case "$C$5"
Call ApplicationType
Case "$C$8" To "$C$9", _
"$C$16" To "$C$23", _
"$C$27", _
"$C$30"
Call PositiveNumber(Target)
Case "$C$10" To "$C$13", _
"$C$26", _
"$C$28" To "$C$29", _
"$C$31", _
"$C$34" To "$C$38", _
"$C$41" To "$C$45", _
"$C$48" To "$C$52", _
"$F$3", _
"$F$5" To "$F$6", _
"$F$11" To "$F$12", _
"$I$3", _
"$I$5" To "$I$6", _
"$I$11" To "$I$12" ', _
*' "$L$3" To "$L$4" ', _
' "$L$43" To "$L$44"*
Call ValidationType
Case "$F$8" To "$F$9", _
"$I$8" To "$I$9", _
*"$L$39"* To "$L$42"
Call ValidationType
Call EnclosureAdd(Target.Address)
* Case "$L$3" To "$L$4", _
"$L$43" To "$L$44"*
Call ValidationType
Case Else
Application.EnableEvents = True
Exit Sub
End Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
On Wednesday, April 25, 2012 12:11:17 PM UTC+1, Anil Pandit wrote:
>
> Update: I commented out the Case structure on the Worksheet_Change routine
> and added a simple If Then Else test:
>
> If Target.Address = "$L$39" Then
> Call EnclosureAdd(Target.Address)
> End If
>
> The code executed successfully. This means that the Case test is not
> catching cell "L39" for some reason.
>
> --Anil
>
> On Tuesday, April 24, 2012 11:12:56 AM UTC+1, Anil Pandit wrote:
>>
>> Hi all,
>>
>> I hope someone can help me, because I cannot see any reason why this
>> isn't working.
>>
>> No action is taken for cell L39, however cells L40-L42 behave as expected.
>>
>> *Step 1: A value is changed in cell "L39"*
>>
>> Private Sub *Worksheet_Change*(ByVal Target As Range)
>>
>> Application.EnableEvents = False
>> Application.ScreenUpdating = False
>>
>> ' Check if the specified cells have been changed and
>> ' call the corresponding routine
>>
>> Select Case Target.Address
>> Case "$C$3"
>> Call DisplayCabinetLayout
>> Call ValidationType
>> Case "$C$4"
>> Call ACDCLocked
>> Call ValidationType
>> Case "$C$5"
>> Call ApplicationType
>> Case "$C$8" To "$C$9", _
>> "$C$16" To "$C$23", _
>> "$C$27", _
>> "$C$30"
>> Call PositiveNumber(Target)
>> Case "$C$10" To "$C$13", _
>> "$C$26", _
>> "$C$28" To "$C$29", _
>> "$C$31", _
>> "$C$34" To "$C$38", _
>> "$C$41" To "$C$45", _
>> "$C$48" To "$C$52", _
>> "$F$3", _
>> "$F$5" To "$F$6", _
>> "$F$11" To "$F$12", _
>> "$I$3", _
>> "$I$5" To "$I$6", _
>> "$I$11" To "$I$12", _
>> "$L$3" To "$L$4", _
>> "$L$43" To "$L$44"
>> Call ValidationType
>> Case "$F$8" To "$F$9", _
>> "$I$8" To "$I$9", _
>> *"$L$39"* To "$L$42"
>> Call ValidationType
>> *Call EnclosureAdd(Target.Address)*
>> Case Else
>> Application.EnableEvents = True
>> Exit Sub
>> End Select
>>
>> Application.EnableEvents = True
>> Application.ScreenUpdating = True
>>
>> End Sub
>>
>> *Step 2: The EnclosureAdd routine is called for cell "L39"*
>>
>> Sub EnclosureAdd(Rng As String)
>>
>> Dim PDU As String
>>
>> ' Extract the column and row Indices
>>
>> Col = Mid(Rng, 2, 1)
>> Row = Mid(Rng, 4)
>> PDU = Mid(Range(Col & Row), 1, 5)
>> CellValue = Range(Col & Row)
>>
>> ' Use the column and row to add enclosures depending on whether
>> ' Extra PDPs/PDUs are selected on sheet 1.
>>
>> Select Case Col
>> Case Is = "F"
>> Select Case Row
>> Case Is = "8"
>> Range(Col & Row).Offset(1, 0) = CellValue
>> Range(Col & 16) = EN2Select(CellValue)
>> Case Is = "9"
>> Range(Col & Row).Offset(-1, 0) = CellValue
>> Range(Col & 16) = EN2Select(CellValue)
>> End Select
>> Case Is = "I"
>> Select Case Row
>> Case Is = "8"
>> Range(Col & Row).Offset(1, 0) = CellValue
>> Range(Col & 24) = EN2Select(CellValue)
>> Case Is = "9"
>> Range(Col & Row).Offset(-1, 0) = CellValue
>> Range(Col & 24) = EN2Select(CellValue)
>> End Select
>> *Case Is = "L"*
>> Select Case Row
>> *Case Is = "39"*
>> * Range(Col & Row).Offset(1, 0) = PDUSelect(CellValue)
>> Range(Col & 9) = ENXSelect(CellValue)*
>> Case Is = "40"
>> Range(Col & Row).Offset(-1, 0) = PDUSelect(CellValue)
>> Range(Col & 9) = ENXSelect(CellValue)
>> Case Is = "41"
>> Range(Col & Row).Offset(1, 0) = PDUSelect(CellValue)
>> Range(Col & 19) = ENXSelect(CellValue)
>> Case Is = "42"
>> Range(Col & Row).Offset(-1, 0) = PDUSelect(CellValue)
>> Range(Col & 19) = ENXSelect(CellValue)
>> End Select
>> End Select
>> End Sub
>>
>> *Step 3: The **PDUSelect and ENXSelect Functions are called* *for cell
>> "L39"*
>>
>> Function PDUSelect(CellValue)
>> Dim PDU As String
>> Dim n As String
>>
>> If CellValue <> "Empty Slot" Then
>> ' Extract the string: "PDU xn" and toggle "n".'
>> PDU = Mid(CellValue, 1, 5)
>> n = Mid(CellValue, 6, 1)
>> If n = 1 Then
>> n = 2
>> ElseIf n = 2 Then
>> n = 1
>> End If
>> PDUSelect = PDU & n
>> Else
>> PDUSelect = "Empty Slot"
>> End If
>>
>> End Function
>> Function ENXSelect(CellValue)
>>
>> Const C1EN2AC As String = "C7000 Enclosure (805-0540-G02)"
>>
>> If Mid(CellValue, 1, 3) = "PDU" Then
>> ENXSelect = C1EN2AC
>> Else
>> ENXSelect = "Empty Slot"
>> End If
>>
>> End Function
>>
>>
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
