Posts by XLDave

    I don't think is going to suit my needs

    Hi Andy

    I have a feeling that I am barking up the wrong tree - excuse the pun based on your old Avatar :)

    My problem is that I somehow need a cell to recognize its contents and then display the appropriate help text.

    For example ANT001 thru ANT012 inclusive are all antennae. The accompanying text would be

    [Antennae location] | [Mounting height]


    ANTX01 thru ANTX01 is an external antennae and the text would be

    [Antennae location] | [Mounting height] | [Mount type] | [length]

    To a trained user the difference would be second nature but to someone less experienced it's a nightmare. They have to keep looking it up and this takes time - and money!

    Is it possible for the cell contents to be recognized and then display the appropriate help text?



    by the way

    Because certain parts are grouped ie have the same description text I was hoping to be able to group programme the balloons to save time. Would that be possible?


    Comment box

    I had considered the comment box but there are just too many comments and the box would be huge!

    I was thinking of something that would recognize a particular part number and display the appropriate balloon. For example

    ABC001 = [Location] | [Height] | [To view]
    PMR023 = [location] | [fitted to] | [height] | [ambient temp] | [temp threshold]

    I have never programmed these balloons before.


    Hi all

    I am trying to make a sheet more user friendly so that people with less engineering experience can use it.

    The sheet simply looks up an engineering part and auto loads the associated hours for its fitting and the part cost. That part works fine but there also needs to be some accompanying text that tells the engineer where this part is going, what it is being fitted to, etc. Experienced users can enter this data without looking it up in a technical manual. For example, they have enter the data like this

    [Location] | [Fitting height] | [Orientation]

    or like this

    [Height] | [Weight] | [Distance from centre] | [Radius]

    Those are just two examples and there many more.

    Could I use a help balloon that would display these prompts when the user hovers their cursor over the part number? Better still would be active text that brought up an associated user form / script box that asked for the related parameters and then filled them in the sheet.

    The balloon would be a good starting point.

    Thanks in advance


    This is the fix

    Hi all

    The fault was in VBA as used by Office to control all of its applications. I don't recall changing any code or using any rogue code but the fault was definitely there.

    I had to remove Office, edit the registry, rename my old office folder (in case I had data I wanted to stay) and then reinstall office - I changed the new install path by adding XP on the end.

    This worked and I now have a VB editor I can use and a workbook with functionality. If anyone needs to know the full registry keys then send me a PM or reply to this post.

    Thanks to all that offered help.


    Woh is me :(

    I tried the detect and repair option - that was the first thing I thought of if I am honest. That didn't work so I removed all Office software including Excel and that also didn't help.

    If I try and use the VBE then then excel just closes on me. This applies to the sheet mentioned and to ALL NEW sheets.



    I have up to date anti-virus software and a good firewall. I have run a scan and there is no virus to be found.

    Is there a way of removing VBA / VB editor and re-installing?

    Please don't say I have to re-install Win XP :mad:


    It must be a VBA problem

    It must be linked to a VBA problem on my PC. If I hit ALT+F11 then Excel closes instantly exactly the same as when I try to call the user form. The sheet is fine because it works on two other PCs.

    Now I really am lost. :(

    Any suggestions?


    I have a multi sheet workbook with a user form designed to aid the input of a client's details. There is a simple button that calls the User Form allowing you to enter details that are then transferred into the sheet.

    I can open the workbook, enable macros and everything is fine. As soon as I click on the button to call the User Form, Excel shuts down totally??

    It has never done this before and now I am totally lost. Have removed Excel and reinstalled it but to no avail. I was looking at the VBA editor earlier but to the best of knowledge I didn't change anything.

    Is there something in Win XP that I can use to check for errors or error messages?



    PS The original user form and code was written by Dave Hawley and has worked fine for 2 years!

    I have a sheet that looks up the associated labour and part cost of a product. I have a sheet with all the parts listed and as I cost a job I enter the part numbers and the quantity required.

    I use this formula on the costing sheet


    Which has worked fine until now. My problem is that a lot of the new part numbers entered on the system are 4 digit numbers.

    On the costing sheet,

    Column A is the part number
    Column D is "I" as in install or supply
    Column E is the quantity required
    The above formula is entered in Column S and finds the relevant hours. Other columns are just for text and description to aid the engineer.


    This formula finds the product cost.

    On a separate sheet I have all the products

    A = part number
    B = labour hours
    C = Part cost
    D = part description (On the cost sheet I use =IF(A9>0,VLOOKUP(A9,DataTotal,4,FALSE),"") this is column Z and is just a visual aid.

    Because the part numbers are just 4 digit numbers then Excel returns #N/A.

    My brain is having its usual off day and whilst I think it is a formatting problem I cannot think how to get around it.

    Any help welcomed



    A simple time sheet is needed so that we can keep a tally of subcontractors hours. They are all granted 45 minutes for lunch which are unpaid and this is agreed in their contract. They are allowed to work any hours they wish provided they accumulate a miniumum 37.5 hours per week. I guess you could call it flexi-time for a contractor. We find it works really well and the workers are happy and very productive.

    I wanted to compile a simple multi-book spreadsheet for the office administrator. It would just be a monthly sheet with the days, start time, end time etc. My problem comes when I need to deduct the times for the breaks. Most people start at 08:00 and finish at 17:30 but we do have others who start at 07:00 and finish at 19:00. The start and end times vary but that it okay and we can collect that data from their clock cards. When I try and set the sheet to automatically deduct the minutes for their break I get an error.

    I was going to convert the times into minutes etc and then subtract but is there a simple way? I don't want to deduct 45 minutes from their end time because this could lead to clerical typos etc.

    I know there is a way to do this but my brain isn't in the mood today??



    I have a user form for entering client data. When they have finished the user clicks enter and the form closes. Could someone help me with extra code to bring up the "Save As" dialog box when the Enter button is clicked? This will mean that they should save the new sheet successfully each time.

    All guidance appreciated.


    Hi to one and all

    I'm back after a personal tragedy - good to be back to normal...if I ever was normal :o

    I am using a workbook to create reports and it works fine. It saves me hours of writing them out by hand. I have one annoying glitch and it is mainly related to Windows as opposed to my humble opinion that is! When I save a report the Save As option always defaults to my desktop. I am guessing that is because I have the template on my desktop?

    Can someone help me with some VBA code that will change the save routine to C:|mydocuments|davesreports|2003

    All help greatly appreciated.


    Hi Oggie

    Have a look at the attached sheet. It does just about everything you want with Football stats.

    I didn't write it, that honour belongs to Michael Way. Click the link and check them out.

    If the attachment doesn't work then visit the site and download one. I know that OzGrid has problems with zipped attachments.
    EDIT: I have mailed the sheet direct to you


    It isn't impossible but it falls into the category of "doing two things at the same time". It will also depend on how you draw the number 6. If you start at the top of the six and come down to then form the loop it makes it difficult. If however you start with the loop and draw it in reverse then it's easier because your hand and foot are moving clockwise.
    If you practise the foot circle long enough your brain will do it almost automatically thus allowing you to use more thought on drawing the number 6.

    Here's another one for you.

    Sit or stand with your arms straight out in front of you.

    Form a fist with both hands and then extend your thumbs out so that they are parallel to you body and the ends are touching each other.

    Draw clockwise or anti-clockwise circles in the air using both thumbs by pulling and pushing your hands away from you. i.e. you must keep your arms out in front of you at right angles to your chest with only your elbows bending

    Now try and make one thumb go clockwise and the other anti-clockwise.........................difficult but not impossible.


    PS Don't try this in the office - your colleagues will think you've lost it.

    I am just about to leave for a trip out with my kids.

    COUNTIF uses the following syntax

    COUNTIF(range, criteria)

    eg COUNTIF (G3:G88,"yes")

    If you have Excel XP then try using the Conditional Sum Wizard. It's great because it allows multiple criteria by using the "add condition" option. I say Excel XP but it may be in earlier versions - not sure though?

    Catch ya later