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
