Paste to next empty row across multiple worksheets from master

Hi six6to8eight
Welcome to the forum
You will needed a commandbutton with VBA code to comply the results.

hey six6to8eight,
the syntax will be (range)copy destination(Range)

eg..

Please Login or Register  to view this content.

Hi six6to8eight;

Just checking to see if you're still looking for an answer.
The thread is not marked "Soved", but I don't want to waste your time, if you are satisfied with Pike's answer.

I don't know what happened.
I responded to this thread a while back, but my message isn't here.
I don't know if it got deleted, or I messed up trying to post it.

Anyway,
I have attached a workbook that has a button on the master sheet.
Click that button, then look at Account 1 sheet and see if that is what you wanted (my guess is that it is not, but it will give us a place to start)

It's not an absolute that you will need a button, but you might find it easier than using the "Enter" to trigger a macro. It can be set up so that an Enter on row 6 triggers the macro to look for an amount in row 5 and continue if it's not 0, but most people find it more user friendly to use a button and let the user decide when to trigger it.

I'm not sure where to go from here, so I will need a better description of what you want.
I think you will enter amounts in rows 5:6 under the correct Account heading, and that is how the macro will know which sheet to copy it to. If that is not correct, you will need to tell me how the macro will know what sheet to post it to.

One last thing:

I am going to be adding Items to the master sheets over time in small groups. This means that I need the macro to only add the new items every time I press the button. It has to be able to disregard the old entries so that they don't get added twice to the account sheets. I can't delete the old Item entries from the Master Data sheet because I need to keep them all there as a historical reference. It would be possible for me to delete them from the Master Accts sheet though, so maybe it would make sense to have the Items on the Master Accts sheet search for and pull their data from the Master Data sheet before the macro enters them onto the corresponding account sheets?

Ideally, I'd like to keep all Item data on both master sheets, but if necessary I can clear off the Items from the Master Accts sheet after each time I run the macro...

Does this make sense? Hope I'm not asking for too much...

Hi six6to8eight;

Originally Posted by six6to8eight

Green (item data): paste from master data to account sheets, but ONLY if there is an amount input for that account in the blue area on the Master Acct sheet.

I did not see any Green on Master Accts. I did find Green on Master Data, but I had no idea what sheet to paste it onto.

I would make a suggestion.
Down the road you will want to improve this workbook, and the way you are currently doing it will cause you problems.

I would not move data from a Master data entry sheet to the individual Account sheets. I would move it to an "all data sheet" (probably hidden) where all the "actual" data is stored. Then on 1 Account sheet, I would have a listbox to select what account to display and use formulas to pull the info you want from the "all data sheet" for that account. This way when you want to change the way the display sheet looks, you don't have to do a lot of sheets all at once. Right now when you want to change the display, you have to stop putting data in until you change every Account sheet.

Fox,

This does sound like a MUCH better solution. I didn't even know such a thing was possible. Could you give me a quick pointer on how I might start to go about using Listboxes? If you just gave me a push in the right direction I could probably figure most of it out myself. I'm pretty familiar with Excel, but just can't write code to save my life... I like the thought of no macros!

Thanks again

EDIT: Now that I've actually looked at what listboxes can do, I'm thinking they may not be exactly what I'm looking for... My concern is about automation: can I still automate the updating of data across all the account sheets? All I'm trying to do here is save time, because there are going to be a LOT of accounts that need to be updated. If I have to go through each account tab and choose the values from a listbox dropdown, it's going to defeat the entire purpose. Is there something I'm missing here? Any help would be awesome.

Originally Posted by six6to8eight

EDIT: Now that I've actually looked at what listboxes can do, I'm thinking they may not be exactly what I'm looking for... My concern is about automation: can I still automate the updating of data across all the account sheets? All I'm trying to do here is save time, because there are going to be a LOT of accounts that need to be updated. If I have to go through each account tab and choose the values from a listbox dropdown, it's going to defeat the entire purpose. Is there something I'm missing here? Any help would be awesome.

You misunderstood me.
I'm saying get rid of sheets("Bob") and sheets("Chris") and just have 1 sheets("Accounts"). On that sheet you would have a listbox with "Bob", "Chris", etc in the list. When you selected a name (say Bob), the formulas would pull all the data for Bob from sheets("All Data") into sheets("Accounts"). This would save you time in the future when you want to redesign sheets("Accounts") because you wouldn't have a sheets("Bob") or sheets("Chris") to redesign.

Setting up the formulas for sheets("Accounts") can take longer to set up than individual Account sheets, but it will save you time in the long run. And if a bug shows up in the macros that copy data from the Master Sheets, it can save you a LOT of time, because debugging formulas is almost always easier to debug that macros.

