Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sharing a workbook breaks VBA code?
The following code acts as conditional formatting (as conditional formatting
only allows for 3 conditions) for a worksheet shared by multiple people. When I added the code and tested it, everything seemed to work fine. That was until another individual let me know that it did not work for them. Upon further investigation, it seems that I am the only one that he code works for. Does sharing the workbook in essence break/ignore the code? Is there a workaround for this type of problem. Many thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) Set t = Intersect(Target, Range("A3:A50")) If Not t Is Nothing Then Upper t FillCells t End If End Sub Public Sub Upper(ByVal Target As Range) Application.EnableEvents = False Target.Value = UCase(Target.Value) Application.EnableEvents = True End Sub Public Sub FillCells(ByVal Target As Range) Select Case Target Case "X" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 15 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "P" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 6 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "L" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 45 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "D" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 50 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "QA" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 38 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "QC" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 55 .Font.ColorIndex = 2 End With Next c Case "S" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 53 .Font.ColorIndex = 2 End With Next c Case Else For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = xlColorIndexNone .Font.ColorIndex = xlColorIndexAutomatic End With Next c End Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sharing a workbook breaks VBA code?
What do you mean "it did not work for them"?
Did it lock up their machine? Generate error messages? Just quietly do nothing? Work properly, but put up the wrong colors? Something else? More info would be helpful. Bill ----------------------------- Jason wrote: The following code acts as conditional formatting (as conditional formatting only allows for 3 conditions) for a worksheet shared by multiple people. When I added the code and tested it, everything seemed to work fine. That was until another individual let me know that it did not work for them. Upon further investigation, it seems that I am the only one that he code works for. Does sharing the workbook in essence break/ignore the code? Is there a workaround for this type of problem. Many thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) Set t = Intersect(Target, Range("A3:A50")) If Not t Is Nothing Then Upper t FillCells t End If End Sub Public Sub Upper(ByVal Target As Range) Application.EnableEvents = False Target.Value = UCase(Target.Value) Application.EnableEvents = True End Sub Public Sub FillCells(ByVal Target As Range) Select Case Target Case "X" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 15 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "P" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 6 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "L" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 45 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "D" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 50 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "QA" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 38 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "QC" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 55 .Font.ColorIndex = 2 End With Next c Case "S" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 53 .Font.ColorIndex = 2 End With Next c Case Else For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = xlColorIndexNone .Font.ColorIndex = xlColorIndexAutomatic End With Next c End Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sharing a workbook breaks VBA code?
Sorry. It quietly did nothing. As if the code didn't even exist.
"Bill Martin" wrote: What do you mean "it did not work for them"? Did it lock up their machine? Generate error messages? Just quietly do nothing? Work properly, but put up the wrong colors? Something else? More info would be helpful. Bill ----------------------------- Jason wrote: The following code acts as conditional formatting (as conditional formatting only allows for 3 conditions) for a worksheet shared by multiple people. When I added the code and tested it, everything seemed to work fine. That was until another individual let me know that it did not work for them. Upon further investigation, it seems that I am the only one that he code works for. Does sharing the workbook in essence break/ignore the code? Is there a workaround for this type of problem. Many thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) Set t = Intersect(Target, Range("A3:A50")) If Not t Is Nothing Then Upper t FillCells t End If End Sub Public Sub Upper(ByVal Target As Range) Application.EnableEvents = False Target.Value = UCase(Target.Value) Application.EnableEvents = True End Sub Public Sub FillCells(ByVal Target As Range) Select Case Target Case "X" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 15 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "P" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 6 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "L" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 45 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "D" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 50 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "QA" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 38 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "QC" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 55 .Font.ColorIndex = 2 End With Next c Case "S" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 53 .Font.ColorIndex = 2 End With Next c Case Else For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = xlColorIndexNone .Font.ColorIndex = xlColorIndexAutomatic End With Next c End Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sharing a workbook breaks VBA code?
Did you examine how they installed it? You're apparently using events to detect
a worksheet change which then calls some simple code to tinker with cell formatting. You can't just put that code into a module somewhere, but it must be within the code module for the particular worksheet you want to monitor -- unlike other non event driven VBA code which can be in other modules and still work. Are you sure they installed it in the proper place? Also it won't work if their systems somehow have events turned off from some program previously run. Or if their systems are set at a high security level to prevent macros from running. Bill ----------------------- Jason wrote: Sorry. It quietly did nothing. As if the code didn't even exist. "Bill Martin" wrote: What do you mean "it did not work for them"? Did it lock up their machine? Generate error messages? Just quietly do nothing? Work properly, but put up the wrong colors? Something else? More info would be helpful. Bill ----------------------------- Jason wrote: The following code acts as conditional formatting (as conditional formatting only allows for 3 conditions) for a worksheet shared by multiple people. When I added the code and tested it, everything seemed to work fine. That was until another individual let me know that it did not work for them. Upon further investigation, it seems that I am the only one that he code works for. Does sharing the workbook in essence break/ignore the code? Is there a workaround for this type of problem. Many thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) Set t = Intersect(Target, Range("A3:A50")) If Not t Is Nothing Then Upper t FillCells t End If End Sub Public Sub Upper(ByVal Target As Range) Application.EnableEvents = False Target.Value = UCase(Target.Value) Application.EnableEvents = True End Sub Public Sub FillCells(ByVal Target As Range) Select Case Target Case "X" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 15 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "P" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 6 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "L" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 45 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "D" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 50 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "QA" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 38 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "QC" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 55 .Font.ColorIndex = 2 End With Next c Case "S" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 53 .Font.ColorIndex = 2 End With Next c Case Else For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = xlColorIndexNone .Font.ColorIndex = xlColorIndexAutomatic End With Next c End Select End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sharing a workbook breaks VBA code?
The code is inside the worksheet's code module.
There was no installation. I placed the code in the worksheet module, tested that it worked, and then placed the workbook on a network drive where it would be accessible by multiple people. I will investigate the security settings though to see if that is the problem. "Bill Martin" wrote: Did you examine how they installed it? You're apparently using events to detect a worksheet change which then calls some simple code to tinker with cell formatting. You can't just put that code into a module somewhere, but it must be within the code module for the particular worksheet you want to monitor -- unlike other non event driven VBA code which can be in other modules and still work. Are you sure they installed it in the proper place? Also it won't work if their systems somehow have events turned off from some program previously run. Or if their systems are set at a high security level to prevent macros from running. Bill ----------------------- Jason wrote: Sorry. It quietly did nothing. As if the code didn't even exist. "Bill Martin" wrote: What do you mean "it did not work for them"? Did it lock up their machine? Generate error messages? Just quietly do nothing? Work properly, but put up the wrong colors? Something else? More info would be helpful. Bill ----------------------------- Jason wrote: The following code acts as conditional formatting (as conditional formatting only allows for 3 conditions) for a worksheet shared by multiple people. When I added the code and tested it, everything seemed to work fine. That was until another individual let me know that it did not work for them. Upon further investigation, it seems that I am the only one that he code works for. Does sharing the workbook in essence break/ignore the code? Is there a workaround for this type of problem. Many thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) Set t = Intersect(Target, Range("A3:A50")) If Not t Is Nothing Then Upper t FillCells t End If End Sub Public Sub Upper(ByVal Target As Range) Application.EnableEvents = False Target.Value = UCase(Target.Value) Application.EnableEvents = True End Sub Public Sub FillCells(ByVal Target As Range) Select Case Target Case "X" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 15 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "P" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 6 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "L" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 45 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "D" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 50 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "QA" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 38 .Font.ColorIndex = xlColorIndexAutomatic End With Next c Case "QC" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 55 .Font.ColorIndex = 2 End With Next c Case "S" For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = 53 .Font.ColorIndex = 2 End With Next c Case Else For c = 1 To 12 With Target(1, c) .Interior.ColorIndex = xlColorIndexNone .Font.ColorIndex = xlColorIndexAutomatic End With Next c End Select End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sharing a workbook breaks VBA code?
Hi Jason,
I agree with Bill's suggestion. Based on my test, a network shared workbook will be opened by multiple use as readonly, and the macro will run. But as Bill said, you need to change your macro security as a lower level, because for the users who opened them from the network, the workbook is opened from a non local place. e.g. you may change the security to low for a test. Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sharing a workbook | Excel Discussion (Misc queries) | |||
Workbook Sharing | Excel Discussion (Misc queries) | |||
After sharing workbook VB code stops working. Runtime Error 1004 | Excel Programming | |||
Locked workstation breaks code? | Excel Programming |