July 25, 2011

VBA Code Indenter 2

A slightly "improved" version of my VBA Code Indenter is available.

This version starts indenting after Sub and Function names. The reason I decided to do this is because it seems to be the standard with other folk's code I have seen posted online. Who am I to go against the grain? (I have a bridge to sell you if you think I'm a conformist!)

The download link is here.  

Posted by andrewe at 18:46

July 24, 2011

VBA Code Indenter

There are 2 VBA code indenters that I know of, and unfortunately, I am not able to install either of them where I need them most to due to admin rights. With "complex" code, indenting can help a lot by making code easier to read so I decided to make my own.

My own version is rather basic. You can either indent a whole project or selected module. The menu items are available at the bottom of the right click menu in the Code Pane.

VBA Code Indenter


Note that you must tick Trust access to the VBA project object model before installing. Here is how to do it with various Excel Versions.

Pre-2007
Go to the Tools Menu at the top of Excel. Select Macros, Security, then Trusted Sources. Tick "Trust access to Visual Basic Project".

Excel 2007
Go to the Developer tab at the top of Excel. Select Macro Security, Macro Settings and then tick "Trust access to the VBA project object model."

If the Developer tab is not visible, select the round Office button at the top of Excel, then Excel Options, Popular, "Show Developer tab in the Ribbon."

Excel 2010
Go to the Developer tab at the top of Excel. Select Macro Security, Macro Settings and then tick "Trust access to the VBA project object model."

If the Developer tab is not visible, select the File button at the top of Excel, then Options, Customize Ribbon and tick Developer on the Customize the Ribbon, Main Tabs list.



Here is the download link to try it out.


  
Posted by andrewe at 11:09

July 16, 2011

Quick Paste Special

When I saw Paste Special in Excel 2010, I thought "Cool!". I was a bit dubious about some of the choices and the icons that represent them, but that was okay because nobody ever thinks the same way.

However, I had 2 problems. The first is I only have 2010 at home, where I get to play with Excel only with permission signed and countersigned in triplicate 3 months in advance. (At this time of writing, I can feel waves of wrath heading in my general direction - this explains my infrequent blogging, right?).

Which leads to the second problem. Where I do most of my development, I have Excel 2007 and the extra coaxing required to paste just what I want can be a little frustrating at times.

Anyway, as you can guess, I decided to make my own. I tried to think of some of the most useful paste special options or combinations thereof. I've added 20 of them to the right click menu (Cells, Rows and Columns) and I also added a most recently used menu item so you don't have to keep looking if you are a (mostly) mouse person like me. You can access the commands by using the keyboard too though.

Here's what it looks like in Excel 2007 and Excel 2010.

Excel 2007


Excel 2010


I have not tested but I'm guessing it will work with other versions. (English menus only, sorry!)

Here is the download link. Hope it comes in useful!  
Posted by andrewe at 09:55

June 12, 2011

Add a Row to an AutoFilter

Adding a row to the bottom of an AutoFilter is something we do often enough but doing it without code requires more clicks than I would like - copy an existing row, select the row below where the existing bottom row is, then opening up Paste Special. We probably want to paste both Formats and Formulas, so we have do one, reopen Paste Special and do the it again. Okay, things have improved slightly with Excel 2010, but even so...

So, let's use some VBA to help reduce some RSI.

Sub AddRowToFilter()

    On Error Resume Next

    With ActiveSheet.AutoFilter.Range

        .Rows(2).Copy

        With .Offset(.Rows.Count).Rows(1)
            .PasteSpecial xlPasteFormats
            .PasteSpecial xlFormulas
        End With

    End With

    Application.CutCopyMode = False

    If Err <> 0 Then MsgBox "This sheet has no filter"

    On Error GoTo 0
End Sub

Note the message box just in case there is no filter.

New Banner: Just in case you have not noticed, I've changed my site banner to a) show my true site address (andrewsexceltips.net), and b) show I'm still alive and kicking. I will miss Einstein poking out his tongue but hopefully the new and simple design will grow on you. See you next time I post!  
Posted by andrewe at 15:37

November 21, 2010

DIY Filter Search

In Excel 2010, one of the handy features that was added is a filter search box for Autofilters.