One final note: It is almost always (I'd say at least 95% of the time) a better idea to just have the data in one place, and use formulas to "look at" the data. It is seldom a good idea to have copies of the data in different places (like on a Master sheet and individual Account sheets).

Hi six6to8eight;

Originally Posted by six6to8eight

What I'm still confused about is how to organize all the data from so many accounts into one sheet. How can I do this without it getting jumbled? Do I just put the accounts next to each other, side to side, extending way off to the right?

The data would be stored down the page. Column A would have the Name of the person it belonged to, and every column would contain it's own piece of the data that belonged to that name.

Then when "Bill" is selected in the ListBox all the formulas would look for "Bill" in the data and pull the data from that row into the Account sheet.
A simple Example

Please Login or Register  to view this content.

And the Account Sheet might look like:

Please Login or Register  to view this content.

You might still use the same forms for collecting the data. Just instead of moving it to individual sheets, it would be moved to a "Data" sheet (it can even be hidden), where the "Account" sheet looks for it.

I recognize that you may not want to try and modify your existing workbook at this time. But you should be able to modify a bit at a time till it's more efficient.

Now back to your problem.
From my earlier message:

I did not see any Green on Master Accts. I did find Green on Master Data, but I had no idea what sheet to paste it onto.

Originally Posted by six6to8eight

The only issue with organizing the data like that is that I need to have a list of items with details for each account, so each account can't just occupy one row. As it is now, the data in each account is about 10 across by 30 down, so I'm not sure how I would format this into a master acct data sheet...

It can all be stored on one row if you don't have more than 256 items for each row. And even then it can be stored in 2 rows. It wouldn't make a difference to the Account sheet. The account sheet can be designed to look exactly like you want.

Please Login or Register  to view this content.

I'll look at your current workbook now.

Originally Posted by six6to8eight

As for the example spreadsheet, the green from 'Master Data' should be pasted into accounts Bob and Chris (to the corresponding green columns), but only if there is a numeric value (in blue cells on 'Master Accts') present for the corresponding "Item." Does this make sense?

No, still doesn't make sense.
'Master Data'!F4 (1/10/2010) - how will the macro know which sheet (Bob or Chris) to paste it into? 'Master Accts' has a total in C5 & D5. So which one does it get pasted into?

Originally Posted by six6to8eight

Do you get what I mean now?

I'm close.
In message # 10, you said:

I can't delete the old Item entries from the Master Data sheet because I need to keep them all there as a historical reference. It would be possible for me to delete them from the Master Accts sheet though, so maybe it would make sense to have the Items on the Master Accts sheet search for and pull their data from the Master Data sheet before the macro enters them onto the corresponding account sheets?

Ideally, I'd like to keep all Item data on both master sheets

How does 'Master Accts'!C5 know what data on 'Master Data' it is attached to? IOW if you enter 'Master Accts'!A8 = "Item1" and 'Master Data'!A7 = "Item1", how will the macro know which row on 'Master Data' (row 4 or row 7) to copy?

Don't try to figure what to store or delete, because it can be set up how ever you want it. You want all the data on 'Master Accts' and 'Master Data' saved, so we will find a way to do it.
For now just tell me how the macro will know which rows on 'Master Accts' to match up with which rows on 'Master Data'.

No two Item names in column A on both sheets will never be the same -- IOW, each unique Item name will only exist once in column A of Master Accts and onnce in column A of Master Data. There will never be duplicates. I was hoping there would be a way to search the column for the string name of the Item and then go from there...

But the more I look this template, the more I'm realizing that it's a pretty cumbersome way to do this... Let me ask you something else: I see the beneift in having a master accounts sheet where each account only has one row and all data for that account is stored "horizontally," from left to right. However, if I store multiple Items in each account in this way, "horizonally" across the master sheet, how will I be able to handle deletions of these Items? Removing them will leave gaps in the account's row on the master sheet. Thinking in terms of arrays of cells, each item is 1down x 8across. If I store all of these Items side to side in a single row for the account, isn't it going to create problems when I want to delete them?

A related question: by using the listbox method to bring up account data stored on a master sheet, can I use the listbox view to edit the master data sheet? The issue here is that I need the master account sheet to be easily editable and user-friendly, but obviously it will be unwise to make edits directly to this master sheet (where each account is jammed into one row). Can I use the listbox to bring up the account data in a viewable way, and then make edits to the master record right from there?

Originally Posted by six6to8eight

No two Item names in column A on both sheets will never be the same -- IOW, each unique Item name will only exist once in column A of Master Accts and onnce in column A of Master Data. There will never be duplicates.

I'm guessing from this that all the data entered on the Master sheets is not stored on those sheets for historical use. After entering the data and copying it to the "Bob" and/or "Chris" sheets, that the data will be removed from the Master sheets and stored somewhere else.
I had the impression that it would stay on the Master sheets and you would just keep adding more rows for later data.

I was hoping there would be a way to search the column for the string name of the Item and then go from there...

No problem, just need to get it all straight in my head about how this all works.

I see the beneift in having a master accounts sheet where each account only has one row and all data for that account is stored "horizontally," from left to right. However, if I store multiple Items in each account in this way, "horizonally" across the master sheet, how will I be able to handle deletions of these Items? Removing them will leave gaps in the account's row on the master sheet. Thinking in terms of arrays of cells, each item is 1down x 8across. If I store all of these Items side to side in a single row for the account, isn't it going to create problems when I want to delete them?

Each item would be on it's own row. So in your Updated Example workbook, the historical data sheet would look like this.
If Account 101 is ALWAYS Bob then we can get rid of the 1st column.

Please Login or Register  to view this content.

A related question: by using the listbox method to bring up account data stored on a master sheet, can I use the listbox view to edit the master data sheet? The issue here is that I need the master account sheet to be easily editable and user-friendly, but obviously it will be unwise to make edits directly to this master sheet (where each account is jammed into one row). Can I use the listbox to bring up the account data in a viewable way, and then make edits to the master record right from there?

Yes, that is the most common way to edit a database. The user selects a Name (or Account if 101 is ALWAYS Bob). The program copies the data from the database into a form of some sort (a spreadsheet is frequently used). The user edits it and clicks a "Save" button that copies the data back into the database (or adds it if it's new data) (or deletes it if a "Delete" button was clicked).

Let me know if you want to change how your data is stored. I will wait until you answer before I write the code to copy the data into the individual sheets.

Hi six6to8eight;

I got a little lost on the new layout.
I'm guessing that you want to use Acct Display to enter in data. It will have a save button on it to save all the data entered onto the Master sheets.

I have a hunch that the green cells on Acct Master need their own sheet, but it's possible I'm wrong. I need to see what the formulas do in order to decide.

By The Way, all data should be keyed on the Acct # not the Name. People do sometimes change their name, but the company will never change the Acct #. The name is just another piece of information about the Acct #. Like an address is a piece of information about a person.

It would help if you described what the company does. Are these sales by a salespersons? I can't think of a business where two people would share an item (what ever that is), but one would get an account total of 10, while the other gets 20.

In theory this seems cumbersome

It's not cumbersome once you start using it. In fact after you use it for a while, you will wonder how you could ever think of doing it some other way.

You're right that I don't need both the acct name and the acct number on the Items page, but I'd like to keep both if possible just in case I need to take a glance at the Items Master sheet for any reason down the line.

Yes you would want to see the name all the time. But you only want to type it in in 1 place, then use lookup formulas to display it next to the Acct #. All the user will type in is "101" and the spreadsheet will pull up the name next to it. That way if Bob starts wanting to be called Robert, you just change it in one place and every report back to the beginning will have his name show as Robert.
Note: This is Critical
Is there any chance at all that Acct 101 will ever be attached to anyone else. Even if it's a one in a million chance, I need to know. I don't care if it would take 5 different people being buried alive on the same day before 9 am. I need to know.

Green cells = acct totals (based on Items in the acct) which will be calculated with forumlas that I'll add later. If you could implement these cells in the single acct listbox display, I can add the formulas at a later point (will this work okay?)

Don't understand this. You will have to describe how they are calculated or a description of what they represent.

Obviously, I'm completely open to any alterations you want to make. I'm interested to know what you think of this layout.

I don't understand what all the columns are for, so I can't comment on the whole thing. The only things I need to know about are whether Acct 101 can ever be attached to Dianne and what do the green cells represent.

If it looks good and you've got the time, it would be a huge help if you could take a shot at the code. No rush on this, though.

I can't do the whole thing for you, but I'll get you started. What kind of time frame are you in?

One last thing: when adding a new Item, I was hoping there would be an easy way for me to input an amount for each account and have that Item get "added" to each account with the amount that I designated. I didn't create a sheet for this in my most recent template, but would it be possible to have a sheet that displays all accounts and allows the user to add an Item and put a certain amount of it in each account? It would then (on a button push, maybe) add multiple rows to the Items Master sheet all at once. Does this sound doable?

Don't understand this either, but I can't imagine that there is anything you want that can't be set up.

Hi six6to8eight;

Just wanted to let you know I haven't forgotten you.

We had a power failure here for over 24 hours. I'm catching up on everything I didn't get done yesterday, so it will be a while, but hopefully today.

What's the best way to handle an account attribute that is boolean? Should I make a column and add either T's or F's for all the accounts? Or just T's and leave the false cells empty? Or should I use 0's and 1's? Or does it not really matter how I do it?

If all you're doing is storing it and looking at it on a report, it doesn't matter at all. Whatever makes the most sense to the user.
If you are going to have other formulas reading it and making decisions based on it's value then it matters. It depends on how the formula is going to use it.

Hi six6to8eight;

I have a hunch that the green cells on Acct Master need their own sheet, but it's possible I'm wrong. I need to see what the formulas do in order to decide.

If they do end up needing their own sheet, that would be okay, since it's all just mater data being stored behind the scenes. I'd sort of like to keep them on there if possible, just to keep the sheet count down, but it doesn't matter all that much either way.

I guess it depends on how many rows the green cells take up for each Acct #.
If they only take 1 row then they can stay where they are.
If they take more than 1 row, then they need to be separated from the Acct # data. They can be on the same sheet, but it's easier if they are on their own sheet. If they are on the same sheet, then you or a macro cannot just insert or delete a row. They have to be careful to only insert or delete the correct columns.

As for your question about whether the account number could ever be linked to a different name/person, I am almost 100% positive that this would never happen.

Ok, you have to make a decision. Each Acct Name has to have a unique key. If you use the Acct # as the key and it ever gets assigned to another person, you will have 1 big mess on your hands (or whoever has to deal with it). If you are unsure then I STRONGLY suggest that you assign a key to each person. The key never has to be seen by any user (not even you). It can be generated by Excel and no one ever has to even see it. But the key is the only thing that will keep the different types of data connected together.

The Items don't refer to sales, but rather holdings. So if an "Item" is bought, it will basically be dished out in differing amounts between the accounts. People will possess different amounts of the same thing. Think stocks -- the model is almost exactly the same.

So on the original sheet, you have Bob's Item 1 total of 10, and Chris's Item 1 total of 20, but they both have the same Item 1 detail. How does that happen? What is Item 1? I don't think I can accurately recommend how you treat it without knowing this.

One last thing: when adding a new Item, I was hoping there would be an easy way for me to input an amount for each account and have that Item get "added" to each account with the amount that I designated. I didn't create a sheet for this in my most recent template, but would it be possible to have a sheet that displays all accounts and allows the user to add an Item and put a certain amount of it in each account? It would then (on a button push, maybe) add multiple rows to the Items Master sheet all at once. Does this sound doable?

Don't understand this either, but I can't imagine that there is anything you want that can't be set up.

This is actually the most crucial element of the whole project and the entire reason why I'm redesigning the spreadsheet in the first place. But I trust that you're right when you say it can be implemented later on. I'll try to better explain it better once we're farther along with the basic setup.

Ok. I don't have to understand this, because I'm not the one who will have to deal with the problems if it's designed wrong.
Here's the critical thing in designing a database (think of it as a table). Each column has to be unique in the table. You can't do this:

Please Login or Register  to view this content.

It has to be set up like this:

Please Login or Register  to view this content.

Each Master row will be assigned a unique #, so that each Item that has that unique Master-Key is attached to it.
If each item can have more than 1 of something (other info) then the item is assigned a unique Key and then the table will look like this

Please Login or Register  to view this content.

and the 'other info' table would look like this (Item-Key 001 only has 1 'other info', Item-Key 002 has 2 'other info')

Please Login or Register  to view this content.

Hopefully you can see that Each Master row has a unique Master-Key, and each Item row uses that Master-Key to identify which Master row it is attached to, and each Item row has a unique Item-Key and each 'other info' uses that Item-Key to identify which Item row it is attached to.

Designing the data structure (tables) and identifying which pieces of data need keys is the most important thing in designing a database application. The rest is just detail. How you design the data input screen is a detail. If the data structure is set up correctly, then just about anything can be done with it.

Hi six6to8eight;

I just noticed your post saying that Green can be deleted. Since you will be setting this up, that is up to you. If you want to see the calculations on the Master sheet, put them there.

Hi six6to8eight;

I was kind of hoping that you would have gone a little further after my last message. What you have done works as far as it goes, but the data storage is incomplete. Just in case you're thinking that I'm talking about data entry, that's not it. Strictly a data storage issue.

I would recommend that you try and figure out what is missing on your own. Don't open the attached file until you have tried doing it on your own.Paste to Next Empy Row Ver 2.xls

Hi six6to8eight;

That's the idea. If you are going to maintain a database, you will need to design data structure. The more you figure out on your own, the easier it will be down the road.

I realize this is important in creating the data structure and why something like this is needed, but can't the Item name itself be used instead? This will be more user-friendly when it comes to adding entries.

The Item name can be used, but it can get tricky using it. Without knowing more about the business, I don't feel comfortable trying to anticipate problems. There is never a problem using a computer generated key. As you can see on the Transaction sheet, the Acct # and Item Key were used to pull the Name and Item Name into the sheet. The columns that actually hold the Acct # and Item Key could be hidden so that the user never sees them
When the user is entering data, they will enter the Item name (probably just clicking on a pull down list), and the program will find the Item Key to put into the correct place.
BTW, the Master sheets are usually never seen by the data entry person. They are only seen by the person maintaining the program to try and spot bugs.

Speaking of adding entries, how can I write the entries made on the transactions page back into the Items master? I assume there will be a button to save them back to the sheet?

You don't enter any thing on the Transaction sheet. It will be updated by a "Save" button on the Transaction data entry sheet.
Nothing is saved on the Acct Master, or Items Master sheets when entering transactions. They are maintained separately from entering transactions. Someone usually uses an Acct Maintenance sheet to add Acct #s to the list of Accts, and an Item Maintenance sheet to add Items to the list of Items. Then the Account and Items lists are used by the Transaction data entry sheet.
If you are going to let a "casual" user maintain the Acct Master and Item Master sheets, then they won't be seen either, just an Account Mainenance sheet. If the "programmer" is the one that maintains the Master sheets, then that person can use the Master sheets as the data entry sheet, but it's usually not a good idea. If you accidentally mess it up the problems cascade throughout the workbook.

I also see that you're using VLOOKUP to pull in the acct data from other pages. Is the VLOOKUP function also what will be used on the last tab to display the accounts via user listbox selection? If you could get me started on this last tab, that would also be very helpful.

In all likely hood I would imagine you will use VLOOKUP or something similar. As long as the 1st column in each data sheet is the key, then VLOOKUP is usually the most used option.

To start a data entry sheet (which I assume is what the "Acct Display" sheet is for). Start by designing it to look as much like whatever the source document looks like. The source document is the sheet of paper that the data entry person is looking at when they are entering data.

To set up a Listbox to select an Account_Name
1) Do you know how to create a Dynamic Range Name? If not here's a good start on understanding them http://www.cpearson.com/excel/excelF.htm#DynamicRanges
Define a Name on Acct Master
Name: "Account_Names" (or whatever makes the most sense to you)
Refers To: "=OFFSET('Acct Master'!$B$1,1,0,COUNTA('Acct Master'!$B:$B)-1,1)"
Make sure that Name does NOT include the sheet name. That way it can be used by a Listbox on any other sheet.
2) In the cell on Acct Display where you want the user to select an Account Name, (A) in the menu DATA>VALIDATION... (B) Allow = List (C) Source = "=Account_Names" (D) Uncheck "Ignore Blanks" (unless you want to allow blanks) (E) make sure "In-Cell Dropdown" is checked (F) Click "OK" (G) Name the cell "'Acct Display'!Account_Name" without the quotes, but with the [']. Make sure Name DOES include sheet name. That way the name won't clutter up the Define Name box when you're on a different sheet.

I will leave it to you to create the Dynamic Name for Items and the Listbox for "Select Item".

I'm attaching a file that has these things, so you can see how I did it. If you look at Define Names when you're on Acct Display, you will see "Account_Name" with the sheet name on the right, and "Account_Names" with nothing on the right. "Account_Name" is attached to the sheet, and "Account_Names" is attached to the workbook. Then go to Acct Master sheet, and look at Define Names, and you will see that "Accout_Name" is not there, because it isn't attached to Acct Master.
On Acct Display, you can move the cell named "Account_Name" to anywhere on the sheet and it will still work. And every macro can find it also.

When you have created the Items_List, upload the file so I can use it to add onto.

Whoops, forgot to attach the file.

Hi six6to8eight;

The one thing I didn't understand was your last step on naming the newly created listbox cell. I wasn't able to define a Name for it with the string you provided...

I'm also unsure of what you meant by including vs excluding the sheet name when defining new Names. Anyways, hope I did this step right...

A name can be attached to the workbook, or to a sheet. A name attached to the workbook can be found by any macro and any sheet easily. A name attached to a sheet requires the sheet name when trying to access it. The big difference is that a name attached to a sheet cannot be used as the source for a listbox on a different sheet (a name attached to the workbook can be used by a listbox on any sheet).
The same name can be used on every sheet and even the workbook. i.e. the name "myName" can be attached to every sheet and also to the workbook. So in a workbook with 4 sheets, there can be 5 myNames's with 5 different Refers To.
When defining a name, if you don't include the sheet name inside the name, then it gets attached to the workbook. The way to include the sheet name in the name is to enclose it in ['] with an [!]. So to define a name to the Item's List box:

