Posts by rem1224

    Hey Will -

    I see I'm late to the party (as usual) - but wanted to add my congrats to the thread on your 2000th :congrats:

    It's a pleasure to get on OzGrid and find a place where friends are the rule and not big egos.


    Hi fliss -
    Don't think their is any direct conditional formatting in the chart section, however, you can create your chart as normal then run a macro on the chart to format the color of the bars.


    You may have to adjust the colorindex values to what you want.


    Thought of something else, Rich

    Each control has in index number that can be used instead of the name - -

    CommandBars("File").Controls(15).Enabled = False
    ''.....turns off the print command on the File menu
    Application.CommandBars("Standard").Controls(6).Enabled = False
    ''......turns off the print button on the command bar

    Those index numbers worked on my system - - they may be different on other PC's depending on how the menus are set up - - but it might work for you.


    Hi Rich,

    Rather than disable the print button, could you put your validation code in the Before_Print event in the ThisWorkbook

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
     . . your validation code
    End Sub

    Then it would check no matter which printer is used.


    Hi Neale,

    The Format statement does return a String (not a date value) as you said.

    I pasted your line of code into a macro in my XL XP setup and it seemed to work OK. The result you are getting (11/13/2002 12:03:00 AM) is, of course, no where close to the formatting you specify in the statement - - I assume the 2002 date was a typo - the formula didn't change the year on you, did it? It looks like for some reason the macro is not even looking at that part of the statement.

    Try taking the format code out of the stmt and see what you get i.e. =Format(CurrAdjFinishDate)
    or try some of the system format codes
    =Format(CurrAdjFinishDate, "Long Date")


    Hi kimberly,

    You've been having a nice conversation with yourself up to now :wink1: - so I'll butt in and see if I can lend a hand.

    Check on the File / Print dialog box and make sure the "Page Range" "ALL" radio button is checked; The "Print What" box says "Document"; and the "Print" box says "All pages in range".

    I can't find anything on my File / Properties window that refers to the number of pages to print (i'm using Word XP) - can you tell a bit more of where you are seeing that (which tab - what else in on the page, etc) - can you change the value there?


    Hi Ted -
    Get into the Visual Basic Editor (Alt-F11), then from the insert menu, select UserForm. You can develop the form with any number of controls and text that links to cells on the worksheets.
    Then you'll need a macro to activate or display the userform like:

    Sub ShowTheForm()
    End Sub

    Check the VBA Help files under "Userform".


    Hi shadrach,

    One way would be to sort the list of measurements then apply the Data / Subtotal and use the "count" function.

    I'm sure there are other ways also.

    Your second question - use the formula

    ="The total is " & SUM(B1:B15) - adjust your sum range to suit

    although you loose the ability to format the number since its now part of a text cell. Sometimes a better way is to put the label ("The total is:") in the cell to the left of the total and right justify the text.

    Hope this helps


    Hi Marielinha,

    Andy beat me to the answer, but I had something to add. If you data does not start in Col A, you have to add the appropriate column offset number to the MATCH Statement.

    example - if you data starts in Col C you could use:

    =MATCH(MAX(C1:G1),C1:G1,0) + 2

    and you would get the answer 6 from Andy's data which would be the correct id for Col F.


    Hi Neale,

    I use both 2000 and XP and haven't had any range name issues such as you describe.

    The fact that some work and others do not as you said in you post would probably suggest that its not a translation from 2000 to XP issue.

    Is it a dynamic range? Other than cell reference changes, is there any difference in the formulas that work and those which return the #NAME?


    Hi and welcome to Ozgrid,

    There are a number of ways to merge 2 files - but could use a bit more info to determine which would work best for your applicaiton.

    Do both files have the same filename - just stored in different directories?
    Do both files have EXACTLY the same format - titles, fields, formats, worksheets, etc the same and in the same place?
    What type of data is in the 2 files - - is it 2 lists (like names/addresses) that you want to combine into 1 master list; or is it numbers that you want to add together and put to totals into the merged sheet?

    The file attachment feature is not working right now - so best you can to is explain all the details in a post.


    Hi Dai,

    Setting up 2 templates should keep thing straight for you.

    1. Create a blank workbook and format EACH worksheet with the headers/footers/fonts/etc that you want as the default.

    Do a "Save As", select "Template (xlt)" as the Save As Type. For the "Save In" location navigate to the subdirectory called XLSTART. If you are using XP it will be under C:/Program Files/Microsoft Office/Office 10. Save as Filename must be BOOK.xlt

    2. Keep that same workbook open and delete all worksheets except sheet 1.

    Do another "Save As" - Template - same directory. This time save the file with the name SHEEET.xlt

    Close everything and you're done.

    Whenever the user opens Excel the blank workbook that comes up will have all the characteristics of the file you save as BOOK.xlt. If the user Inserts a new Worksheet into the file, it wil have the characteristics from the file SHEET.xlt

    I don't think you can do anything about workbooks that have already been created and saved - except to go in and add the footers you need and resave them.

    Hope this helps


    Hi Cronina,

    Try the following macro - it just uses the built in AutoFilter function with the "Top 10" selection then copy and paste the results to the right. (Assumes your data is in Col A - E)


    Hi GB,

    Try something like this - - -

    At the beginning of the macro put

    sh1 = ActiveSheet.Name
    (Stores the name of the active sheet)

    At the end of the macro put

    (Goes back to the stored name sheet)

    Hope this helps