Lock Row and Column

[majidsiddique]

Hi all,
i want to lock corsor movement in in specific column like C - D and E means not to enter specific column like C-D and E. when you try to cursor goes to C then it will goto directly f.
rest of all column will be enter cursor.
can anybody tell me how is this?

Thanks

[mehmetcik]

You will need a small macro.

Right Click On Your Sheet Name At the Bottom Of Excel and Select View Code
Paste this code in the module that opens and close it.

Save the workbook as macro enabled.

Please Login or Register  to view this content.

[majidsiddique]

hello mehmetcik,
thanks to reply. i use this but column C is not lock, D and E are lock, and when move to cursor back from F to A it is nto going to back and stay in F. i want to move cursor back, only C -D-E will be locked.
Thanks

[Richard Buttrey]

Please clarify,

It's not clear (at least to me) what you mean by locked.
Locked with resepect to cell has a very specific meaning when Sheet Protection is switched on.

Neither do I understand whether you want to prevent columns C/D/E being selected and column F the selected column or whether ONLY C/D/E can be selected.

[majidsiddique]

Richard Buttrey Sir,
actually i have a file to protect it with some range to get formula or change any thing. so i want given range lock first and then cursor movement not working on it.
like:
range C2 to E10 totally lock cell and cursor never be entered in these cells. if you goto c2 cursor move to out of this range from left to right.
if any more clarification then i will send you excel file.
Thanks.

Thanks.

[Richard Buttrey]

Have you left all cells as locked(that's the default setting, and are you Protecting the Sheet 'with Format Protect Sheet'?

When you protect a sheet you are given the options to Select Locked cells AND Select Unlocked cells. However even if you allow selection of locked cells the user can't change them only view them. Does that help?

[majidsiddique]

Hi Richard,
i sending you a file. i revived this from someone, but i dont know this setting. kindly tell me abount this setting i a excel.
i use this setting in my file.
thanks.

[Richard Buttrey]

See attached,

I'm still not sure exactly what you want to happen.
In this workbook if the user selects a cell in columns C:E the cursor will jump to column F.

It would have been easier if you had mentioned the password for the protection. However I'm not sure it's absolutely necessary, this one is just protected without a password but apply a password if you wish.

[majidsiddique]

Hi, Richard
there is a difference between your and my file. in your file i cannot goto A to E column. you lock cursor movement at F. in my file only C8 to E12 cursor movement lock and you can move everywhere. kindly tell me about this setting.
i understand your point if there is no need of protection please leave it but cursor movement lock within the cell.
kindly generate macro which i can choose myself lock area.
Thanks.

[Richard Buttrey]

Still not sure I understand what you want.

If you want to be able to select any column other than columns C:E then change the macro to

Please Login or Register  to view this content.

Why do you want a macro to change the cells which are locked.
As I said earlier all cells are locked by default once Sheet protection is set.

Just use the Format Cells Protection option to change the 'locked' status

[majidsiddique]

Hi Richard,
i use this code and apply and run it but message come to new window macro name. why this?
Thanks.

[Richard Buttrey]

What exactly is the error message you get and what line of code is it debugging on?

Are you sure you edited the original I gave you and are using the Worksheet Selection Change event?

If you are then please upload the wprkbook you are using.

[majidsiddique]

Hi Richard,
please see the actuall error.
i dont know enough knowledge regarding VBA coding.
Thanks.

[Richard Buttrey]

That's not an error. It's simply the normal Excel Macros dialog box. All its telling you is that there is no Module in VBA which contains a macro,

In VBA there are two sorts of macro.

There are macros that react to sheet events of various sorts. e.g. double clicking a cell, right clicking a cell, activating a sheet, selecting cell, changing a cell.....and several others. Sheet event macros do NOT appear in the Macos dialog box - simply because you can't run them manually out of choice. They will automatically run if a certain event happens.

These event macros can be seen when you choose 'Worksheet' in the drop down box in VBA. In the other drop down you can see all the event macros. In the picture you show you can see it is the Selection_Change event. So in your case when you select a cell in columns C:E (which are locked) the cursor jumos to column F. As I said earlier the only reason you can select a column C:E cell at all is because when you Protect the sheet you are given the option to Select Locked cells. It doesn't mean that you can change cells, and in the normal course of events you could select them to see what's in them (but not edit or change). In your case however there is a selecion_change event which takes priority so the second you try to select the cell the cursor goes to column F.

Macros that you create to do other stuff are created inside a VBA Module which you can create by choosing Insert Module from the VBA menu bar.
These macros you WOULD see in the Macros dialog box. In your case there aren't any.


Hope this helps

[majidsiddique]

Hi Richard,
Lets finish this.
kindly tell me in the new file to apply new setting.
i want cursor movement not allow in the range of C3 to G13.
is this possible by simple way means without MACRO or VBA coding?
if YES please tell me.
rest of all cell will be treated as normal working.
protection is nescessry for this setting or NOT?
please explain in very simple way to apply this setting.
i use property setting lock scroll setting but in this cursor movement set withing scroll bar area.
i want cursor not allow in the given range area.
Thanks.

[Richard Buttrey]

The simplest way to prevent locked cells being selected is to add sheet protection and ensure the 'Select Locked Cells' option is NOT, repeat NOT ticked. i.e. the point I made way back in post #6

Hence Lock Cells C3:G13, switch on sheet protection and don't allow selection of locked cells.

[majidsiddique]

Hi richard,
Can you explain me this step by step.
I am doing but dont get desired result as my wish.
Thanks

[majidsiddique]

Hi richard,
I got your point. I do it,
Thanks for your guidance.
Thanks

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Richard Buttrey]

Hi richard,
I got your point. I do it,
Thanks for your guidance.
Thanks

My pleasure, glad to have assisted