SmittyPro

Creating Effective Forms with Excel – The Worksheet Form (Part 2 of 3)

"Smitty" — Mon, 09 Jul 2012 22:10:37 +0000

Note: this article was written specifically for Excel 2010 and its Ribbon interface, however the concepts reviewed will apply for the most part to earlier versions.

In our last article we discussed how Excel is a fantastic tool for creating forms, and reviewed the native Data Form, which is built into Excel (but you need to work to find it in Excel 2007+). It’s a simple tool for entering transactional data, like check register or vehicle service information, but isn’t a great tool for one-off purpose-built forms that generally only need to capture your information one time, like an application form, employee expense report or time-off request. The next form type we’ll discuss is the worksheet form, in which you model a worksheet to mimic what you would see with a printed form. In fact, many companies make forms like this in Excel so they can replace pre-printed forms, and it saves quite a bit of expense, both from not having to send something to a commercial printer, as well as being able to capture data electronically (although tragically, most companies that employ digital forms – in Excel or specific form building software applications – never do anything more with that digital information). Another key point about this article is that we’ll be talking about how to design an intelligent user-entry form for one-time use, not being able to actually capture the data in a transactional sense, like keeping a record of invoices you generate. That is easy enough to do, but it requires VBA (Visual Basic for Applications) code, which is outside of the scope of this article. However, there will be a link to a Company Invoice template form that can capture transactional data that you’re free to modify for your own use.

As mentioned in the last article, the key to intelligent form design is making it as easy as possible for your users to give you the information that you want. If you make it hard for them to give it to you, they will make it hard for you to get it out. It’s really as simple as that. This isn’t necessarily by any conscious decision on their part, it’s just a fact that if you make it difficult for someone to give you information, they generally won’t be inclined to spend a whole lot of time error checking their entries. We’ve all had negative experiences filling out one form or another, so that leads to two primary principles in design: first, make it easy and intuitive for people to enter information (data), and second, structure your form in such a way that it flows from point to point in a natural progression. Here’s a simple example for entering phone numbers: most people will enter “(212) 555-1212”, and waste time with the parentheses and hyphen, when Excel has a Telephone number format that will accept “2125551212” and automatically format it as “(212) 555-1212”. Do you have to educate your users to a certain extent, sure, but they’ll thank you once you do, and that can cascade down to how they interact with other intelligently designed forms & applications in the future. In fact, intelligent design extends far beyond just filling out forms, but how people interact with applications in the first place.

With that in mind we’ll be covering various elements native in Excel to be able to facilitate efficient design and user input:

Worksheet Protection – Using some of Excel’s internal functionality to direct data entry, and prevent them from overwriting important formulas or other information.
Data Validation (to limit or direct user entry) – For instance in a Time-Off request you wouldn’t want to let a user enter a date before today. Or using lists to limit what a user can select, like all of the US states, instead of giving them the opportunity to misspell Mississippi (or in one case I saw not too long ago: “Missasagua”, and that’s probably misspelled).
Conditional Formatting – You can use this to indicate to a user that a cell needs to have an entry.
Cell Formatting – Just like the telephone number formatting example.
Worksheet Formulas (to prefill entries based on a Data Validation selection) – Let’s say you have a customer list in a worksheet, you can use VLOOKUP to pre-fill the customer address information as soon as the user selects Customer Name from a Data Validation list.
Worksheet Controls – Check Boxes, Option Buttons, and other web-type features that make it easier for your users to fill out a form faster. An example would be a “Tax Required” button on an Invoice form that could then be used to calculate the appropriate tax.

None of the methods we’ll be discussing are infallible, the data/input that you receive is only as good as the users giving it, and there will always be mistakes. But if you can limit that on the front end, then you greatly simplify both your life and theirs, and you can all subsequently spend more time on more important things.

Worksheet Protection – What is that anyway? Well, by default all cells in an Excel worksheet (over 1 billion now) are protected. Which means that if you protect a worksheet, then user entry into any cell is restricted. But you can use that to your advantage, so let’s say that you want to allow a user to enter information into certain cells, but not alter others, then you simply unlock those cells and protect the worksheet. Once you do that users can only enter information into those unprotected cells.

NOTE: you can select multiple, non-contiguous cells with CTRL+Left-Click, then CTRL+1 launch the Format Cells dialog. From there goto the Protection tab and uncheck the “Locked” check box. When you protect the worksheet afterwards, then just those cells will be open for entry. The Hidden option is for formulas; when you select that then users will only see the results of formulas, as opposed to the actual formulas themselves. This can be very handy if you have novice users who might get confused when they see formulas (or if you have a very complex formula that you don’t want to share).

Unlocking Cells

An added bonus to Worksheet Protection is that it introduces a natural Tab order (meaning that your users can use the Tab key to move from each unprotected cell to the next). A key note here with regards to design is that the natural Tab order goes from Left-to-Right, then back down to the next row and does not deviate, so you need to keep that in mind with your “intelligent” design. By way of example, think of any Internet form you’ve filled out where you tabbed to different fields in the wrong order, requiring you to redirect to go back to the right place. What’s that do? Wastes time right? While it might not seem consequential, think of a company like Verizon with 200,000 employees. If they have a poorly designed form that even only 20% of the staff use, and they spend an extra x minutes per week/month working with an inefficiently designed process then you can only imagine the cascading effect that can have on their internal efficiency, and subsequently why our rates go up each year. Another thing that taking advantage of the natural Tab order does is keep your user’s fingers on the keyboard instead of transitioning to the mouse to move from cell to cell. Anytime you can eliminate wasted motion like that is a good thing and again, it might seem inconsequential, but it adds up over time. And time goes two ways, you waste it or save it, but you can’t get it back.

Once you’ve selected the cells you want users to be able to enter data into you can goto the Review Tab and select Protect Sheet. Or in the vein of efficiency, use the keyboard shortcut ALT+T+P+P, both of which will display the Protection dialog:

Worksheet Protection Options

Beginning with Excel 2003 Microsoft added significantly more protection options, some of which you might find helpful beyond just locking/unlocking cells. Deciding which options to allow is entirely up to you, and it may well very change from form to form, so it’s well worth experimenting with the options to see how they all work.

