Call tracker - VBA coding help

[dimitrz]

Hi There

I have created this basic call tracker with the help of one of my colleague

Currently we are stuck at 5 place , any help with the coding would be much appreciated

need help on

1) We need the Input screen to auto clear the fields once the " Save " button has been pressed and data transfered to the appropriate tracking sheet

2) We want to ensure that if any of the Red cells are empty the " Save " button will give an error ( one complication If the "Transfer Type" highlighted in Blue has an entry " either STD/ISD/ESP " then " Call Purpose " needs to be mandatory

3) somehow the information on the "Comment " field is not getting captured

4) If possible we want a warning indicator if two rows in a tracking sheet ( example London ) are the same based on Coloums B to I ( except C )

5) Is there any possibility to give a pop calender in the " scheduled date " section of Input screen ( Row 33 )

Any help with any of the codes is much appreciated

I have uploaded a sample file with the codes for reference

We are not experts in excel so please tune your answers accordingly

We are using Excel 2007 ( some machines which will access this file may have excel 2003)


Thanks in advance

Due to urgency in needing an answer ( working on tight deadline ) I have cross posted this at two more locations -

http://www.mrexcel.com/forum/showthread.php?p=2099848#post2099848

http://www.excelforum.com/excel-programming/704566-call-tracker-vba-coding-help.html#post2186620 (http://www.excelforum.com/excel-programming/704566-call-tracker-vba-coding-help.html#post2186620)

[lucas]

All seem to be fairly easy to resolve. I see you started a userform but abandoned it. Why? This would be much cleaner from a userform.

[mdmackillop]

Give this a try. You might want to consider keeping most recent data at the top of the logs. Note that the code writes concatenated values into Column S to test for duplicates.

[mdmackillop]

Alternative version

[dimitrz]

Thank you so much mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87) you are a real gem of a person

I am testing the tracker - will get back after that

[dimitrz]

lucas (http://www.vbaexpress.com/forum/member.php?u=223) to answer your question - This is not the end product .

Brief background : this file needs to be used by around 10 members of my team who get call every other sec .

previously I had seperate workbook like the LA / Liverpool sheet as a shared version - which started crashing on a more frequent basis after we upgraded to Ms office 2007 :doh: ( previously crashes used to be once a month after the upgrad it became once every 2 days and the files were unrecoverable)

So the plan is to have separate files like above sample for each person and then the master workbook will periodically (say every 5 min) extract data from it - thereby eliminating the tracker crashes.


Since thats the end product - we though having a out and out user form might be too complicated and difficult to modify in future. :)

[dimitrz]

Dear mdmackillop
The tracker and your codes works marvelously (thanks once again) :bow:

Few observations and queries after my initial testing

1) Is it possible to ensure cell B7 is not cleared when the “Clear command button” is pressed (also I plan to lock the input sheet (keeping the inputs cells open)- locking it currently gives an error when the “Clear command button” is pressed.

2) Instead of having a separate “Clear command button” can it be auto configured so that the moment the “Save button” is clicked the data in the input boxes clear off ( however if the Save button offers a prompt example " Red cells not filled " then the data should not clear off)

We tried copying the “Clear command button” code into the Save command button - however if the save is not successful and a prompt comes - the entire data goes off - which means that the employee would have to refill all the fields from the scratch.

Ideally this will also reduce the need to check duplicate entries , or in another way is it possible to give a prompt when a duplicate entry is made and the entry will be saved only if that prompt is accepted example " This is a duplicate entry - do you wish to proceed - Yes or NO" if
3) it possible to Loop " Telephone Number " F16 along with Call purpose cell C22 ( so that if STD / ISD / ESP is entered ) then the telephone number in cell F16 is also mandatory as is cell C22

4) I just realized that fields C22 (call purpose) F12 / F14 Caller designation is not getting picked up - I forgot to give a column for it - can they be inserted now without effecting your coding
We realized that you have replaced some of our schoolboy coding with more seemingly complex but short codes - so now we are a bit concerded that any tampering by us might effect the coding done by you.

5) An auto save workbook function be incorporated in the save button - so that when we click SAVE it also saves the workbook in the same process ?

6) F33 somehow doesn’t seem to be getting captured in the tracking sheets
These are the only issues we found with the now otherwise awesome tracker thanks to your help. ( we used your Alternative version file for testing)

Ps : is it a good idea for the new row to come on the top row pushing the other entries down – instead of simply coming at the bottom row ?

[mdmackillop]

Try this version. I think it answers your points
The sheet is protected to allow tabbing between cells to be completed. There is no password.
Re your PS, it's personal preference. If you want new data at the end, it's easily done.

[dimitrz]

Try this version. I think it answers your points
The sheet is protected to allow tabbing between cells to be completed. There is no password.
Re your PS, it's personal preference. If you want new data at the end, it's easily done.


Thanks once again mdmackillop

GRIN :-) guess you missed out to upload the file :)

[mdmackillop]

So I did!

[MZLZL]

Gooooood job

[dimitrz]

Gooooood job
Yea really a good job - Malcolm I have modified my real tracker based on your sample.
Two days of test has shown that its working great. :)

Thanks - we can call this project officially closed now. :)


------------

Ps: Need to take your advise on one small thing - though its not actually in your domain as a VBA code and due to that does not belong to this forum.

previously my team used to use a common shared tracker without any input form ( directly in LA or London tracker)

Because of which they had an advantage - where instead of actually selecting a item from the dropdownlist - simply typing the first couple of characters would self populate the cell ( since it was already entered in one of the above rows)

naturally thats not possible now since now they have a standalone input form

is there any possibility in excel to get that function in an input form like the one we are using

Eample

we have a drop down list (designation) with entries like Partner, Manager, staff

Is there any way for the words to auto fill based on the defined list when the first two or three charters are typed

eg if I type " Pa " the cell will prompt me "partner"

[mdmackillop]

Use an ActiveX combobox instead of Data Validation. Set the ListFillRange and LinkedCell properties (to the cell underlying the combobox), and the KeyDown macro as shown for the Designation items. You'll need to add these to the Cell Clearing code as well.

[dimitrz]

For someone who wants to use a similar function /tracker- there is a standalone yet related issue for which I have created a new thread

http://www.vbaexpress.com/forum/showthread.php?t=29058 (http://www.vbaexpress.com/forum/showthread.php?t=29058)

New thread is created as it can also be a standalone issue - thus helping more people with similar issues.

Presume this is in tune with forum rules.

[Anomandaris]

I have no idea how this got posted here, this site has been giving me a lot of trouble, I was able to post this on my 3rd attempt and not surprisingly in the wrong place

[dimitrz]

welcome to the 20th century technology :)