Help with Macro call tracker by adding a date option.

[CalvinM]

Hi everyone,

I have searched all over the site and cannot find anything similar to this but please forgive me if this is the case.

I'm fairly new to VBA and have a very limited knowledge, I have built a macro button call logger with information I have found online. Essentially, the user(s) open up their workbook and are shown a few macro buttons on the sheet, depending on which button they click a simple tally runs in the background.

The below code is what I have duplicated many times over for all the options I need, this will just keep adding 1 to B3 each time the button is clicked for example.

Please Login or Register  to view this content.

A2 to A57 have the data we wish to record against, B2 to B57 has a simple tally.

This has worked well for the last 6 months or so however I am now looking to log these entries based on the date.

What I would like to have is:

Column A to stay the same and in B1, C1, D1 etc. I would have dates (22/05/18, 23/05/18 and so on).

When you click the relevant macro button, I want it to check the system date against B1, C1 etc and then input the results under that date. (B2, C2, etc)

Is this possible? I have spent the last few days googling this to no avail, any input or guidance will be greatly appreciated..

Thanking you in advanced.

Calvin

[dflak]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[CalvinM]

Thank you for your reply, I have attached two stripped back version of the workbooks one with how it currently works and the second of the proposed changes.

The current design is simple and most likely flawed, the button click simply enters a +1 in the column A, then on the data sheet it pulls that data out so you can view it.

What I would like the system to do is check the date when the button is clicked and then enter it in the data tab under the correct date.

Do you think this would be possible?

Thanks again.

[dflak]

I am going to throw a lot at you here, but if you take it one piece at a time, you should be able to follow it.

First of all there is the requirement. You have to keep track of dates and when things are added and add dates as the calendar advances, etc. That’s a lot of work to do. But it is something Excel already does well with pivot tables – IF you can get the data in a suitable format.

My design philosophy is to make “basic” Excel do as much of the “heavy lifting” as possible. So rather than do all the work in VBA, we’ll use VBA to collect data into an Excel table and use the table to drive a pivot table.

My apologies. I realize that this is a totally different design concept than you had.

I mentioned Excel tables. Once you start using them, you’ll wonder how you ever got along without them. Tables have many advantages, but the one most useful here is that tables “know” how big they are. They know how many rows they contain, so you don’t have to guess how many rows to include in a formula.

As tables grow, formulas, pivot tables and charts that are based on the table grow with it.

Here is more information on tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

I decide to track the following items in the table:
- Time (Actually Date Time) the record was created
- Date - the date the record was created
- Type – either Return Contact Request or Unable to Log in Online
- Person – the Windows login name of the person who clicked the button

The only things we really need are date and type, the other two items are nice to have and easy to get.

The other design consideration I use is if I am doing the same thing over and over again with minor differences, I’ll make a macro to do it and pass the minor differences on as parameters.

In this case, the only difference between logging a return contact or an unable to log in is the type. The time, date and person are the same as well as figuring out on what row to put the record. So I wrote a macro called LogData that does all this, and pass the type (DataType) to it so when it comes to that part in the code that needs it, it uses to passed parameter instead of a fixed value.

I re-made the buttons you had and called them ReturningContactRequest and UnableToLogIn. This way I can tell what code refers to which button more easily.

The code is very simple

Please Login or Register  to view this content.

This is how you call a subroutine with a parameter. If the subroutine is set up to need more than one parameter, separate them by commas. The LogData “logic” is applied to the string passed to it.

There’s a subtlety here: if you decide to track a third kind of event, you are already set up to do it. Just call LogData with the new string.

Now to LogData itself

It’s always a good idea to start any module with Option Explicit. I'll include instructions in the next post.

It is always a good idea to tell Excel where you want it to be. Your idea of active sheet or active cell might be different from where Excel thinks it is.

I like to use what I call “pointers” to Excel sheets. They are declared as a worksheet and they are “set” somewhere in the program. One of the advantages of pointers is that they can be “switched” to point to different sheets by the code. This comes in handy in advanced coding.

Also sheet pointers know what workbooks they are in. This is very handy when you have more than one workbook open.

But mostly, they save typing. It’s easier to type shD than it is to type Sheets(“Data”) everywhere it is used in the code. Besides, if I decide to rename the sheet, I only have to change the name once in the code.

I recommend you get in the habit of using sheet pointers (there are other kinds of pointers too).

In this case, shD points to Sheets(“Data”).

The next thing I want to do is figure out on what row I want to enter the next record. This is one row below where the existing data is. Normally this is done with the formula:

Please Login or Register  to view this content.

This formula gets the last row with data in column A. It’s the same as going to the last row on the spreadsheet and pressing CTRL-Up-Arrow.

However, I’m using a table. When there is no data in the table, the row below the headers appears to be blank. But it isn’t – Excel knows that there is a table there. So the formula thinks that row 2 is occupied. That is the reason for the if statement. I check to see if there is data in row 2. If there isn’t then row two is where I want to start entering data. Otherwise, if row 2 has data, then the formula works.

Then it is a matter of making cell assignments. Notice how each formula starts with shD. I’m telling Excel, I don’t care where you are when this code is running, this work will be done on Sheets(“Data”).

There are three ways I could have addressed the cells:
- shD.Range(“A”&LRowD)
- shD.Cells(LRowD, 1)
- shD.Cells(LRowD, “A”)

They all do the same. I think the third incantation is the easiest to read.

The very last part of the refreshes the pivot table so you don’t have to do it manually.

Running this code adds a row of data to the data table. I also “dummied up” some dates in this table for the sake of testing. Make sure you read the tutorial on tables to make sure you clear out this test data properly when you are ready to go live.

The pivot table has Type in the Rows section and Date in the columns section. I had to get a count of something, so I chose Person. As dates are added, they will be added to the columns to the right. If you started to track a new Type, it will appear as soon as it hits the database.

The Grand Totals do not have to be displayed if they don’t do anything for you.

[dflak]

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

[CalvinM]

Oh wow, thank you so much for all this effort!! I will look into this and feedback my results.

[CalvinM]

Hi there,

I have had time to go through what you sent and firstly thank you so much for taking the time to explain everything, it really helped me.

I have achieved exactly what I wanted so thank you again for making it so easy & painless!

You are right, using tables are so much easier than what I was doing before hand.

I cannot express enough how thankful I am to you.

Regards

Calvin