It's great that the filter actually changes as you type. But I don't always work with 2010, so I decided to make something close. I wanted something where I could simple type in criteria and the filter would work. (Yes, I know there are Text or Number filter options already, but I wanted something even faster and easier - the less clicking and selecting, the better)

This is what I came up with. Select cells in a row above the filter and type in "rCriteria" in the Name Box.



Then open up the Visual Basic Editor (Alt + F11) and paste this code into the appropiate sheet module of your workbook's VBA project.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iFilterColumn As Integer
    Dim rFilter As Range
    Dim sCriteria As String
    On Error Resume Next
    With Target
        Set rFilter = .Parent.AutoFilter.Range
        iFilterColumn = .Column + 1 - rFilter.Columns(1).Column
        If Intersect(Target, Range("rCriteria")) Is Nothing Then GoTo Terminator
        Select Case Left(.Value, 1)
        Case ">", "<"
            sCriteria = .Value
        Case Else
            sCriteria = "=" & .Value
        End Select
        If sCriteria = "=" Then
            .Parent.Range(rFilter.Address).AutoFilter Field:=iFilterColumn
        Else
            .Parent.Range(rFilter.Address).AutoFilter Field:=iFilterColumn, Criteria1:=sCriteria
        End If
    End With
Terminator:
    Set rFilter = Nothing
    On Error GoTo 0
End Sub

Now enter your criteria. You can type in the whole search string you want or you can use wilcards such as * or >=.



And if you delete the criteria (leaving the cell blank), the filtered column will show all cells once more.

A small saving in time? Perhaps, but it all adds up and helps you keep focus :-)
  
Posted by andrewe at 10:52

June 08, 2010

A Dynamic Sorted List

I like Dynamic Ranges. You can use them to get a list of values that expands or contracts, such as a list of clients that is constantly getting updated.

One problem however can be that your list might not be sorted. If the list is long, you might have a problem trying the find a certain value. Also, the list might contain duplicates. Better to get rid of them if possible.

You could go about getting a sorted list of unique values by adding some code to make yet another dynamic range from the original dynamic range. But it occurred to me, why use dynamic ranges at all? How about just doing everything in code?

Here is a picture of some names in Column A (unsorted and with duplicates). And there is a dropdown (validation) list in Cell B1.



And here is the code used to get the unique values, sort the list and add the dropdown. Open the Visual Basic Editor, locate your workbook and paste the code into the appropiate sheet module. Anytime you add or delete names in Colummn A, it will update the list accordingly.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim UniqueList As New Collection
    Dim itm As Variant
    Dim c As Range
    Dim rList As Range
    Dim lCount As Long
    Dim sList As String
    Dim varList() As Variant
    Dim varTemp As Variant
    Dim i As Long, j As Long
    
    If Target.Column <> 1 Then Exit Sub
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    On Error Resume Next
    
    'Turn off events (remember ALWAYS set back on when finished!)
    Application.EnableEvents = False
    
    sList = ""
    
    'Set rList as range from Cell A1 downwards
    Set rList = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
    
    'Add all values in the range to a collection
    'Only unique values will be added (errors ignored via On Error Resume Next)
    For Each c In rList
        UniqueList.Add c.Value, CStr(c.Value)
    Next c
    
    'All all items in collection to array
    For Each itm In UniqueList
        lCount = lCount + 1
        ReDim Preserve varList(1 To lCount)
        varList(lCount) = itm
    Next itm
    
    'Now sort the array
    For i = LBound(varList) To UBound(varList) - 1
        
        For j = i + 1 To UBound(varList)
            
            If varList(i) > varList(j) Then
                varTemp = varList(j)
                varList(j) = varList(i)
                varList(i) = varTemp
            End If
        
        Next j
    
    Next i
    
    'Loop though array and make a string that conntains the list
    For i = UBound(varList) To LBound(varList) Step -1
        
        If sList = "" Then
            sList = varList(i)
        Else
            sList = varList(i) & "," & sList
        End If
    
    Next i
    
    'Add a dropdown list of names to Cell B1
    With Cells(1, 2).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:=sList
    End With
    
    'Turn events back on
    Application.EnableEvents = True
    
    On Error GoTo 0
End Sub

A little long? Maybe, but the code is very re-usable :-)  
Posted by andrewe at 19:11

December 16, 2009

Classic Excel Colors Remade

Im pleased to announce a major rewrite of the code in my Classic Excel Colors add-in.

