Cell Entry

[Guest]

I have VBA code that updates protected cells in a protected worksheet. The
user selects the cells to update. How do I restrict this update to certain
cells only? Do I have to check if the selected cell (ActiveCell) is within a
certain range of cells? What is the best way to do this? Thank you.

 

[Guest]

Can you elaborate a bit?
Do you have more than one user?
Do different users' have access to different cells or do all users get
access to the same cells? If this is the case, do you want to simply unlock
the cells that users can edit, then protect the sheet?

 

[Guest]

Only one user. To elaborate, I have drop down and scroll boxes and standard
command boxes occupying some of the space where cells a1 to g12 reside. I
want the user to only select cells in the 'free' area of a14 to g22.
Currently they can also select cells in a1.g12.
Thinking I need to get the active cell and if it is outside the a14.g22
area, then give them a msg to select only in the requested area. Else,
continue processing the update. Just not sure how best to do this.
The way I wrote this, all cells on the sheet are protected. The cells in
a14.g22 simply show an update from another worksheet. But I want this update
to only show in the a14.g22 cells. Hope this explains it. Thanks.

 

[Guest]

The way I wrote this, all cells on the sheet are protected. The cells in
a14.g22 simply show an update from another worksheet.
**********************************************
First, lets look at a little terminology: Cells are not protected, only
sheets or workbooks. Cells are locked or unlocked with
==>Format>Cells>Protection TAB> CheckMark in front of Locked.
However, no visible difference takes place to a locked vs unlocked cell
until the SHEET is protected. Then a user cannot enter data directly into a
locked cell. The value of a locked cell can still change if the cell has a
formula in it that updates from some calculation on another cell.
You mention that your range a14:g22 shows an update from another worksheet.
I therefore come to the conclusion that these cells have formulas in them.
Yet you also, as I understand your post, want the user to be able to select
and change cells in this range? If you allow the user direct access to these
cells the formulas will be lost. Do you really want that?
If you really want the user to be able to edit a14:g22 directly, and not
just by entering data elsewhere, which then updates this range thru formulas,
then:
1. Unprotect the sheet
2. Select the range a14:g22
3 Format>Cells>Protection> Remove the check in front of Locked
4 click ok
5 Protect the sheet

Now the user has access to that range.
In addition:
If you want the user to ONLY be able to SELECT cells that are unlocked when
the sheet is PROTECTED: use the vba code
Activesheet.EnableSelection = xlUnlockedCells

or add it to the Workbook_Open event:

Dim Sh as Worksheet
For each Sh in Worksheets
Sh.EnableSelection=xlUnlockedCells
Next Sh

 

[Guest]

Almost there; thanks for your patience with someone who knows just enough to
be dangerous. The cells in range a14.g22 do have formulas in them to show
the values from another sheet. The cells are locked and the worksheet
protected. I don't want the user to be able to edit these cells. I only
want to restrict selection of ONLY these cells. When selected I capture the
active cell address. The routine updates a cell in the 'other' sheet and, as
you note below, the value in the locked cell in the range a14.g22 that
references that sheet changes. I just want to restrict the user to
selecting only within the a14.g22 range. If he selects anything outside that
range I want to send a msg. If inside the range, the routine completes the
updating of the other sheet. Thanks for you help.

 

[Tom Ogilvy]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldRange As Range
Dim rng As Range
If OldRange Is Nothing Then
Set OldRange = Range("A14")
End If
Set rng = Range("A14:G22")
If Intersect(Target, rng) Is Nothing Then
Application.EnableEvents = False
OldRange.Select
MsgBox "Invalid Selection, only A14:G22"
Application.EnableEvents = True
Else
Set OldRange = Target
' run your code for selection in
' A14:G22
End If

End Sub

 

[Guest]

Thanks very much!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldRange As Range
Dim rng As Range
If OldRange Is Nothing Then
Set OldRange = Range("A14")
End If
Set rng = Range("A14:G22")
If Intersect(Target, rng) Is Nothing Then
Application.EnableEvents = False
OldRange.Select
MsgBox "Invalid Selection, only A14:G22"
Application.EnableEvents = True
Else
Set OldRange = Target
' run your code for selection in
' A14:G22
End If

End Sub

 

[Guest]

Another method:
add this to say the Workbook_Open event:
Sheets("Sheet1").ScrollArea="A14:G22"

 

[Guest]

Gocush, thanks for your help.

Another method:
add this to say the Workbook_Open event:
Sheets("Sheet1").ScrollArea="A14:G22"

 

[Tom Ogilvy]

Unless A14:G22 fits the visible portion of the screen exactly (and the user
can't adjust that area), that should not restrict selection to the subject
area. That only prevents scrolling which I guess is why it is named the
scrollarea property.