$$Excel-Macros$$ Re: Failure of code when trying to break a section out into a seperate routine
Thanks Asa, I changed the code so that, in the parent procedure (PowerReturnOutput), I changed the data type of the array (Partnumber()) from String to Variable. When passing an array to the routine: EnterPN, I included open and closed brackets after the variable name. I also used brackets in EnterPN's first argument and it now seems to work fine. Thanks for the help again. I also do have an On Error Resume Next Statement in the parent routine of Sub PowerReturnOutput(), which would explain the seemingly strange flow. I did see values in the array of the called procedure (PNArray) before it would exit, in the immediate window, which seems strange. The array was declared with a data type of String in the parent procedure (PowerReturnOutput()), before it was passed as an argument to the child (EnterPN) procedure. If you can offer an explanation for this, that would be great, however I am just happy it is working and that I know what I should do to pass an array as a sub or function argument in future. Cheers! Anil On Sunday, July 15, 2012 11:04:27 AM UTC+1, Anil Pandit wrote: Hi all, I have the following code in a sub routine which works (please see code in bold font), but which I want in a separate routine, so I can re-use the code. -- Sub PowerReturnOutput() ' This sub procedure looks up the part number on sheet 4 and returns the ' part number description to sheet 2 Dim PwrAndGnd() As Variant Dim Distance As Long Dim CableType As String Dim PwrColour As String Dim RtnColour As String Dim PartNumber(1 To 6) As String Dim PN As Variant Dim Cell As Range Dim TOPwrGndPN As Range Dim TOPwrGndDesc As Range Dim XGPwrAndGnd As Range Set TOPwrGndPN = Range(TOPwrGndPN) Set TOPwrGndDesc = Range(TOPwrGndDesc) Set XGPwrAndGnd = Worksheets(Eagle XG Input).Range(XGPwrAndGnd) ' Return an array with the XGArray function. PwrAndGnd = XGArray(XGPwrAndGnd) ' Extract elements into variables, adding 10 to BDFB distance. Distance = PwrAndGnd(1) + 10 PwrColour = PwrAndGnd(3) RtnColour = PwrAndGnd(4) CableType = PwrAndGnd(5) ' Extract part numbers into an array PartNumber(1) = AWGxPwrRtnPN(Distance, PwrColour, CableType) PartNumber(2) = AWGxPwrRtnPN(Distance, RtnColour, CableType) PartNumber(3) = AWG1GndPN(CableType) PartNumber(4) = AWG6PwrRtnPN(PwrColour, CableType) PartNumber(5) = AWG6PwrRtnPN(RtnColour, CableType) PartNumber(6) = AWG6GndPN(CableType) *' Enter the part numbers in sheet 2 Call EnterPN(PartNumber, TOPwrGndPN) 'For Each PN In PartNumber() 'For Each Cell In TOPwrGndPN 'If IsEmpty(Cell) Then 'Cell.Value = PN 'Exit For 'End If 'Next Cell 'Next PN* ' Enter the descriptions in sheet 2 For Each PN In PartNumber() For Each Cell In TOPwrGndDesc If IsEmpty(Cell) Then If PN = Not Available Then With Cell .Value = EAAA Brown AWG 2/0 cable is not available. .Font.Italic = True .Font.Color = vbRed .Offset(0, -1).Font.Italic = True .Offset(0, -1).Font.Color = vbRed End With Exit For Else Cell = AWGxPNDesc(PN) Exit For End If End If Next Cell Next PN End Sub -- I created another sub-routine which is called from the PowerReturnOutput routine: -- Sub EnterPN(PNArray, OutputRng) ' This sub procedure enters part numbers into the output range. Dim PN As Variant Dim Cell As Range For Each PN In PNArray() For Each Cell In OutputRng If IsEmpty(Cell) Then Cell.Value = PN Exit For End If Next Cell Next PN End Sub -- The code does not work. When I set a break-point and step through the code, I can see that the EnterPN routine is called and that PNArray() and OutputRng have values in the immediate window. After the line: For Each PN In PNArray() The sub exits and returns to the parent of the parent routine from which it was called (i.e the routine which called PowerReturnOutput())! I hope someone can help me understand and hopefully fix this behaviour. Regards, Anil -- -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent
$$Excel-Macros$$ Failure of code when trying to break a section out into a seperate routine
Hi all, I have the following code in a sub routine which works (please see code in bold font), but which I want in a separate routine, so I can re-use the code. -- Sub PowerReturnOutput() ' This sub procedure looks up the part number on sheet 4 and returns the ' part number description to sheet 2 Dim PwrAndGnd() As Variant Dim Distance As Long Dim CableType As String Dim PwrColour As String Dim RtnColour As String Dim PartNumber(1 To 6) As String Dim PN As Variant Dim Cell As Range Dim TOPwrGndPN As Range Dim TOPwrGndDesc As Range Dim XGPwrAndGnd As Range Set TOPwrGndPN = Range(TOPwrGndPN) Set TOPwrGndDesc = Range(TOPwrGndDesc) Set XGPwrAndGnd = Worksheets(Eagle XG Input).Range(XGPwrAndGnd) ' Return an array with the XGArray function. PwrAndGnd = XGArray(XGPwrAndGnd) ' Extract elements into variables, adding 10 to BDFB distance. Distance = PwrAndGnd(1) + 10 PwrColour = PwrAndGnd(3) RtnColour = PwrAndGnd(4) CableType = PwrAndGnd(5) ' Extract part numbers into an array PartNumber(1) = AWGxPwrRtnPN(Distance, PwrColour, CableType) PartNumber(2) = AWGxPwrRtnPN(Distance, RtnColour, CableType) PartNumber(3) = AWG1GndPN(CableType) PartNumber(4) = AWG6PwrRtnPN(PwrColour, CableType) PartNumber(5) = AWG6PwrRtnPN(RtnColour, CableType) PartNumber(6) = AWG6GndPN(CableType) *' Enter the part numbers in sheet 2 Call EnterPN(PartNumber, TOPwrGndPN) 'For Each PN In PartNumber() 'For Each Cell In TOPwrGndPN 'If IsEmpty(Cell) Then 'Cell.Value = PN 'Exit For 'End If 'Next Cell 'Next PN* ' Enter the descriptions in sheet 2 For Each PN In PartNumber() For Each Cell In TOPwrGndDesc If IsEmpty(Cell) Then If PN = Not Available Then With Cell .Value = EAAA Brown AWG 2/0 cable is not available. .Font.Italic = True .Font.Color = vbRed .Offset(0, -1).Font.Italic = True .Offset(0, -1).Font.Color = vbRed End With Exit For Else Cell = AWGxPNDesc(PN) Exit For End If End If Next Cell Next PN End Sub -- I created another sub-routine which is called from the PowerReturnOutput routine: -- Sub EnterPN(PNArray, OutputRng) ' This sub procedure enters part numbers into the output range. Dim PN As Variant Dim Cell As Range For Each PN In PNArray() For Each Cell In OutputRng If IsEmpty(Cell) Then Cell.Value = PN Exit For End If Next Cell Next PN End Sub -- The code does not work. When I set a break-point and step through the code, I can see that the EnterPN routine is called and that PNArray() and OutputRng have values in the immediate window. After the line: For Each PN In PNArray() The sub exits and returns to the parent of the parent routine from which it was called (i.e the routine which called PowerReturnOutput())! I hope someone can help me understand and hopefully fix this behaviour. Regards, Anil -- -- 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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
$$Excel-Macros$$ Re: Custom functions not being performed on L39, but they are performed on L40-L42
Asa - thanks again. I am busy this morning revisiting, rewriting and retesting all of the code in my project after your suggestions. I strongly believe in good practice and I'm grateful to you for helping me to improve in this area. Best regards, 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 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
$$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
$$Excel-Macros$$ Re: Custom functions not being performed on L39, but they are performed on L40-L42
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
$$Excel-Macros$$ Custom functions not being performed on L39, but they are performed on L40-L42
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
Re: $$Excel-Macros$$ Run-time error message: Method 'Hidden' of object 'Range' failed
Thanks for the reply Don. I'm still getting the same Run-time error and then an Excel crash. The debug points to the line: Columns.Hidden = False, stating: Run-time error message: Method 'Hidden' of object 'Range' failed. I created a sub-procedure to display a MsgBox with the value of Columns.Hidden. I could see that the value was False unless all columns were hidden, so I tried this statement: Columns(E:L).Hidden = False Excel produces the same error message and then crashes. Regards, Anil On Thursday, March 29, 2012 12:02:53 AM UTC+1, Don Guillett wrote: 'Assumes this in DV list 'DC36U 'DC44U 'AC42U Private Sub Worksheet_Change(ByVal Target As Range) Dim DC36U As String Dim DC44U As String Dim AC42U As String '??DC36U = Tekelec Eagle XG 870-3040-06 (DC) '??DC44U = Tekelec Eagle XG 870-3068-06 (DC) '??AC42U = Tekelec Eagle XG 870-3042-06 (AC) Application.ScreenUpdating = False ' Test the value in Target and display the ' appropriate frame layout Columns.Hidden = False Range(C5) = DC Select Case Target Case DC36U Columns(G:L).Hidden = True Case DC44U Range(E1:G1,J1:l1).EntireColumn.Hidden = True Case AC42U Columns(E:J).EntireColumn.Hidden = True Range(C5) = AC End Select Application.ScreenUpdating = True End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* Anil Pandit apand...@gmail.com *Sent:* Wednesday, March 28, 2012 11:04 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Run-time error message: Method 'Hidden' of object 'Range' failed Hi, I'm new to VBA and I'm posting this because I've been tearing my hair out over this all day, so I hope someone has seen this before and can point me in the right direction. I have created a worksheet change event which hides some columns according to a string that is chosen from a list in cell C3. This all works fine until I add any of the Range statements into the Select Case structure. When I select one of the three values of the cell from the Data Validation drop-down list I have in cell C3, I receive: Run-time error message: Method 'Hidden' of object 'Range' failed and then Excel crashes! I am using Excel 2007. I would be very grateful if you could look at the following code and help me. Best regards, Anil Private Sub Worksheet_Change(ByVal Target As Range) Dim DC36U As String Dim DC44U As String Dim AC42U As String DC36U = Tekelec Eagle XG 870-3040-06 (DC) DC44U = Tekelec Eagle XG 870-3068-06 (DC) AC42U = Tekelec Eagle XG 870-3042-06 (AC) ' Turn off screen updating Application.ScreenUpdating = False ' Assign an object reference to the Target variable Set Target = Range(C3) ' Test the value in Target and display the ' appropriate frame layout Select Case Target Case DC36U Columns.Hidden = False Columns(G:L).EntireColumn.Hidden = True *Range(C5) = DC* Case DC44U Columns.Hidden = False Columns(E:G).EntireColumn.Hidden = True Columns(J:L).EntireColumn.Hidden = True *Range(C5) = DC* Case AC42U Columns.Hidden = False Columns(E:J).EntireColumn.Hidden = True *Range(C5) = AC* End Select End Sub -- 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 On Thursday, March 29, 2012 12:02:53 AM UTC+1, Don Guillett wrote: 'Assumes this in DV list 'DC36U 'DC44U 'AC42U Private Sub Worksheet_Change(ByVal Target As Range) Dim DC36U As String Dim DC44U As String Dim AC42U As String '??DC36U = Tekelec Eagle XG 870-3040-06 (DC) '??DC44U = Tekelec Eagle XG 870-3068-06 (DC) '??AC42U = Tekelec Eagle XG 870-3042-06 (AC) Application.ScreenUpdating = False ' Test the value in Target and display the ' appropriate frame layout Columns.Hidden = False Range(C5) = DC Select Case Target Case DC36U Columns(G:L).Hidden = True Case
Re: $$Excel-Macros$$ Run-time error message: Method 'Hidden' of object 'Range' failed
Another point I wanted to note is that, when I remove the setting: Application.ScreenUpdating = False, Excel looks like it is in some kind of perpetual loop or at least going through a high level of CPU cycles before it crashes. Regards, Anil On Friday, March 30, 2012 11:48:10 AM UTC+1, Anil Pandit wrote: Thanks for the reply Don. I'm still getting the same Run-time error and then an Excel crash. The debug points to the line: Columns.Hidden = False, stating: Run-time error message: Method 'Hidden' of object 'Range' failed. I created a sub-procedure to display a MsgBox with the value of Columns.Hidden. I could see that the value was False unless all columns were hidden, so I tried this statement: Columns(E:L).Hidden = False Excel produces the same error message and then crashes. Regards, Anil On Thursday, March 29, 2012 12:02:53 AM UTC+1, Don Guillett wrote: 'Assumes this in DV list 'DC36U 'DC44U 'AC42U Private Sub Worksheet_Change(ByVal Target As Range) Dim DC36U As String Dim DC44U As String Dim AC42U As String '??DC36U = Tekelec Eagle XG 870-3040-06 (DC) '??DC44U = Tekelec Eagle XG 870-3068-06 (DC) '??AC42U = Tekelec Eagle XG 870-3042-06 (AC) Application.ScreenUpdating = False ' Test the value in Target and display the ' appropriate frame layout Columns.Hidden = False Range(C5) = DC Select Case Target Case DC36U Columns(G:L).Hidden = True Case DC44U Range(E1:G1,J1:l1).EntireColumn.Hidden = True Case AC42U Columns(E:J).EntireColumn.Hidden = True Range(C5) = AC End Select Application.ScreenUpdating = True End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* Anil Pandit apand...@gmail.com *Sent:* Wednesday, March 28, 2012 11:04 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Run-time error message: Method 'Hidden' of object 'Range' failed Hi, I'm new to VBA and I'm posting this because I've been tearing my hair out over this all day, so I hope someone has seen this before and can point me in the right direction. I have created a worksheet change event which hides some columns according to a string that is chosen from a list in cell C3. This all works fine until I add any of the Range statements into the Select Case structure. When I select one of the three values of the cell from the Data Validation drop-down list I have in cell C3, I receive: Run-time error message: Method 'Hidden' of object 'Range' failed and then Excel crashes! I am using Excel 2007. I would be very grateful if you could look at the following code and help me. Best regards, Anil Private Sub Worksheet_Change(ByVal Target As Range) Dim DC36U As String Dim DC44U As String Dim AC42U As String DC36U = Tekelec Eagle XG 870-3040-06 (DC) DC44U = Tekelec Eagle XG 870-3068-06 (DC) AC42U = Tekelec Eagle XG 870-3042-06 (AC) ' Turn off screen updating Application.ScreenUpdating = False ' Assign an object reference to the Target variable Set Target = Range(C3) ' Test the value in Target and display the ' appropriate frame layout Select Case Target Case DC36U Columns.Hidden = False Columns(G:L).EntireColumn.Hidden = True *Range(C5) = DC* Case DC44U Columns.Hidden = False Columns(E:G).EntireColumn.Hidden = True Columns(J:L).EntireColumn.Hidden = True *Range(C5) = DC* Case AC42U Columns.Hidden = False Columns(E:J).EntireColumn.Hidden = True *Range(C5) = AC* End Select End Sub -- 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 On Thursday, March 29, 2012 12:02:53 AM UTC+1, Don Guillett wrote: 'Assumes this in DV list 'DC36U 'DC44U 'AC42U Private Sub Worksheet_Change(ByVal Target As Range) Dim DC36U As String Dim DC44U As String Dim AC42U As String '??DC36U = Tekelec Eagle XG 870-3040-06 (DC
$$Excel-Macros$$ Run-time error message: Method 'Hidden' of object 'Range' failed
Hi, I'm new to VBA and I'm posting this because I've been tearing my hair out over this all day, so I hope someone has seen this before and can point me in the right direction. I have created a worksheet change event which hides some columns according to a string that is chosen from a list in cell C3. This all works fine until I add any of the Range statements into the Select Case structure. When I select one of the three values of the cell from the Data Validation drop-down list I have in cell C3, I receive: Run-time error message: Method 'Hidden' of object 'Range' failed and then Excel crashes! I am using Excel 2007. I would be very grateful if you could look at the following code and help me. Best regards, Anil Private Sub Worksheet_Change(ByVal Target As Range) Dim DC36U As String Dim DC44U As String Dim AC42U As String DC36U = Tekelec Eagle XG 870-3040-06 (DC) DC44U = Tekelec Eagle XG 870-3068-06 (DC) AC42U = Tekelec Eagle XG 870-3042-06 (AC) ' Turn off screen updating Application.ScreenUpdating = False ' Assign an object reference to the Target variable Set Target = Range(C3) ' Test the value in Target and display the ' appropriate frame layout Select Case Target Case DC36U Columns.Hidden = False Columns(G:L).EntireColumn.Hidden = True *Range(C5) = DC* Case DC44U Columns.Hidden = False Columns(E:G).EntireColumn.Hidden = True Columns(J:L).EntireColumn.Hidden = True *Range(C5) = DC* Case AC42U Columns.Hidden = False Columns(E:J).EntireColumn.Hidden = True *Range(C5) = AC* End Select End Sub -- 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
