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
Re: $$Excel-Macros$$ Run-time error message: Method 'Hidden' of object 'Range' failed
Send ME to MY email your file with my macro and this msg Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Anil Pandit Sent: Friday, March 30, 2012 5:48 AM To: excel-macros@googlegroups.com Subject: 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, 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
$$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
Re: $$Excel-Macros$$ Run-time error message: Method 'Hidden' of object 'Range' failed
'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, 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
