Information générale
Titre:
VBA Code in Shared Workbooks
I have written VBA code in Excel 2010, to lock cells programmatically, under certain conditions. For example, if Column N gets filled in, Columns O, P, and Q should be locked, since they are mutually exclusive. My VBA code also puts user and date stamps in columns that are otherwise locked. I need to make this spreadhseet writable for multiple users at once. However, when I set enable Share Workbook, my VBA code crashes, seemingly because it now can't lock cells and programmatically insert code into locked cells. Is there a way to get around this, such as by programmatically turning Share Workbook on and off as needed, or allowing multiuser editing another way, or allowing my VBA code to do its stuff regardless of the Share Workbook setting?
Here are the relevant parts of my code:
Private Sub Workbook_Open()
ActiveSheet.Protect UserInterfaceOnly:=True
gcITNames = (names of applicable users)
ActiveWorkbook.ActiveSheet.Columns(19).Locked = (Not InStr(gcITNames, Application.UserName + ",") > 0)
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
ActiveWorkbook.ActiveSheet.Cells(x, 14).Locked = (Not (ActiveSheet.Cells(x, 15).Value = "" And ActiveSheet.Cells(x, 16).Value = "" And ActiveSheet.Cells(x, 17).Value = ""))
ActiveWorkbook.ActiveSheet.Cells(x, 15).Locked = (Not (ActiveSheet.Cells(x, 14).Value = "" And ActiveSheet.Cells(x, 16).Value = "" And ActiveSheet.Cells(x, 17).Value = ""))
ActiveWorkbook.ActiveSheet.Cells(x, 16).Locked = (Not (ActiveSheet.Cells(x, 14).Value = "" And ActiveSheet.Cells(x, 15).Value = "" And ActiveSheet.Cells(x, 17).Value = ""))
ActiveWorkbook.ActiveSheet.Cells(x, 17).Locked = (Not (ActiveSheet.Cells(x, 14).Value = "" And ActiveSheet.Cells(x, 15).Value = "" And ActiveSheet.Cells(x, 16).Value = ""))
If Trim(ActiveSheet.Cells(x, 13).Value) = "" And Not Trim(ActiveSheet.Cells(x, 14).Value) + Trim(ActiveSheet.Cells(x, 15).Value) + Trim(ActiveSheet.Cells(x, 16).Value) + Trim(ActiveSheet.Cells(x, 17).Value) = "" Then
ActiveSheet.Cells(x, 12).Value = Application.UserName
ActiveSheet.Cells(x, 13).Value = Date
ElseIf Not ActiveSheet.Cells(x, 13).Value = "" And Trim(ActiveSheet.Cells(x, 14).Value) + Trim(ActiveSheet.Cells(x, 15).Value) + Trim(ActiveSheet.Cells(x, 16).Value) + Trim(ActiveSheet.Cells(x, 17).Value) = "" Then
ActiveSheet.Cells(x, 12).Value = ""
ActiveSheet.Cells(x, 13).Value = ""
End If
Application.EnableEvents = True
End Sub
Jerry
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement