Prevent changes to cells but let user move columns?

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> Thanks Tom.
> Is there a way to trap which key was pressed on a sheet?
>
> Sai
>