Standard worksheet protection with unlocked cells should suffice for most forms, but it does take some thought when you plan the form to ensure that you can follow the Tab Order directionality. There are other ways to allow tabbed behavior using Defined Name Ranges and/or VBA (Visual Basic for Applications) code, but that’s another discussion. You’ll find more details on worksheet protection in this article: Automating Data Entry and Protecting your Data.

Data Validation – This is one of the most powerful tools you have when it comes to building powerful, user-friendly and intelligently designed forms. Data Validation allows you to direct a user to only input specific information, like select from a list of States, input a certain range of numbers, or dates. For example, if you have an Employee Time-Off request, you wouldn’t want someone to be able to enter a day before today. Or you might have an Annual Employee Performance Appraisal and want to limit a recommended annual merit increase to a certain percentage. For more information on how to set up Data Validation, see our previous article here: Automating Data Entry with Data Validation Lists.

The example workbook for this article is a very simple customer information form. Here is an example of using Data Validation to allow a user to select from a list of Titles:

Data Validation List

Notice how the cells requiring are shaded orange, but the Data Validation cell where “Mr.” has been selected is white? That brings us to our next topic, which is Conditional Formatting.

Note: Excel internally recognizes Data Validation input on a worksheet, and it’s captured with the VBA Worksheet_Change event, which can be a very powerful tool for directing workflow based on a user’s selection.

Conditional Formatting - Once you’ve set up your form, and for this article we’re using a very simple example, you can use Conditional Formatting to focus the user’s attention on where they need to enter data. This won’t stop someone from submitting your form with incomplete information (you can do that with VBA), but it serves as a visual clue that they need to fill in certain fields. To employ Conditional Formatting, simply select the cells that you want shaded (HINT: this can be combined with the steps to unlock cells, as you have already selected the cells, so why repeat it?), goto the Home tab and select Conditional Formatting, or use the keyboard shortcut ALT+O+D. In this case we applied a very simple condition that simply checks to see if each cell has a value of nothing (“”) or not. If the condition is true, then Excel applies the format, otherwise it won’t. There are a lot of features available in Conditional Formatting, far too many to review here, so please spend some time getting used to it. Beginning in Excel 2007 Microsoft greatly enhanced your capabilities with this tool, so it’s well worth getting to know. For instance, you were previously limited to 3 conditions, now you have 64. Although please think conservatively, as actually having that many conditions could cause just as much confusion as not.

Conditional Formatting

Note on Quotes: when you initially enter =”” in the Conditional Formatting Rule criteria and confirm it with OK/Apply you probably won’t see anything happen, which might lead you to believe that you did something wrong. You didn’t, but Excel will initially change the formula to =””””””, so first hit Apply, then go in and Edit the rule and remove the additional quotes that Excel added. Unfortunately, this is by design as Excel has a hard time with quotes (and apostrophes), as they have internal functions as well as what you’re trying to do with them.

Cell Formatting – The next element of form design is using custom cell formats to your advantage. It’s all too common to see users fill out forms using literal formats, like entering “(212) 555-1212”, which is unnecessary, as you can tell Excel to do it for you. For forms especially, there’s even a series of “Special” options that have been pre-built for you. In this case we’ll select Phone Number. Now all your users need to do is enter “2125551212” and Excel will automatically format it for you. Cell formatting also preserves the integrity of a value, whereas user entered punctuation can render it nearly useless as Excel will then read it as text. It is possible to get to the value with the use of some formulas, but why go to that extent if you can avoid it up front? The key is to use Excel’s tools so that it works for you, as opposed to you working for Excel (which happens a lot). Fortunately, Excel’s pretty smart, so it knows a lot of things, like “$123.45” is a number even though the $ has been added by hand, but you should never count on it, just like you shouldn’t anticipate that users won’t make mistakes (especially since we all do).

Format Cells - Phone Number

Worksheet Formulas – You can use formulas to your advantage in Excel forms, and it is one of its most powerful features. While we’re not going to get into a full-blown formula discussion here, as there have been entire books devoted to the subject, we’ll cover a few concepts to give you an idea of what can be done, which is primarily having Excel perform calculations so your users don’t have to do it themselves (can you say error management?). Let’s go back to the Employee Performance Appraisal example. Imagine that you have a list of all employees on another worksheet (Employee ID, Name, Hire Date, Hourly Salary, etc.), you can use formulas to populate all of that information simply by selecting the Employee ID from a Data Validation list. Here’s an article on IF statements & LOOKUPS, which allow you to do that. You can also use a method called Concatenation to combine information from certain cells and create unique text strings. For instance, let’s say that your employee e-mail naming convention is “LastName.FirstName@Company.com”, and you have First Name in cell A1 and Last Name in cell B1. You can use:

=B1&”.”&”A1”&”@CompanyName.com”

Which would create “Jones.Bob@XYZco.com”. The key here is the Ampersand (&) operator, which lets you link both cell references and values. Since that formula creates a text string you can coerce it to be a hyperlink by using the following:

=HYPERLINK(B1&”.”&”A1”&”@CompanyName.com”)

This brings up an interesting point that if you try to do this with numeric references you’ll lose any number formatting you might have applied as Excel now sees that data as text, but you can use the TEXT function to coerce it back within your new text string. The following example lets you concatenate text and today’s date all within one cell:

=”Report Printed on: ”&TEXT(TODAY(),”mm/dd/yyyy”)

Which would read: “Report Printed on: 07/09/2012”. Otherwise you’d see: “Report Printed on: 41099” (41099 is Excel’s way of storing the date value). Pretty slick, huh? I liken this to Excel knows that I’m on Windows 7, it knows my system’s regional preferences, but it doesn’t know my intention with the value I’m referencing, even if I’ve already formatted the a referenced cell properly, so it runs home and converts the value into its basest element, in this case the system date value.

Worksheet Controls - The final element to worksheet forms is using worksheet controls, of which there are two distinct categories: Form Controls & ActiveX Controls. These are items like Check Boxes, Option Buttons, List & ComboBox controls, etc., all of which can be used to simplify form use. For example, you could add a Check Box to a form for “Tax Required?”. Once checked it would trigger a formula that will calculate the Tax due on a purchase order. For the purpose of this article, we’re only going to discuss Form Controls, as they can be formatted and controlled within Excel itself. ActiveX controls on the other hand are VBA components and you generally need to write code against them in order to expose their full capabilities. While they are more flexible and robust than their Form control counterparts, they are more difficult to master.

