| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Msg Deactivating Code
Upon opening this workbook, some, not all, network users
are receiving: Run-time error '438' Object doesn't support this property or method. They dismiss the message (by clicking on "End" and continue normally. The "Debug" option is not available. The only thing I have found that does not work for these "error" users is the below code, among many other macros and Subs that do work. Can someone give me an idea of what the problem is and how to correct it? Private Sub Worksheet_Change(ByVal Target As Range) 'Forces upper case in cells on the BSC page On Error GoTo Error_Handler With Target If Not .HasFormula Then Application.EnableEvents = False If Target.Column = 14 And Target.Row = 3 Then_ Target.Value = UCase(Target.Value) 'Ucase for title block, BSC page, cell C14 If Target.Column = 16 And Target.Row = 12 Then_ Target.Value = UCase(Target.Value) 'Ucase for Customer Perspective title block, BSC page, cell P12 If Target.Column = 16 And Target.Row = 30 Then_ Target.Value = UCase(Target.Value) 'Ucase for Financial Perspective title block, BSC page, cell P30 If Target.Column = 42 And Target.Row = 12 Then_ Target.Value = UCase(Target.Value) 'Ucase for Learning and Growth Perspective title block, BSC page, cell AP12 If Target.Column = 42 And Target.Row = 30 Then_ Target.Value = UCase(Target.Value) 'Ucase for Internal Business Process Perspective title block, BSC page, cell AP30 Application.EnableEvents = True End If End With Error_Handler: Resume Next End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error Msg Deactivating Code
For Cell C14, you have the row and column reversed in the IF statement, so
that may give the appearance of not working. When I copied you code, all your line continuation characters (underscores) were butt up against the Then statement. They must be preceded by a space If condition Then _ Perhaps this is the source of your problem. When I put in the space, the code worked fine for me. -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Upon opening this workbook, some, not all, network users are receiving: Run-time error '438' Object doesn't support this property or method. They dismiss the message (by clicking on "End" and continue normally. The "Debug" option is not available. The only thing I have found that does not work for these "error" users is the below code, among many other macros and Subs that do work. Can someone give me an idea of what the problem is and how to correct it? Private Sub Worksheet_Change(ByVal Target As Range) 'Forces upper case in cells on the BSC page On Error GoTo Error_Handler With Target If Not .HasFormula Then Application.EnableEvents = False If Target.Column = 14 And Target.Row = 3 Then_ Target.Value = UCase(Target.Value) 'Ucase for title block, BSC page, cell C14 If Target.Column = 16 And Target.Row = 12 Then_ Target.Value = UCase(Target.Value) 'Ucase for Customer Perspective title block, BSC page, cell P12 If Target.Column = 16 And Target.Row = 30 Then_ Target.Value = UCase(Target.Value) 'Ucase for Financial Perspective title block, BSC page, cell P30 If Target.Column = 42 And Target.Row = 12 Then_ Target.Value = UCase(Target.Value) 'Ucase for Learning and Growth Perspective title block, BSC page, cell AP12 If Target.Column = 42 And Target.Row = 30 Then_ Target.Value = UCase(Target.Value) 'Ucase for Internal Business Process Perspective title block, BSC page, cell AP30 Application.EnableEvents = True End If End With Error_Handler: Resume Next End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error Msg Deactivating Code
Tom, Thanks for your reply. In the comment, C14 is
actually cell N3. Could this code possibly have another bug? Since I have only two users, out of many, with this error problem, I'm thinking there must be a local problem or setting to be addressed. Your view? Phil -----Original Message----- For Cell C14, you have the row and column reversed in the IF statement, so that may give the appearance of not working. When I copied you code, all your line continuation characters (underscores) were butt up against the Then statement. They must be preceded by a space If condition Then _ Perhaps this is the source of your problem. When I put in the space, the code worked fine for me. -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Upon opening this workbook, some, not all, network users are receiving: Run-time error '438' Object doesn't support this property or method. They dismiss the message (by clicking on "End" and continue normally. The "Debug" option is not available. The only thing I have found that does not work for these "error" users is the below code, among many other macros and Subs that do work. Can someone give me an idea of what the problem is and how to correct it? Private Sub Worksheet_Change(ByVal Target As Range) 'Forces upper case in cells on the BSC page On Error GoTo Error_Handler With Target If Not .HasFormula Then Application.EnableEvents = False If Target.Column = 14 And Target.Row = 3 Then_ Target.Value = UCase(Target.Value) 'Ucase for title block, BSC page, cell C14 If Target.Column = 16 And Target.Row = 12 Then_ Target.Value = UCase(Target.Value) 'Ucase for Customer Perspective title block, BSC page, cell P12 If Target.Column = 16 And Target.Row = 30 Then_ Target.Value = UCase(Target.Value) 'Ucase for Financial Perspective title block, BSC page, cell P30 If Target.Column = 42 And Target.Row = 12 Then_ Target.Value = UCase(Target.Value) 'Ucase for Learning and Growth Perspective title block, BSC page, cell AP12 If Target.Column = 42 And Target.Row = 30 Then_ Target.Value = UCase(Target.Value) 'Ucase for Internal Business Process Perspective title block, BSC page, cell AP30 Application.EnableEvents = True End If End With Error_Handler: Resume Next End Sub . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error Msg Deactivating Code
Phil,
If the code works in most workbooks, then it probably doesn't have anything to do with syntax - which is about all that would be evident from looking at the code. You would need to look at the two problem systems and see what is the same about them that is different from the ones where it works. Look at addins, references, other workbooks being open, operating system version, version of excel - those type things. Environmental things that your macros might depend on. -- Regards, Tom Ogilvy -----Original Message----- For Cell C14, you have the row and column reversed in the IF statement, so that may give the appearance of not working. When I copied you code, all your line continuation characters (underscores) were butt up against the Then statement. They must be preceded by a space If condition Then _ Perhaps this is the source of your problem. When I put in the space, the code worked fine for me. -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Upon opening this workbook, some, not all, network users are receiving: Run-time error '438' Object doesn't support this property or method. They dismiss the message (by clicking on "End" and continue normally. The "Debug" option is not available. The only thing I have found that does not work for these "error" users is the below code, among many other macros and Subs that do work. Can someone give me an idea of what the problem is and how to correct it? Private Sub Worksheet_Change(ByVal Target As Range) 'Forces upper case in cells on the BSC page On Error GoTo Error_Handler With Target If Not .HasFormula Then Application.EnableEvents = False If Target.Column = 14 And Target.Row = 3 Then_ Target.Value = UCase(Target.Value) 'Ucase for title block, BSC page, cell C14 If Target.Column = 16 And Target.Row = 12 Then_ Target.Value = UCase(Target.Value) 'Ucase for Customer Perspective title block, BSC page, cell P12 If Target.Column = 16 And Target.Row = 30 Then_ Target.Value = UCase(Target.Value) 'Ucase for Financial Perspective title block, BSC page, cell P30 If Target.Column = 42 And Target.Row = 12 Then_ Target.Value = UCase(Target.Value) 'Ucase for Learning and Growth Perspective title block, BSC page, cell AP12 If Target.Column = 42 And Target.Row = 30 Then_ Target.Value = UCase(Target.Value) 'Ucase for Internal Business Process Perspective title block, BSC page, cell AP30 Application.EnableEvents = True End If End With Error_Handler: Resume Next End Sub . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Deactivating a macro button | Excel Discussion (Misc queries) | |||
| Deactivating an Excel Warning | Excel Discussion (Misc queries) | |||
| Deactivating a Forms macro button based on a worksheet condition? | Excel Discussion (Misc queries) | |||
| Deactivating an embedded chart | Excel Discussion (Misc queries) | |||
| Hide/Show modeless userform when activating/deactivating workbooks | Excel Programming | |||
Linear Mode
