Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
VBA Code in Shared Workbooks
Message
Information générale
Forum:
Microsoft Office
Catégorie:
Excel
Divers
Thread ID:
01564375
Message ID:
01564711
Vues:
38
J'aime (1)
Thanks, Yuri.
Because Protection can't be changed when the spreadsheet is Shared, my code was crashing on

ActiveSheet.Protect UserInterfaceOnly:=True

It also would not allow me to Lock and Unlock cells.
However, yesterday evening, I came up with a solution, which I was surprised not to have seen on the Web. I wrote code to simulate locking and unlocking, without actually performing those actions. Here's what I did:

1) I created a Global variable, "PreviousCell" that stores the last Active Cell (after the user moves to a new cell).
2) The initial value of PreviousCell is set in the Open event.
3) I created a Workbook_SheetSelectionChange event, which checks if the user tried to enter a forbidden cell. If so, it sends the user back to either the PreviousCell or another cell that I designated, depending on the circumstances. It also updates PreviousCell.

Now, the user can't go where s/he shouldn't, my VBA code can update any fields that it needs to, and my Visual FoxPro's Excel automation can manipulate the data. That's exactly what I needed. Here's my code (I'm new to VBA; so, maybe it can be done better.):

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'Exit Sub
Application.EnableEvents = False
If Selection.Count > 1 Then
ActiveCell.Select
End If
Select Case ActiveCell.Column
Case 1 To 10, 12, 13, Is > 19
PreviousCell.Select
Case 14 To 17 And ActiveCell.Value = "" And Not Trim(ActiveSheet.Rows(ActiveCell.Row).Columns(14).Text + ActiveSheet.Rows(ActiveCell.Row).Columns(15).Text + ActiveSheet.Rows(ActiveCell.Row).Columns(16).Text + ActiveSheet.Rows(ActiveCell.Row).Columns(17).Text) = ""
If Not ActiveSheet.Rows(ActiveCell.Row).Columns(14).Text = "" Then
ActiveSheet.Rows(ActiveCell.Row).Columns(14).Select
ElseIf Not ActiveSheet.Rows(ActiveCell.Row).Columns(15).Text = "" Then
ActiveSheet.Rows(ActiveCell.Row).Columns(15).Select
ElseIf Not ActiveSheet.Rows(ActiveCell.Row).Columns(16).Text = "" Then
ActiveSheet.Rows(ActiveCell.Row).Columns(16).Select
ElseIf Not ActiveSheet.Rows(ActiveCell.Row).Columns(17).Text = "" Then
ActiveSheet.Rows(ActiveCell.Row).Columns(17).Select
End If
Case 19 And Not InStr(gcITNames, Application.UserName + ",") > 0
PreviousCell.Select ' I know that it matches the first condition's statement, but I don't know if VBA can combine the conditions
End Select
Set PreviousCell = ActiveCell
Application.EnableEvents = True
End Sub
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform