How to Disable Cell Merging on Single Sheet (without using sheet protection)

Hi Folks,

I have a workbook with multiple sheets with at least one of which being the subject "list sheet".
On this list sheet there is a header area which has some intended merged cell ranges and a list area below which the user fills in which the cells are not merged (and not intended to be merged).
The list area (consisting of 8 columns, n x rows) in this sheet is filled in by multiple users.
Some use the sheet as intended but as always there is someone who finds a "weakness" in the design.
The weakness in this case is that a user has merged cells in the list area (in this case in a column, vertically).
The intention is that the user fills in one row per item and as this sheet is subject to subsequent post-processing it needs to be clear of merged cells.

[REQUEST]
- How do I disable / not allow merging of cells on a sheet in a workbook without using Sheet Protection?
- The workbook can contain multiple copies of this sheet so it would be necessary for the functionality to disable merging cells to move with the copied sheet.
- Should be XL 2003/2010 compatible

I currently use Sheet Protection to prevent merging of cells but this is affecting other areas on the list sheet which I am having to find workarounds for, all making the template file larger than required when all I need to do really is not allow merging in the first place.

Anyone come across a solution for this problem before?
Your help would be appreciated.

Nutty

Try:

Formula:

Please Login or Register  to view this content.

Hi Ben,

Thank you for your quick reply. Your suggestion is a nifty solution to the stated problem.

After testing the code out today, my initial reaction was ... yes it stops row cells being merged together (vertical merging of cells in same column) but not cells across columns (horizontal merging of cells in same row).
So I played around with "...Target.Columns(1)" and "...Target.Cells(1)" as in the code below just to see what would happen.
In the end I reverted to using your original suggestion however still using this in conjunction with slightly different sheet protection options which now gave the desired result.

Please Login or Register  to view this content.

The reason for not using ...Target.Cells(1) is that I can't then mark a row in my list to copy down / delete etc

So I'll put this one as SOLVED for now

BUT

If you (or anyone else) has any ideas on how to disable the merge function button itself (if possible) then I'd still be interested to find out how to do that (similar to disabling copy/paste button in menus/ribbon etc as in other posts and on web).

I modified this code and it works for me

Please Login or Register  to view this content.

Sorry if there are errors this is my first post!