H
Henk
To make some data input sheets more user friendly, I created the possibility
for the user to choose whether he may select locked cells or not. To disable
the access of locked cells I wrote the following code :
Public Sub InputModeLock()
Application.ScreenUpdating = False
Dim BackSheet As String
Dim BackCell As String
Dim BackCellFound As Boolean
Dim StartCell As String
BackSheet = ActiveSheet.Name
BackCellFound = False
StartCell = ActiveCell.Address
If ActiveCell.Locked = True Then ' Just to direct the CellPointer to an
unlocked cell
Do While ActiveCell.Row > 11
ActiveCell.Offset(-1, 0).Range("A1").Select
If ActiveCell.Locked = False Then
BackCell = ActiveCell.Address
BackCellFound = True
Exit Do
End If
Loop
If BackCellFound = False Then
Range(StartCell).Select
Do While ActiveCell.Row < 100
ActiveCell.Offset(1, 0).Range("A1").Select
If ActiveCell.Locked = False Then
BackCell = ActiveCell.Address
BackCellFound = True
Exit Do
End If
Loop
End If
Else
BackCellFound = True
BackCell = ActiveCell.Address
End If
ActiveSheet.Unprotect Password:="xxxx"
Range("A2502").Value = True
ActiveSheet.Shapes("InputLocked").Select
Selection.Delete
Sheets("Globals").Select
ActiveSheet.Unprotect Password:="xxxx"
Range("InputModeUnlocked").Select
Selection.Copy
Sheets(BackSheet).Select
ActiveSheet.Paste
If BackCellFound = True Then
Range(BackCell).Select
End If
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveSheet.Protect Password:="xxxx"
End Sub
This works okay, but preparing the file for the user I run some code to
protect, hide, reformat ecetera etcetera. To put all sheets where appliccable
in locked position, I call the InputModeLock routine. Which again works
perfectly. The only thing is that my CellPointer (the rectangle around a
selected cell) is gone. In the upper left corner of my screen, where the
selected cell address or name is displayed, I can see that I can select the
unlocked cells, but I do not see the CellPointer. The moment I input anything
in an unlocked cell, which is possible, then the CellPointer is back
immediately.
The code calling InputModeLock routine is :
Sheets("Sheet x").Select
Rows("500:5000").Select
Selection.EntireRow.Hidden = True
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayGridlines = False
If Range("A2502").Value = False Then
Call InputModeLock
End If
Anyone any clue?
Thanks in advance,
Henk
for the user to choose whether he may select locked cells or not. To disable
the access of locked cells I wrote the following code :
Public Sub InputModeLock()
Application.ScreenUpdating = False
Dim BackSheet As String
Dim BackCell As String
Dim BackCellFound As Boolean
Dim StartCell As String
BackSheet = ActiveSheet.Name
BackCellFound = False
StartCell = ActiveCell.Address
If ActiveCell.Locked = True Then ' Just to direct the CellPointer to an
unlocked cell
Do While ActiveCell.Row > 11
ActiveCell.Offset(-1, 0).Range("A1").Select
If ActiveCell.Locked = False Then
BackCell = ActiveCell.Address
BackCellFound = True
Exit Do
End If
Loop
If BackCellFound = False Then
Range(StartCell).Select
Do While ActiveCell.Row < 100
ActiveCell.Offset(1, 0).Range("A1").Select
If ActiveCell.Locked = False Then
BackCell = ActiveCell.Address
BackCellFound = True
Exit Do
End If
Loop
End If
Else
BackCellFound = True
BackCell = ActiveCell.Address
End If
ActiveSheet.Unprotect Password:="xxxx"
Range("A2502").Value = True
ActiveSheet.Shapes("InputLocked").Select
Selection.Delete
Sheets("Globals").Select
ActiveSheet.Unprotect Password:="xxxx"
Range("InputModeUnlocked").Select
Selection.Copy
Sheets(BackSheet).Select
ActiveSheet.Paste
If BackCellFound = True Then
Range(BackCell).Select
End If
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveSheet.Protect Password:="xxxx"
End Sub
This works okay, but preparing the file for the user I run some code to
protect, hide, reformat ecetera etcetera. To put all sheets where appliccable
in locked position, I call the InputModeLock routine. Which again works
perfectly. The only thing is that my CellPointer (the rectangle around a
selected cell) is gone. In the upper left corner of my screen, where the
selected cell address or name is displayed, I can see that I can select the
unlocked cells, but I do not see the CellPointer. The moment I input anything
in an unlocked cell, which is possible, then the CellPointer is back
immediately.
The code calling InputModeLock routine is :
Sheets("Sheet x").Select
Rows("500:5000").Select
Selection.EntireRow.Hidden = True
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayGridlines = False
If Range("A2502").Value = False Then
Call InputModeLock
End If
Anyone any clue?
Thanks in advance,
Henk