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