Name : 'Acct Display'!ItemName
Refers To : "$E$9" (or 'Acct Display'!$E$9)

You can see which names are attached to a sheet, by INSERT>NAME>DEFINE and in the list you will see the sheet name on the right side of the list of previously defined names. If there is no sheet name there then it is attached to the workbook. Names that are attached to a different sheet than the active sheet will not be in the list at all.

So in your workbook. Select Items Master and INSERT>NAME>DEFINE. You will see "ItemName" in the list attached to the workbook. If you select ItemName and put your cursor inside the "Refers To:" box, you will be taken to Acct Display with the marching ants around $E$9.
But you won't see "Account_Name".

By attaching the names to sheets, it keeps the Name List uncluttered.

Somehow you move Account_Name from $C$9 to $E$9 so that both Account_Name and ItemName referred to same cell.

I'm leaving right now for eye surgery. I should be back in about 6 hours. I was hoping you would have started designing the appearance of Acct Display to match the source document that the Data Entry person will use.

How did the operation go? I've had family members go through a number of eye surgeries, so I can relate (I've also got pretty bad eyes myself). Really hope all is okay on your end...


I've set-up the Account View tab and have it successfully pulling in the account data from the Master sheet via listbox account selection. The formulas seem to work fine, as I've tested them with 50+ accounts, but I'd be interested to know what you think of my methods & formulas. After you got me started with the Define Name feature and VLOOKUP funciton, I went from there. Is the way I have it set-up now decent enough? Would it be inefficient/slow with very large databases? My data set will be relatively small, so I think I'd like to leave it the way it is, but I'd be very interested to know whether this is a good implementation for future reference/projects...

The one thing I have not been able to do is get the Item data to be pulled into the Account View sheet. I have both the Item Additions and Holdings Master sheets, but I'm unsure of how to use them together to accomplish this. The point of the Items Master is to allow the user to add an Item to multiple accounts at once in differing amounts. When adding an Item to multiple accounts at once, ALL general Item attributes (the 8 columns in blue visible on Account View and in yellow on Holdings Master) will be the same for every account, EXCEPT for the Amount and TE attributes. So I need to have a way to have a sheet that looks something like Item Additions that can allow me to add new Items to accounts AND attach these 2 numeric values (Amount and TE) to these accounts which I'm adding the Items to. Does this make sense? I was planning to write from Item Additions to Holdings Master and then have Account View reference Holdings Master. It would be great if the user could then also edit the items in individual accounts right from the Account View sheet. Does this seem doable?

