Hi again Anil,
I made suggestions for Worksheet_Change in another post, but I see that you
included your EnclosureAdd routine in your original message, so I will offer
you a revision to EnclosureAdd that takes a Range parameter instead of a
string parameter. This is more efficient and also solves a problem: a
simple text range address does not identify the worksheet it is on. And
it's not foolproof to assume that the worksheet being changed is always the
active sheet, as your existing code did.
Sub EnclosureAdd(Rng As Range)
' Dim PDU As String ' this isn't used
Dim CellValue As Variant ' You didn't have this declared
Dim Col As Long, Row As Long ' You didn't have these declared
Dim WS As Worksheet ' added this to identify worksheet for ranges
Set WS = Rng.Parent
' Extract the column and row Indices
Col = Rng.Column
Row = Rng.Row
CellValue = Rng.Value
' PDU = Mid(CellValue, 1, 5) ' this isn't used
' Use the column and row to add enclosures depending on whether
' Extra PDPs/PDUs are selected on sheet 1.
Select Case Col
Case 6 ' Col. F
Select Case Row
Case 8
Rng.Offset(1, 0).Value = CellValue
WS.Cells(16, Col).Value = EN2Select(CellValue)
Case 9
Rng.Offset(-1, 0).Value = CellValue
WS.Cells(16, Col).Value = EN2Select(CellValue)
End Select
Case 9 ' Col. I
Select Case Row
Case 8
Rng.Offset(1, 0).Value = CellValue
WS.Cells(24, Col).Value = EN2Select(CellValue)
Case 9
Rng.Offset(-1, 0).Value = CellValue
WS.Cells(24, Col).Value = EN2Select(CellValue)
End Select
Case 12 ' Col. L
Select Case Row
Case 39, 41
Rng.Offset(1, 0).Value = PDUSelect(CellValue)
WS.Cells(9, Col).Value = ENXSelect(CellValue)
Case 40, 42
Rng.Offset(-1, 0).Value = PDUSelect(CellValue)
WS.Cells(9, Col).Value = ENXSelect(CellValue)
End Select
End Select
Set WS = Nothing
End Sub
If you want to allow this routine to handle multiple cells at once, that can
be done with only a small modification to have it loop through all the cells
in the Rng parameter:
Sub EnclosureAdd(Rng As Range)
' Dim PDU As String ' this isn't used
Dim CellValue As Variant ' You didn't have this declared
Dim Col As Long, Row As Long ' You didn't have these declared
Dim WS As Worksheet ' added this to identify worksheet for ranges
Dim Cell As Range
Set WS = Rng.Parent
For Each Cell In Rng.Cells
' Extract the column and row Indices
Col = Cell.Column
Row = Cell.Row
CellValue = Cell.Value
' PDU = Mid(CellValue, 1, 5) ' this isn't used
' Use the column and row to add enclosures depending on whether
' Extra PDPs/PDUs are selected on sheet 1.
Select Case Col
Case 6 ' Col. F
Select Case Row
Case 8
Cell.Offset(1, 0).Value = CellValue
WS.Cells(16, Col).Value = EN2Select(CellValue)
Case 9
Cell.Offset(-1, 0).Value = CellValue
WS.Cells(16, Col).Value = EN2Select(CellValue)
End Select
Case 9 ' Col. I
Select Case Row
Case 8
Cell.Offset(1, 0).Value = CellValue
WS.Cells(24, Col).Value = EN2Select(CellValue)
Case 9
Cell.Offset(-1, 0).Value = CellValue
WS.Cells(24, Col).Value = EN2Select(CellValue)
End Select
Case 12 ' Col. L
Select Case Row
Case 39, 41
Cell.Offset(1, 0).Value = PDUSelect(CellValue)
WS.Cells(9, Col).Value = ENXSelect(CellValue)
Case 40, 42
Cell.Offset(-1, 0).Value = PDUSelect(CellValue)
WS.Cells(9, Col).Value = ENXSelect(CellValue)
End Select
End Select
Next Cell
Set Cell = Nothing
Set WS = Nothing
End Sub
If you use a version like this, obviously you'll need to change the call(s)
to EnclosureAdd to pass the desired range rather than its Address.
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Anil Pandit
Sent: Wednesday, April 25, 2012 4:11 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Re: Custom functions not being performed on "L39",
but they are performed on "L40-L42"
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
--
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
