Hi six6to8eight;
Put EnableEvents = False sooner in Account View!Worksheet_Change.
This line triggers Worksheet_Change again right in the middle of filling in the sheet.
Turn off ScreenUpdating at the same time. Your sheet will load faster.
These kind of things are usually better left until after all the data has been put in, and then format the entire range at once, rather than 1 line at a time.
An even better idea, is to manually format the entire column, then you don't even have to worry about the macro doing it.
Be able to remove/edit the Items in an account right from the Account View sheet (after they've been displayed). From at GUI standpoint, it would be fine to simply have checkboxes next to each Item where you could check off the ones to be deleted (and then hit a "Delete" button)
That's the way that Excel Forum does it. Look at the Private Message screen for example. Buttons could be used as long as you "Are you sure?" when the button is clicked. If deleting more than 1 item could happen frequently, then use checkboxes. If deleting is rare then maybe use a menu selection (no point in cluttering up the screen for rare events).
I'm guessing that to accomplish this, I'm going to need to add a unique ID# to each Item (each row, really) on the Holdings Master sheet.
I don't know what "Items" are, so I'm guessing.
If "Item 1" is something that could be chosen from a ListBox, then each Item should probably have it's own "Key", and all Items would be listed on their own sheet, and you should design an "Item Mainenance" screen. OTOH, if "Item 1" is just words that can be changed at any time then they don't need their own key.
If each Item has it's own key and each Account # can have only 1 "Item 1" attached to it, then it's not necessary to have a unique key on the Holdings Master sheet. Just look for the Account # & Item_Key combination (because it will be unique).
OTOH, If you can change "Item 1" to "My Item" on the Account View sheet, or if Bob can have more than 1 "Item 1" attached to him, then you will need a unique Holdings_Key for each row on the Holdings Master sheet.
When an Item is deleted from an account on the Account View sheet, its entire row on Holdings Master should be deleted, and, if possible, everything below should move up a row to fill the gap (mimicking what happens when you right click a row and choose "delete").
range.EntireRow.Delete
A smaller issue that I've found a fix for, but would like to make more efficient: the arithmetic formulas that I'm using to fill some of the yellow Item cells on Account View.
You can enter a formula instead of a value:
Since they are a calculation and you want to edit the data on this sheet, you should just enter the formula into the cells (like the line below). I didn't test this line, so you will have to debug it.
Or you can do it the way I do it. Enter the formula into a hidden row (I usually use row 1), and then copy the formula into the cell.
Or have the formulas already entered in the cells for 100 rows, and just hide/show the rows.
but it ends up taking almost twice as long to fill the sheet,
Turn off ScreenUpdating and the time will be LOTS quicker.
When I protect the Account View sheet, the macro is no longer able to fill the sheet
Protect the sheet using the InterfaceOnly option. Unfortunately that option is not saved with the sheet, so you have to protect the sheet every time the workbook opens. A lot of people put this into their workbooks:
Then the macros can put values, formulas, etc into locked cells.
Get the Notes merged cell to write back to the Accounts Master sheet
While the Copy - Paste method might not work for merged cells, this does:
Bookmarks