Call Tracker - VBA Coding: Input Screen To Auto Clear The Fields Once The " Save " Button
Oct 24, 2009
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)
Making a mission tracker that requires less upkeep than my units previous methods. I am using Excel 2007 and have some things in mind that I would like it to do. On the down side, I have only basic experience with excel and have never used macros or VBA. Even if what I am after could be solved with functions (which I prefer), I simply do not quite know how to make what I want without assistance. Ok, now on to the description.
The mission tracker aside from showing a list of the missions we have done during our program, it needs to also generate metrics automatically. In my worksheet, I am using columns A-G and rows 1-501.
The mission scheduling type in Column E are listed below. The letters on the left are what I am using in the sheet, the part to the right is what it really means:
Column D has the day of the week listed as: Mon, Tues, Wed, Thurs, Fri, Sat, Sun.
now the metrics I need to be generated are the number of different types of each mission compared, how many of each type of mission was flown per day of the week, how many of each type of mission was flown per month.
Basically what I need is a way to automatically count the number of rows that meet a specific criteria throughout the range. This would require being able to check the value/text of multiple cells at a time and count the number of rows that meet that criteria.
[SOLVED] 1. The first part would be to have the worksheet be able to count the number of each type of the 9 mission scheduling codes throughout the range. I would be storing the count in new cells that would then be connected to pie charts.
[SOLVED] 2. The second part would be to count the number of each type of mission that was flown per day of the week. That would require searching both mission type and day of the week. All I would need is a working formula for 1 set, then I could switch mission codes and weekdays to match all the 63 possible outcomes.
3. Knowing the number of each type of mission flown per month. This would be checking the date and the mission type. The trickier part that comes up in my mind is that our programs span multiple calendar years, so it would have to track it by month and year, that way there is a difference between Jan 2009 and Jan 2010. The counted numbers would be used to make histograms. It would be nice if the chart titles could be automatic based on the dates inputed in the mission tracker. If I needed to choose a maximum time length for it to cover, I would pick 3 years.
4. I would like to figure out how to make an inputted line of data on the main tracker sheet to create a copy in the corresponding worksheets based on schedule type, that way a person could look at/print a specific type without having to sort the main list.
The actual goal is to make it where a person can enter the 1 line of data per mission (cells A-G) and the mission metrics update automatically after each mission is inputed allowing upper level supervision to have current information quickly whenever it is needed. Currently we need about a weeks lead time to get the information asked for on our current progress.
I want to be able to see the average rating for each employee.. Is there a way to put in the rating from one survey and have it automatically add it another cell and then have it clear the first cell?
Here is an example of what I would like to do: Say we get 2 surveys with one rating of 5 and one rating of 10... If I input the 5 into cell Q4 and have it automatically put into cell R4 and then it gets deleted from Q4 but saved to R4... and then I can add the rating of 10 to Q4 and it automatically ads it to R4 and clears Q4 and totals R4 to an average of 7.5.
This way, with each new survey I can just input each rating to Q4 and have it cleared for the next rating and then have an average rating of all the surveys in R4... we do not need to keep track of how many ratings we are getting, just the average rating.
I have attached the excel sheet if you would like to take a look at what I am trying to do..
I have a macro attached on Sample 01 that outputs a couple files after a certain date. The problem is the date that it choses is always "todays" date and I want to be able to change the date, the cell that I need to be able to change is cell c2. The problem is the rest of the formulas in the cells change to number format and don't stay in date format.
how to add code to a button. I think I've more or less solved my own problem with merging the data of two spreadsheets together (not as cleanly as I'd like of coarse), but the problem is the person I'm going to train to use this spreadsheet is not very computer savvy and this needs to be as easy as possible. So I want them to just be able to click 3 buttons and be done with the process.
I've have a tool/code that takes the screen prints of the active window (when I press F9) and pastes it in a word document.
But whenever I do copy some text or any other image and when I press F9 to take the screen print of the active window. I get the text pasted into the word, this is because the clipboard content has text first and then the screen print of the active window.
Instead When I press F9, I wanted the below to happen
1. Content from the Clipboard should be cleared
2. Take the screen print
3. Paste it in the word document.
Note: My tool/code already does 2 and 3. Looking for 1 alone, ,
this are function which button needs to be able to do after clicking on it.
• When the value of only one of Description or Unit/Price is entered, a warning message appears.
• When the message is confirmed, return to the entry status for the new data.
• When the values of both Description and Unit/Price are entered, save the values in the list of product (worksheet Product), and increase Product No. by 1 and erase values from NewProduct worksheet this is the screen how it should look but button which should be able to do all the thing above at once is called List Entry [url]
after creating this function all the values from the table product need to be update into another table which sould look like that: [url]
Is there anyone how could write code which will do thing above?
in the attachemnt is the excel file which shows what I have done so far, as i am not a good programmer,so I do not know if it's right
i have an excel workbook containing approx 20 worksheets. What I would like to do is make the workbook go full screen when the user opens the file and allow the user to save as into the same file path the workbook is stored when closed
Example: Order Number from VendorOrder Number from SupplierTracking Number from Supplier12345123451ZABCD13456134561ZBCDE134561ZCDEF14567145671ZDEFG1456714567
In these three orders, the first order nubmer has a 1-to-1 relationship with a single tracking number. In the second, there are two different tracking numbers associated with one number from column A. In the third, there is only one tracking number but three different lines of the same order number that need to be updated with it.
The question is, is there a way to line up data fields head to head even when there are variable inputs like this?
I have an excel sheet that has two text fields "A2" 2 digit (Month) and "B2" 2 digit (Day of week), both are currenlty in a text format. I need to force the users of this sheet to enter values in both of these fields prior to saving. Is there a simple macro that I can run to check these fields.
I have made an excel spreadsheet. The area with data exactly fills my screen.
When I send it to other colleagues, it perfectly fills their screen too. But for some colleagues the data area is either too big for their screen or takes up a small proportion of their screen based on their screen resolution. I would like the area with data to perfectly fill the screen of all people who will access the file.
I am thinking that I would do an automatic event so that I can change the settings upon opening the file. I have Excel VBA programming for Dummies which explains about automatic events but I have no idea what sort of sub procedure to use.
VBA script I can run on a worksheet that will clear certain cells when I input a fresh value into a separate cell please? For example: By entering a new value into A1, cells B1 and C1:C3 are automatically reset to empty.
I am trying to save some time converting a cut up inventory catalog number. Specifically focusing on the color code. I have the list of colors associated with their specific part number. The order cannot change or my sheet will mismatch.
What I want to do is convert the # in column A to the color code text in column b so that it matches the translated code in column C? For example
1 I want to tell 1 to = Polished Chrome anytime #1 appears in my column.
What formula should I choose to achieve that goal. As well, how do I know what data to imput into the formula wizard?
Sheet is attached.
I have Cut a few thousand items off the bottom of the sheet. I can apply to additional data left off.
I got all three dropdowns to be dependent on each other, now however, whenever I change the value in the top Input Dependent Box M39, I would like M43 & M47 to appear blank. I want these two cells to appear blank until you select the dropdown which will allow you to select your values of choice.