Posts by GrahamB

    Hi All,

    I am not sure I like Excel 2007 yet, I have been playing around with it all day and while it has some good features, it is hard to fathom in places. I expect it is just a matter of time to get used to it.

    With previous versions of Excel, hiding and un-hiding commandbard and toolbars can be achieved in a number of ways, such as with the code below

    ActiveWindow.DisplayHeadings = False
    Application.DisplayFormulaBar = False
    Dim cbar As CommandBar
    For Each cbar In CommandBars
    If cbar.Enabled And cbar.Type = msoBarTypeNormal Then
    cbar.Visible = False
    End If
    Next cbar
    CommandBars("Worksheet Menu Bar").Enabled = False

    This does not work in Excel 2007, I have tried many methods but all seem to be redundant in 2007 - appreciate any help



    Re: Conversions To 2007

    Silly questions never end up being silly... I suspect it has not been selected and that would make a lot of sense as everything else is working just not the VBA section. And it does raise other questions

    Can it be selected after installation of the trial version?

    When loading the full version is it available by default or is it selected in the loading process?

    I will reinstall the trial version and see if that gets me over this (silly) situation

    Much thanks


    Re: Conversions To 2007


    There was no mention of any restrictions, however I am trying opening files that have the old extention .xls

    I can normally figure out what is happening, but this has me stumped. If I open a new file, and go the the Developer tab, I am not even given the option to insert a module etc, nor import a .bas file etc

    Appreciate any ideas


    Re: Conversions To 2007

    Thanks Andy,

    I have done that, rebooted the computer - still nothing - I have even tried to open a new file a write some code but it will not let me do that you think the Excel 2007 and Excel 2003 being on the same computer operate in conflict?



    ps when I open and old version file, the title includes "Read Only", "Repaired" and "Compatibility Mode" Isuspect this must have something to do with it - do these files need a conversion?


    Hi All,

    I have downloaded Excel 2007 as a trial on to my computer (with Excel 2003 in another directory), when I load an excel file developed in Excel 2002 (containing macros) and try an open it with Excel 2007 the message "This workbook has lost its VBA, ActiveX controls and any other programmability-related features" - no problems in Excel 2003

    My operating system is XP SP2

    Any thoughts?

    Appreciate any light

    Graham B

    Re: Pdf Attachements On An Email


    Thanks for your reply, I had actually thought I had replies but must have hit the wrong button.

    I have found a method of attaching files and emailing from within Excel. Unfortunately I am using Outlook 2002 which has an annoying security warning dialog box that requires a manual press of the yes button. While this means I can partially automate my email function, I still have to sit there and press yes every 5 or 6 seconds.

    I understand Outlook 2007 allows for the warning to be suppressed, but no so in earlier versions, if there is anyone that knows a work around, I would appreciate it



    Hi All,

    Thanks to John Walkenbach's book, I have been able to use the code below to email a list of customers putting in subject and body information, however, to make it most effective I would like to also attached a pdf file for example "TrailCopy.pdf" found in my "c:\trials " directory.

    I have searched but can only find examples of how to attach the worksheet or workbook.

    I expect the code needs to be amended during the hyperlink build.

    Appreciate any help,

    Kind regards

    Graham B

    Re: Selecting A Worksheet By Relative Reference

    Thank Okk,

    Your solution is excellent

    I used your code

    Function SheetName(num As Long) As String
        SheetName = Worksheets(num).Name
    End Function

    Did not understand "Indirect" function - read Walkenbach, not enough info, tried Microsoft - wasted time their, googled and got "Pearson" and he had an actual example of the formula I needed


    where H3 has the "Sheetname" function and A1 is the cell on the indirect sheet, if that makes sense.

    I am always amazed at Excel, when you think it cannot be done, some bugger some where has already done it.

    Thanks - I will be up until all hours playing with this one!!!!!

    Graham B (I have a huge smile on my mush!)

    Re: Selecting A Worksheet By Relative Reference

    Thanks Okk,

    I know I am supposed to be smart (my mum told me when I was 10 - that was over 40 years ago) - I have looked up my Walkenbach books and have not figured out how this function works, can I ask a favour - shed some light?



    Re: Selecting A Worksheet By Relative Reference

    Many thanks Okk,

    The code you supplied is excellent but, unfortunately will not solve my dilemma.

    Using the UDF gets the name, but I cannot then reference it in a formula like


    It seems that I will forced into writing the code to run through all the worksheets and pick out the info required.

    Thanks for your help.


    Re: Selecting A Worksheet By Relative Reference

    Thanks DR,

    My aim is to reference the worksheet index number so that I can set up a series of formulas without having the knowledge of the worksheet name.

    Currently the formula would read =Billy!f2 where Billy is the worksheet name

    If Billy's worksheet index number was 10 (say) then I would like a formula like


    Do you think this can be done?

    Would a UDF be better?



    Hi All,

    It sounds simple, but I am not sure it can be done.

    I have a workbook with 50 worksheets, each worksheet has an Alpha name (ie names of people). Rather than write some code to go through each worksheet to create a report, is it possible to write a formula in a "Reports" worksheet that reads something like


    where worksheet10! is the number of the worksheet (as seen in the VBA project window) while the name is "Billy".

    Appreciate any help...


    Re: Web Based Input Output


    Thanks for the response, I have not got a URL to use as an example so let me try and describe the situation.

    Currently I have a number of people around Australia using an Excel based program to determine axle loads on heavy vehicles. The inputs required are simple (select a truck (lorry for those in the UK), select trailer/s, select chemicals to be loaded and then press a 'MAX' button.

    The output is a loading plan for that truck / trailer combination with details of calculated axle weights. The loads are saved in a database which is incorporated into the Excel program

    The people who use this program are all from the same company, use the same trucks, trailers etc.

    The issue is when a new truck, trailer or chemical is added or adjusted all the programs need to be updated, when it comes to analyse what has happened - all the individual databases need combining for the anaysis to occur.

    The objective is to streamline all this so the people can do it online - use the one Excel based program.

    One solution is to re-write the program in either VB or XML, but as the program took 3 years to 'perfect' and I am not into VB or XML I am not wanting to go that way.

    I have seen a major company in Australia use some form of mapping to set up the inputs on line, it submits it to the Excel program (based on a company server) and then an output is created on screen.

    I hope the description is not too wordy, appreciate all the help and understanding possible.


    Evening All,

    I got a question where I cannot think of a good descriptive title for, I have seen this done but do not know how it is done...

    An internet user inputs data onto a web form, and and presses a submit button. The submitted input is 'processed' on a through excel and outputs the result back the internet user.

    Two questions
    1 - what is a good title for this?
    2 - how is it done?



    Ps just trying to understand

    Re: Convert Macros To Html?


    I looked into this a few years ago, there is a program called ExcelEverywhere - if you google that you should get more info. I am not sure about converting macros - you might need to consider XML



    Re: Lookup Number In Cells Holding 2 Numbers

    G'day Bott59,

    Would you consider using [if]*[/if] eg if(and(x>152,x<161),2,0)?

    Doing it this way means it is all done in formulas rather than vba. If VBA is required it can still be done by nested if statements.