I've tried:
Application.DisplayAlerts = false
Application.AlertBeforeOverwriting = False
but nothing can stop this message.
I have written code to control all the different types of drag and drop, but the "we can't do that to a merged cell" message appears before my code can handle it. All the google searches say I have to write the 2 lines above, but it doesn't work.
Thanks!
What exactly are you trying to do to the merged cell and what is in it when that warning is issued? It would help if you showed us your code. Better yet would be if you showed us your workbook (sanitize any sensitive data first though)... see the yellow banner at the top of the forum webpage to see how to post the workbook.
Hi superlative,
I'd suggest you remove the merged cells in your sheet and the message would go away. Excel should never had put merged cells in their work. It simply screws things up, like you've found out.
I hate merged cells!!!
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
like I said, I control drag and drop.Originally Posted by Rick Rothstein
What happens is the first 2 rows are supposed to be header rows, and I don't want users to drag and drop into those rows, so when my code detects the drag and drop row, it will undo it before redoing it as an insert just below the header. The code works fine, but I can't get rid of the message. Sorry, I can't share my workbook, because there is 60,000+ lines of code.
I can't remove the merged cells, because it is part of the header. As soon as my code detects any drag and drop into the header, it will undo it before redoing it as a permitted action. So any "problem" is undone, but not before the pesky message appears.
Okay, I created a small sample workbook to illustrate the error.
Instructions are in the workbook. The merged cells are similar to how I've merged cells to create the header. I wouldn't want to unmerge them because the presentation of the header is a certain way that would not be efficient unmerged.
Thanks for the help. As you can see, in the worksheet_activate event, I have disabled alerts, and yet the message still appears.
Hi,
as you can get the same efficient presentation without merging cells - so no message - with an Excel Basics :
format the horizontal cell aligment to 'Center across column' …
As merging cells is like to know to have a screw in a car wheel and leaving home without doing anything :
you will have to stop to change it along the road in the cold under the rain
but obviously it would be easier to change it at home !
What 's the final result you want ??
If you copy paste the complete row ( Control + c, Control + v), it seems no issue.
- Battle without fear gives no glory - Just try
I get what you're trying to say. I actually tried to do that, but couldn't find the option to center across column or row. But I will look harder.
The final result is an app that functions like a typical app. User's can drag and drop rows naturally, not have to use Ctrl-C/Ctrl-V like it's an Excel spreadsheet. However, allowing drag and drop has its own problems.
Okay, I found the center across selection option in the Horizontal settings, and that will work fine for horizontal, but what I really need is a center across selection across vertical rows, which is what is causing the merge warning message: merged vertical cells. Any suggestions? Thanks.
No solution as it's how Excel is designed …
Do not use merged cells, maybe a workaround with rectangle text forms …
Thanks Marc. That is very disappointing. I can't unmerge because it will make the header look weird. To the right of the header, I am using 2 different rows to represent 2 different types of data (day of week, and day of month), and to the left, I want to have a large Title that takes up both those rows. If I just use 1 row for the title, then I have 1 blank row which looks weird; that's why I merge the cells to have a tall title.
Last edited by superlative; 12-25-2019 at 04:03 PM.
Hi,
How about leaving a blank row below your merged top header row? You could also make the second row height very small so it doesn't show. You could also take your top header row that is merged and move that test to the center column, make the text large and center it.
Thanks for the suggestion, but I don't think it will work. Take a look at the attached screenshot, and see how the title is 2 rows high.
I think you are suggesting a small row to somehow prevent people from dragging the row higher? Which wouldn't work, because the user can still drag that row into the title row.
I think the only way to fix this is to make the title 1 row high, and use the second row to store some other information so it doesn't look like a blank row.
Last edited by superlative; 12-28-2019 at 05:00 AM.
What about cells protection ??the user can still drag that row into the title row.
Cell protection doesn't stop the error message (those cells are protected and the worksheet is protected).
How many times 'Do Not Use Merged Cells' the web is littered with tales of woe.
Why must people be so stubborn not to learn from the fate of their fellow man.
As this message can't be overided until you unmerge, as we already gave you workarounds, well read again our answers …
Displayalerts is reset when your procedure ends and since the error occurs before any event, you can't intercept/cancel the message. (Well, I'm sure you could with mouse hooks, but I really wouldn't recommend it)
Rory
Thanks everyone for helping me out with this, and educating me about how displayalerts works.
I resolved this by unmerging as advised, then I created code to create title shapes that will imitate the position that a title would occupy. This also allows me to have more control over headers. My headers now behave more like an app, not like Excel rows/columns, so that was a side-benefit of covering the headers with shapes.
Now you are safe on the road …
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks