Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
Daily Dose of Excel » All Tags » General (RSS)

Browse by Tags

  • Count Active Customers

    Jake wants to know, given an active date and an inactive date, how to count the customers that were active in a certain time period. The ones we want are highlighted in yellow. The formula is =COUNT(D2:D21)-SUMPRODUCT(($C$2:$C$21>=D25)+($D$2:$D$21< =C25)) It’s easier to figure out who is not active during that date range and subtract it from the total. [...] Read More...
    Filed under:

  • Showing Hidden Sheet and Workbooks Dialog in VBA

    A tip from Scott: Windows - Unhide, from the menu, shows the Unhide dialog box for unhiding workbooks. In vba, Application.Dialogs(xlDialogUnhide).Show gets the job done. Where it gets tricky is unhiding worksheets. Via the menu, Format - Sheets - Unhide In vba, the name of the dialog is not so intuitive. Application.Dialogs(xlDialogWorkbookUnhide).Show Poorly named, for sure, but you’ve been warned. [...] Read More...
    Filed under:

  • Contextual PivotTable Userform

    Sometimes when I write code, I feel as if I’m writing on a cloud with a unicorn’s horn dipped in angel tears. And sometimes I feel as if I’m using a sledge hammer. This is the latter. There have been some PivotTable shortcuts posted about (here, here, and here). When I get too [...] Read More...
    Filed under:

  • Regular chart version of a sparkline

    In one of the social.answers.microsoft.com web forums (Microsoft’s replacement for the newsgroups no longer hosted on its own server) someone wanted to see a regular chart version of a sparkline when s/he clicked on the cell. I thought that was an interesting idea since one can see much more detail in a large object [...] Read More...
    Filed under:

  • Lightening colors

    On an ongoing project, I had to create lighter shades of a color. Since we were working with Excel 2007, I decided to use Range.Interior.TintAndShade to achieve the desired result. TintAndShade is a number between -1 and 1 that lightens or darkens a color. Initial tests showed it seemed to work fine. [...] Read More...
    Filed under:

  • The True Cost of Spreadsheets

    Rob wrote a white paper called Spreadsheet Addiction: The True Cost to the CPG Industry. In it, he lists the reasons why Excel is so popular and widely used and he lists the reasons why it costs more than you think. I’d excerpt some of that text, but I don’t know how to [...] Read More...
    Filed under: ,

  • Test an Object and Its Property

    When you need to test an object’s property, there are times when you must also test that the object exits. E.g. If Not rControl Is Nothing Then     If IsEmpty(rControl.Offset(0, 1).Value) Then         rControl.Offset(0, 1).Resize(1, 7).Value = clsTimeSheet.TimeArray     Else         MsgBox "Operation Failed"     End If Else     MsgBox "Operation Failed" End If This [...] Read More...
    Filed under: ,

  • Properties and Object Continued

    Yesterday, I was writing about how you can access worksheet functions in different ways. And I was going on and on about it to the point where I’m now recreating a slimmed down Excel Object Model in VBA. How did this topic get away from me so fast? Anyway, I set up the CCell [...] Read More...
    Filed under:

  • Properties and Objects

    I know you’re thinking that class module week is over, and you’re right. This post is more about the Excel Object Model and how it is set up. It stems from a couple of comments on another post that correctly point out that Application.Transpose(...) and Application.WorksheetFunction.Transpose(...) do the same thing. For this example, we’ll [...] Read More...
    Filed under:

  • Incongruity

    40355 = 101001 Read More...
    Filed under:

  • Alternative Time Picker Problems

    Like wlad, I pine for an alternative to the DTPicker that comes in mscomctl2.ocx. I can’t move workbooks between my WinXP and Win7 machines without problems with this file, not to mention distributing it outside of my house. A few weeks ago I set about making an alternative using built-in controls. I didn’t go [...] Read More...
    Filed under:

  • VBA Forums

    Did you know that Microsoft is discontinuing nntp in favor of web based forums? I’m a little sad because I cut my teeth on nntp (Network News Transfer Protocol, or more familiarly newsgroups). But I spend very little time on nntp these days, so it won’t effect my life too drastically. There seems to [...] Read More...
    Filed under:

  • Calendar Control DLL on Windows 7 64 Bit

    I got a message like “MISSING: Microsoft Windows Common Controls 2.6.0″ and I’m using 64bit Windows 7. Here are the steps I had to take to fix: Download the cab files from this knowledgebase article. Right click on the CAB file and choose 7-zip - Extract to \mscomct2\ Open that folder and copy the two files (.ocx [...] Read More...
    Filed under:

  • Comparing Two Lists

    I want to describe for you a process that I do many times every month. Then I’m going to tell you how I tried, and failed, to make it easier. Put on your seat belts, here we go. I repeatedly have situations where I have two CSV files containing lists. Each list has [...] Read More...
    Filed under: ,

  • Excel control idMso’s of Excel 2010

    Hi all Maybe a useful file if you want to change the Excel 2010 Ribbon? I add a file to my site with all Excel control idMso’s of Excel 2010 RTM I add a Advancedfilter to the MS file so it will filter on groups and display a picture of the group. http://www.rondebruin.nl/files/RDB_Built-in_idMso’s_Excel_2010.zip You can also find the download link [...] Read More...
    Filed under:
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.