$$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
> --
$$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 &
$$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",
$$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) = ENXSel
$$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.
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
>> *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") = "
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
> *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, for
$$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