Although mostly okay, I had noticed that some colors did always not change as expected and made some version/selection specific amendments to the code.

I also added a way to change the font color of specific text (not just all text)



You can download the new version here :-)
  
Posted by andrewe at 13:41

December 06, 2009

Excel 2010

Got Excel 2010? So do I. Looks like some good stuff has been added, right?

In case you are wondering, I have updated JMT Excel Utilities to work with Excel 2010 but it's still in "beta" at the momment. I will do some more testing over the next week or so. but you can try out the new version 4.4 now. If anything needs to be changed, I will upload a new version later on.

You can download it from here.




  
Posted by andrewe at 09:19

Auto Cell Edit

Auto Cell Edit is an addin that edits cell entries as you make them.

Toggle the addin on and also what other options you want to be edited -



  • Column Width (Autofit)
  • Row Height (Autofit)
  • Proper Case
  • First Letter Upper Case
  • Spell Check

    Whatever options you have selected are saved even if you stop using Auto Cell Edit (toggle it off) or shut Excel down. Simply toggle the addin back on and the settings will be restored.

    I figure it might be useful for people who are entering long lists into Excel and don't want to continuously edit each cell one at a time. If you have any suggestions for other options, let me know by email or telepathy, whichever is more convenient :-)

    Download it here!
      
  • Posted by andrewe at 09:03

    November 03, 2009

    DIY Slicers

    Better late than never...

    After seeing Microsofts post about Slicers on their Excel blog, I thought it might be fun to make my own version. Maybe I cant 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.

    Heres 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 dont want - refreshing the pivot table to show all slicers once again in their default position which is to the right of the pivot table.

    Heres 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!
      
    Posted by andrewe at 06:44

    September 23, 2009

    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). Ive 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 cells 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, its 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.
      
    Posted by andrewe at 20:10

    August 30, 2009

    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.  
  • Posted by andrewe at 14:34

    August 05, 2009

    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.  
    Posted by andrewe at 19:24

    July 04, 2009

    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!  
    Posted by andrewe at 10:58

    June 28, 2009

    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 :-)
      
    Posted by andrewe at 10:55

    June 26, 2009

    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? Im always adding new stuff!

    While I am at it, Ive made a new add-in that works with Custom Lists and will upload it in the near future :-)
      
    Posted by andrewe at 19:30

    June 06, 2009

    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.  
    Posted by andrewe at 08:25

    May 16, 2009

    Cell Watch Form

    At one of my reader's request, I have improved my Cell Watch Toolbar Addin. This time it's not a toolbar though, it's a userform.



    As you can see, there have been some changes. Add Cell Watch allows you to watch a cell in any worksheet, in any workbook. So far, it sounds similar to Excel's Watch Window, and indeed it is based on this. However, I think you will find the Cell Watch Form does so much more.

    Push Add Cell Watch to select a "base" cell. There is an option to name the label for ease of understanding.



    The first label shows the base cell plus the optional name I used, both in dark blue. In black, you can see the current cell actuallly being referred to, and it's value, format and formula (shown as None if no formula exists). You can choose to show whatever you like by using the checkboxes above.



    Now we move to another cell, A4.



    You can see the cell reference and value have changed. Because Column is the option selected, the cell that is "watched" will always refer to the same column as the base cell, only the row will change according to where you select, be it another column, worksheet or even another workbook.

    Using the Row option would do the same, except the "watch" cell would depend o what column you select. The Sheet option would refer to the same cell reference in whatever worksheet you select. The Fixed option will always refer to the base cell, in it's own worksheet and workbook (This is how Excel's built-in Watch Window works).

    Now we add a new label. As opposed to my former add-in with a set number of 3 cells to watch, the new version allows you to add as many as you like! As the new label refers to B1, selecting the 4th row of any column, will refer to cell B4. As you can see, the Cell, Value, Format and Formula details have changed accordingly.



    Use Delete Cell Watch to remove labels from the bottom up. The On/Off toggle button might be useful if you want to temporarily retain some information without the labels updating.

    The download link is here. (I may add some more features in future for a shareware version but for now, it is free :-) )  
    Posted by andrewe at 11:22

    April 18, 2009

    Drawing Tools

    Recent changes to JMT Excel Utilities -

    Make Transparent Shape
    This is something I did long ago and thought it might be a handy tool to use. Don't forget that in addition to being a great spreadsheet prgram, Excel is also very convenient to make simple pictures and diagrams.

    Before


    After


    Change To Classic AutoShapes
    This enables you to change Excel 2007 shapes to the previous version's default formatting style. (This is on the JMT Excel Utilities for 2007 version only)


    User Interface
    I have also made some changes to the menus. I have added a new group called Object Tools to the Sheet Utilities in the 2007 version of the utilities. You will find Comment Tools here also.


    The menu items are also available on the Drawing Tools contextual tab. (Chart items are also duplicated on the Chart Tools contextual tab)


    Finally, I have broken up menus of the 97-2003 version of the utilities. It had to happen because they were overflowing with stuff! You might have to search a bit for some tools but it should not take long as the changes are reasonably logical (?). I think they look a lot better now :-)

    The download link is here.

    P.S. I am taking some time off to travel to Japan (I lived there for 16 years and really miss the place) See you when I get back, about 3 weeks from today.  
    Posted by andrewe at 09:15

    April 10, 2009

    Chart Tools

    I am a chart newbie. At least, compared to some of the chart gurus I see, I am still taking my first steps. That has not stopped me from making some simple chart tools - I figure it's a good way to learn and we all have to start somewhere, right?

    Okay, this is what I have added to the existing chart tools that come with my main utilities.

    Gantt Chart Maker


    You can use it to show hours or days (dates). If you make Gantt charts on a regular basis, this might help. Settings for formatting are recorded so it may save a little time.


    Add Custom Gridline


    This allows you to add a gridline to line or scatter charts where you want it, to get around the default restrictions. With scatter charts, you have the option of adding a horizontal or vertical gridlines. You can show the value to the left (or below) the gridline. You can then make use of Excel's built-in formatting features if you want the gridline to stand out.


    Axis Alignment


    This was formerly called "Make X Axis Cross At Minimum Value". I can't remember the reasoning behind it but it did not seem to work when I tried it recently. Anyway, I added some functionality and hopefully it will come in useful, I think it is a little easier to understand and use than the default version of formatting for axis alignment.

    I also made some improvements to Add Data LabelsFrom Range and Show Fonts Sheet.

    The most recent version of the utilities can be downloaded here.  
    Posted by andrewe at 07:23

    March 21, 2009

    Unique Random Numbers in VBA

    As mentioned in my last post, this is how I got random numbers for my new Concentration game. It uses the fact that only unique items (as far as I know) can be added to a collection. After attempting to add a new item (random integer), the code then counts the items. If continues to do this until the limit (in this case 52) is reached.

    Sub GetUniqueRandomNumbers()
        Dim iAdd As Integer
        Dim iLimit As Integer
        Dim iCheck As Integer
        Dim iRnd As Integer
        Dim UniqueNumbers As New Collection

        Workbooks.Add

        iLimit = 52

        For iAdd = 1 To iLimit

    StartAgain:

            iRnd = Int((iLimit - 1 + 1) * Rnd + 1)

            On Error Resume Next
            UniqueNumbers.Add iRnd, CStr(iRnd)
            On Error GoTo 0

        Next iAdd

        If UniqueNumbers.Count < iLimit Then GoTo StartAgain

        For iCheck = 1 To iLimit

            ActiveSheet.Cells(iCheck, 1).Value = UniqueNumbers.Item(iCheck)

        Next iCheck

    End Sub

    As promised, the Concentration game has been addded to JMT Excel Utilities, along with some other new utilities.

    Export Charts As Images
    This allows you to choose what charts (all of them if you want) to export in the active workbook in 3 different image formats.

    Show Fonts Sheet
    This shows what fonts you can use with Excel, as well as samples of how the fonts appear.

    In the near future, I hope to add some more chart utilities. But for now, you can download the most recent version here.

    I have also improved the code for Classic Excel Colors. It's download link is here.  
    Posted by andrewe at 09:51

    March 09, 2009

    New Tools

    JMT Excel Utilities has 3 new tools -

  • Highlight Row Differences (as previously promised)
  • Compare Sheets
  • Copy VBE Modules

    The following tools were re-written to work properly with Excel 2007.

  • Delete Active Workbook
  • Rename Active Workbook

    Rounded Comments has been updated too. You can now drag the form to make it larger (or smaller). Great for those oversize comments.

    Enjoy!  
  • Posted by andrewe at 19:34

    March 01, 2009

    Document Properties 2

    CustomDocumentProperties are as the name custom properties that you can add to a workboook, just as BuiltinDocumentProperties exist by default.

    You can add CustomDocumentProperties using a variation of this code.
    ThisWorkbook.CustomDocumentProperties.Add LinkToContent:=False, Name:="Internal Use", Type:=msoPropertyTypeBoolean, Value:=True

    Looking at the Help files, we see that Type in this case is msoPropertyTypeBoolean, there are five types in MsoDocProperties Enumeration.

    msoPropertyTypeBoolean

    msoPropertyTypeDate

    msoPropertyTypeFloat

    msoPropertyTypeNumber

    msoPropertyTypeString

    I think msoPropertyTypeFloat is use for decimal numbers (msoPropertyTypeNumber is used for integers or whole numbers)

    Looking at the bigger picture, CustomDocumentProperties allow you to save your own information regarding a workbook. You can also use save settings to the registry, write to another file such as a text file, or add things to the workbook's cells, comments, even add VBA code to a module. But CustomDocumentProperties have the advantages of both going everywhere the workbook goes and being less likely to be deleted by mistake.
      
    Posted by andrewe at 10:32

    February 07, 2009

    Document Properties

    Document properties allow us to get information about a workbook. Default properties are called BuiltinDocumentProperties in VBA. Straight from the Help files, here is some slightly modified code to view these properties and their values.

    Sub ShowBuiltinDocumentProperties()
    Dim rw As Long
    Dim p As DocumentProperty

    On Error Resume Next

    For Each p In ActiveWorkbook.BuiltinDocumentProperties
        
        Cells(rw + 1, 1).Value = p.Name
        Cells(rw + 1, 2).Value = p.Value
        
        rw = rw + 1

    Next p

    On Error GoTo 0
    End Sub


    And here they are in Excel 2007. I'm not sure whether they are the same in all Excel versions, I'll leave that with you to find out.

    Title
    Subject
    Author
    Keywords
    Comments
    Template
    Last author
    Revision number
    Application name
    Last print date
    Creation date
    Last save time
    Total editing time
    Number of pages
    Number of words
    Number of characters
    Security
    Category
    Format
    Manager
    Company
    Number of bytes
    Number of lines
    Number of paragraphs
    Number of slides
    Number of notes
    Number of hidden Slides
    Number of multimedia clips
    Hyperlink base
    Number of characters (with spaces)
    Content type
    Content status
    Language
    Document version

    To edit a value, use this code, changing the property name and value where required.
    ThisWorkbook.BuiltinDocumentProperties("Author").Value = "Andrew Engwirda"

    Note that you will need to use the right type of value, for example, you cannot use text where Excel would expect a number.

    Short and sweet, next time I'll look at CustomDocumentProperties and what you can do with them.  
    Posted by andrewe at 11:42

    January 25, 2009

    FindControl

    This is an example of FindControl. You can use it to refer to a command bar control by using it's ID. The following code will add a comment by "executing" the Insert Comment control.

    Application.CommandBars.FindControl(ID:=1589).Execute

    Of course, you can just use much simpler to understand code to do the same.

    Activecell.Addcomment

    There are however other advantages to FindControl, such as using the Font Names control to add items to a combobox on a userform. The code below will use FindControl to get the names, then use the userform events to give a preview of what the font looks like just like the control itself. It also set the font back to the original font to make it more legible when changing to another selection.

    Private sFontName As String
    Private bChangeFont As Boolean
    Private iListIndex As Integer

    Private Sub UserForm_Initialize()
        On Error Resume Next

        Dim Fonts
        Dim iAddItem

        iAddItem = 1

        Set Fonts = Application.CommandBars.FindControl(ID:=1728)

        bChangeFont = False

        With ComboBox1

            sFontName = "Tahoma"
            
            .Font.Size = 10

            Do While Err = 0
                .AddItem Fonts.List(iAddItem)

                iAddItem = iAddItem + 1

            Loop
            
            .ListIndex = 0
            .SelStart = 0
            .SelLength = Len(.Text)

        End With
        
        bChangeFont = True

        On Error GoTo 0
    End Sub

    Private Sub ComboBox1_Change()
        
        If bChangeFont = False Then Exit Sub
        
        With ComboBox1
            .Font.Name = ComboBox1.Value
            iListIndex = .ListIndex
            .SelStart = 0
            .SelLength = Len(.Text)
        End With

    End Sub

    Private Sub ComboBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        
        With ComboBox1
            .ListIndex = iListIndex
            .Font.Name = sFontName
            .SelStart = 0
            .SelLength = Len(.Text)
        End With

    End Sub

    Don't forget you can use the same technique to get the font size too. The ID is 1731 :-)  
    Posted by andrewe at 09:02

    November 29, 2008

    Inserting Rows At Column Differences

    The following code might be useful. You can use it to insert rows, lines or pagebreaks where values change in a column by changing one line of code.

    Sub InsertRowsAtColumnDifferences()
        Dim lStartRow As Long
        Dim lLastRow As Long
        Dim lCounter As Long

        On Error Resume Next

        If ActiveWorkbook Is Nothing Then Exit Sub

        If TypeName(Selection) <> "Range" Then Exit Sub

        With Selection

            lStartRow = .Row

            lLastRow = lStartRow + .Columns(1).Cells.Count - 1

            For lCounter = lLastRow To lStartRow Step -1

                If Cells(lCounter, .Column).Value <> Cells(lCounter, .Column).Offset(1).Value Then

                    If Cells(lCounter, .Column).Value <> "" And Cells(lCounter, .Column).Offset(1).Value <> "" Then

                        Cells(lCounter, .Column).Offset(1).EntireRow.Insert 'insert rows
                        
                        '.Rows(lCounter - lStartRow + 1).Borders(xlEdgeBottom).LineStyle = xlContinuous 'add lines
                        
                        'ActiveSheet.HPageBreaks.Add Before:=Cells(lCounter, .Column).Offset(1) 'add pagebreaks

                    End If

                End If

            Next lCounter

        End With

        On Error GoTo 0
    End Sub

    I will be adding and other code this to my utilities in the near future. But I would like to hear from your ideas too.

    What new tools would you like to see? Are there any repetitous or tiresome tasks you perform that could be made eaiser? (Housework and taking out the garbage not included!)

    Send me an email (look on the left of this blog for the address). If you can suggest something that might be useful for a large number of Excel users, I want to hear from you :-)  
    Posted by andrewe at 09:59

    October 25, 2008

    Bring Back Those Old Style Menus

    When Excel 2007 came out, the old style menus were replaced with the Ribbon.

    Actually no, this isn't what really happened.The right click menus are still there (always have been) and the menus and toobars that were formerly at the top were just hidden. But they are still there, and you can unhide them quite easily. (Note that a few menu items such as File Search are disabled for various reasons, although most of them will still work fine) This code will show the top menu and selected items from the Standard and Formatting toolbars on the Add-Ins tab.

    Private Sub ShowOldStyleMenus()
        On Error Resume Next
        Dim cBar As CommandBar
        Dim cBarCtrl As CommandBarControl
        Dim sMenuName As String
        Dim sToolbarName As String
        Dim iMenu As Integer

        sMenuName = "Old Style Menu"
        sToolbarName = "Old StyleToolbar"

        CommandBars(sMenuName).Delete

        Set cBar = CommandBars.Add(sMenuName, , , True)

        With cBar

            .Visible = True

            For iMenu = 1 To 10

                Set cBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30001 + iMenu)

            Next iMenu

            Set cBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30022) 'Chart
            Set cBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30177) 'AutoShapes

        End With

        CommandBars(sToolbarName).Delete

        Set cBar = CommandBars.Add(sToolbarName, , , True)

        With cBar

            .Visible = True

            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=2520) 'New
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=23) 'Open
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=3) 'Save
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=4) 'Print
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=109) 'Print Preview
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=2) 'Spelling
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=21) 'Cut
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=19) 'Copy
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=22) 'Paste
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=108) 'Format Painter
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=210) 'Sort Ascending
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=211) 'Sort Descending
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=984) 'Help
            Set cBarCtrl = .Controls.Add(Type:=msoControlComboBox, ID:=1728) 'Font
            Set cBarCtrl = .Controls.Add(Type:=msoControlComboBox, ID:=1731) 'Font Size
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=113) 'Bold
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=114) 'Italic
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=115) 'Underline
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=120) 'Align Left
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=122) 'Center
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=121) 'Align Right
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=402) 'Merge and Center
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=395) 'Accounting Number Format
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=396) 'Percent Style
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=397) 'Comma Style
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=398) 'Increase Decimal
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=399) 'Decrease Decimal
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=3162) 'Decrease Indent
            Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=3161) 'Increase Indent

        End With

        Set cBar = Nothing
        Set cBarCtrl = Nothing

        On Error GoTo 0
    End Sub


    Here's how it looks.


    Keep in mind it's also possible to do this with XML and do it better, I've seen it done with both freeware and shareware downloads (very impressive) and have also experimented with it myself. But the advantage of doing it with VBA like above is that you can just copy and paste it somewhere like your Personal workbook, or you can email it to someone in a hurry. That said, if you do have 2007, you will probably be better off learning where everything is in the long run :-)

    I've added the above code to the 2007 version of JMT Excel Utilities (Show Old Style Menus in the the Developer Tools tab). The download link is here.  
    Posted by andrewe at 11:19

    September 28, 2008

    Checklist Maker

    This is a file I thought might come in useful at work. Here it is when you open it.



    After entering a suitable title in cell D2, start to enter items starting from (and including) cell D4 downwards. When you that the item number appears in column B and a drop down (data validation) list appears in column C. The default value is X (unchecked). If you choose O (checked), the item row cells, (columns B,C and D) change color as an easy means of identification.



    You can have spaces between the rows. In the following image, I have widened the size of column A and added some dummy headers. Even with spaces, the code will produce the correct item number in column B.



    After your checklist is ready, you can copy it to another workbook using Excel's built-in commands on the sheet tab right click menu. Then you can choose to delete the checklist code or comment it out. The latter might be better if you might need to make changes in future.

    Download it here :-)
      
    Posted by andrewe at 13:28

    September 06, 2008

    Quick Filter Updated

    Quick Filter has new some new buttons.

    If you have used it before, you would remember that you could jump from item to item by double clicking the list below.



    By this, I mean that if you had the same item several times in a row or column, you could jump to each instance, changing the order in which way you jump by using the Previous and Next options.

    You could add navigate to adjacent rows or columns, even when hidden, by pushing the appropriate Up, Down, Left and Right buttons. Doing so would refresh the list, but you could refresh manually if you edited some items since the form was displayed (being modeless for Excel 2000, you can select and edit cells even while the form is displayed)

    Not forgetting the Sort button, the form was a "filter" of sorts and quick to use, hence the name. I liked the fact that I could use it both horizontally and vertically, something that the regular filter could not.

    So what is new? I have added a button to "Filter" a selected item in the list. This very basic capability will hide all other rows or columns, depending whether the Horizontal Filter or Vertical Filter options are used. Not much of a feature? Well, you can also apply the filter on a group of selected cells, instead of an entire row or column if you prefer. And also, already hidden rows and columns before filtering will stay that way when you push the Show All button. That makes it a little more interesting :-)

    Quick Filter is available with 2 of my downloads.

    JMT Excel Utilities

    New Book Navigator

    Hope it comes in useful!  
    Posted by andrewe at 10:45

    August 24, 2008

    File History 2

    I have added a feature to my File History add-in which allows you to bookmark files and folders. On both the Recent Files and Recent Folders menu, you will see new "add" buttons as below (Add Active Workbook to Bookmarks and Add Active Workbook Folder to Bookmarks in the case of Recent Folders)



    Once a workbook or it's folder is bookmarked, a new menu item appears named Bookmarks. You can move their position and delete them if necessary by selecting Edit Bookmarks.



    For lots of bookmarks, or those with long paths, you can resize the form by dragging it on the right or bottom. (The cursor will change shape and a tooltip will appear)

    Open the bookmarks themselves the same way as the files in Recent Files and folders in Recent Folders, just click on them.

    It's a big timesaver. Here is the download link :-)  
    Posted by andrewe at 09:38

    July 20, 2008

    Replace Text Tools

    Another tool has been added to JMT Excel Utilities. Here it is in action.



    It's good for parsing names and stuff. Just be sure check "Include text to find?". In the example above, the find text is both a comma and a space.

    Hope it comes in useful, the download link is here :-)  
    Posted by andrewe at 08:07