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)"
>> '?? 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 "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
>>
>
--
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
