Posts by Gizzmo

    Re: Vba Control Add-in Functions


    Try putting a 5 second delay in your code between each sendkey see attached code


    Hope it helps,


    Gizzmo



    Re: Summarize By Month & Year to Date


    OK,


    Have found a solution through a work colleague (who wishes to remain anonomous!)
    and attached the file (without details referred to below) in case it is of use to any one else.


    I have also deleted my previous file as it contained details which I had not intended to be public.


    Simply change the date in Cell H4 on the "Report" spreadsheet and run "GenerateWorkOrderTotal" macro.


    Cheers,


    Gizzmo

    Re: Summarize By Month & Year to Date


    Quote from Dave Hawley

    I don't see why a PivotTable cannot be used.


    Hi Dave,


    If I use a pivot table how would I go about:


    1. Displaying Work Orders which do not have a Cost $ for the 6 months or YTD but do have an ITD Cost $.


    2. How do I calculate the the ITD Cost $ as well as the YTD Cost $ in the Pivot Table.


    Appreciate your advise.


    Cheers,


    Gizzmo

    Hi,


    First some back ground:


    I have data which I import from CSV files from a JDE application on a monthly basis, using "JobCostData" macro ( have not posted code here as it is contained in the attached file).


    For each month I import the data my macro creates a seperate spreadsheet and names the spreadsheet as the "mmm-yy" the data relates to, and then takes the information from each sheet and collates this on to on spreadsheet named "Pivot Data".


    I then run a second macro "MacroXX", ( again have not posted code here as it is contained in the attached file), this macro looks at the data on "Pivot Data" and creates a unique list of Work Order Numbers and Descriptions and copies them to the spreadsheet named "Report".


    Now what I would like to do (and can't get my head around):


    Once the "Report" has been populated I would like to come up with a series of total "Cost $" for each Work Order by:
    1. Month, for the past 6 months based on a Calendar Month End date (Note: For now I have hard coded the dates, but I will add code at a later stage to prompt user to select a date which will then write this to cell H4 on the "Report" spreadsheet and have the other dates derived from this date.)
    2. Year to Date (YTD) based on the Financial year the Calendar Month End Date falls
    3. Inception to Date (ITD) based on the


    Note:
    1. I have tried using Pivot Tables but this will not give me ITD totals.
    2. I would prefer to use a macro rather than formulas.
    3. I have populated the "Report" spreadsheet with the expect result based on the current data.


    Any help or suggestions would be much appreciated.


    Cheers,


    Gizzmo

    Re: Password Vb With Multi Users.


    Quote from 2001swimmer

    Never mind I figured out what the problem was how stupid I am.


    Thank you Gizzmo


    2001Swimmer,


    What was the problem in the end?


    Cheers,


    Gizzmo

    Re: Password Vb With Multi Users.


    try changing

    Code
    iCounta = Me.tbxGoes.Value 'set tbxUser visible prpety to false before using


    to


    Code
    iCounta = frmPW.tbxGoes.Value 'set tbxUser visible prpety to false before using


    Otherwise can you attach you file?

    Re: Password Vb With Multi Users.


    Quote from 2001swimmer

    Thanks Gizzmo
    It come up with a problem here

    Code
    iCounta = Me.tbxGoes.Value 'set tbxUser visible prpety to false before using


    To the second question I don't have an example. I wish I did. Sorry


    What was the problem, did it give you any error message? It worked for me.


    Also you haven't really posted an example rather it is the original code that you got from RoyUK example, and have simply changed the original passwords! the link you had was sufficient to see RoyUK code.

    Re: Password Vb With Multi Users.


    Hello 2001Swimmer,


    Try this, although RoyUK is a much better at this than I am.


    Code assumes you have two users but you could Dim multiple users and then adjust code accordingly.


    Hope it helps.


    I am not sure about the second part of your request can you post your example.


    Cheers,


    Gizzmo

    Re: Enter Data To Multi Sheet Through Userform


    Hi LOG,


    Welcome to the forum.


    Sorry but your question is a bit vague and the attached file does not help. It makes it some what hard for forum users to help you. Can you please elaborate on what it is you are trying to do and provide a clearer example.


    Cheers,


    Gizzmo

    Re: Pivot Tables & Formulae


    Hi markporter,


    I believe you can tell a pivot table to select different field items to show based upon an input from elsewhere, the input from else where being a user form.


    If you where to create a user form with all the different field Items listed and a tick box for each then you could prompt the user to tick the items they wish to have the pivot table display and then click a refresh button on the user form which updates the pivote table.


    You have to question if this is the best way of doing this. But I have used user forms and vba to change what a pivot table displays before so the concept should work.


    Regards,


    Gizzmo

    Re: Paste Special While In Filter Mode


    Hello Joozh,


    Q. How is the selected range being selected i.e. with vba code or are you selecting it.


    Either way perhaps try selecting the visible cells of your filtered data first i.e. go to visible cells.


    From your comment "...and sometimes it also adds the x number of days to the date fields of the data which is not part of the filtered data..." it sounds like this because you have selected the entire range of filtered data and not just the visible cells in the filtered range.


    For example lets say the range is A1:A10 and A3, A4,A7 & A9 are dates, if you filter this range based on where they are dates etc. then cells/rows A1, A2, A5, A6, A8 and A10 will be hidden. If you then select A3 to A9 without going to the visible cells your are actually selecting A3 to A9, hope this makes sense!


    Also your post is a bit vague, perhaps give a clearer example, post vba code or attach a small sample.


    Cheers,


    Gizzmo

    Re: Closing Application And Userform


    Hi SuzieQ,


    If your file size is to big then perhaps you can post the code only, or try to post a sample file with little or no data to reduce the file size.


    Remember to use Code Tags!


    Gizzmo