Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.


  • Excel 2007 - Getting External Data

    On of the most visited parts of my web site is the part on external data. Most of it was based on Excel 2003 and before, so I thought I should update it for Excel 2007. INTRODUCTION The first thing you will notice about 2007 is the ease in which you can find the data 'movement' tools. Now, say what you think about the ribbon in Excel 2007, but finding where to import and export data is not one of them! It's on the Data tab. (as below) I have only shown you the section above. The tab extends...
    Posted to Nick Hodge's Excel Blog by Nick Hodge on Tue, Nov 4 2008
  • Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)

    So we've explored the file formats, created the shell for a dictator application, made some basic ribbon, tab, group and control changes and customised the Office menu. We've re-purposed some Microsoft controls to our own use and now we will be running at pace through some of the other controls available to you. I doubt we will cover all of them, but you can download a bunch of my files here , which demonstrate more of what you are learning here and also contain a 'popular' control...
    Posted to Nick Hodge's Excel Blog by Nick Hodge on Thu, Feb 14 2008
    Filed under: , ,
  • Ribbon, Step-by-Step. Part 3 (The Office Menu and Re-purposing)

    In our first two parts we covered the file structure of Excel 2007 and some very simple customisations which, in the main, do not require any VBA code. in this part we are going to try and accomplish two things. Customisation of the Office Menu (under the office button) Re-purposing built-in controls to do other things. In the next one or two parts we will cover more details on the various controls, including Galleries and Contextual tabs and then how we can capture the ribbon object and change controls...
    Posted to Nick Hodge's Excel Blog by Nick Hodge on Sun, Feb 3 2008
    Filed under: , ,
  • Ribbon, Step-by-Step. Part 2 (Begin Customisation)

    In the last part of this series , we went through the different file formats as a precursor to starting Ribbon customisation. You should by now have downloaded the Office 2007 Custom UI Editor , as this is the tool we will be using to customise our RibbonX and generate callbacks for VBA. We'll start with a run down of the tool itself. Remember, you need to have the .Net 2.0 framework, or above loaded on your development machine. The Office 2007 Custom UI Editor Without worrying too much about...
    Posted to Nick Hodge's Excel Blog by Nick Hodge on Thu, Jan 31 2008
    Filed under: , ,
  • Removing The Macro Security Warning

    Many times after macros have been recorded or written users want to remove them as they are no longer required, but they cannot get rid of the 'macro warning' pop-up that appears each time the workbook is open, (see below). (You should only follow these instructions if you have no further need for the macros or UserForms!) . Excel has the ability to put code in three places, ( see here ). If the code is in a standard module the key to stopping the dialog is to remove the entire module(s)...
    Posted to Nick Hodge's Excel Blog by Nick Hodge on Sat, Jan 26 2008
    Filed under:
  • UK Excel User Group Conference

    A meeting of the above group will take place on Wednesday 1st and Thursday 2nd April 2009 in London . This FREE event has some fantastic speakers from the Excel community. The agenda for the two days is outlined below and you can book for either or both days by emailing bookings@excelusergroup.org . Microsoft will be providing the venue and the hospitality and we look forward to a great and informative couple of days. The agenda is below, but if you want the full version with session details and...
    Posted to Nick Hodge's Excel Blog by Nick Hodge on Fri, Dec 5 2008
    Filed under:
  • Importing Data with Parameters (ODBC)

    Using Parameters In External ODBC Data Queries Here we can demonstrate the linking of data in an external data source with Excel, using the values in cells to determine the data presented, using parameterised data queries. Open a workbook and on the active sheet in cells A1 enter Start Date and in B1 enter the date 01/01/2003. In A2 enter End Date and in B2 enter the date 02/02/2003. Take the menu options Data>Import External Data>New Database Query... You will fire from here a dialog asking...
    Posted to Nick Hodge's Excel Blog by Nick Hodge on Thu, Jan 24 2008
  • Reset Excel's UsedRange

    If you are frustrated by your scrollbars racing way passed your last row, or they are difficult to control, it is likely Excel thinks it has more data than it actually does and you need to reset it's UsedRange. All versions of Excel have an 'issue' with resetting the area it believes it is using on any sheet (UsedRange). This is brought about by say, entering data in columns to the right of the actual data or rows below it. To test where Excel believes it's furthest row or column...
    Posted to Nick Hodge's Excel Blog by Nick Hodge on Tue, Jan 15 2008
    Filed under:
  • Ribbon, Step-by-Step. Part 1 (File Formats)

    I am going to endeavour to enthuse some more people into development of the new ribbon in Excel 2007 (well other applications too, but I will use Excel throughout). You will need a knowledge of XML and VBA, as these are the two technologies I will be using. I suspect it will take about 4 or 5 'sessions' to get up to some kind of understanding and I intend to cover the following: File Formats Dictator Applications Custom Tabs and the Office Menu Using Built-In Groups and Controls Using Custom...
    Posted to Nick Hodge's Excel Blog by Nick Hodge on Wed, Jan 30 2008
  • How to build an awesome user interface for results

    The best work in the world is undervalued if presented badly. Suppose you've built a big model with lots of results to show. Imagine having a single Results sheet, with a list of charts and tables. Each time you select one, it appears like magic on the screen, replacing the one that was there before. Best of all, it needs no VBA code whatsoever! There are two ways I know of to do it. Both are demonstrated in this file ( Dynamic UI.xls ). 1. Hyperlinks A simple method is simply to create a hyperlink...
    Posted to Dermot's Blog by dermot on Tue, Jan 15 2008
  • Posting Code to excelusergroup.org

    I have been testing several solutions for posting formatted code to these forums and blogs. There are a few that I can build-in, but I am nervous messing with the code base as we are just getting going. The code below is formatted using Windows Live Writer, a free blogging tool from here . I have added to that an add-in, (there are dozens of them) to insert code snippets . (There are lots of these too). You can just add this as a plug-in very simply and it appears as a link in the right sidebar of...
    Posted to EUG Admin's Blog by Admin on Tue, Jan 22 2008
    Filed under: , ,
  • Questions on customising the ribbon

    I have, via the blog contact form received three questions from Milan, these were Can you remove the Office Button? Can you link a combobox (on the ribbon) to a cell/range? Can you link a label (on the ribbon) to a cell? The answer to No.1 was answered in the 2nd of my series on customising the ribbon. In the section under Full Dictator Application. No. 2 is yes you very much can but it is not a simple process like having a 'link' property or similar. The write-up below demonstrates how you...
    Posted to Nick Hodge's Excel Blog by Nick Hodge on Sun, Mar 2 2008
  • Alternative Pivot Tables

    As many of you know, I don't do anything with technology 'in earnest', but love tinkering and it is great to have a 'project' with a functional 'end game'. Recently, I have been a little 'off' my usual Excel activity and developing (for fun) in the evening, on trains, etc, some additional reporting for our Company Intranet, using VB.Net, ASP.Net and SQL Server. (I had already done some, but it called an iSeries server in the US, from the UK and as it has become...
    Posted to Nick Hodge's Excel Blog by Nick Hodge on Thu, Jul 24 2008
    Filed under:
  • COUNTIF SUMIF Limitations

    I'm sure many of you knew this but it was new to me and I came across it today, so in an effort to get it out there a bit more, i document it here. It concerns the fact that COUNTIF and SUMIF (and their 2007 plural counterparts SUMIFS and COUNTIFS) carry forward the limitation that Excel has of 15 significant digits. (As does 1-2-3 and OpenOffice) http://support.microsoft.com/kb/269370 I knew this limitation, but didn't realise the two functions concerned followed this limit' I had to...
    Posted to Nick Hodge's Excel Blog by Nick Hodge on Fri, Jun 27 2008
  • Playing with OpenAsTextStream

    We produce individual text files for new items to go into our catalogue. These are sent to the creative people for laying out on the page. One file contains the Item#, Description, Text and Bullets for one item and is named with that item's number. Of course, for page layout it is good for them to copy and paste the text this way. It looks as below: 12345E 2500PSI 15LPM Petrol-Powered Pressure Washer This pressure washer, powered by a Honda GX160 engine, will blast away even the most stubborn...
    Posted to Nick Hodge's Excel Blog by Nick Hodge on Thu, Mar 13 2008
1 2 3 4 5 Next > ... Last »
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.