DIY Slicers

Better late than never...

After seeing Microsoft’s post about Slicers on their Excel blog, I thought it might be fun to make my own version. Maybe I can’t wait until the official release of 2010?

Anyway, I put together something that would show which items on a pivot table were visible and which were not. I wanted code that would be fairly versatile, as far as I tested (twice!), the code shows each item in each field to the right of the pivot table.It's still a prototype but feel free to use the code if you find it useful.

Here’s how they look. On the right of the pivot table items, there is a simple validation list that allows you to choose from O (visible) or X (not visible).



I should mention that you can drag your slicers around the worksheet just like the real thing. The only thing that you must do is to ensure there are blank rows and columns around each slicer for them to work properly.

You can delete slicers that you don’t want - refreshing the pivot table to show all slicers once again in their default position which is to the right of the pivot table.

Here’s the link :-)

PS I also tried using an ActiveX listbox. While the code did work okay, I noticed some problems with the screen when the code was run – the items did not show up on some PCs until I forced the screen to update by zooming in and out, hence using cells instead. I imagine a little more experimentation might overcome this if anybody wants to try. Have fun!
  

Latest Version Of JMT Excel Utilities

Recently I have made a number of improvements to my utilities. Sorry for not posting sooner!

CHOOSEFROMRANGE and CHOOSEFROMLIST are 2 useful new functions. You can use them to return a random value from a range (for the former) or a custom list (for the latter). I’ve also updated a few of the other functions – special thanks to Jimmy Peña from Code for Excel and Outlook for his help and suggestions.

I have added new features to Insert Sequential List In Selection. You can add, subtract, multiply and divide by a secondary number when inserting Sequential Numbers, Unique Random Numbers and Repeating Random Numbers. You can also change the formatting of the cells for all of the above options as well as Sequential Dates or use the default (not change existing cell formatting). Another feature is that you can start over after inserting a set number of values. After some head scratching, I have decided to move this utility to Text Tools as I felt it fits in better with the other text tools I have on that sub menu.

Finally, something that code dabblers might find handy! You can copy the active cell’s formula to the clipboard for use in VBA, eg ActiveCell.Formula = “”. (Those dreaded double quote marks are replaced with Chr(34)). Choose from A1 or R1C1 conversion, it’s in Developer Tools.

You can download the latest version here :-)

Quick Tip on the Quick Access Toolbar
While I am at it, you can add any tool, sub menu or the entire group to the QAT in Excel 2007 by right clicking it and selecting Add to Quick Access Toolbar.
  

Days Per Week Between 2 Dates

Out of all the formulas (formulae to the more educated than me) that I work with, I would say that Date formulas are the hardest to get right. Maybe not so surprising considering how weeks don't divide nicely into months. It really is very naughty of them.

Anyway, I put together a formula to work how many days exist in each week between 2 dates. For it to work as expected, the following must be true -

The end date must be equal or higher than the start date (D'oh!)

The start date must be equal to or higher than the first week's date

The end date must be equal to or lower than the last week's date + 6



Weeks that are not within the date range show as blank. Note the formula also includes the start date as being a day, so if we consider the total days, the calculation would be

End Date - Start Date + 1 Day

And the formula? With the Start Date and the End Date being in Cells A2 and B2 respectively, select the appropiate cells starting from Cell C2, enter this formula, then push Ctrl + Enter simultaneously. (Alternatively, enter the formula into Cell C2 and drag to the right)

=IF(AND(C1>=$A2-6,C1<=$B2),IF(AND($A2-C1>0,$B2<C1+7),$B2-$A2+1,IF(OR($B2=C1,$A2=$B2),1,IF(AND($A2-C1>0,$A2-C1<7),C1+7-$A2,IF(AND($B2-C1>0,$B2-C1<7),$B2-C1+1,7)))),"")

Can you shorten it? Great if you can, don't hurt your head though :-)

There is room for improvement though. As is, holidays are not calculated, neither weekends or public holidays. If you come up with a way, (perhaps and another formula or 2 in rows below?), let me know and I will append it to the bottom of this post!

Update
Sébastien Labonne of The Financial Modeler has come up with a much shorter formula! (About a third the length of mine and far fewer cell references!)

=MIN(MAX(0,C1+7-$A2),7)-MIN(MAX(0,C1+6-$B2),7)

Well done Sébastien! :-)
  

More Chart Tools

I recently added 2 new chart tools to JMT Excel Utilities -

Reposition Data Labels

Resize Chart

They are rather simple tools compared to what you might pay for. Then again, they are free :-)

Here is the download link.  

Excel 2007 Colors - Color Tools

Better late than never, I have made some improvements to my main utilities again. The color picker and the GETTINTANDSHADE function are more acccurate. I also added a new tool caled "Show Excel 2007 Color Details" which shows the Theme, Tint And Shade, Color number, the HTML number and RGB number of each of the 60 Theme Colors.

