Prevent changes to cells but let user move columns?

  • Thread starter Thread starter sai
  • Start date Start date
S

sai

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
 
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.
 
Only if you want to assign specific keys to execute macros

See OnKey in Excel VBA help.
 
Back
Top