The first thing you need to do is expose the Controls themselves, which are located on the Developer tab in the Ribbon. As this also exposes VBA elements, Microsoft chose to hide this Ribbon element by default, since unfortunately most Excel users will never use it. In Excel 2010 goto FileàOptionsàCustomize Ribbon and check the “Developer” check box in the right-hand pane. In Excel 2007 this is a check box under the General options.

Show the Developer Tab on the Ribbon

Once you confirm this you’ll now see the Developer tab appear on the right side of the Ribbon. From there you’ll see a Ribbon group called Controls, and clicking on the Insert button will display the controls that you have available:

Like Conditional Formatting and Formulas, there are so many options here, that we’re just going to briefly touch on the topic with a general example. But you should experiment with as many of the controls as you can. Once you do you’ll probably find yourself using them all the time, even for your own work. One of the most common controls to use is the Button. Buttons are great because you can assign macros to them to perform certain tasks, like print a form once it’s been filled out. What’s really cool is that you can assign a macro to almost any object. Form Controls are objects, but so are any of the shapes you’ll find from the InsertàShapes Ribbon group. I prefer to use shapes to assign macros to since they’re much more visually appealing, and while it may seem inconsequential, people will actually fill out a well-designed and appealing form much faster and with less errors than one that‘s poorly designed.. Which would you rather see:

Form Button vs. a nice Shape Button

Building a good form not only takes functionality into consideration, but needs to appeal to the end user as well. Just think about how much easier your tax forms would be to fill out if they were thoughtfully designed.

Note: in the example workbook both buttons have a macro assigned to them to display the worksheet form in Print Preview mode, so you’ll need to enable Macros in order for them to work. You’ll find an Introduction to Macros article here: Automating Tasks with the Macro Recorder – First Steps.

For our working example we’ll add a Check Box to our form to indicate whether our contact wants to receive opt-in e-mails. From the Form Control dialog simply select the Check Box control (the Form control, not ActiveX), then draw it on your worksheet with the mouse.

Check Box Form Control

From here you can right-click the Check Box to gain access to its internal controls and formatting. Obviously you want to edit the text to suit your needs, but you can also do things like linking the control to a particular cell. When checked the control has a value of “TRUE”, and when unchecked its value is “FALSE”, so you can perform calculations against that. Most of the time you’ll put the linked cell in either a location away from the form itself, or format its text color to match the form background so it doesn’t display. If a user doesn’t need to see the TRUE/FALSE value, then don’t show it to them. There’s no need to confuse people by displaying something that they don’t need to see.

Format your Form Control

In this case the Check Box is linked to cell B11, which will toggle/display TRUE/FALSE depending on whether it’s checked or not. As you see in the Value options, you can decide whether the Check Box’s initial state should be Checked or Unchecked. A quick note on formatting is that you can’t change the font size with Form controls. That’s controlled strictly by the active worksheet’s Zoom setting. On the other hand, you can manipulate font attributes with ActiveX controls.

Using a Formula based on a Check Box's value

In this example the formula in cell A15 simply reads:

=”This customer “&IF(B11=TRUE,”does”,”does not”)&” want to receive e-mail communication.”

This is another example of using concatenation to create custom messages for your users, or perform calculations that they might otherwise need to do manually. Granted, these are simple examples, but they should give you an idea of what you can do. If anything it will help you realize how you can set things up in a time-saving and well thought out fashion to make it easier to collect information from people. The other thing it does is greatly reduce errors, which directly equates to bottom line savings.

Review – in this article we discussed the various elements that you can employ to streamline data collection processes without having to purchase an expensive purpose-built form creation software application simply by using tools readily available to you in Excel. You’ll find loads of great example forms on the internet that are free for you to use, modify and distribute as you see fit. Most notably is the Microsoft Template Gallery, which you’ll find here: http://office.microsoft.com/en-us/templates/. The key points to remember about building effective forms in Excel are relatively simple:

If you make it hard for people to give you data, then they’ll make it hard for you to get it, so be creative, and design forms with the end user in mind. If you focus on HOW the user interacts with your form, then the data flow will be easy. If you focus on just getting the information with little or no regard to their interaction/process then you’re all in for a miserable experience.
A well-built and intelligently designed form can greatly reduce errors, and allow your users to give you information much faster than if they had to write it out by hand, or perform manual calculations on their own.
Use the tools that you have available to you, Worksheet Protection, Data Validation, Conditional and Custom Cell Formatting, and Worksheet Formulas and Controls.

Here’s a link to the sample workbook: http://sdrv.ms/PHXUm1

Chris “Smitty” Smith is Director of Training and a Developer for the boutique consulting firm www.excelandaccess.com

Creating Effective Forms with Excel – The Simple Data Form (Part 1 of 3)

"Smitty" — Mon, 09 Jul 2012 00:30:52 +0000

Have you ever gotten an online form from a company or government organization and received a PDF that you had to print out and fill out by hand (and you’ve probably even seen one that looks like a scan of a scan of a poorly built form on an old IBM typewriter)? What’s the point? If the point is to be able to use an electronic medium, then what purpose is served by making you print something and fill it out by hand? Not only does that process waste your time, think about what happens on the other end: you print out the form, fill it out, scan/fax/mail it, then when it gets back to the other side what do they do? Put all of that information back into a computer. What a monumental, no colossal, waste of time (not to mention the time that needs to be spent trying to error check your/my lousy handwriting)! So why do people make it so hard to get information from each other? The answer isn’t simple, but the solution is:

Build a fillable form!

There are many software applications that are purpose built for form building, but unless you build forms for a living you need not go to the expense. If you have Microsoft Office, which most of us do, you can easily build a form in Excel, which is ubiquitously seen in the business world as a form tool (oh, and some of us use it for financial analysis). But it’s an amazingly effective form building tool, and allows us to keep information in the electronic/digital environment, after all, once information is put into a computer, there’s no need for that work to be redone by hand!

So what is a form? Simply put, a form is a medium into which you enter information. We all have to fill them out, DMV registration, passport applications, opening bank accounts, online flight bookings, etc. But what’s the biggest problem with doing we generally encounter with any of these processes? The lack of intelligent design and structure is the problem. Printed/non-digital forms notwithstanding, have you ever gone to an online form to buy something, or book a flight and the tab order is out of sequence, or not logical at all? (For those of you who navigate forms with the mouse, here’s a hint: use the TAB key) We all have, and it’s frustrating, but it can all be avoided if you simply understand that the easiest way to get concise information from people is to make it easy for them to give it to you. If you make it hard for them to do that, then they will make it hard for you. So which would you rather do? Save time and make it easy, or make the experience painful for everyone? Unfortunately, too many forms are seemingly built with the latter in mind.

