Posts by Pesky Weasel

    Re: Search for files !!!!


    major-b


    The following code will find all files in directory 'H:\' only, beginning with the word 'Book', and place them in a list on the active worksheet starting in A1.


    You will need to be specific as to where you want that list as the code to place it in a sheet control or a userform control etc differs greatly.


    Regards
    Weasel


    Re: Sort protected worksheet excluding blank cells


    Without seeing your data its a little hard to give you an concrete reason but heres one thing I noticed:


    With the VBA range you have used it is selecting data from A3 to the last cell in column AT that contains data. Is there data all the way down to the end in Column AT?


    If column A is the only column guaranteed to contain values all the way down you could try something like this:


    Code
    Range("a1", Range("a65536").End(xlUp).Offset(0, 45)).Select


    Regards
    Weasel

    Re: VBE6.DLL, trouble with "Left" function in Excel 2000


    I think I read somewhere once about Acrobat5.0 not playing nicely with VBE6.DLL & Excel2000 (no longer have 2000 so I cant test it).


    By any chance do you have Acrobat5.0 installed? If so, try uninstalling it and going with v4.0.


    Regards
    Weasel

    Re: Simple VBA question


    purvispisgah,


    Welcome to Ozgrid!


    Just a couple of pointers to help make your (and others) time here more productive.


    To help out other users (and Admins) please try to use more useful titles for your questions. I can think of about 500 simple VBA questions without trying too hard - something like 'VBA not equal to operator' would have made a much more meaningful search term.


    The second part of question is very far from simple - infact it sounds like it may be quite complicated. you will need to supply far mor information before anyone could even attempt an answer. eg. what file, what is the file format, what sort of manipulation do you mean, what code do you have so far....


    Regards
    Weasel

    Re: Bind Control to Query


    Barry,


    Thanks, project back on track. For any following this post this code did nicely:



    Took me ages to get it going cause I always get the dates messed up in VBA. My PC and my DB have all dates displayed dd/mm/yyyy but when using VBA you must pass dates as mm/dd/yyyy (damn Seppo dates).


    Regards
    Weasel

    Re: countA from a subtotal


    Kim,


    AFAIK you will not be able to achieve the desired result using permutation based around the SUBTOTAL worksheet function.


    An array formula will do the trick:


    {=SUMPRODUCT(IF(A8:A25=A2,1,0),B8:B25,IF(C8:C25=C2,1,0))}


    Notice the curly brackets {} , dont type these but use Ctrl-Shift-Enter when entering the formula and Excel will put them in for you.


    The above formula works regardless of whether you have clicked the AutoFilter button or not, but it does use your advance filter criteria.


    Regards
    Weasel

    Re: Bind Control to Query


    Barry,


    I have been looking all afternoon for a non VBA solution to this and have not found one, so yes, I think I will have to use the approach you have described.



    I am fairly new to Access VBA but I'm going to have a stab at this (will test later after dinner :) ), any suggestion to make this more efficient would be welcome:



    Thanks for the reply
    Weasel

    Re: Mouse Trailers


    Mouse trailers would be set using the Control Panel in windows.
    Doubtless this could be turned on and off using API calls but I would have to research more.


    Regards
    Weasel

    Re: Variable Chart...


    Oeste


    What you are thinking of there is INDIRECT.


    You can set a charts Source Range using INDIRECT but unfortunately it only works once as excel resolves the forumla into and address and uses that.


    This could be accomplished using VBA however. What would be the trigger factor that would cause the chart to change, ie. in the example below anytime the worksheet calculates and the cell A3 changes the chart also changes.



    Regards
    Weasel

    Re: Sort protected worksheet excluding blank cells


    Nick,


    Rather than declaring the range as a fixed range you could use one of two other methods.


    1st: Select only those cells that contain data as you range


    eg:

    Code
    Range("A2", Range("A65536").End(xlUp)).Select
        Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal


    This code will sort all values in column A where Row 1 is the column header.
    See here for more help on VBA ranges


    2nd: Use a Dynamic Named Range


    eg:

    Code
    Range("myRange").Select
        Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal


    Where myRange is a Dynamic Named Range


    Kind Regards
    Weasel

    Howdy All,


    I have a form that contains a number of unbound text boxes. I would like to fill the values of the text boxes with values returned by a query but the text boxes DO NOT refer to query fields - rather they refer to records retuned by the query.


    eg.


    Query returns 10 records of [field1], [field2], [field3] where [field1] contains numbers 1 through 10. I would like the textboxes to show the values in [field2] based on the value in [field1]. So txtbox1.value = [field2] WHERE [field1] = 1



    This kinda sounds like a SELECT in SELECT thing, maybe, sort of...


    Hope this is clear....


    Regards
    Weasel

    Re: Setting Location of Programmatically added Controls by Cell


    Martin,


    Code below will recursively name the command buttons as they are created.


    Regards
    Weasel


    Re: Conditional Date


    Earlman,


    In your last reply you state that the value of A1 returns to 0 after the formula runs. Does this mean you have a macro? Formulas dont actually run, they just are - if you change a dependancy all formulas that refer to this change also.
    If you have a macro, please post a copy of the code (hit Alt - F11 to see the VB Code Debugger)


    Regards
    Weasel

    Re: insert spaces or tabs when inputting data


    Alfred,


    No, you are right - a macro will not fire while in edit mode in a cell.
    your question was a little unclear as your example is all single spaced. Where do you want the 5 spaces to appear in your text string?


    Regards
    Weasel

    Re: Template file in main directory for New file


    RAF,


    For 2003 - If Office is installed on C: then save you file as a template in:


    C:\Documents and Settings\Administrator\Application Data\Microsoft\Templates


    NB - I always log onto windows under the administrator login - change as required


    Regards
    Weasel

    Re: lookup function in vba


    While the macro is in debug mode try hovering the cursor over each of your variables and the values of each should appear in a 'comment' type box.


    If you can pinpoint which of the variables is not being parsed correctly it will be easier to identify why it is falling over.


    Regards
    Weasel

    Re: Conditional Date


    Earlman,


    Welcome to Ozgrid.


    It may be easier to help you if you post an example of what you wish to do in a sample excel spreadsheet.


    Regards
    Weasel