Posts by Pesky Weasel

    Re: Stopwatch


    runner,


    the attached example has a rudimentary records sheet built in. it stores the times as 3 seperate entries minutes, seconds and 1/100ths (would need hours for me)


    check out the code and have a play with it to make any changes you may need.


    regards
    weasel


    ps. show me a happy runner and i'll show you someone with a 'thing' for pain!

    Re: Paste-Special Number Like 123,456,789 Goes Into 3 Cells


    Brian - feel free - post as often as you need :)


    In order to determine if a macro has changed a setting it would be handy if we could see the code.


    I am unable to duplicate your results using XL2002 on Windows XP, what Paste Special Option are you using?


    Cheers
    Weasel

    Re: Auto Filter Probs


    gazman,


    I moved this post to the Excel/VBA forum. The lounge is just for shooting the breeze.


    Yes there is a limit - only the first 1000 records will be displayed by the dropdown.


    Cheers
    Weasel

    Re: Save Worksheet & Print Automaticaly


    No worries, just a minor change required:


    assumes 2 named cells, one for each half of the name



    Regards
    Weasel

    Re: Linear Programming


    This can be done using the solver addin. Do you know what the correct answer should be? Are you trying to find the largest value of Z - I get 96.25? (i slept, doodled & tried to score during math at school - much to my chagrin now)


    Regards
    Weasel

    Re: Textbox error


    gtrujillo


    I dont really have the time to set up a test bed for this but try:



    Regards
    Weasel

    Re: save sheet & print automaticly


    Mat,


    We dont encourage people to send answers by email - mainly because that information is then restricted to the parties involved, thus making the forum 'information poorer'.


    The following macro assiged to your command button should do the trick:



    Note:
    I named the cell containg the clients name 'clientname'
    If you invoice template was blank to start with then it will stay that way as all changes are saved to the new file.


    Regards
    Weasel


    [edit] forgot Close :)

    Re: From Worksheet to VBA


    Cameron,


    This code in the Workbook module should serve the purpose for option 2. Please note I included and extra named range 'lastclear' which contains the date that the range was last cleared, I also named F10 'firstday'.


    Regards
    Weasel



    [edit] - i did not read your formula and did not notice the time part :) this code is only tested with dates.
    [edit2] - geez I'm lazy, should have read code too.... there is no need to select a range to perform an action on it. while the loss of performance is negligable here it can effect more complicated macros. the line Range("Initials").Clear will suffice with no need to Select...

    Re: From Worksheet to VBA


    Cameron,


    What would be the trigger of this macro?


    The user clicks a button?:
    This is no problem and can proceed almost as written but relies on the user clicking a button to activate.


    Date is checked whenever the sheet is opened?
    You will need to include another cell that hold the month/date that the cells were last cleared so as to only run the macro when the date is equal to the first working day of the month AND it hasnt already been cleared.


    Regards
    Weasel

    Re: lookup an interval in a UNsorted column


    try array entering (Ctrl-Shift-Enter)


    =MIN(IF(A1:A11>C1,A1:A11,""))&", "&MAX(IF(A1:A11<C1,A1:A11,""))
    where:
    list is in a1:a11
    value to test is in c1


    please bear in mind that this returns a string - not sure what you will do with these numbers in excel but you may better off splitting them into seperate formulas if you need to use the values.


    regards
    weasel

    Re: work-around for pasting data into merged cells


    In addition to Roys warning, a suggestion:


    If you must have that merged cell look to pretty things up, try using Format Cells/Alignment/Center Across Selection. Highlight the range of cells you want to center across (with the text being in the first cell), then apply the formatting. It looks the same but still allows sort,filter, cut/paste etc.


    nb: only works horizontally


    Regards
    Weasel

    Re: Search for files !!!!


    Glad you liked it!
    See code comments for relevant changes.


    Re: Sort protected worksheet excluding blank cells


    Nick,


    This is happening because your primary key field is also populated by formulas. For the previous methods to work Column A must contains the values only.


    Try the range selector

    Code
    Range("A2", Range("AK2").Offset(WorksheetFunction.CountIf(Range("A2:A65536"), ">""") - 1, 0)).Select


    Regards
    Weasel

    Re: using the hyperlink function under a command button...


    In the click event of the command button paste the following code.

    Code
    ActiveWorkbook.FollowHyperlink Address:="\\03-Server\data\database\Holding_Schedule_BM.xls", SubAddress:="'jobs'!a1", _
        NewWindow:=True


    Regards
    Weasel

    Re: Hide Headers On Every Computer not just mine


    Chris,


    I see other posted while I typed :) . To hide headers on all sheets in the workbook (and resatore them afterwards) -


    In a standard module:


    In the ThisWorkbook module:


    Having said that, usually not a good idea to play too much with the users environment unless absolutely necessary.


    Regards
    Weasel

    Re: Printing the contents of a ListBox


    Design a report that uses the query 'qryFilterResults' and then print the report after you have altered the query definition using:


    Code
    DoCmd.OpenReport "myReport", acViewNormal


    acViewNormal will send the report directly to the printer


    Regards
    Weasel

    Re: Creating a field to &quot;Tally&quot; existing fields in a table


    kernel,


    Access tables are unlike excel tables, formulas are not stored in data tables. These are for stroring the raw data, any calculations you want to make are performed with queries or reports. It is possible to use a query to calculate the totals and then add them to the table as a new field but there are no 'table formulas' as we would recognise tham in XL.


    A database is designed to store very large amounts of data to be used by one or (most often) more users. Resources would soon dwindle and things would slow to a crawl if the database stored not only the millions of records (or as many as you can fit into 2Gb in Access' case) but the formulas as well.


    Regards
    Weasel

    Re: Sort protected worksheet excluding blank cells


    Nick,


    In my second response you will see that I used the offset method with the range argument. This is because I am using column A to dictate how many rows down to go. Change the offset amount to match the number of columns you have in the list.


    Regards
    Weasel