I'm also wondering about how adding new accounts should be handled. I'm tempted to just let the person maintaining the spreadsheet simply add them by hand by doing Insert Row on the Accounts Master, but I'm guessing this is bad form for database management. If possible, I'd like to avoid needing to have a separate tab simply for adding accounts (which won't happen very often). Any ideas on this?

Finally, I'm hoping that the large merged Notes cell on the Account View sheet can be used to edit the Notes entry for the selected account back on the Accounts Master sheet. Right now, it displays the notes just fine when the user selects an account, but I'd like to be able to edit the Notes entry for that account right there from the Account View sheet. If possible, I'd like to do this without having to use a "Save" button and without having to use macros, but I'm guessing I will need to use at least one of these two things. Could you get me started on this as well?


Sorry to throw so much at you. I really appreciate your help -- I'm learning a ton about how this all works, and now I finally feel like I'm making real progress.

Hi six6to8eight;

How did the operation go?

I was mistaken. I assumed when the doctor said "laser my eye" that it would be an operation. All he did was apply a laser to the back of my eye to stimulate growth to repair the swelling on the back of my eye (not an actual operation). I go in for the other eye next week.

This is a long posting. You're asking about several things in one post, but I don't know enough about the company to be able to limit my answers. If you were to just deal with one thing at a time, then with each question I would learn more about the company and be able to answer each additional question with more specific recommendations. Also, I'm having to make educated guesses about where things are headed, and when they are wrong then all the answers after that are probably wrong also.

I gather that the user will enter data on the Item Additions sheet. Does that sheet look as much like the source document as possible. You want the user to be able to move their eye around the sheet of paper that they are reading from in the same order as the screen. So when they see something unusual on the paper, they can easily find it on the screen (because it will be in the same position).
Where do the TE and Amount data that gets entered come from? Are they on the source document? Are they calculated? If so where is the formula?
Is the data entered 1 item at a time? If so then you want to put a listbox in A4 to select the item, and you can have as many rows on the right side of the screen to select Names and enter Amounts as you want.
If more than one item is entered at a time, is there a maximum # of names that will be assigned an amount for each item?

will be the same for every account, EXCEPT for the Amount and TE attributes

So you want the sheet to be able to attach the TE to the account, either under the Account like you have Amount now, or each Account will be on it's own line and the Amount and TE will be on the same line.
For best results, you want to set up the screen with some dummy data that looks the way you want it to look when the user clicks "Save". Then you figure out how to code everything.
I feel fairly comfortable thinking that you don't want to list every account # across the screen like it is now (H3:I3). I feel fairly comfortable thinking that you will want to have Column H for selecting accounts (either by name or #), 1 row per account. So set it up that way then figure out how to code it.
On the other hand if ONLY 2 accounts will be assigned to an item, then put listboxes in H3:I3.


Re: the Account View sheet.
You want to make sure that the sheet is protected and that A7 and B4:E4 are locked. People will have a tendency to change the values, which will wipe out your formulas and accomplish nothing for them.

Re: A7:L13 (Notes). I don't have a good solution for this, but it's not a great idea to use merged cells. They can get in the way of some formulas and macros. Sometimes you have no choice, but if you can avoid it, you should.
In the menus FORMAT>CELLS>ALIGNMENT>HORIZONTAL towards the bottom of the list is "Center Across Selection". You would highlight A7:L7 and select that option. It then centers it in the range A7:L7 and doesn't merge the cells. It can't center across rows though. This probably won't work in this situation, but it might be worth exploring.

Re: The formulas in B17 & D17. You would be better off using Dynamic Defined Names instead of hardcoded addresses. =SUM(B21:B65536) would be better as =SUM(Amounts) . Whether you like it or not, you will want to change the appearance of the sheet in the future and having hardcoded addresses can make it hard. Exmple: Say you want to insert another column between Item Name & Amount. You can't insert an entire column because that would mess up A7:B13. But you also can't highlight B15:B25 and "Insert". your formula in B17 (now C17) would still read =SUM(B21:B65536). But if it uses a Dynamic Defined Name (that's defined correctly), it will still be accurate when you move B17 (you could also move it to a different row and the formula would still be correct).

I'm hoping that the large merged Notes cell on the Account View sheet can be used to edit the Notes entry for the selected account back on the Accounts Master sheet....I'd like to do this without having to use a "Save" button and without having to use macros

You're right you will have to use macros. You will have to use macros for everything that "Saves" data. You don't necessarily have to use a button. It can be set up so that when the user changes a particular cell that it automatically saves, but that's not a practical solution unless there is only 1 cell being saved. In this case, as long as you're only allowing the user to edit the "Notes" on this sheet, then that would work - as soon as they hit the enter key it would trigger a macro to save the "Notes" and nothing else.

I'm also wondering about how adding new accounts should be handled. I'm tempted to just let the person maintaining the spreadsheet simply add them by hand by doing Insert Row on the Accounts Master, but I'm guessing this is bad form for database management.

I don't know enough about the company to tell you this is a good or bad idea. The one thing that's important is that once an Account # is used anywhere else in the workbook, it CANNOT be changed on Accounts Master. If there is the possibility that the user might try to change it, then you would probably want a separate sheet to maintain accounts. BTW, Accounts Master can be hidden, so assuming that the reason you don't want the sheet count to go up is to make it easy for the user, I would be inclined to recommend a separate "Account Maintenance" sheet. From what I have read about your situation so far, I think that you will want 1 Account Maintenance sheet (which can be the Accounts Master sheet if you want), 1 Item Maintenance sheet (which I don't see in your workbook), 1 Transactions sheet (which I'm guessing is your Holdings Master sheet), 1 data entry sheet (I'm guessing that's what Item Additions sheet is for), and 1 Account View sheet (total 5 sheets visible, maybe 7 or 8 sheets total). Another thing you can hide all but 1 sheet if you want and include a menu that lets the user select which sheet they want visible. Or 2 sheets visible (data entry and Account View) and have menu selections for Account Maintenance and Item Maintenance.

The one thing I have not been able to do is get the Item data to be pulled into the Account View sheet.

When the user selects an Account, the macro would look down through some kind of Transactions sheet (the Holdings Master sheet ?) looking for all the account #s and copy them to the Account View sheet. It's might be possible to create formulas instead of macros, but they might be harder to understand than the macros, because they would need to skip over those Account #s not included on the Account View (which is easy for a macro to do).

If possible, I'd like to avoid needing to have a separate tab simply for adding accounts (which won't happen very often). Any ideas on this?

Hide the sheets and put menu selections in to show them.

I would like to have the option of completely locking the Account View sheet, such that people don't have to have to go in and add formulas, but the number of Items could conceivably become very large, and so I want to make sure I have those formulas in place going as far down as I need them...

If you try to have formulas do the searching for Account # on the Holdings Master sheet, then you have to copy the formulas down as far as might ever occur. But it's easy for a macro to add formulas into the sheet where they are needed. And delete old ones that are not needed any more.

Hopefully I have answer all your questions. I had a hard time sifting through everything you asked because everything relates to everything else. If I missed something just say so.

Hi six6to8eight;

Ok, here we go again.

I gather that the user will enter data on the Item Additions sheet. Does that sheet look as much like the source document as possible.

More or less. Given that the data is coming in from a couple different sources, this type of layout makes the most sense.

I'm working under the assumption that the "raw data" will be entered on the Item Addition screen. There will be maintenance screens for Accounts and Items, and the ability to update "Notes" on the Account View screen, but the "raw data" that comes from an outside source will be on this screen. If I'm wrong then correct me before I get too far off track.

I'm also working under the assumption that the data for 1 Item, along with some kind of breakdown into different Accounts, will be entered at a time. When the user is finished with the Item, (s)he will click "Save" and move on to the next Item.
I'm also assuming that TE is specific to the specific Item and Account combination (a different TE will be entered for Item1-Bob and Item1-Chris).

I guess in this setup I would have accounts running down the left-hand side, and Items running across the top from left to right, spaced 2 cells apart each, so that there would be room to add both Amount and TE to each account -- is this what you were referring to? I'll set it up that way and see if I can take a stab at the coding as well before I come back to you about this one...

On the Item Additions sheet you have "Date", "Y", "M", and "Total Amount". Assuming that they are specific to the Item selected, then this setup would not work. (see next answer)

With those Assumptions, I would put a ListBox in A4 to select an Item, and several rows of Listboxes in Column H to select different Accounts (1 per line). In B4:G4 would be data about the Item, and in Column I would be TE and Column J would be Amount (or vice versa) and additional columns for additional raw data that is specific to the Account selected in Column H. If data is needed from the Item Master sheet to help enter data in the sheet, they would be pulled in in the same way that Account # is pulled in on the Account View sheet.

So I assume then that I need a Dynamic Defined Name for each column under the yellow headings at the bottom of the Account View sheet? And I assume I would want to implement these as Local to the sheet rather than Global to the entire book?

Correct. The main reason you would need a global name is to provide a list for a Listbox on a different sheet than where the list is located (just a quirk of Listboxes). I can't imagine that you would ever want a Listbox to select the amount from the Account View sheet.

Could you help me with the macro code to allow automatic writing (on "Enter" keystroke) of the Notes cell from Account View back to the Account Master after it has been edited on the former?

To do this you cannot use a formula to display the Notes on Account View. You will have to copy the data from Accounts Master to Account View. And then save it back when it's changed. I just tried to do it, and ran into a problem with the merged cells. I'll have to work on finding the solution to this problem. In the meantime do this. This will give you an idea of what is involved.

1) On the Account View sheet - Name the cell that holds the Listbox to select an Account Name(A4) 'Account View'!Account_Name
2) Also name the cell that holds the Account Number 'Account View'!Account_Number.
3) Also name the range that is the Notes 'Account View'!Notes
4) copy the formula from A7 into some cell on the Account View sheet where it can be hidden (say AA1). Don't allow AA1 to be a merged cell. Name that cell 'Account View'!Original_Notes. (This step can be done in the macro, but this is faster and easier to debug).
5) In another cell that can be hidden (say AA2) enter this formula =Match(Account_Name,Account_Names,0). Name that cell 'Account View'!Account_Row
6) Name the cell that contains "Item Name" 'Account View'!Items

7) On the Account View sheet, Right Mouse Click the sheet tab. That will open the module for that sheet. Paste this into that window.

Please Login or Register  to view this content.

You can see that the Items from Holdings Master are pulled over to the Account View when the Account Name is selected.

In Step 5) you created a formula that found the row # of the Account Name, and you named it "Account_Row".
You can use that name in your other formulas on the sheet. In B4, you currently have =INDEX(Account_Numbers,MATCH(A4,Account_Names,0)). That formula can be =INDEX(Account_Numbers,Accounts_Row,0). Similar formulas could be entered into C4:E4 That way Excel only has to do the Lookup once rather than 5 times (twice in E4).

Thanks -- this code works beautifully. I'll work on getting it to pull in all the data.

I was actually in the process of trying to use some code to also add some formatting to the cells filled with Items. I wanted to add a light yellow background to them, but only as far as they were auto-filled (via the listbox selection). The following is the code I had in the Account View sheet module, which *sort* of worked, but it was returning a 1004 error every time I hit enter (saying the Range could not be defined for Object worksheet, I believe. It fills the cells w/ color correctly, but has trouble sometimes with clearing the formatting and will often return errors

NOTE: "Displayed_Holdings" is a Dynamic Defined Name which refers to all cells in column A from cell A21 and downwards which contain values (Item Names) on the Account View sheet. It didn't exist in the file that I sent you but I've since created it and it works fine to define the correct cells.

NOTE: I had this code pasted in the Account View sheet module with "Worksheet" and "Change" selected in the top dropdown boxes.

Here is the code:

Please Login or Register  to view this content.

I've three small requests pertaining to the code:
1) Could you help me debug this? I don't know what to look for in terms of red flags...
2) is there an easier & more efficient way to actually change the color of the cells besides setting ColorIndex for each individual cell, as I'm doing now?
3) How do I get these two macros (this one and the one you just posted) to "coexist" together? Can they both be pasted into the single code window for the Account View sheet module? Or do they need to each have their own separate window/module?

I'll work more on implementing the essentials before I worry too much about making the sheet look nice, but I'd like to use all possible opportunities to learn more about writing macros.

EDIT: I have successfully incorporated this into the code, and it seems to be working fine. My main issue now is the account number formatting problem (see next post)

I fear I may have spoken too soon... The code that lists the items on Account View doesn't work when I plug in my real accounts. I've figured out why:

