Posts by AndrewJ

    Re: A Macro To Open A Specific Workbook Based On The Cell Value In My Current Workboo

    The way that macro is set up, is when a value is typed into cell B2, and the 'enter' key is pushed, the cell accepts the value, and then the worksheet_change event (the worksheet_change macro i posted) get automatically triggered. It will be triggered every single time the value in cell B2 changes.

    If you want to change it so that no workbooks are opened until a user clicks a button, just change the macro to

    Sub OpenWorkbook()
        Dim varCellvalue As Long 
        varCellvalue = Range("B2").Value 
        Workbooks.Open "c:\Program Files\Excel\" & varCellvalue & ".xls" 
    End Sub

    and then cut and paste the whole thing into a new module (in the VBE, go to insert, then new module). You now should not have anything in the worksheet modules, and the macro should be in the module that got named 'module1' when you inserted it.

    Then, you can use the drawing toolbar to create a shape (rectangle, circle, etc). Right click on the shape, and go to assign macro, then choose your macro from the list (there should only be one macro listed).

    You can also use the control toolbox bar to add a commandbutton and then assign a macro to it.

    Re: A Macro To Open A Specific Workbook Based On The Cell Value In My Current Workboo

    Just read your post again, sounds like you may have a ton of workbooks to open based on the number typed in, if you have one workbook for each item in your business. You can then set a variable to the number typed in the cell and use that to open the workbook, so you wont need a hell of a lot of elseif statements.

    Note tho that this will error if a user types a number of a part that doesnt exist. You can add

    On error resume next

    to ignore the error, and if the workbook doesnt exist it shouldnt do anything.

    Re: A Macro To Open A Specific Workbook Based On The Cell Value In My Current Workboo

    So you want the workbook to open after the user enters a number into B2?

    if so:

    Put that in the worksheet module for the worksheet you're using. Hit alt-f11 and double click sheet1 under microsoft excel objects.

    And you can add additional elseif statements for more workbooks, and of course change the 1111 and 2222 and the workbook's path and name to your specific stuff.

    Re: Chart Menu Bar Wont Change To Data Menu Bar

    follow up - The array I was using, I was starting with '2' as the first menu bar to enable, which is actually the worksheet menu bar WITH the chart replacing the data menu. Use the array from 3 to 117 in all subs to avoid turning on chart when you dont want it.

    btw, sorry to bump this but my first 'solved' post was pretty unclear about the problem, I ran into it again in another instance and looked this thread up

    Re: Complete Statusbar Progress Meter

    Very welcome :)

    I've used this site so much for help with excel and vba over the last year or so its not even funny, time to give a little back when I can ::D

    btw, a little tip on the use for anyone who might need it - if you're formatting a lot of rows, you can set lower to the starting row # and refer to "theloop" in your cell statements "Cells(theloop, 1)", which will perform your main loop code on every cell in column A from the value of 'lower' to 'upper'.

    Re: Complete Statusbar Progress Meter

    and of course, you can use the exact same calculations and simply change the output to something else besides the statusbar, say cell A1 or a userform ;) Cant say how fast that is tho

    Hey, so I recently have been using this in my projects, as I do alot of sql inserts/queries and lots of list formatting where the lists are 50k+ records. I added a ton of comments so hopefully my weird naming scheme will be intuitive for anyone trying to understand it. As it is you can start and end the loop at any row (or any loop values) and it will accurately calculate:

    - the record # out of the total the macro is on
    - total macro run time
    - estimated time left
    - percent of records completed
    - overall average records/second
    - last 10 seconds average records/second
    - minutes and seconds automatically formatted for timeleft and run time
    - everything rounded and formatted so things like '1.10' dont become '1.1' and cause 'blurring' of the text in the statusbar

    I know its rather long for a status bar progress meter... but if anyone can see any improvements or ways to cut it down pls tell me!

    Also, dont worry about the length slowing your macro down... with this empty template it only takes 20 seconds for 100,000 loops on my computer.

    Re: Autofill From Multiple Sheets

    You can change the cell values

    Cells(y, x)

    to make it go horizontally or vertically, or both ;)

    y is verticle, x is horizontal, origin is the upper left of excel, moving to the right or moving down increases the values, moving left or up will descrease them.

    You can write a couple different subs to do what you need, and you can use


    and others to find where to start at dynamically.

    Re: Autofill From Multiple Sheets

    vba would be best for this


    Re: Speed Check On Returning File Path Strings

    I cant see anything specific but play around with variable declaration... going from a double to a single in ONE variable cut my macro to 1/4 the time.. and it was 75-100 lines too, wasnt like that one variable was a big part of the program, and all I was doing was multiplying with it.

    Re: Constantly Running Macro

    Quote from mikerickson

    There are application level events , like the saving of any workbook, that will trigger an application level event which will trigger a routine residing in only one of the workbooks.

    But will that work across a network? I thought application level events were restricted to the application on the specific machine.

    Re: Constantly Running Macro

    Just make sure you compare countit to a new count everytime. If the two counts dont equal each other, the file numbers changed. To do sub-directories - just change the path ;)

    Btw - testing the above as it is will probably result in 0 unless you have files in the base program files dir.

    Re: Constantly Running Macro

    Well, if the code is in the master file, theres no way to trigger it without some event from the user workbooks, or by periodically testing the folder the user books are saved in.

    Sry reafidy, I see you must have corrected it but I dont remember forgetting tags anywhere!

    Re: Constantly Running Macro

    Btw, what Mike was talking about was putting the same code in every workbook (on the user end) to send a signal to you when the specific book gets saved.

    Re: Constantly Running Macro

    Found this on the web, hope it works for ya. Itll count total items in a folder. I looked for folder modified property for a few mins but didnt find a way. I still think its possible tho.

    Re: Set Range Of Varying Size

    I dont have a lot of experience with listboxes so hopefully someone else can come along to finish this, but it would be similar to:

    first, concatenate all the rows into one cell (for one value to add into the listbox)

    Then, loop from x to y again and add all those values to a listbox

    for z = x to y
    ListBox1.AddItem cells(x, 5).value
    next z

    Havnt tested it so tell me if it breaks ;)

    Re: Constantly Running Macro

    You may also be able to test a folder for its last modified date property.... infact I'm almost sure you can. That would probably be best unless you want to put code in all 123 worksheets to trigger the beforesave event.