Hi,

I have a situation where I would like to control what the user types
into cells of a specific column. But at the same time, since I am
using a ListObject I would like to let the user move columns around by
selecting an entire column and then dragging it another location.

I have code in the workbook's sheet_change event. This event is
triggerred when a cell is changed or a whole column is moved. Is there
a way I can disable the checking of values in cells when a column is
being moved?

Thanks,

Sai

Without knowing all the details of what you are doing I would recommend

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 65536 then Exit sub
' existing code
End Sub

--
Regards,
Tom Ogilvy


"sai" wrote in message
oups.com...
Hi,

I have a situation where I would like to control what the user types
into cells of a specific column. But at the same time, since I am
using a ListObject I would like to let the user move columns around by
selecting an entire column and then dragging it another location.

I have code in the workbook's sheet_change event. This event is
triggerred when a cell is changed or a whole column is moved. Is there
a way I can disable the checking of values in cells when a column is
being moved?

Thanks,

Sai

Tom,

I see the logic of what you are suggesting.

The cells.count is usually much less than 65536 and is variable. But I
can get the cell count I need.

How do I distinguish between a move and say a delete? If the user
selects an entire column and hits delete, I want to be able to prevent
that. I want to allow only a move. Any ideas on how to trap the key
pressed or something like that I can use to judge user intention before
I exit sub?

Thanks for your response.

Sai

If you want to have that type of control, I suggest you fully protect your
sheet and handle all user interactions through Userforms. There is no
direct way to determine what has triggered a change event other than
indirect methods such as scanning through the sheet and making comparisons.

--
Regards,
Tom Ogilvy


"sai" wrote in message
oups.com...
Tom,

I see the logic of what you are suggesting.

The cells.count is usually much less than 65536 and is variable. But I
can get the cell count I need.

How do I distinguish between a move and say a delete? If the user
selects an entire column and hits delete, I want to be able to prevent
that. I want to allow only a move. Any ideas on how to trap the key
pressed or something like that I can use to judge user intention before
I exit sub?

Thanks for your response.

Sai

Thanks Tom.
Is there a way to trap which key was pressed on a sheet?

Sai

Only if you want to assign specific keys to execute macros

See OnKey in Excel VBA help.

--
Regards,
Tom Ogilvy


"sai" wrote in message
ups.com...
Thanks Tom.
Is there a way to trap which key was pressed on a sheet?

Sai