I realize this is a major oversight on my part, but the actual account numbers I'm using are in the format "ABC-100001", "ABC-100002", "ABC-100003", etc. etc. The prefix before the number, "ABC-", is the same for EVERY account currently listed. There is a small possibility that new accounts added in the future would have the prefix "DE2-", such that they would be in the format "DE2-100001", "DE2-100002", and so on. There will NEVER, EVER (in a billion years, until the end of time) be any prefixes besides these two types, but ALL accounts will always have one OR the other (currently they only have the former, but it's possible that some added in the future will instead have the latter).

Sorry I didn't mention this sooner or build it into the template... I simply didn't foresee it being an issue.

Googling around, I can now see that the problem with this implementation of the code is (I think) the use of the Value function, which returns a number from a text input. Since the account "numbers" are technically not numbers, this function isn't going to work in this case. I'm currently looking for a solution, and I think I may be able to find one myself, but if you have any ideas that would be helpful as well. Right now I'm guessing we need to instead use some sort of string function, treating the "numbers" as text...

Hi six6to8eight;

You're using quite a bit. I'm relatively impressed with how much you're picking up.
I would suggest reading about Data Normalization. Here's a link to Google search http://www.google.com/search?hl=en&c...zation&spell=1. I think you have the general idea, but reading about it could help. Generally Data Normalization is discussed when dealing with Relative Database programs, but that is what you're building. Excel is primarily built around people using flat databases (tables) and it makes it easy to ignore the normalization rules, but if you get it down pat in your head, it will help you in the future.

The following is the code I had in the Account View sheet module, which *sort* of worked, but it was returning a 1004 error every time I hit enter (saying the Range could not be defined for Object worksheet

1) Could you help me debug this? I don't know what to look for in terms of red flags...

I would have to see the workbook in order to figure out why it was crashing.

2) is there an easier & more efficient way to actually change the color of the cells besides setting ColorIndex for each individual cell, as I'm doing now?

Changing the cell color when certain things happen is for conditional formatting FORMAT>CONDITIONAL FORMATTING.... That's where you tell the cell to change it's color if the value <> "". It automatically changes back to whatever you have it set at when the value = "". If you do a search in the Forum, you will find a lot of threads from people asking how they can get conditional formatting to do what they want. Probably the biggest complaint people have is that in Excel 2003 they can only put in 3 conditions. That changes in Excel 2007 (or before). Example:
Condition 1) If value<>"" change color to yellow
Condition 2) If $A$3=today() change color to purple and put a thick border around it (only executes if condition 1 failed)
Condition 3) If Account_Name = "Bob" set font size=44 (only executes if 1 & 2 failed)

3) How do I get these two macros (this one and the one you just posted) to "coexist" together? Can they both be pasted into the single code window for the Account View sheet module? Or do they need to each have their own separate window/module?

There can only be 1 Worksheet_Change() per sheet. If you have 2 sets of code that you want to execute when something changes in the sheet (either manually or by a macro - formulas that change values do not trigger the Worksheet_Change event), you just put both sets of code into the same sub().
Generally inside Worksheet_Change you will test Target to see if the cell(s) that changed are cell(s) you're concerned about. You probably don't care if AA1 is changed, so you don't want the macro wasting time by changing the cell colors again. You only want the macro to change the cell colors if the user has selected another Account Name. (Cell colors is just an example - as in the previous answer cell colors are usually best handled by Conditional Formatting).

The different events that the worksheet can handle are all listed in the right box at the the top of the module. It just depends on when you want something to happen. If you want something to happen every time the user changes selection, then use SelectionChange(). If you want it to happen when the user first selects a sheet, then use Activate() or Deactivate(), etc.
I use Worksheet_Activate to set up menus and other properties for that specific sheet (some sheets are protected, others aren't), and Worksheet_Deactivate to remove the menus for that sheet.

I can now see that the problem with this implementation of the code is (I think) the use of the Value function, which returns a number from a text input

It depends on where you're using Value. If you use it in a formula on a sheet then yes it returns a number or error. But most objects (like Rng in your code) have a Value property that just returns whatever is in the object. It can be text, number (several different types of numbers), date, etc.


About the Account Numbers.
This has to do with Data Normalization.
If it's Excel that will decide what prefix to use, then there may not be any point to putting the prefix in it's own cell on the sheets. But if the user decides which one to use, then you will probably want it in it's own cell. That's not an absolute, you could have the user type the prefix into Account Number and then Excel calculates what the next one in order is and replaces what the user typed in with the actual Account Number. Or the user can create the Account Number with no pattern at all. The only requirement is that once the Account Number is used anywhere else, IT CANNOT BE CHANGED on the Accounts Master sheet. It is immaterial how the Account Number is generated. It can be random or in order. Generally most programmers will let Excel pick the new Account Number, but that's just convenience, not a rule.

If ABC & DE2 have other info that is unique to them (like a dept, or whether it's foreign or domestic, or anything else), then you will want a separate sheet for them.
Example:
Say ABC and DE2 are separate depts in the company. Then you might want a column that says who the department head is, and a column that says what room they occupy in the building, etc. So you want a separate sheet for them (even though there can only be 2). That way you can have 100 sheets that list the department head and room #. And when the company hires a new head for ABC and (s)he moves the department to another room, you only have to change it on one sheet and all 100 sheets get changed automatically.
If there is nothing unique to ABC or DE2, and there are only 2 choices, then just put them in a list somewhere (on a hidden sheet maybe, and use a dynamic name to define the 2 item list so that if a 3rd one is ever added the dynamic name picks it up automatically), and use ListBoxes to prevent the user from mistyping the choice.

As a general rule, I would recommend putting the prefix in it's own cell on the Accounts Master sheet. And then maybe another cell with a formula that concatenates the prefix & the number together. But without knowing what the prefix means to the company I can't say for sure.


I did forget a few things in the last post about your workbook.
You have global names for Fe, Fu, I, and Notes.
I don't think there is any reason to name those cells. It can make the formulas on Account View a little less complex, but for the most part, they just clutter up your Defined Name list. And definitely don't make them a global name. If you want them, attach them to the sheet. Probably 99% of the time, the only reason you need a global name is so that you can use it in Listboxes on different sheets. I can't see you ever using the Fe column from the Accounts Master in a Listbox.

What I do is create a hidden row (say row 2) above the headings on Accounts Master.
C2 : =Column()-Column($A$2)+1 and name the cell I_Col
D2 : =Column()-Column($A$2)+1 and name the cell Fu_Col
In these formulas $A$2 represents the first column in the table. If you ever insert a column before column A that is not in the table, you would want the formulas to be using $B$2 which would happen automatically in this example.
Then on Account View, I would use "I_Col" in C4 formula and Fu_Col in D4 formula. Then if you ever insert a column between I & Fu on Accounts Master, it's already accounted for on Account View. Right now, if you insert a column between I & Fu the formula in 'Account View'!D4 will have the wrong result.

Hopefully you're getting the idea that you don't want to use hardcoded addresses and constants in your formulas and macros. You want to use Defined Names as much as possible, so that as you upgrade the workbook, you don't need to worry about all the references in formulas and macros.

One last note: On Account View, the Notes should only be 1 row, not 7. The notes only take 1 cell. If you need to use 7 rows to display a long note, use word wrap FORMAT>CELLS...>ALIGNMENT Wrap Text. You will probably want it to still be 12 columns, so you would still merge A7:L7, but not the rows.
Hopefully I'll get around to figuring out how to put a value into a merged cell today.

Hi six6to8eight;

Now I just need to be able to control the Number Formatting (date vs value, adding commas/controling how many decimals in numbers, etc. etc.), which, unfortunately, is not something I see in the Conditional Formatting options. I imagine there must be a way to do this in VBA, so I'll keep hunting. I think I'll get it sooner or later.

Yes this is in conditional formatting. If you can format a cell manually the way you want, then the conditional formatting can do it also.

Very useful to know that there can only be one Worksheet_Change() per sheet. I had assumed that the right box at the top of the module controlled event type, but wasn't really sure. Thanks for explaining the VBA editor side of things a bit.

Selecting something in the right box, creates the Sub for you. It creates it with the EXACT wording that the sheets look for. You just have to fill in the code that you want it to do.

What you mention about Value is strange... If it is indeed just returning my alphanumeric account "numbers" as objects, then why wouldn't it be able to match them correctly? I assumed that it was trying to return them as strictly numeric values, which obviously wouldn't work. I guess I just don't fully understand what's going on behind the scenes when the code is run...

I'm lost here. I don't understand how and where you are using Value. It does work different in formulas than it does in macros. But I'm having trouble figuring out why you're having trouble with it.

This is going to sound stubborn, but if possible I'd **reeealllly** like to avoid having to use a separate prefix/column/variable for the account number prefixes.

You are the only one who can decide what is best for your users. There is always a trade off between usability and ease of construction. I have never found a situation where I was sorry that I didn't break the data down into as many different pieces as possible. I have found situations where I was sorry I didn't.
Example:
In my first major program, I just had a name field (like you do). After 1 year, the owner of the company wanted to start sending letters to his clients and he wanted the letters to be addressed "Dear Mary". Well I had one hellofa time trying to extract the first name from the name field. Some had middle initials. Some last names were Mac Donald with a space. Some had Jr. in them. That really taught me my lesson. Now I always break names into at least 2 fields.

In your situation, the user doesn't have to know that it's broken into 2 fields, especially since 1 field is letters and 1 field is numeric. The user can enter "ABC134" into a field, and then the Worksheet_Change kicks in and breaks it down into "ABC" and "134" into the cells that (s)he never sees.

But since you say that ABC and DE2 have no meaning, it sounds like it's strictly whether you want to do the work involved in keeping it in 2 fields.
If you say that Account Numbers are assigned by some external source, then there may be no reason at all to break them apart. Most data keys are immaterial to the data. They are just a means to keep the data organized in a way that the program can easily find data (Data Normalization). Most programmers set up a means of generating a new key when they click a "New Account" button, and they just fill in the Name, Address, Dept, etc. and never even see the key or know what it is. If you have to type in the Account Number because it's given to you, then there's probably no reason at all to break it down into 2 fields.

I can say this. If your key has meaning to the user, then it's usually a bad idea to have that be the key. It is usually a better idea to have it be just another piece of information about the Account (or Item or whatever is being tracked).
I haven't heard anything from you to indicate you would have a problem using the Account Number as the key, but from experience I can say that I would initially recommend not using it as the Account key. I would use a behind the scenes key that is straight numeric and the Account Number is just another piece of information. As you saw on the Account View sheet, the user selects a name and the program puts up the Account Number, Fe, Fu, etc. In the Holdings Master sheet, it doesn't really matter whether the Account key is the Account Number or a computer generated key. As long as the program can find the right Account from what it stored there, it doesn't matter. And what you display on the screen is immaterial also. If you don't want the key displayed, don't show it.

it's just that I'm aiming for MAXIMUM simplicity for the end user

You're confusing maximum simplicity for the user and ease of building the program. Most of the time the programs that are extremely simple for the user are extremely complex behind the scene. One of my strong points is that I know how to make a program extremely user friendly. When my programs need to be upgraded, I have no problem upgrading them, because I made sure that the data was stored properly (Data Normalization again).

I tried doing this exactly as you describe, but it doesn't work. Even with "Wrap Text" selected under cell formatting, I can't get it to wrap down as it does now.

I have a hunch that you're not formatting right. I use word wrap all the time and selection centering, and have no problem. I'm uploading a file that shows the Notes the way I believe you should show them. Although I reserve the right to change my mind later on, if I can figure out a way to show a large Notes in just one cell without centering it.

six6to8eight;

I thought I would point out that in my biggest project (a company estimated that they would charge between $50,000 and $100,000 to replace my program), I used keys that had meaning to the company and I had no problem doing it. The keys on 2 separate "files" were very much like your ABC. I had to string 2 of them together with a 3rd numeric # to identify the "item" that the company was selling. If I had it to do now, I would probably do it again, but I'm not positive. There was no extra info that was attached to my "ABC" so if I had attached it to a random key the file would have just had 2 columns the computer key and the company "key". But I do remember being hampered by the fact that the key meant something to the company (but not overly so).
But the company "Account Number" was made up of 2 "keys" and a computer generated #.

Please Login or Register  to view this content.

You're right I can't tell what's wrong without seeing more. I could guess if it was crashing, but if it's just giving the wrong results, then I would have to see more.


I have completely forgotten about trying to paste a value into a merged cell. I'm going to wait until you feel confident that you have the display set up the way you want it.

Fox -- Here's the file I'm working off of now.

The two big features I'm still trying to implement:

1) Get the Notes merged cell to write back to the Accounts Master sheet. (Now you can see why I can't unmerge the cell with this new setup...)

2) 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), or maybe put delete buttons next to each one (this might be dangerous because it allows for errors)? Maybe you have a better idea of how to implement this? 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").
EDIT: 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. Then maybe the macro could delete an entire row (Item) at a time, to ensure that the unique ID# gets erased and is not used again. Is there a way to automatically add the next unused ID# to new Items when they're added (manually by hand) to the Holdings Master?




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. Take a look at the for loop in the macro for that sheet, and notice every time that I use the range "Total" in an arithmetic calculation (3 times, I think). "Total", as you can see on the sheet, simply refers to cell A15, which totals the entire amount of all Items in the account. The problem is that it does this by simply summing up the totals from column B as they're added to the sheet by the macro. Thus, when "Total" is referenced within the macro, it's not actually the total, as not all numbers have been added and accounted for at that point (within the for loop). Thus, the results of these calculations are wrong. Does this make sense? If you take a look at it, I think you'l see what I mean. This is the biggest issue with the current layout at the moment, and I can't for the life of me think of how to restructure the macro to deal with this.
EDIT: I've resolved this issue by simply moving those formulas into a second for loop which is run after the first one. This way, "Total" is actually the correct total when the formulas are calculated. It's working fine like this, but it ends up taking almost twice as long to fill the sheet, since it needs to run through and perform two separate string searches on the Holdings Master. If there's a way to avoid having to do this, that would be great. If not, this will be fine.

