Okay, so I wasn’t able to make a blog post on the 17th, (the real day of my 2nd ICT session of the week), and I did a bit of work today too (the 18th, the day after my lesson), so I’m going to compile all the work I did into this blog post. So my work the past two days was mainly spent on finalizing for the testing of the project. Here’s a list of what I did:
- Fixing up the UserForm submit button code – I (through close to an hour’s worth of repeated testing), was finally able to figure this out. Eventually, I decided to copy the code from Contextures: http://www.contextures.com/xluserform01.html After that, I modified it a bit and changed the names of some things so that it would work for my UserForm, and then it worked. I did have to tinker around with the whole thing though. The coding looks sorta like this:
It’s pretty long. It sure did take a lot of effort for me though. I tried to annotate it as best as possible, to make it as clear as I could how the coding works, and what I have learnt. Of course, I didn’t understand everything in the code. After all, I didn’t make it up from scratch. I added in the Option Buttons to select the type of customer. I couldn’t figure out how to make it so that if I selected a option button, it would appear on my Customer Info sheet, so instead, I used the coding that you can see in the annotated screen shot above. There was a need for basic VBA language knowledge though, as I needed to understand the code to modify it to my specifications.
This is what my UserForm looks like right now:
- Editing the Customer Info page- So another problem I tackled was finalizing my Customer Info page. This was the bulk of what I did. After much thought, I decided that I should include the type of customer (Adult/Student/Senior) (this is why I now have option buttons in my UserForm, as seen in the above image) and the price of the booking. I added these in for future reference when I make the mailings. So first off, I added in the new columns (type of customer and price). It looks like this now:
Adding IF statements for the Price was a bit difficult (at this stage, the IF statements always get pretty confusing). I wasn’t sure how to make it so that if the cell to its left (the cell which will have the type of customer in it) had the word Adult in it, it would say “180, or if Student, “120”, or if Senior, “100”. At first, my IF statement looked like this:
After I made the IF statement, I autofilled it until the 100th something cell (so that I could easily place the formula into the cell. Excel is smart. It changed the cell, E2, to E3 and E4 and so on for each corresponding cell). It worked, but there was a problem. The problem with this coding was that even though it would work, and the cell would change accordingly to the type of customer, whenever the type of customer cell next to it was empty (basically, when a customer’s details have not yet been added) the Price cell would have the word “FALSE” in it. This was because the IF statement had not been fulfilled. It looks like this:
At first, I thought I would just use Conditional formatting and turn the font white if the word FALSE was in the cell, but then I realized the real problem.
My UserForm works like this: It looks for the first EMPTY ROW in the sheet. If the word FALSE was in the Price cell, the system would count the row as filled, and not empty. Then it would keep moving down until the system found an empty row, which would make the customer’s info go all the way to the 100+ cells.
So I had to modify it a bit. After a bit of tinkering, I fixed it. The new code looks like this:
I basically just added in a comma at the end, and two quotation marks (“”). I added in a comma because it would change the formula so that if none of the previous IFS were true then “” (this is the universal sign for empty in VBA) would be used. That way, if no customer details have been added, and there was none of the words “Adult”, Student”, or “Senior” were in the type of customer cells, the price cell would be empty. This way, the cell would normally be empty, and the UserForm would not be affected.
- Testing Sheet – I added in a few questions for the testing sheet. Things like scenarios and sample bookings.
- Adding Buttons – Earlier on I realized a problem with my Customer Info sheet and Userform. I couldn’t do multiple bookings. If I wanted to book 5 people, I could select 5 spots, but only one UserForm would pop up. It would be tedious to click the book adult button for 5 spots, so I added in a few buttons to the customer Info sheet to go along with the navigation.
The two new buttons on the right will be used in adding Customer Details. The add info button will bring up the UserForm so that users can just select multiple booking spots, fill in the UserForm that pops up, and then go to the Customer Info page and use this button to fill in the remaining customer info.
The code for the add info was: UserForm4.show
I also couldn’t unbook customer details, so I added in the clear info button. To unbook, they simply need to click on any cell in the row that contains the customer they want to delete, and then press the clear info button. It will clear the entire row, deleting the customer info.
The code for the clear info button is: Selection.EntireRow.Delete
So that’s about it. Sorry about the huge blocks of text, but I had to explain in detail. Progress has been good. If all goes well, I should be able to send out the testings by Tuesday or Thursday. Although, there still lies the daunting task of making the Help system, and I have yet to add in the mailings (because quite frankly, I don’t really get it, and I don’t know how). The help system will probably be made in a word document, then turned into a PDF.