Excel Spreadsheet Questions RRS feed

  • Question

  • I am working on making an equipment check sheet in which the person filling out the form inputs information in certain areas.  I would like to have certain text in a cell change color depending on the number inserted in the form (ie: Oxygen Levels), but I want the text in the form to stay (ie: On-Board Oxygen PSI, Oxygen Bottle PSI, Stretcher Oxygen PSI), and the number that goes and is placed in the cell coming from another cell to change color (ie: PSI between 0-500 would be red, PSI 500-600 would be yellow, and PSI 600-2000 would be green).  I have the cell (F9) formatted as follows: ="Oxygen Bottle PSI: "&I18.  

    How would I make the check sheet show up this color change [Question 1]?

    I would also like to add a window which pops up when you click on a certain cell so that you can input a quantity of what was found (ie: 4x4 Gauze which shows that 24 are needed) and then change the color of the text but also place the amount found along side what was supposed to be found.  How would I do this [Question 2]?  

    As you can see I have drop down menus to select either the ambulance, or crew riding and doing the check of the ambulance.  I also have check boxes that you can click on and have the box checked meaning the equipment is there.  The sheet works how I have it now, but I wish to make other improvements to make it easier to find out if the vehicles were checked and have what is supposed to be in them.  Also it would be easier to find out who is not doing the job of restocking the equipment.  The person doing the check sheet would be filling out the form at the beginning of their shift, and before restocking if needed.  

    I would also like to have a timestamp in which the person filling out the form does not see it, but a supervisor can look at the timestamp to see when the apparatus was checked.  How would I go about this [Question 3]?

    Would this all work if the file was uploaded to Google Drive, or OneDrive [Question 4]?

    Please answer all questions (four in total) above and any help with this would be greatly appreciated.  Thanks.  

    Image will follow once my account is verified.

    Sunday, March 10, 2019 2:59 PM

All replies

  • Hello,

    • Is this in VB.NET and if so what have you tried so far.
    • Is this not VB.NET e.g. VBA  as this forum is only for VB.NET and can be moved if not VB.NET.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    profile for Karen Payne on Stack Exchange

    Sunday, March 10, 2019 3:11 PM
  • This is VBA, not VB.NET.  As for question 1 I tried ="Oxygen Bottle PSI: "&I18, but was unable to get the number from cell I18 to change color unless I made another row with just the number.  I used conditional formatting to change the text color.  It works well, but I was looking to see if there is a way to use VBA and to have the formula write Oxygen Bottle PSI and then the number.  I would like to have the words Oxygen Bottle PSI in black and then the information from cell I18 to change color depending on the number that is being inputted (ie: if the PSI is in between 0 and 499 it would be written in red, if the PSI is between 500 and 599 it would be written in yellow, and then if the PSI is between 600 and 2000 the number from cell I18 would be green in color.  

    Question 2 I do not know at the moment how to get a box to come up when you click on the cell that you wish to input the amount of supplies that you found when checking the ambulance equipment.  

    Question 3 I am not sure how to get the spreadsheet to make a time stamp on when someone opens the file and then inputs the information needed to complete the file.  

    Question 4 I believe that OneDrive would work better as it appears to be using the Excel program that is on the computer and not a web version of it which does not allow VBA or Macros to work.  

    Please reply as soon as possible.  Thank you.

    PS:I do not know all the codes that can be done within Excel, but I know that it is a very powerful program and can do many things.  If you can let me know where I can find all the codes (VBA, ASCII, functions) it would be greatly appreciated.  

    I have Excel 2007 version.
    • Edited by MedicJimK Sunday, March 10, 2019 8:21 PM add information on version of software
    Sunday, March 10, 2019 8:11 PM
  • Hi

    This is a forum for questions related to VB.NET and not for questions related to VBA - you will need to find a VBA/Excel forum for your questions.

    Regards Les, Livingston, Scotland

    Sunday, March 10, 2019 8:26 PM