And one last thing:

EDIT: (This one is not a priority and I can probably get by without it): -I'd love to also make the Item display on Account View "error-proof." What I mean by this is that I'd like to be able to put anything (numbers or text or both) into ANY cell on the Items master, and have the Items display properly on the Account View sheet. As it is now, If I put a text string into a cell that I'm using in an arithmetic formula (in the macro), there is (obviously) an error in the computation of the formula, and the Items stop being listed on Account View at the point where the error occurs. I imagine it's easy to fix this by simply having the formulas check to see if the cells they're dealing with actually do have numbers in them, but I'm unsure of how to implement this. These are the arithmetic formulas in the code that I'm talking about (that I'd like to make "error-proof"):

Please Login or Register  to view this content.

Hi six6to8eight;

Put EnableEvents = False sooner in Account View!Worksheet_Change.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Forgot to answer this one.

Is there a way to automatically add the next unused ID# to new Items when they're added (manually by hand) to the Holdings Master?

If you delete a Holdings_Key, it can be reused. The only requirement is that it is unique.

Put this formula into a hidden cell on the sheet and name it "Last_Key".

Please Login or Register  to view this content.

Thanks, this is great! A lot of really good tips and solutions in your last couple posts.

Updates:

1) I moved EnableEvents=False farther up in the code (out of the for loops) as you instructed, and removed duplicate instances of it. Could you explain how this benefits the macro?

2) I'm not sure what you meant by this:

Range(shView.Cells(lItemRow, "A"), .Cells(.Rows.Count, .Columns.Count)).Clear
This line triggers Worksheet_Change again right in the middle of filling in the sheet.

Where do I put this line? I don't understand what you mean by "triggers it again." I'm afraid I don't understand what the advantage to doing this is...

3) I set screenUpdating to false and it has made a HUGE difference. Thanks!

4) I'd like to keep the formatting hard-coded in the macro, since there's no way (as far as I can tell) to use conditional formatting to achieve what I'm trying to do. I can't just manually format the rows, because I've now got it set up to flag expired Items as red on Account View. Thus, I need to be able to clear and refill the cells every time a new account is selected. Conditional formatting doesn't really seem to cut it here. But I'll definitely try to clean it up. Could you show me how to apply the formatting to an entire row at a time? I've been playing with Range and Offset but can't seem to get it...

5)

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 think I'd like to have a "delete" button automatically created (and the old ones cleared) next to each Item entry on Account View every time a new account is selected. An "Are you sure?" prompt would be perfect, and fast enough for my user's needs. Unfortunately, I don't even have the faintest idea of how to get this code started... As for the unique Item ID#, each account will only have one instance of an Item in it at a time, so it should be fine to just cross-reference the deletion search on Holdings Master with the Item name and account number. The names of Items are text strings which aren't arbitrary, and which can't be changed, but they're also not the type of thing that you'd select from a listbox dropdown. I think the delete button method will work pretty well in this instance. I'd love to get my hands dirty with the code, but definitely need help in getting it started.

6) I have succesfully fleshed out & implemented the Worksheet_Open code that you gave me; the macro is now able to override the sheet protection. Thanks again!

7)

While the Copy - Paste method might not work for merged cells, this does:
.Range("Displayed_Notes") = .Range("Original_Notes")

I'm now successfully using this to display Original_Notes in the merged Displayed_Notes cell (without vlookup/formulas, as I was before), but I can't seem to get the merged Displayed_Notes cell to paste back into the notes field on the Accounts Master sheet.. Would this be done by hitting the Enter key? I've been fiddling with the code but can't seem to get it... I will keep trying, but I've been at it for the better part of an hour and have a feeling I'm not gonna get it... EDIT: New problem -- if I have notes that are too long, it ends up not being able to set these ranges equal for some reason, and crashes the sheet (Items do not change...).