Like it or not, data collection and management is central to almost everything we do, be it keeping track of personal finances, managing aspects of a business, or even booking a vacation. Unfortunately, most organizations make it difficult for you to give and get information (big corporations and governments most notably), so we’re going to discuss how to keep it simple with one tool that most of us have at our fingertips. Excel.

There are two primary types of forms and the way in which you use them. The first is a form that allows you to capture continuous information, like keeping track of a check register or vehicle service history. This type of information is stored with columns holding relevant transactional details, like Date, Check Number, Payee, Amount, etc., while rows store individual records, or transactions. The second is a single use form, like an employment application, invoice or little league sign-up. This type of form usually requires input in various places on a worksheet. Note that a single-use form can be created in such a way as to capture transactional details, but doing so requires VBA (Visual Basic for Applications) programming, which we’re not going to discuss as part of this series.

In this article we’re going to discuss the simplest way to create a continuous form in Excel, which is to use the DataàForm method (a built-in functionality in Excel). However, it’s not very robust, and only supports a limited number of data fields, but it can be a great tool for allowing people to quickly enter data. Unfortunately, it’s one of those features that Microsoft chose to hide in Excel 2010, so you have to find it. We’ll show you that in a minute, first here’s an example of the Data Form:

That’s about as easy as it gets, but it’s not robust in that there’s no way to prevent users from entering incorrect information, so this should only be used for very simple data collection needs. Otherwise you can spend as much time validating what your users entered as you would entering it by hand, knowing what needs to go where, and how. Note in the example how the data is clearly structured with each column storing a different type of information, while each row represents a unique record. One nice thing about this structure is that it readily lends itself to being represented as a Pivot Table or Chart, which are great ways to analyze your data, and display it graphically.

Note: good data at the end is only as good as the data going in!

To expose the Data Form tool in Excel 2010 goto the Quick Action Toolbar above the Ribbon, select the drop-down for more options, then the More Commands option.

QAT

Once you do that the Customize Ribbon dialog will appear, in the left-hand pane select All Commands, then find “Form” and drag it to the right hand pane like you see here:

Customize the QAT - More Commands

Add the Data Form to the QAT

From here on out, anytime you’re in a data set that Excel can recognize when you click on the Form button the Data Form will appear. But there are some simple rules for using this tool: first, your data needs to be structured in such a way that Excel can read and understand it (in much the same way that your users need to understand what’s expected of them). So your worksheet needs to be set up in a contiguous manner, meaning that if you have empty columns or rows in your worksheet, then don’t expect Excel to pick it up (then again, don’t expect your users to either). Second, you need to have column headers for the form to be able to identify, and as you saw in the Data Form example, Excel uses the first row in your worksheet to define the entry fields. Third, if you have more data fields then the form can accept, this won’t be the appropriate tool for you (but we’ll discuss that in the next article). Fourth, and key to intelligent form design is to layout your worksheet in such a way that makes sense for data collection and use once you get it, which means that individual records should ideally be stored in a series of rows going down the worksheet, not columns going across, as columns should be used for individual components of each overall record (row). Also think about Excel’s capabilities: while you now have over 1 million rows of data, you “only” have 16,000 columns. If you were to collect data in columns as opposed to rows, you could run out of room. Unfortunately, we see this type of structure all too often, so you can avoid the pain of having to rebuild a workbook just by building it right in the first place. Here’s an example of the simple Data Form in use:

Data Form Example

Notice that your column headers do have Accelerators attached to them. Those are the underlines beneath the S in Store, a in Date, etc. To quickly acces any of those fields directly from the keyboard, all you need to do it press ALT+ the corresponding letter for that field. I’ve never tried to push one of these forms to its field limit, but I’d imagine that you’re going to run out of Accelerators when you get beyond 26 fields (corresponding with 26 letters in the alphabet).

If you do like the Data Form, but need a more robust version, longtime Excel MVP John Walkenbach has a free add-in he created called the Enhanced Data Form. You can find it here: http://spreadsheetpage.com/index.php/dataform/home.

John Walkenbach's Enhanced Data Form

You can find a copy of the Simple Data Form workbook here: http://sdrv.ms/OLHFSh. Note that it contains a simple macro attached to a button to display the form in case you haven’t exposed the Data Form button on the QAT, which will require you to enable macros. In versions of Excel prior to Excel 2007, the Data Form is available from the Data menu.

In the next article we’ll discuss building single-use forms, like an invoice, or employee expense report.

Excel: Automatically consolidate data from multiple worksheets?

"Smitty" — Tue, 05 Jun 2012 17:44:51 +0000

We had a question at #ExcelHelp this week from somone who wanted to consolidate data from multiple worksheets to a master sheet. My first thougt was use Excel’s native PivotTable functionality for multiple consolidations ranges (easier than it sounds, just follow the Wizard), but this case just needed to move an inconsistent range of worksheets just a few times, so VBA code seemed more appropriate for a one-off situation like this where you copy data from each worksheet to the master. The problem (especially if you’re just starting with VBA) is that one worksheet might have 52 rows of data and the next 73, or 1,008. So how do you deal with dynamic ranges like that? Unfortunately, this isn’t one that the Macro Recorder can take care of for you, so you need to go beyond that and actually start writing some code of your own, but this is where VBA’s power shines; you just need to know how to tap into it…

What might seem very complicated, really just needs a bit of thought and some dynamic ranging, so we can identify the range of data in each worksheet to be copied (A2:A1274 let’s say). But that just defines the copy from range, then we need to identify the first empty row in the worksheet where the data is going to be copied, and each time new rows of data are added, we need to find the new last row. Within all of that we’ll go through each worksheet and then redifine those ranges.

Here’s the resulting code, with comments so you can see what it’s doing:

Sub CopyfromSheets() ' Declare Variables for Worksheets & lr to get the last used row in a range Dim ws As Worksheet Dim lr As Long

