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
