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

April 2010 - Daily Dose of Excel

Sort by: Most Recent | Most Viewed | Most Commented

  • Consulting Tools

    Are you an Excel/Office consultant or freelancer? If so, I’d like to know what tools you use. Specifically how you track your time, invoice your clients, and account for your business. I track my time in Excel. Whenever I complete a task, I add a line to a spreadsheet with the client, project, [...] Read More...
    Filed under:

  • Dynamic Columns in MS Query II

    Earlier, I posted about using ADO to query a select number of columns from a spreadsheet. It used the CopyFromRecordset method to get the data in the spreadsheet. In this example, I change the CommandText (SQL) property of an existing QueryTable to show the data. First, I used Data - Get External Data - [...] Read More...
    Filed under:

  • Dynamic Columns in MS Query

    Jeff commented that he wants to bring in the latest x columns in from spreadsheet using MS Query, where x is some number. The columns are expanding, so he doesn’t always know what the latest will be. I started with this example spreadsheet Note that the “date” headers aren’t really dates but strings (they have a [...] Read More...
    Filed under: ,

  • Prioritize List on a Worksheet

    In a previous Prioritize List on a Worksheet, I showed how to use a spin button to move values up or down. This code does the same thing, but moves the whole row rather than just one value. I tried to incorporate Eric’s comment, but it wasn’t any move efficient because I was [...] Read More...
    Filed under:

  • Windows Live Mail (WLM) and Excel problem part two

    Hi all If you set Windows Live Mail as your default mail program and try to mail from Office you get a general mail failure error two times, the third time it is working OK. The reason for this is that Windows Live Mail isn’t fully MAPI-qualified like Windows Mail or Microsoft Outlook. If you want to mail with from [...] Read More...
    Filed under:

  • TM AutoChart: Connect chart axis parameters to cell values

    TM AutoChart is a shareware Excel add-in that links the minimum, maximum, major, and minor values of a chart axis to worksheet cells. http://www.tushar-mehta.com/excel/software/autochart/index.html Over the past few months I have been trying out different Ribbon layouts for chart related add-ins. For now, I have settled on a split button where the large button provides [...] Read More...
    Filed under: , , ,

  • Illegal File Names

    Function IsValidFileName(sFileName As String) As Boolean         Dim vaIllegal As Variant     Dim i As Long         vaIllegal = Array("/", "\", ":", "*", "?", "< ", ">", "|", """")     IsValidFileName = True         For i = LBound(vaIllegal) To UBound(vaIllegal)         If InStr(1, sFileName, vaIllegal(i))...
    Filed under:

  • Creating Dynamic Names in VBA

    I had an occasion to create about 25 dynamic named ranges. A tedious process, that. So I cooked up some code. All the ranges needed to start in row 2 and extend down as far as there is data. There wouldn’t be any blanks, so I didn’t worry about it. In this [...] Read More...
    Filed under:

  • Listing Format Conditions

    I wrote some code to list out all the conditional formatting rules in a worksheet. It wasn’t as easy as I thought it would be. In Excel 2007, MS introduced some new format conditions like Icon Sets and Databars that complicate things. As a result, I didn’t get too fancy with the [...] Read More...
    Filed under: ,

  • Display a message for a specific duration

    There are a number of instances where one may want to show a message for a specific duration. In other instances it might be for a specific duration or until the user acknowledges the message, whichever comes first. In the first category would be something like a splash screen or a very brief [...] Read More...
    Filed under:

  • On a Personal Note

    So, do you want to know what’s been happening lately? Too bad, I’m telling you anyway. I’ve been using a Dell Latitude D810 for nigh on five years now. I really like this laptop with one exception. The power is good, the keyboard is great, the touchpad is awesome. The VBA port [...] Read More...
    Filed under:

  • Windows Live Mail (WLM) and Excel problem

    Hi all Because Windows Mail is removed from Windows 7 you must install a third party mail program or use the free Windows Live Mail (also with newsgroups). I see that a lot of people start using WLM now but Office has a problem with this program. Note: you have the same problem if you use WLM in Vista If [...] Read More...
    Filed under:

  • Modern Excel Charts

    Debra sez: If you want to make your Excel charts look like they were made in 2010, instead of 1982, see the Excel Charting tutorials on Jon Peltier’s blog. However, if you feel like kicking it old skool… Problems: I couldn’t read the title and y-axis, so I took a stab. I don’t know how to turn the y-axis text [...] Read More...
    Filed under:

  • ByRef Arguments and the Application.Run Method

    The Application.Run method is a versatile mechanism to call a subroutine particularly in another workbook or add-in. Its one documented limitation, if we want to call it that, is that all arguments are passed by value (ByVal). So, how does one pass an argument by reference (ByRef)? For a version in a page by [...] Read More...
    Filed under:

  • Syncing Data Validation

    I answered a question over at superuser.com the other day having to do with syncing data validation. If you use data validation that links to a range, and you change the range, the data may no longer be valid. I have a range in A1:A3 that I’m using as a data validation list. [...] Read More...
    Filed under: ,
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.