' Loop through each worksheet in the workbook For Each ws In ActiveWorkbook.Worksheets ' Tell the code to ignore the "Master" worksheet If ws.Name <> "Master" Then ' Find the last used row in column A in each sheet lr = ws.Cells(Rows.Count, "A").End(xlUp).Row ' Rows.Count counts the number of rows in the worksheet and doesn't care if it's 65,536 or 1MM+. ' End(xlUp) goes from the last row at the bottom up to the last row at the top ' You can also use End(xlDown), but it will fall apart if you have blank rows in your data ' .Row gets the Row #, so "lr" can then be 52, 74, 1,008, etc., as it resets for each worksheet ' Copy/Paste - As long as you're not trying to Paste Special, like Values, then you can have Range.Copy -->Destination all on one line ' In the Paste part we use Offset(1) to move down to the next Empty row so you don't paste over existing data ws.Range("A2:A" & lr).Copy Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1) ' Exit the IF statement evaluating the sheet name End If ' Move on to the next worksheet. This doesn't care how many sheets you have, as it will go through them all ' If you only wanted to do this on 20 of 100 sheets you can define the For Each Next construct like this: ' For i = 10 to 30 ' Sheets(i)... ' Next i ' Which goes from sheet #10 to sheet #30 and ignores any others Next ws End Sub

So now you have a way to dynamically copy data from multiple worksheets to a master sheet. And while this is a relatively simple example, it wouldn’t be hard to set it up to copy multiple columns. You could even set it up for a variable number of columns by adding the same methodology used to identify the last row:

lc = Cells(1,Columns.Count).End(xlToLeft).Column

Excel VBA Training & Certification

"Smitty" — Mon, 07 May 2012 19:50:09 +0000

If you’ve followed any of our posts, then you’ll see that we’re huge proponents of using VBA (Visual Basic for Applications) as a means of automating everyday tasks in the Office environment.

One of the most frequent forum comments/requests that we see is for how to learn VBA. Most of the advice that you’ll get starts with your most valuable free tool, which is the macro recorder. Then there are lots of online tutorials you’ll find via Bing/Google, and finally you can buy some books (John Walkenbach’s Power Programming with Excel VBA is the best). But what if you want to step up your game and actually get certified in VBA?

Well, look no further than http://www.vbaexpress.com/training/. The folks over there have done an amazing job of not only creating fantastic VBA courseware, but they offer the ability to be certified in VBA as well.

If you want to step up to your “A” game then you can use the following coupon codes to get a discount:

SP10OFFTRAINING and SP5OFFCERTIFICATION

A word of caution here though: I’ve had the pleasure of being a tech editor for some of the content and it’s not for the faint-of-heart. This is serious stuff. But you’ll without a doubt walk away being the best VBA user around. And while we’re not tax professionals, we’ll bet good odds that it’s tax deductible.

VB HTML Maker Update

Zack — Wed, 25 Apr 2012 02:43:58 +0000

When I first starting really getting into VBA, it was pretty invaluable to me to be able to read over my code. And over and over and over my code again. And again. As well as others code. This really helped me learn the syntax and flow. An add-in which was great, which I can’t remember the name of, allowed me to print out my VBA code as an HTML file, so I could view the code as it looked in the VBIDE. This was great! I could now review my code and keep it as a hard copy for backup!

In the meantime, I have done a lot of posting to forums of sorts. One of the forums in particular is Mr Excel.com. This board utilizes vBulletin software and is laid out very nicely. There are a few add-ins to help you post things to make it present nicely when viewed. Some of these add-ins were (a list of which can be found here) Colo’s HTMLMaker, Excel Jeanie 4, ASAP Utilities, etc. Among these was the VBHTMLMaker add-in, which let you post your VBA code to the forum and have it present as it did in the IDE. Awesome!

So now to my point. A few years ago I asked Juan Pablo González, original developer of the VBHTMLMaker add-in (amongst other collaborators) if I could amend his add-in. What I wanted to do was add an option to save the HTML generated code into an HTML file. Easy, portable, documented, and printable! He graciously agreed and that’s where I sat until yesterday. I made excuses that I didn’t have time, but more or less I didn’t make it a priority. I finally had enough with my procrastination and I added the code yesterday, tested today, and am now uploading to share with the world.

There is also some minor modifications to keep it as the font you have set in your VBE options. To get this value we need to read the registry, which retains the font name (among properties). A portion of the code was graciously utilized from Dev Ashish and Terry Kreft. This is used by the following code:

‘ Txt = IIf(HTML, ““, “[face=Courier New]“)
Txt = IIf(HTML, ““, “[face=Courier New]“)

If you do not want to use such code and want it in Courier New every time, uncomment the commented line and take out the other. Unfortunately this only works for HTML and not BB code. The new file is named VBHTMLMaker2007, but does not utilize the Ribbon in any way. So if you have a need or desire to save your VBA as an HTML file for whatever reason, this file has the amended code as such. Enjoy!

VBHTMLMaker2007

Regards,

Zack Barresse

Create a Table of Contents in Excel

Zack — Mon, 30 Jan 2012 02:28:47 +0000

There has always been a need for a good Table of Contents in Excel. While Word features this natively with its built-in Styles, unfortunately, Excel lacks this functionality due to a host of reasons, which are quite valid. However, we can create a workaround by incorporating some VBA.

The following code is an updated version I created a few years ago. It was simple and served my needs. Over the years it’s needed work, but like a mechanic with his own vehicle, I never really put the time forth to do it, always pushing it to the back burner. Not so anymore! Today I updated the code and made it much more versatile and dynamic! Plus I added comments, which are always helpful when looking back on it.

The code will now take any type of worksheet and create a hyperlink to it. Previously, the problem was if the user had a Dialog, Macro or Chart sheet in a workbook, standard hyperlinks do not work, as you can only use a standard hyperlink to a worksheet (all of the above objects are in the Sheets collection, but not a Worksheet type). In order to bypass this we can create a button on top of each unsupported object it which is linked to a procedure in a we have stored in a module. You can change the variables at the top of the module to whatever you’d like, but it will work as-is if you copy/paste to a new module. The code It will automatically change the name of the module to whatever you specify for the sModuleName variable.

Here is the code. If you have any comments, please feel free to let me know!

