Sharing a workbook breaks VBA code?

[Guest]

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

 

[Bill Martin]

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

 

[Guest]

Sorry. It quietly did nothing. As if the code didn't even exist.

 

[Bill Martin]

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

 

[Guest]

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.

 

[Peter Huang [MSFT]]

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.