change cells
 

I hope someone can help .... Excel 2000

I have a spreadsheet where users need to be able to change values in columns
B:G but not in Column A or H...

I could lock the necessary cells and protect the sheet, however....
The users should be able to delete the enitre row if they wish.

I thought of using the Private Sub Worksheet_Change(ByVal Target As Range)
function - but it executes after the cell is changed. and is there a way to
determine if the cell is being changed or the entire row was deleted?

any ideas?

Thanks
wAyne_

change cells
 

Hi Wayne,
You could possibly restrict the selection, don't know if that would work for you
or not. I am assuming from your question that you are not using H or any column
past H. I this does not work for your situation please indicate what problems
are encountered..

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
If ActiveCell.Column 7 Then
Cells(ActiveCell.Row + 1, 2).Select
Exit Sub
End If
Set rng = Intersect(Selection, Columns("B:G"))
If rng Is Nothing Then
Cells(ActiveCell.Row, 2).Select
Else
rng.Select
End If
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"wAyne" wrote in message ...
I hope someone can help .... Excel 2000

I have a spreadsheet where users need to be able to change values in columns
B:G but not in Column A or H...

I could lock the necessary cells and protect the sheet, however....
The users should be able to delete the enitre row if they wish.

I thought of using the Private Sub Worksheet_Change(ByVal Target As Range)
function - but it executes after the cell is changed. and is there a way to
determine if the cell is being changed or the entire row was deleted?

any ideas?

Thanks
wAyne_

change cells
 

Thanks David,

Interesting idea -- it works somewhat -- gets me a little closer
-- however, it causes another issue --
when you try to delete the row - it pops up with shift cells up left or
delete entire row. I don't want them to do anything other than delete the
entire row ..
is there a way to manipulate / stop the pop up from giving them any other
choice?


"David McRitchie" wrote:

Hi Wayne,
You could possibly restrict the selection, don't know if that would work for you
or not. I am assuming from your question that you are not using H or any column
past H. I this does not work for your situation please indicate what problems
are encountered..

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
If ActiveCell.Column 7 Then
Cells(ActiveCell.Row + 1, 2).Select
Exit Sub
End If
Set rng = Intersect(Selection, Columns("B:G"))
If rng Is Nothing Then
Cells(ActiveCell.Row, 2).Select
Else
rng.Select
End If
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"wAyne" wrote in message ...
I hope someone can help .... Excel 2000

I have a spreadsheet where users need to be able to change values in columns
B:G but not in Column A or H...

I could lock the necessary cells and protect the sheet, however....
The users should be able to delete the enitre row if they wish.

I thought of using the Private Sub Worksheet_Change(ByVal Target As Range)
function - but it executes after the cell is changed. and is there a way to
determine if the cell is being changed or the entire row was deleted?

any ideas?

Thanks
wAyne_

change cells
 

Hi Wayne,

If you are deleting rows one row at at time you could use another event
macro, BeforeDoubleClick, to delete a row.
http://www.mvps.org/dmcritchie/excel/event.htm#event

Right click could be used instead (also needs Cancel = True), but that
might interfere with something else, don't know.

Of course being a macro you would not be able to use undo (Ctrl+Z).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True 'get out of edit mode
ActiveCell.EntireRow.Delete
End Sub

Shift+SpaceBar to select entire row is subject to the same reselection
restrictions as selecting the row by clicking on the row number.

BTW, I presume users are not allowed to insert rows, is that true, although
a row can be inserted through the insert menu, but can't enter content into A or H.

..
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"wAyne" wrote in message ...
Thanks David,

Interesting idea -- it works somewhat -- gets me a little closer
-- however, it causes another issue --
when you try to delete the row - it pops up with shift cells up left or
delete entire row. I don't want them to do anything other than delete the
entire row ..
is there a way to manipulate / stop the pop up from giving them any other
choice?


"David McRitchie" wrote:

Hi Wayne,
You could possibly restrict the selection, don't know if that would work for you
or not. I am assuming from your question that you are not using H or any column
past H. I this does not work for your situation please indicate what problems
are encountered..

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
If ActiveCell.Column 7 Then
Cells(ActiveCell.Row + 1, 2).Select
Exit Sub
End If
Set rng = Intersect(Selection, Columns("B:G"))
If rng Is Nothing Then
Cells(ActiveCell.Row, 2).Select
Else
rng.Select
End If
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"wAyne" wrote in message ...
I hope someone can help .... Excel 2000