'--------------------------------------------------------------------------------------- ' Module : Mod_TOC_RPA ' Author : Zack Barresse ' Updated : 1/27/2012 ' Purpose : This code module serves as a stand-alone module to create a Table of ' Contents of the active workbook. The other sub routines are supportive ' of this functionality. This will handle the following sheet types: ' Worksheets ' Chart sheets ' Dialog sheets ' Macro sheets ' For those sheets which are not Worksheet types, a shape is utilized. This ' is done because you cannot hyperlink to these sheet types with the normal ' functionality. So instead we add the sheet name to it with an old ' Excel4Macro and add an OnAction property for it to use a sub routine. All ' other 'normal' worksheets will receive a hyperlink to that sheet, with a ' destination of cell A1. ' References: Microsoft Visual Basic for Applications Extensibility 5.3 ' You must set this reference for the code to compile without error. To ' add this reference library, click Tools | References, find the correct ' reference, check the box next to it, then click Ok. If you don't see it ' in the list, look for it in its normal location: ' C:\Program Files (x86)\Common Files\microsoft shared\VBA\VBA6\VBE6EXT.OLB ' or ' C:\Program Files\Common Files\microsoft shared\VBA\VBA6\VBE6EXT.OLB ' Change the 'sModuleName' to whatever you want the module to be named. ' With the above reference, you can change it to whatever you want. If you ' name it to another module name that exists in the same project, the ' current module name will be retained. ' Examples : Some other examples of creating Table of Contents can be found here: ' http://dmcritchie.mvps.org/excel/buildtoc.htm ' http://www.vbaexpress.com/kb/getarticle.php?kb_id=120 ' http://www.mrexcel.com/articles/table-of-contents-macro.php ' http://alturl.com/nrk83 ** ' http://extremecpa.blogspot.com/2005/08/table-of-contents-in-excel-with-vba.html ' ** Like this code, will take any type of sheet (the others will fail), ' but uses VBA to write to the Table of Contents sheet code module ' for the functionality of hyperlinking. '---------------------------------------------------------------------------------------
Option Explicit Private Const sModuleName As String = "Module1" Private Const sDefaultProcName As String = "CreateTOC" Private Const sSheetName As String = "Table of Contents" Private Const DNL As String = vbNewLine & vbNewLine Private sModuleRename As String Private sProcName As String Private bModuleName As Boolean '/// *************************************************************************************** '/// MAIN ROUTINE '/// *************************************************************************************** Sub CreateTOC() '--------------------------------------------------------------------------------------- ' Procedure : CreateTOC ' Author : Zack Barresse ' Updated : 1/27/2012 ' Purpose : Explicitly setting object references, cleaned up variables, added some ' additional error handling, added the handling of Macro and Dialog sheets ' as well, and generally cleaned up code. 'Thanks to : Ken Puls ' Simon Lloyd ' Anne Troy '--------------------------------------------------------------------------------------- Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim LineNum As Long Dim ProcKind As VBIDE.vbext_ProcKind Dim WB As Workbook Dim WS As Worksheet Dim cb As Shape Dim iWriteToRow As Long Dim iLoop As Long Dim iWsTotalCnt As Long Dim iWsCnt As Long Dim iChartCnt As Long Dim iOtherCnt As Long Dim iLeft As Long Dim iTop As Long Dim iHeight As Long Dim iWidth As Long Dim iShade As Long Dim sMsg As String Dim sAddy As String Dim shtName As String Dim bTrigger As Boolean '/// Used to get procedure name bTrigger = True '/// Check if there is an activeworkbook or not (i.e. this code was dropped into a '/// workbook which wasn't visible, or an add-in) If ActiveWorkbook Is Nothing Then MsgBox "You must have a workbook open first!", vbInformation, "No Open Book" Exit Sub End If '/// Set variables iShade = 37 iWriteToRow = 3 iWsCnt = 0 Set WB = ActiveWorkbook '/// Turn off some application settings to make code more efficient and run faster Call TOGGLEEVENTS(False) '/// Check if the TOC worksheet already exists in the workbook, and if so question user If WSEXISTS(sSheetName, WB) = True Then sMsg = "You already have a Table of Contents page. Would you like to overwrite it?" If MsgBox(sMsg, vbYesNo + vbQuestion, "Replace TOC page?") = vbYes Then GoTo ExitEarly WB.Worksheets(sSheetName).Delete End If '/// Add a blank worksheet to the far left, rename it Set WS = WB.Worksheets.Add(before:=WB.Sheets(1)) WS.Name = sSheetName '/// Ensure the code module is the proper name, as needed for the shape OnAction property Call ChangeModuleName '/// Change format of TOC sheet WS.Cells.Interior.ColorIndex = iShade WS.Rows("4:" & WS.Rows.Count).RowHeight = 16 WS.Range("A1").Font.Bold = False WS.Range("A1").Font.Italic = True WS.Range("A1").Font.Name = "Arial" WS.Range("A1").Font.Size = "8" WS.Range("A2").Value = "Table of Contents" WS.Range("A2").Font.Bold = True WS.Range("A2").Font.Name = "Arial" WS.Range("A2").Font.Size = "24" WS.Range("A4").Select '/// Add a shape which will act as an update for the TOC sheet With WS.Range("C1") Set cb = WS.Shapes.AddShape(msoShapeRoundedRectangle, .Left, .Top, .Width, .Height) End With sAddy = "R1C3" cb.Select Selection.ShapeRange.Fill.ForeColor.SchemeColor = 0 Selection.ShapeRange.TextFrame.VerticalAlignment = xlCenter Selection.Font.Underline = xlUnderlineStyleSingle Selection.Font.ColorIndex = 5 Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Line.Visible = msoFalse '/// Get the current routine name '/// Kindly adapted from Chip Pearson, found at: '/// http://www.cpearson.com/Excel/vbe.aspx On Error GoTo ErrHandle Set VBComp = ThisWorkbook.VBProject.VBComponents(sModuleName) Set CodeMod = VBComp.CodeModule LineNum = CodeMod.CountOfDeclarationLines + 1 sProcName = CodeMod.ProcOfLine(LineNum, ProcKind) GoTo NoCodeModErr ErrHandle: '/// Code module was not found, use default sProcName = sDefaultProcName NoCodeModErr: If sProcName = vbNullString Then sProcName = sDefaultProcName Selection.OnAction = IIf(Len(sModuleRename) > 0, sModuleRename, sModuleName) & "." & sProcName '/// Set heading text for TOC WS.Range("C1").Value = "Update TOC" WS.Range("C1").Interior.ColorIndex = iShade WS.Range("C1").Font.Bold = True WS.Range("C1").Font.Italic = True WS.Range("C1").Font.Name = "Arial" WS.Range("C1").Font.Size = "10" WS.Range("C1").Font.Underline = xlUnderlineStyleSingle '/// Get a total of the sheets '/// NB: We do not use "WB.Worksheets.Count" as that will only count 'worksheets', which '/// does not include chart sheets iWsTotalCnt = WB.Sheets.Count '/// Loop through all sheets, excluding the first (TOC) worksheet For iLoop = 2 To iWsTotalCnt '/// Set the write row, increment by 1 every iteration iWriteToRow = iWriteToRow + 1 '/// Check if the sheet iteration is a chart sheet or not If IsChart(WB.Sheets(iLoop).Name) Then '/////////////////////////// '/// Sheet is a chart sheet '/////////////////////////// '/// Get a total count of chart sheets, set variables iChartCnt = iChartCnt + 1 shtName = WB.Charts(iChartCnt).Name WS.Range("C" & iWriteToRow).Value = shtName WS.Range("C" & iWriteToRow).Font.ColorIndex = iShade '/// Get dimensions of write cell to apply button to it iLeft = WS.Range("C" & iWriteToRow).Left iTop = WS.Range("C" & iWriteToRow).Top iWidth = WS.Range("C" & iWriteToRow).Width iHeight = WS.Range("C" & iWriteToRow).RowHeight sAddy = "R" & iWriteToRow & "C3" '/// Add the shape to the correct cell, add text to it and assign macro "GotoChart" Set cb = WS.Shapes.AddShape(msoShapeRoundedRectangle, iLeft, iTop, iWidth, iHeight) cb.Select ExecuteExcel4Macro "FORMULA(""=" & sAddy & """)" Selection.ShapeRange.TextFrame.VerticalAlignment = xlCenter Selection.ShapeRange.TextFrame.HorizontalAlignment = xlLeft Selection.ShapeRange.Fill.ForeColor.SchemeColor = 0 Selection.Font.Underline = xlUnderlineStyleSingle Selection.Font.ColorIndex = 5 Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Line.Visible = msoFalse Selection.OnAction = IIf(Len(sModuleRename) > 0, sModuleRename, sModuleName) & ".GotoOther" '/// Write number to TOC index WS.Range("B" & iWriteToRow).Value = iLoop ElseIf IsWorksheet(WB.Sheets(iLoop).Name, WB) = True Then '///////////////////////// '/// Sheet is a Worksheet '///////////////////////// '/// Grab count iWsCnt = iWsCnt + 1 '/// Sheet is a regular worksheet shtName = WB.Sheets(iLoop).Name WS.Range("C" & iWriteToRow).Hyperlinks.Add Anchor:=WS.Range("C" & iWriteToRow), _ Address:="#'" & shtName & "'!A1", _ SubAddress:="#'" & shtName & "'!A1", _ TextToDisplay:=shtName WS.Range("C" & iWriteToRow).HorizontalAlignment = xlLeft '/// Write number to TOC index WS.Range("B" & iWriteToRow).Value = iLoop Else '//////////////////////////////////// '/// Sheet is a dialog or macro sheet '//////////////////////////////////// '/// Grab count ' iWsCnt = iWsCnt + 1 '/// Get a total count of chart sheets, set variables iOtherCnt = iOtherCnt + 1 shtName = WB.Sheets(iLoop).Name WS.Range("C" & iWriteToRow).Value = shtName WS.Range("C" & iWriteToRow).Font.ColorIndex = iShade '/// Get dimensions of write cell to apply button to it iLeft = WS.Range("C" & iWriteToRow).Left iTop = WS.Range("C" & iWriteToRow).Top iWidth = WS.Range("C" & iWriteToRow).Width iHeight = WS.Range("C" & iWriteToRow).RowHeight sAddy = "R" & iWriteToRow & "C3" '/// Add the shape to the correct cell, add text to it and assign macro "GotoChart" Set cb = WS.Shapes.AddShape(msoShapeRoundedRectangle, iLeft, iTop, iWidth, iHeight) cb.Select ExecuteExcel4Macro "FORMULA(""=" & sAddy & """)" Selection.ShapeRange.TextFrame.VerticalAlignment = xlCenter Selection.ShapeRange.TextFrame.HorizontalAlignment = xlLeft Selection.ShapeRange.Fill.ForeColor.SchemeColor = 0 Selection.Font.Underline = xlUnderlineStyleSingle Selection.Font.ColorIndex = 5 Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Line.Visible = msoFalse Selection.OnAction = IIf(Len(sModuleRename) > 0, sModuleRename, sModuleName) & ".GotoOther" '/// Write number to TOC index WS.Range("B" & iWriteToRow).Value = iLoop End If continueLoop: Next iLoop '/// Set viewing options Sheets(sSheetName).Range("C:C").EntireColumn.ColumnWidth = 30 Sheets(sSheetName).Range("A4").Activate '/// Add string to message prompt if there were any chart sheets added If iChartCnt > 0 Then sMsg = DNL & Space(13) & iChartCnt & " Chart sheets have been added" End If '/// Add string to message prompt if there were any dialog or macro sheets added If iOtherCnt > 0 Then sMsg = sMsg & DNL & Space(13) & iOtherCnt sMsg = sMsg & " Other sheet" & IIf(iOtherCnt > 1, "s have", " has") & " been added (i.e. Dialog & Macro sheets)" End If '/// Append string to message prompt for how many worksheets were added sMsg = sMsg & DNL & Space(13) & iWsCnt & " Worksheets have been added" '/// If module name was changed, add that in the message prompt text If bModuleName = True Then MsgBox "Complete!" & vbNewLine & "The code module name was changed." & sMsg, vbInformation, "Complete!" Else MsgBox "Complete!" & sMsg, vbInformation, "Complete!" End If ExitEarly: '/// Reset application settings back to default Call TOGGLEEVENTS(True) End Sub '/// *************************************************************************************** '/// SUPPORTIVE FUNCTIONS '/// *************************************************************************************** Public Sub TOGGLEEVENTS(blnState As Boolean) '--------------------------------------------------------------------------------------- ' Procedure : TOGGLEEVENTS ' Author : Zack Barresse ' Date : 1/27/2012 ' Purpose : Quickly toggle application Properties instead of calling them in each procedure ' Variables : blnState - pass a true/false value indicated the property state desired '--------------------------------------------------------------------------------------- If Not blnState Then Application.Calculation = xlCalculationManual Application.DisplayAlerts = blnState Application.EnableEvents = blnState Application.ScreenUpdating = blnState If blnState Then Application.CutCopyMode = False If blnState Then Application.StatusBar = False If blnState Then Application.Calculation = xlCalculationAutomatic End Sub Public Function WSEXISTS(wksName As String, Optional WKB As Workbook) As Boolean '--------------------------------------------------------------------------------------- ' Procedure : WSEXISTS ' Author : Zack Barresse ' Date : 1/27/2012 ' Purpose : To test whether a worksheet exists in a workbook or not ' Variables : wksName - pass a string name for a sheet to test for ' WKB - optionally pass a workbook to test the sheet in, if not specified ' the active workbook will be used '--------------------------------------------------------------------------------------- If WKB Is Nothing Then If ActiveWorkbook Is Nothing Then Exit Function Set WKB = ActiveWorkbook End If On Error Resume Next WSEXISTS = CBool(WKB.Sheets(wksName).Name <> "") On Error GoTo 0 End Function Public Function IsWorksheet(shNameTemp As String, Optional WKB As Workbook) As Boolean '--------------------------------------------------------------------------------------- ' Procedure : IsWorksheet ' Author : Zack Barresse ' Date : 1/27/2012 ' Purpose : To discern whether or not a sheet in a given workbook is an actual ' worksheet, as opposed to a macro, dialog or chart sheet. ' Variables : shNameTemp - pass a string name for a sheet to test for ' WKB - optionally pass a workbook to test the sheet in, if not specified ' the active workbook will be used '--------------------------------------------------------------------------------------- Dim wksTemp As Worksheet If WKB Is Nothing Then If ActiveWorkbook Is Nothing Then Exit Function Set WKB = ActiveWorkbook End If On Error GoTo ErrFound '/// Macro and Dialog sheets will throw an error in one of the next two lines Set wksTemp = WKB.Sheets(shNameTemp) If wksTemp.Name = wksTemp.CodeName Then IsWorksheet = TypeName(WKB.Sheets(shNameTemp)) = "Worksheet" End If ErrFound: On Error GoTo 0 End Function Public Function IsChart(chtName As String) As Boolean '--------------------------------------------------------------------------------------- ' Procedure : IsChart ' Author : Zack Barresse ' Updated : 1/27/2012 ' Purpose : Check if the name passed is a Chart sheet or not '--------------------------------------------------------------------------------------- On Error Resume Next IsChart = CBool(ActiveWorkbook.Charts(chtName).Name <> "") On Error GoTo 0 End Function Private Sub GotoOther() '--------------------------------------------------------------------------------------- ' Procedure : GotoOther ' Author : Zack Barresse ' Date : 1/27/2012 ' Purpose : Attached to a shape to allow for hyperlinking to other sheets '--------------------------------------------------------------------------------------- Dim obj As Shape Dim objName As String On Error Resume Next Set obj = ActiveSheet.Shapes(Application.Caller) objName = obj.TopLeftCell.Value ActiveWorkbook.Sheets(objName).Activate On Error GoTo 0 End Sub Sub ChangeModuleName() '--------------------------------------------------------------------------------------- ' Procedure : ChangeModuleName ' Author : Simon Lloyd ' Date : 4/20/2011 ' Purpose : Check if a code module name already exists, if not, create it '--------------------------------------------------------------------------------------- Dim vbModule As VBIDE.VBComponent bModuleName = False If VBComponentExists(sModuleName, ThisWorkbook.VBProject) = False Then Call fxModuleNameChange(sModuleName, ThisWorkbook) bModuleName = True End If End Sub Public Sub fxModuleNameChange(newMacro As String, Optional WB As Workbook) '--------------------------------------------------------------------------------------- ' Procedure : fxModuleNameChange ' Author : Simon Lloyd ' Date : 4/20/2011 ' Purpose : Change a module name ' Variables : newMacro - pass a string name for a module name to change to ' : WB - optionally pass a workbook to look in '--------------------------------------------------------------------------------------- Dim VBC As VBIDE.VBComponent, modCode As String If WB Is Nothing Then Set WB = ThisWorkbook For Each VBC In WB.VBProject.VBComponents If VBC.CodeModule.CountOfLines > 0 Then modCode = VBC.CodeModule.Lines(1, VBC.CodeModule.CountOfLines) If InStr(1, modCode, newMacro, vbTextCompare) > 0 Then VBC.Name = sModuleName Exit For End If End If Next VBC End Sub
Public Function VBComponentExists(VBCompName As String, Optional VBProj As VBIDE.VBProject = Nothing) As Boolean '--------------------------------------------------------------------------------------- ' Procedure : VBComponentExists ' Author : Simon Lloyd ' Date : 4/20/2011 ' Purpose : Check to see if a code module already exists. If it does, and if the name ' is not equal to the current module name (which will result in shape ' hyperlinks being broken) then set the sModuleRename variable to the ' current module name ' Variables : VBCompName - pass a string name to check if it exists ' : VBProj - otionally pass a specific project (workbook) to look in '--------------------------------------------------------------------------------------- Dim VBP As VBIDE.VBProject If VBProj Is Nothing Then Set VBP = ActiveWorkbook.VBProject Else Set VBP = VBProj End If On Error Resume Next sModuleRename = vbNullString VBComponentExists = CBool(Len(VBP.VBComponents(VBCompName).Name)) If VBComponentExists And VBCompName <> VBP.VBE.ActiveCodePane.CodeModule Then sModuleRename = VBP.VBE.ActiveCodePane.CodeModule End If End Function

Copy, paste, run CreateTOC. Done.

NB: There is a link in the comments section of the code created from ShortURL.com, to which the full link is:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3515-Excel-VBA-to-create-a-Table-of-Contents-TOC-summary-sheet.html

This was code written by Dave Brett (brettdj) which is just as functional as this (unlike most other code out there to create a Table of Contents), but his differs from this functionally in that he used VBA to write code to the worksheet itself as an event, which is quite ingenious.

Many thanks to Simon Lloyd, Ken Puls, Anne Troy, Chip Pearson, for helping with pieces of code, testing, and inspiration.