Worksheet_Change is triggered whenever a cell on the sheet is changed, either by the user or a macro (a formula that changes values doesn't trigger it).
The line "Range(shView.Cells(lItemRow, "A"), .Cells(.Rows.Count, .Columns.Count)).Clear" clears the sheet of all the old items. It's already in the macro before your loop. It clears the old items by changing all the values to "" (which triggers the Worksheet_Change).
The code below puts you in an endless loop.

Please Login or Register  to view this content.

The code below will trigger it only once

Please Login or Register  to view this content.

The code below prevents the trigger

Please Login or Register  to view this content.

I'd like to keep the formatting hard-coded in the macro, since there's no way (as far as I can tell) to use conditional formatting to achieve what I'm trying to do.

I didn't see anything in you code that couldn't be handled by conditional formatting. If you can format a cell manually (or in code) the way you want, then conditional formatting can handle it.
There is only 1 limitation on conditional formatting. There are only 3 tests. So if you want the cell to have 5 different formats under different conditions then you would have to do it in code.

I can't just manually format the rows, because I've now got it set up to flag expired Items as red

So that would be condition 1 in conditional formatting.

Could you show me how to apply the formatting to an entire row at a time?

Please Login or Register  to view this content.

If the words "Item 1" can be change on the Account View sheet, then you want to have a Holding_Key on Holdings Master. If the user changes "Item 1" to "My Item" then tries to delete it, the macro won't find the correct row to delete.

Could you show me how to apply the formatting to an entire row at a time?

You will probably have do do something like this:

Please Login or Register  to view this content.

The same way you did it to get the value into Display_Notes in the 1st place.

Creating and Deleting buttons in a macro is not for amateurs. I would have a tough time trying to figure out how to tell the button what Item to delete in your worksheet. If you want to try working with them, I suggest recoding a macro. I think it would even be harder trying to create and delete checkboxes. I would just manually create 100 buttons or checkboxes then just hide the rows that don't have an item in them.

I suggested showing and hiding rows earlier.

but when it comes to number formatting, I don't see that in conditional formatting.

Well I'll be a ..... In all the years that I have used conditional formatting, I never noticed that I couldn't set the number format. As they say, you learn something every day.
Well any way, you shouldn't be changing the number format in a macro unless it actually changes. If you just set the number format for the whole column (right mouse click on the letter at the top of the column and select "Format Cells....", then you won't need to change it in the macro.

The Item names *can* be changed, meaning that they're actually user-created descriptions of what they represent and are thus somewhat arbitrary, although they NEVER *will* be changed after they've been created (never ever). So I think it can be done without an item key.

This would fall under the category of bullet proofing your app. There's an old saying that you can't make anything fool proof, because fools are so damn clever. Believe me, if it CAN happen, it will happen eventually. It may be that the user realizes that the Item was miss-spelled, so (s)he innocently just changes the spelling, and your app crashes.
If the user CAN change the "Item" in the Account View (either by retyping the word(s) or selecting a different item, then I would advise you to create a Holdings Key, even it it will never happen. The 2nd less perfect option would be to delete all items in the Holdings Master when the user clicks "Save" and then add them again from the Account View. The 3nd option (one that I would never depend on) would be to lock the cells, so that they can't be changed.

Having the buttons be dynamically created and erased would be the cleanest solution

I have rethought my objections. When you first mentioned using a macro, that the user would trigger, to create buttons, I was thinking of all the problems I have seen in other people's button code. My gut tells me that you will have more problems than you want to deal with, but I don't know enough about your app to say it's a bad idea.
Here are the macros I use to manually create buttons. I will leave it to you to figure out how to adapt them to your macro. Hopefully in the process you will discover what I consider to be the biggest headache with buttons, so you won't do the same thing. I will also leave it to you to figure out how to delete a button. I look forward to seeing your findings.

Please Login or Register  to view this content.

Okay, I'll give it a shot with the buttons. I'm coming up on my deadline though (next few days), and so I'm worried that I won't get this working.
EDIT: I am unable to get this code to work. I pasted these two subs into a new general module and am able to create the buttons, but I don't see how I'm supposed to connect them to a macro. I can't get them to do anything when I click on them....

Also, can you tell me how I go about creating an item key that will automatically be created in holdings master as a user adds items to that sheet?

If the button was created using my "Create_Button" macro, then it already has a macro assigned to it. If you right mouse click on the button (you may have to unselect it first), you should see an option to "Assign Macro". You can click "Edit" right there to go to the macro that is assigned.
When you right mouse click the button, you can "Edit Text" also.

This is one way
Assuming that you want the Key put in column A:

Please Login or Register  to view this content.

1) The code you gave me (ActiveCell.Create_Button) wasn't working (see my attached file, which has your code in it, commented out), so I tried using this instead:

Please Login or Register  to view this content.

(NOTE: .Offset(0, 12) refers to cells in column M, where the buttons are to be made)

This code I'm using creates the buttons just fine, and when I click on them they do try to run the macro, but something strange happens. First, I get a prompt asking if I want to enable macros (even though I already did on file open). Then I get a 'File in Use' dialog box that says the file has already been opened by myself, and will ask me if I want to open the file again as a read-only. Weird, almost like it's going into a recursive loop or something? I think maybe the last line of code needs to include the .xla reference? I don't know much about the syntax for this... If possible, could you either edit file I'm attaching, which contains your code, to get it to work, or maybe tweak mine to get it to work? Mine is close to working, I think, since it does create the buttons and attempt to call a macro when they're clicked.



2) Also, to remove the buttons, I know I need to somehow select all Shapes in column M (where the buttons are being created) and Delete all, but I can't figure out how to do this. I realize it would go right after the following code:

Please Login or Register  to view this content.

I just don't know the proper syntax for finding and deleting them. I tried deleting all shapes on the sheet (first 2 lines of code below), but this deleted my account selection listbox as well. I'm not sure how to narrow the deletion area down to just column M (where the buttons will be). Below are three different code approaches that I tried to delete the buttons with (none of them worked, but you can see them all in the file I'm attaching, commented out, in the "1st clear old items" section):

Please Login or Register  to view this content.

3) Finally, using the following code works fine for displaying most notes, but not all:

Please Login or Register  to view this content.

If a notes cell is long (to the point that it almost fills the entire merged cell), it actually won't even set the two ranges equal. The macro crashes, and Account View does not get populated with the account's items. This is very strange behavior... Can you think of any reason why this would work, but only for notes cells containing strings that are short enough? See my attached file, and try to display Bob's notes (doesn't work). If you shorten his notes on the Master though, it does work. Very peculiar... Any ideas why it's behaving like this?

six6to8eight;

The reason my Create_Button won't work for you is because it's how I MANUALLY create a button. I haven't had any reason to dynamically add buttons. My Create_Button creates a button on the active cell. To have a macro call it, you need to figure out how to pass it the location where you want the button to be created.

You should not "Select" or "Activate" inside macros. They just waste time.

Please Login or Register  to view this content.

You don't need the .xla reference unless you're trying to run a macro in another workbook.

I can't tell you why you get such weird things when you click the buttons. They work fine on my computer. I would suggest putting a break (or Stop) in the Button_Click macro and see what it's doing.

I don't know of any way to select all buttons at once that are located in column M. You will just have to cycle through them looking for the ones that have a TopLeftCell in column M

Please Login or Register  to view this content.

BTW, you're not creating OLE objects, you're just creating form buttons.

I recently ran into a problem with vlookup. It wouldn't lookup a value that was longer than 256 characters. I'm guessing that you have a similar problem. You may have to eliminate
.Range("Original_Notes"). It was just an intermediate step to getting the Notes into the sheet. After seeing more of your app, I now don't think it's good idea anyway. It's just quirk of mine. If I can do something with formulas, I tend to do it that way, but it's the only thing on the sheet that's being copied that way, so it might get confusing down the road.

Please Login or Register  to view this content.

It's also possible that there is a limit of 1024 for string lengths. Unfortunately, I'm on a deadline right now, so I can't research it for you.

I found the following page related to our length problem:

http://en.allexperts.com/q/Excel-105...ll-trouble.htm

It looks like 1024 characters is the maximum that can be displayed in an Excel 2003 cell. In my case, the notes cell in question has 1083. Do you think that might be why it's not able to set the two ranges equal to one another? Sounds like it. I suppose there's no workaround for this...

EDIT: This problem has been resolved. I fixed it by changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

this is also a workable solution:

Please Login or Register  to view this content.

six6to8eight;

Take a look at Worksheet_Change(Target as Range). Excel passed "Target" to the sub().

You would

Please Login or Register  to view this content.

Some old timers would:

Please Login or Register  to view this content.

but "Call" is no longer needed.

And you will have to edit Create_Button to accept the passed range. Lookup "Calling Sub and Function Procedures" in help.

Off the top of my head, the only thing I can think of to associate a button with a row, would be to name it with the row # in the name.

In Create_Button

Please Login or Register  to view this content.

Then in Button_Click

Please Login or Register  to view this content.

The name of the button is never seen anywhere except in the Name Box when you select it (not when you click it).

That might actually be a better choice for deleting the buttons also. Instead of checking the .TopLeftCell.Column, you would check for Left(.Name, 4) = "Row "

Originally Posted by foxguy

Off the top of my head, the only thing I can think of to associate a button with a row, would be to name it with the row # in the name.

In Create_Button

Please Login or Register  to view this content.

Then in Button_Click

Please Login or Register  to view this content.

The name of the button is never seen anywhere except in the Name Box when you select it (not when you click it).

That might actually be a better choice for deleting the buttons also. Instead of checking the .TopLeftCell.Column, you would check for Left(.Name, 4) = "Row "

