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 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 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
--
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