All of the above tools are in the 2007 version only. Download it here.  

Formula Tools

2 new tools have been added to JMT Excel Utilities.

Find Precedents and Dependents
An alternative to using Excel's built-in auditing tools. A form appears listing the addresses of all precedents or dependents of the active cell, including other worksheets or workbooks. Click on the the list entries to navigate to the precedents or dependents.


Find Circular References
Similar to the above but this list circular references only. You can click on the list entries to navigate to them also.

I have found them to easier to use than the built-in audit navigation arrows. You can download the most recent version of the utilities here.  

Using APIs with UserForms

Here are a few things you can do with APIs to make your userforms more functional. All of the code shown will work with Excel 2000 or higher. Note that I am using modeless forms which enable you select ranges and the user interface (menus, other useforms etc)

You might notice that a lot of the code in the samples is the same. I have commented the parts that the code perform it's unique purpose.

Aplogies if I have posted some of the code before, this is just to put all the samples together.

Add a Minimize and Maximize Button
Place this code in a standard module.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long) As Long

Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Declare Function ShowWindow Lib "user32" _
(ByVal hWnd As Long, ByVal nCmdShow As Long) As Long

Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

Declare Function SetFocus Lib "user32" (ByVal hWnd As Long) As Long

Public Const WS_MAXIMIZEBOX As Long = &H10000 'This refers to the the maximize button property
Public Const WS_MINIMIZEBOX As Long = &H20000 'This refers to the the minimize button property
Public Const GWL_STYLE As Long = (-16)
Public Const SW_SHOW As Long = 5

Sub ShowForm()
    UserForm1.Show vbModeless
End Sub

Place this code in the userform.

Private Sub UserForm_Activate()
    Dim lStyle As Long
    Dim hWnd As Long

    hWnd = FindWindow("ThunderDFrame", Me.Caption)

    lStyle = GetWindowLong(hWnd, GWL_STYLE)
    lStyle = lStyle Or WS_MAXIMIZEBOX 'This adds the maximize button
    lStyle = lStyle Or WS_MINIMIZEBOX 'This adds the miniimize button

    SetWindowLong hWnd, GWL_STYLE, lStyle

    ShowWindow hWnd, SW_SHOW
    DrawMenuBar hWnd
    SetFocus hWnd

End Sub

Make a Form Resizeable (Drag the edges to resize)
Place this code in a standard module.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long) As Long

Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Declare Function ShowWindow Lib "user32" _
(ByVal hWnd As Long, ByVal nCmdShow As Long) As Long

Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

Declare Function SetFocus Lib "user32" (ByVal hWnd As Long) As Long

Public Const WS_THICKFRAME As Long = &H40000 'This refers to the the form resize property

Public Const GWL_STYLE As Long = (-16)
Public Const SW_SHOW As Long = 5

Sub ShowForm()
    UserForm1.Show vbModeless
End Sub

Place this code in the userform.

Private Sub UserForm_Activate()
    Dim lStyle As Long
    Dim hWnd As Long

    hWnd = FindWindow("ThunderDFrame", Me.Caption)

    lStyle = GetWindowLong(hWnd, GWL_STYLE)
    lStyle = lStyle Or WS_THICKFRAME 'This allows you to resize the form

    SetWindowLong hWnd, GWL_STYLE, lStyle

    ShowWindow hWnd, SW_SHOW
    DrawMenuBar hWnd
    SetFocus hWnd

End Sub

Remove the Title Bar
Place this code in a standard module.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long) As Long

Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Declare Function ShowWindow Lib "user32" _
(ByVal hWnd As Long, ByVal nCmdShow As Long) As Long

Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

Declare Function SetFocus Lib "user32" (ByVal hWnd As Long) As Long

Public Const WS_CAPTION As Long = &HC00000 'This refers to the title bar
Public Const GWL_STYLE As Long = (-16)
Public Const SW_SHOW As Long = 5

Sub ShowForm()
    UserForm1.Show vbModeless
End Sub

Place this code in the userform.

Private Sub UserForm_Activate()
    Dim lStyle As Long
    Dim hWnd As Long

    hWnd = FindWindow("ThunderDFrame", Me.Caption)

    lStyle = GetWindowLong(hWnd, GWL_STYLE)
    lStyle = lStyle And Not WS_CAPTION 'This removes the title bar

    SetWindowLong hWnd, GWL_STYLE, lStyle

    ShowWindow hWnd, SW_SHOW
    DrawMenuBar hWnd
    SetFocus hWnd

End Sub

Remove the Close (X) Button (Note FindWindowA is added)
Place this code in a standard module.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long) As Long

Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Declare Function ShowWindow Lib "user32" _
(ByVal hWnd As Long, ByVal nCmdShow As Long) As Long

Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

Declare Function SetFocus Lib "user32" (ByVal hWnd As Long) As Long