Okay -- I've made more progress with this code. Now it's able to add and delete buttons, but it only deletes one button at a time (despite the fact that there's a For loop in your code). Thus, if there's one button on the screen, changing accounts erases it correctly and pulls up the new account, but if there are multiple buttons, it only erases the topmost one, and does not display the new account holdings. Trying to bring up subsequent accounts deletes one additional button for each account selected, but the holdings are not displayed... So, basically, it seems like it's not cycling through the for loop. Strange behavior... Do you think the problem is related to the fact that we're using ActiveSheet.Buttons? Can you recreate this issue in your version of the file?

It sounds like you have an On Error Goto <some label> and that label drops you out of the macro without notice. I looked at Ver 9 and it has an "On Error Goto EF" at the top. Comment out that line, then when the error occurs it will stop on the line causing the error.

Brilliant idea. Here's what I get:

Please Login or Register  to view this content.

And then if I hit debug it takes me to the problematic line of code, which is of course:

Please Login or Register  to view this content.

...which resides in this block of code:

Please Login or Register  to view this content.

Strange, since it obviously IS working, as it's able to successfully find and delete the first button in the column based on its Name... Still seems to me that it's not looping correctly, since it removes the first one but then leaves the rest... Perhaps it's not correctly moving from one oButton to the next? Or not correctly redefining oButton as a different object each time through?

Sometimes shapes will behave oddly. Sometimes Excel will recognize Shapes("Button_Name"), but it doesn't recognize Buttons("Button_Name"). If you look in my macro Button_Click, you'll see that I do an On Error Resume Next before actually trying to get the name, then immediately set the On Error back to what it was, then test to see if it found a valid button. That way when it hits a shape that it doesn't like (that somehow got included in with Buttons), it just skips right over it.

Also, some shapes don't have Name properties, so when you try to extract the name it kicks out an error.

I think Dim stands for Dimension. I wouldn't be a lot on it, but that's what's in the back of my mind.

I'm still running into the same problem, which is that Button_Click (called by .OnAction = "Button_Click") oddly attempts to reopen the file. Whenever I click one of the created buttons, Excel asks me if I want to open a read-only copy of the same file (since I already have it open). There is no evidence that any additional code is run...

Here are the three subs, all in the ButtonMacros module:

Please Login or Register  to view this content.

I've tried doing lots of different things with this code, but it all yields the same result: an attempt to reopen the file when a button is clicked. Any thoughts?



EDIT: one other thing I just thought of: I changed the file's name, but this was back before I'd even attempted to implement any of the button code or anything at all to do with buttons. The file it attempts to open is the same file (the new one with the new name), regardless... Just thought I'd mention this in case there was any chance it could be causing the issue.

EDIT 2: After clicking the button, when the macro asks me if I want to reopen the file, if I click "Enable Macros" and "Read Only," Excel opens a duplicate read-only copy and displays the following error message:

The macro "C:\FILEPATH\FILENAME.xls'!Button_Click' cannot be found.

So it can't find the macro... Strange. Almost looks like there are some quotes missing from that path, too (that is exactly how the error message appears). What do you think?

Use parentheses when calling a function that returns a value. Don't use parentheses when calling a sub.

Please Login or Register  to view this content.

Get rid of the line below. I'm not sure what it's supposed to do, but I would bet that this is the line that is causing your problem. What are you trying to do with it?

Please Login or Register  to view this content.

Okay, thanks. I removed those parentheses from the sub.

The line shView.Worksheet_Change was intended to refresh the sheet so that the deleted item would not linger on Account View after the user has deleted it. I realized that you can't call Worksheet_Change in that way, so I fooled around with changing arbitrary cells on the sheet in order to get it to refresh. I wasn't able to get this to work, but I'm sure there's an easier solution I'm not thinking of.

Triggering Worksheet_Change, however, is a secondary goal. I still cannot get the buttons to run Button_Click, as it's unable to find it (even though the sub is right there in the same module. Here's what the code looks like now:

Please Login or Register  to view this content.

Regardless of whether I include the Select Case lines to create a prompt dialog box, the macro still tries to reopen the file and then -- if the user allows it to reopen the file as a read-only -- tells the user that it cannot find the macro (error message in my last post). This is driving me absolutely nuts -- I've been working on it and googling it and tweaking the code in every way I can think of almost all day. I'd be incredibly grateful for any insight whenever you get the chance.

EDIT: Delete_Holding does work, and I actually got the buttons (with all three macros) to work at one point in the past, but then I changed something and now I don't remember how to get back to that point. My point is just that I believe all three macros are working okay, there's just something wrong with the way they're interacting in the context of my file. Can you see anything in the code that looks wrong?
EDIT: ..although I just tried doing this again (cutting out most of the data) and it did nothing to help...

or

Please Login or Register  to view this content.

To complete it:

Please Login or Register  to view this content.

PS. This is the only code you need, instead of 2 macro's (button_Click and delete_holding)

I can't see anything obviously wrong in you macros. I did find 5 issues.

1) Important Put the line "On Error GoTo EF" back into Delete_Holding.
If something goes wrong in the macro, you want to make sure that "Application.EnableEvents" gets turned back on. You will go crazy trying to figure out why Worksheet_Change never triggers (because EnableEvents was left turned off).

2) Put the "Select Case msgbox" in Delete_Holding, not in Button_Click.
You can use the same macro "Button_Click" for ALL buttons anywhere in your workbook. It looks at the caption and/or name (you can even have it look at other button properties, like TopLeftRow), to decide what macro to run. Right now you have it asking if the user wants to "Delete", which obviously is not something you want to happen when the user clicks a "Save" button.
Also, logically it makes more sense. The question has nothing to do with what button was clicked which is what that macro was designed for. But it has everything to do with whether a row gets deleted. When someone else (like me) is looking through your macros, they won't expect to see a question about deleting a row in a macro named "Button_Click", but they would expect to find it in a macro named "Delete_Holding", and when they didn't find it, they would be inclined to put it in (which would then have it asking the question twice).

3) Change vbDefaultButton1 to vbDefaultButton2.
Sometimes users will accidentally hit the enter key when they don't want to, you don't want the default action to be something they can't reverse.

4) Put in "Exit For"

Please Login or Register  to view this content.

5) I would use "If vbYes = MsgBox(....) Then" instead of "Select Case MsgBox(....)"
When anyone is reading your code, "Select Case" forces them to read through all your options (I had to look to see what heppens when "MsgBox = vbNo"), but "If vbYes =" makes a little more sense since something happens if it's true. Also, since there is only one case that gets triggered, "Select Case" is overkill. "If ... Then" is a better choice when there is only one "case".
I admit that this is a personal preference reason. VBA won't let you edit "Select Case" commands while the code is running. But it will let you edit "If ... Then" commands. Most programmers spend more time debugging than writing and any time it can be made easier to debug, I'm for it.


Without seeing the actual workbook, I can't track it down. Here's what you can do (you might as well get used to it). Put a "Stop" as the first line in "Button_Click". When it stops, you can use "F8" to step through the code one line at a time to see what is happening. While you're stepping through the code one line at a time, you can use the immediate window (Ctrl+G to make it visible) to see what is happening. For example, in the immediate window you can type "? <range name>.Address" (? - means Print) or "? <variable name>.Value", etc.

There should be no problem integrating the 2 solutions.

snb is showing you a "quicker" way to delete the appropriate row. You can use that method to "find and delete" the correct row quicker.
(S)he has also combined the 2 macros into 1. If you never create any other buttons, then that's fine. I personally like to have 1 macro that is assigned to ALL buttons. That way if I want to have something happen for ALL buttons, I only have one macro to mofidy. For example, I might put in Application.ScreenUpdating = False in Button_Click, and then I wouldn't need to put it into every macro that different buttons trigger.

I personally don't like using xlCellTypeVisible to delete data in a macro. I would only use it when the user has a chance to view what is about to be deleted. If you look around, you will find many stories about files getting corrupted (I have 2 stories myself - one that I'm still using despite the corruption). As long as your file never gets corrupted xlCellTypeVisible to delete data will probably work fine. I would make a recommendation if you want to use xlCellTypeVisible. Instead of deleting the row, copy it to a hidden sheet. That way if anything ever goes wrong, you have a way to restore the "deleted" data.

snb,

After stepping through with F8, I can see that the Mid function is successfully pulling the numbers, in the format: "Key ###"

Still getting a 1004 application-defined/object-defined error at the line:

.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

If I hover over xlCellTypeVisible it gives me: xlCellTypeVisible = 12

I'm totally lost... Ideas? I feel like I'm very close to having this working but today is my deadline! Would be incredibly grateful for insight. Many thanks.

Put this code in your macro in front of the ".Delete" line and when it gets to this line

Please Login or Register  to view this content.

see what prints in the immediate window (Ctrl+G to make it visible).
If there are no visible cells, that might be what is crashing.
As I said earlier, I don't use xlCellTypeVisible to delete data, so I don't know if it would crash with no visible cells.

My computer isn't working right this morning. I can't see any code in any workbooks, so I'm guessing here.

I have a hunch that you don't want the ".Offset(1)" in the command. I don't know what it is being offset from, but I'm betting that there are no visible cells in the range described with .Offset(1).

If neither one of those is the problem, I would suggest going back to your other code that did work. Figure this out later.

This is weird. Your workbook seems to be only one that I can't see the code in. When I open your workbook, there are no modules in it. I know they were there yesterday, but not today. Even earlier versions don't have any modules. I may not be able to help any more. I'll keep trying to figure this out.

Update:
I was able to download ver 10 from the forum, and it has 1 module in it. So maybe I'm back up to speed now.

Off the top of my head (without seeing your current workbook) I can think of 2 ways to refresh your page.

1) Just delete the row that has the delete button on it.
2) In the listbox that selects the account just update it.
This will erase any changes made that weren't saved.

Please Login or Register  to view this content.

Originally Posted by six6to8eight

Finally figured out the cause of the Button_Click macro issue -- the filename. If I have the file named with a certain name, it works. If I try to change the filename (which I did try to many times), it can no longer find the macro Button_Click. Weird... For now everything is working, as long as I don't try to change the file name.

I conclude from this that you have Button_Click in the sheet module instead of a standard module.

In the VBE open the Project_Explorer (Ctrl+R). With your project selected - Insert a Standard Module INSERT>MODULE. It will insert a module named "Module1" (or similar). Put the macro in that module. Then when you change the filename, it will be able to find the macro. You will have to reassign the macro to the macro in the Standard Module before closing the file.