Forcing a selection
 

I have a named range that covers data in columns A and B. (A) contains
acompany names and (B) contains the Town the company is in.

Users have the ability to delete entries that aren't theirs by selecting
both (A) and (B) across the row, right clicking and selecting delete. Finally
when asked select to shift the cells us. To add a new entry they add it to
the very bottom of the list then click on a button that runs a macro to
re-sort the list.

This works fine in practice but some users are deleting the company only and
not the town, causing things to go wrong.

How can I force the user to select both columns when deleting. It might not
always be a single line they want to delete. It might be a whole block of
entries.

Anybody got any ideas?

Forcing a selection
 

Maybe have a look in the vba help index for ONKEY

--
Don Guillett Excel MVP
SalesAid Software

"Keith" wrote in message
...
I have a named range that covers data in columns A and B. (A) contains
acompany names and (B) contains the Town the company is in.

Users have the ability to delete entries that aren't theirs by selecting
both (A) and (B) across the row, right clicking and selecting delete.
Finally
when asked select to shift the cells us. To add a new entry they add it to
the very bottom of the list then click on a button that runs a macro to
re-sort the list.

This works fine in practice but some users are deleting the company only
and
not the town, causing things to go wrong.

How can I force the user to select both columns when deleting. It might
not
always be a single line they want to delete. It might be a whole block of
entries.

Anybody got any ideas?

Forcing a selection
 

maybe just make a form with 1 textbox and 1 command button asking for the row
number. assign a shortcut to it, control m or something,

use this code for the command button

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
With ws.Range("A" & TextBox1.Value & ":B" & TextBox1.Value)
.Delete Shift:=xlUp
End With


End Sub

--


Gary


"Keith" wrote in message
...
I have a named range that covers data in columns A and B. (A) contains
acompany names and (B) contains the Town the company is in.

Users have the ability to delete entries that aren't theirs by selecting
both (A) and (B) across the row, right clicking and selecting delete. Finally
when asked select to shift the cells us. To add a new entry they add it to
the very bottom of the list then click on a button that runs a macro to
re-sort the list.

This works fine in practice but some users are deleting the company only and
not the town, causing things to go wrong.

How can I force the user to select both columns when deleting. It might not
always be a single line they want to delete. It might be a whole block of
entries.

Anybody got any ideas?

Forcing a selection
 

Trying to detect the delete would be difficult.

What about using the Worksheet_change event to do

if Target.column < 3 then
on Error Resume Next
set rng = Range("A:B").specialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
on Error go to ErrHandler
Application.EnableEvents = False

set rng1 = Intersect(rng.entireRow,Range("A:B"))
rng1.Delete Shift:xlShiftUp
end if
End if

ErrHandler:
Application.EnableEvents = True

This is an untested idea since I have minimal information on your situation.

--
Regards,
Tom Ogilvy



"Keith" wrote:

I have a named range that covers data in columns A and B. (A) contains
acompany names and (B) contains the Town the company is in.

Users have the ability to delete entries that aren't theirs by selecting
both (A) and (B) across the row, right clicking and selecting delete. Finally
when asked select to shift the cells us. To add a new entry they add it to
the very bottom of the list then click on a button that runs a macro to
re-sort the list.

This works fine in practice but some users are deleting the company only and
not the town, causing things to go wrong.

How can I force the user to select both columns when deleting. It might not
always be a single line they want to delete. It might be a whole block of
entries.

Anybody got any ideas?