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
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only