I have a spreadsheet where users need to be able to change values in columns
B:G but not in Column A or H...

I could lock the necessary cells and protect the sheet, however....
The users should be able to delete the enitre row if they wish.

I thought of using the Private Sub Worksheet_Change(ByVal Target As Range)
function - but it executes after the cell is changed. and is there a way to
determine if the cell is being changed or the entire row was deleted?

any ideas?

Thanks
wAyne_

change cells
 

Hi Wayne,
Another thought if you go back to using sheet protection, which you were probably
thinking of to begin with.

You can intercept the use of the Del key see (posted by TroyW in 2004), protections
avail depen of version of Excel.
http://groups.google.com/groups?thre...upernews.co m

You would be able delete the entire row, by turning off protection in the macro, delete
the row, then turn protection back on -- record a macro for that part of coding.

--
HTH,
David McRitchie, Microsoft MVP - Excel

change cells
 

Hi wAyne

Maybe protect the sheet if user selects locked cells (alternatively
Intersect with col's A or H), but unprotect if user selects anything else,
including entire rows. First unlock all cells, then lock the two columns.

Sub SetLock()

With Worksheets(1) ' change to suit
.Activate
.Unprotect
.Cells.Locked = False
.Range("A:A,H:H").Locked = True
.Range("B2").Select
End With

End Sub


' in worksheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim b As Boolean
Dim v As Variant

If Target.Columns.Count = Me.Columns.Count Then
'entire row(s) selected
b = True

ElseIf (Range("B1") = "password") Then ' optional
' allow administrator to edit the locked cells
b = True

Else
v = Target.Locked = False
If Not IsNull(v) Then
b = v
' Else
' b = False ' mixed locked & non-locked
End If

' or with hard coded range of the locked cells
' b = Intersect(Target, Range("A:A,H:H")) Is Nothing
End If

If b Then
Me.Unprotect
Else
Me.Protect
End If

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

For Each r In Target.Areas
If r.Columns.Count = Me.Columns.Count Then
If Application.CountA(r) Then

' not empty so not deleted, probably pasted with values
MsgBox "Not with entire rows, will now Undo"

Application.Undo ' if this doesn't work try Ctrl-z
' Application.SendKeys ("^z")
Exit For
End If
End If
Next

End Sub


Not sure about the "Change" event, an attempt to stop user pasting with
values over entire row(s). I'm slightly surprised Application.Undo works
(for me) in this context, I had expected to need SendKeys Ctrl-z.
Some loopholes though, eg paste rows of empty values - but that's similar to
deleting rows.

Regards,
Peter T

"wAyne" wrote in message
...
I hope someone can help .... Excel 2000

I have a spreadsheet where users need to be able to change values in
columns
B:G but not in Column A or H...

I could lock the necessary cells and protect the sheet, however....
The users should be able to delete the enitre row if they wish.

I thought of using the Private Sub Worksheet_Change(ByVal Target As Range)
function - but it executes after the cell is changed. and is there a way
to
determine if the cell is being changed or the entire row was deleted?

any ideas?

Thanks
wAyne_

change cells
 

Thanks Peter,

an excellent Idea, works well.

wAyne_

"Peter T" wrote:

Hi wAyne

Maybe protect the sheet if user selects locked cells (alternatively
Intersect with col's A or H), but unprotect if user selects anything else,
including entire rows. First unlock all cells, then lock the two columns.

Sub SetLock()

With Worksheets(1) ' change to suit
.Activate
.Unprotect
.Cells.Locked = False
.Range("A:A,H:H").Locked = True
.Range("B2").Select
End With

End Sub


' in worksheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim b As Boolean
Dim v As Variant

If Target.Columns.Count = Me.Columns.Count Then
'entire row(s) selected
b = True

ElseIf (Range("B1") = "password") Then ' optional
' allow administrator to edit the locked cells
b = True

Else
v = Target.Locked = False
If Not IsNull(v) Then
b = v
' Else
' b = False ' mixed locked & non-locked
End If

' or with hard coded range of the locked cells
' b = Intersect(Target, Range("A:A,H:H")) Is Nothing
End If

If b Then
Me.Unprotect
Else
Me.Protect
End If

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

For Each r In Target.Areas
If r.Columns.Count = Me.Columns.Count Then
If Application.CountA(r) Then

' not empty so not deleted, probably pasted with values
MsgBox "Not with entire rows, will now Undo"

Application.Undo ' if this doesn't work try Ctrl-z
' Application.SendKeys ("^z")
Exit For
End If
End If
Next

End Sub


Not sure about the "Change" event, an attempt to stop user pasting with
values over entire row(s). I'm slightly surprised Application.Undo works
(for me) in this context, I had expected to need SendKeys Ctrl-z.
Some loopholes though, eg paste rows of empty values - but that's similar to
deleting rows.

Regards,
Peter T

"wAyne" wrote in message
...
I hope someone can help .... Excel 2000

I have a spreadsheet where users need to be able to change values in
columns
B:G but not in Column A or H...

I could lock the necessary cells and protect the sheet, however....
The users should be able to delete the enitre row if they wish.

I thought of using the Private Sub Worksheet_Change(ByVal Target As Range)
function - but it executes after the cell is changed. and is there a way
to
determine if the cell is being changed or the entire row was deleted?

any ideas?

Thanks
wAyne_

change cells
 

Hi Peter,

I added one more thing to your code, its working great by the -
for others that may read this - I added the following::
ActiveCell.Next.Activate just after you protect the sheet, if it is to be
protected - that way when the user hits tab he goes to the next unprotected
cell...

here is the new version...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim b As Boolean
Dim v As Variant

If Target.Columns.Count = Me.Columns.Count Then
'entire row(s) selected
b = True

ElseIf (Range("B1") = "password") Then ' optional
' allow administrator to edit the locked cells
b = True

Else
v = Target.Locked = False
If Not IsNull(v) Then
b = v
' Else
' b = False ' mixed locked & non-locked
End If

' or with hard coded range of the locked cells
' b = Intersect(Target, Range("A:A,H:H")) Is Nothing
End If

If b Then
Me.Unprotect
Else
Me.Protect
ActiveCell.Next.Activate
End If

End Sub


wAyne_

p.s. this actually works better and gives you more control than just
protecting the worksheet on its own .. at least in Excel2000 whe you aren't
given as many choices in what to protect.

thanks again peter...

"Peter T" wrote:

Hi wAyne

Maybe protect the sheet if user selects locked cells (alternatively
Intersect with col's A or H), but unprotect if user selects anything else,
including entire rows. First unlock all cells, then lock the two columns.

Sub SetLock()

With Worksheets(1) ' change to suit
.Activate
.Unprotect
.Cells.Locked = False
.Range("A:A,H:H").Locked = True
.Range("B2").Select
End With

End Sub


' in worksheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim b As Boolean
Dim v As Variant

If Target.Columns.Count = Me.Columns.Count Then
'entire row(s) selected
b = True

ElseIf (Range("B1") = "password") Then ' optional
' allow administrator to edit the locked cells
b = True

Else
v = Target.Locked = False
If Not IsNull(v) Then
b = v
' Else
' b = False ' mixed locked & non-locked
End If

' or with hard coded range of the locked cells
' b = Intersect(Target, Range("A:A,H:H")) Is Nothing
End If

If b Then
Me.Unprotect
Else
Me.Protect
End If

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

For Each r In Target.Areas
If r.Columns.Count = Me.Columns.Count Then
If Application.CountA(r) Then

' not empty so not deleted, probably pasted with values
MsgBox "Not with entire rows, will now Undo"

Application.Undo ' if this doesn't work try Ctrl-z
' Application.SendKeys ("^z")
Exit For
End If
End If
Next

End Sub


Not sure about the "Change" event, an attempt to stop user pasting with
values over entire row(s). I'm slightly surprised Application.Undo works
(for me) in this context, I had expected to need SendKeys Ctrl-z.
Some loopholes though, eg paste rows of empty values - but that's similar to
deleting rows.

Regards,
Peter T

"wAyne" wrote in message
...
I hope someone can help .... Excel 2000

I have a spreadsheet where users need to be able to change values in
columns
B:G but not in Column A or H...

I could lock the necessary cells and protect the sheet, however....
The users should be able to delete the enitre row if they wish.

I thought of using the Private Sub Worksheet_Change(ByVal Target As Range)
function - but it executes after the cell is changed. and is there a way
to
determine if the cell is being changed or the entire row was deleted?

any ideas?

Thanks
wAyne_

change cells
 

Hi Wayne,

Good idea, certainly stops users straying out of bounds. If they tab from
say G20 H20 sends them back to B1, trust that's not disconcerting.

With your ActiveCell.Next.Activate I wonder if it's even necessary to
protect the sheet at all. But perhaps best also to do that as a double
safeguard.

Worth noting that use of ActiveCell.Next.Activate causes the SelectionChange
event to run a second time, not a problem with the code as-is but might be
if you include code to do other things.

Curiosity, do you use the optional extra I threw in to allow easy access for
an administrator. Also do you use the "Change" event aimed at preventing
paste over entire rows (I never tested that properly).

Regards,
Peter T

"wAyne" wrote in message
...
Hi Peter,

I added one more thing to your code, its working great by the -
for others that may read this - I added the following::
ActiveCell.Next.Activate just after you protect the sheet, if it is to be
protected - that way when the user hits tab he goes to the next
unprotected
cell...

here is the new version...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim b As Boolean
Dim v As Variant

If Target.Columns.Count = Me.Columns.Count Then
'entire row(s) selected
b = True

ElseIf (Range("B1") = "password") Then ' optional
' allow administrator to edit the locked cells
b = True

Else
v = Target.Locked = False
If Not IsNull(v) Then
b = v
' Else
' b = False ' mixed locked & non-locked
End If

' or with hard coded range of the locked cells
' b = Intersect(Target, Range("A:A,H:H")) Is Nothing
End If

If b Then
Me.Unprotect
Else
Me.Protect
ActiveCell.Next.Activate
End If

End Sub


wAyne_

p.s. this actually works better and gives you more control than just
protecting the worksheet on its own .. at least in Excel2000 whe you
aren't
given as many choices in what to protect.

thanks again peter...

"Peter T" wrote:

Hi wAyne

Maybe protect the sheet if user selects locked cells (alternatively
Intersect with col's A or H), but unprotect if user selects anything
else,
including entire rows. First unlock all cells, then lock the two
columns.

Sub SetLock()

With Worksheets(1) ' change to suit
.Activate
.Unprotect
.Cells.Locked = False
.Range("A:A,H:H").Locked = True
.Range("B2").Select
End With

End Sub


' in worksheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim b As Boolean
Dim v As Variant

If Target.Columns.Count = Me.Columns.Count Then
'entire row(s) selected
b = True

ElseIf (Range("B1") = "password") Then ' optional
' allow administrator to edit the locked cells
b = True

Else
v = Target.Locked = False
If Not IsNull(v) Then
b = v
' Else
' b = False ' mixed locked & non-locked
End If

' or with hard coded range of the locked cells
' b = Intersect(Target, Range("A:A,H:H")) Is Nothing
End If

If b Then
Me.Unprotect
Else
Me.Protect
End If

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

For Each r In Target.Areas
If r.Columns.Count = Me.Columns.Count Then
If Application.CountA(r) Then

' not empty so not deleted, probably pasted with values
MsgBox "Not with entire rows, will now Undo"

Application.Undo ' if this doesn't work try Ctrl-z
' Application.SendKeys ("^z")
Exit For
End If
End If
Next

End Sub


Not sure about the "Change" event, an attempt to stop user pasting with
values over entire row(s). I'm slightly surprised Application.Undo works
(for me) in this context, I had expected to need SendKeys Ctrl-z.
Some loopholes though, eg paste rows of empty values - but that's
similar to
deleting rows.

Regards,
Peter T

"wAyne" wrote in message
...
I hope someone can help .... Excel 2000

I have a spreadsheet where users need to be able to change values in
columns
B:G but not in Column A or H...

I could lock the necessary cells and protect the sheet, however....
The users should be able to delete the enitre row if they wish.

I thought of using the Private Sub Worksheet_Change(ByVal Target As
Range)
function - but it executes after the cell is changed. and is there a
way
to
determine if the cell is being changed or the entire row was deleted?

any ideas?

Thanks
wAyne_