Declare Function FindWindowA Lib "user32" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long 'Note this line exists only in this sample code

Public Const WS_SYSMENU = &H80000 'This refers to the close button
Public Const GWL_STYLE As Long = (-16)
Public Const SW_SHOW As Long = 5

Sub ShowForm()
    UserForm1.Show vbModeless
End Sub

Place this code in the userform.

Private Sub UserForm_Activate()
    Dim lStyle As Long
    Dim hWnd As Long

    hWnd = FindWindow("ThunderDFrame", Me.Caption)

    lStyle = GetWindowLong(hWnd, GWL_STYLE)
    lStyle = lStyle And Not WS_SYSMENU 'This removes the close button

    SetWindowLong hWnd, GWL_STYLE, lStyle

    ShowWindow hWnd, SW_SHOW
    DrawMenuBar hWnd
    SetFocus hWnd

End Sub

Use a "Toolbar" Style Title Bar (Note GWL_EXSTYLE is used, not GWL_STYLE)
Place this code in a standard module.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long) As Long

Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Declare Function ShowWindow Lib "user32" _
(ByVal hWnd As Long, ByVal nCmdShow As Long) As Long

Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

Declare Function SetFocus Lib "user32" (ByVal hWnd As Long) As Long

Public Const WS_EX_TOOLWINDOW As Long = &H80 'This refers to the "toolbar" style
Public Const GWL_EXSTYLE As Long = (-20)
Public Const SW_SHOW As Long = 5

Sub ShowForm()
    UserForm1.Show vbModeless
End Sub

Place this code in the userform.

Private Sub UserForm_Activate()
    Dim lStyle As Long
    Dim hWnd As Long

    hWnd = FindWindow("ThunderDFrame", Me.Caption)

    lStyle = GetWindowLong(hWnd, GWL_EXSTYLE)
    lStyle = lStyle Or WS_EX_TOOLWINDOW 'This changes the title bar to the "toolbar" style
    SetWindowLong hWnd, GWL_EXSTYLE, lStyle

    ShowWindow hWnd, SW_SHOW
    DrawMenuBar hWnd
    SetFocus hWnd

End Sub

Have fun!  

Custom Lists

Custom Lists

Custom Lists are great but I've always felt they could be used more easily. I hate dragging down with the plus cursor if I can help it, so I made a new toy to make life more bearable.


Here is what I wrote in the read me file.

Custom Lists
"Custom Lists” is an Excel add-in that allows you to insert entries from your (both default and custom) Custom Lists into cells quickly and easily.

To enable the add-in in Excel, click the Tools menu at the top, then select Add-Ins. If not already visible in the list provided, navigate to the AddIns folder with the Browse button. When ready, tick the check box and close the Add-Ins dialog box.

How to use it
There are 4 options. To select the first 3, right click a range and select Custom Lists, Insert Custom List Form.



1. Enter the full list. Select the list you want to enter, Full List, then either Rows or Columns. No need to drag again with the plus cursor.

2. Enter the list into selected cells. Select as many cells as you like. The list will be inserted into the cells, repeating from the beginning if more cells than list entries are available. A fast way to enter something like days of the week or months etc over several rows or columns.

3. Enter a validation list. Use this to add a drop down list with all of the entries into each in a selected range. This will save a lot of time if you work with the same drop down lists on a frequent basis.

4. You can also add a single entry in selected cells by choosing from any of the lists (Mon,Tue,Wed – Monday,Tuesday,Wednesday - Jan,Feb,Mar etc)



Tips
1. You can resize the from to show all list entries if not visible by dragging to the right.

2. Previous settings are recorded so you can enter the same list with the same insert options quickly

Download it here :-)
  

JMT Excel Utilities Download

Some people might have be having problems accessing the JMT Excel Utilities download site.

So I have made a temporary download site where you can download them here.

Do you the most recent version? I’m always adding new stuff!

While I am at it, I’ve made a new add-in that works with Custom Lists and will upload it in the near future :-)
  

Cell Watch Form 2

No pictures this time but here is what I have changed with the new version of the Cell Watch Form.

If the workbook of the cell you want to watch is not open or has been deleted or renamed etc, the watchbook referred to will be the active workbook. The code will then look for a sheet with the same name. If not found, the active sheet will be referred to. Cell details which are using the active workbook and/or active worksheet show as dark red. Otherwise, cell references with the original workbook and worksheet are black.

This is handy if you do not want to associate the cell references with a particular workbook or worksheet. For example, you could refer to the same columns or rows in any worksheet you work with. This would allow you to watch several different ranges at the same time without the need to set things up manually. Just select the worksheet to see everything you want to see.

Also, you change the value or formula of the cells referred even if you can't see them. Click the white details section(s) on the form and an input box will allow you to edit the cells. The form then updates to show the new details.

The download link is here.