Moving Sheet Groups within a Workbook

Last week I created a keyboard shortcut to move a sheet within a workbook. This week I’m changing it to work with groups of sheets rather than just the active sheet. Not because I need it. I rarely work with grouped sheets as it is. But sometimes you have to program just for the fun of it.

This will require a change to NextVisibleSheetIndex function. I tried to determine if the ActiveSheet was in a group and where it was in the group. That resulted in some inelegant code and I could tell I was doing it wrong. Then I realized that I should stop futzing with the ActiveSheet and just pass a sheet into the function where I want to start. That made things much simpler.

Public Function NextVisibleSheetIndex(ByRef shStart As Object, ByVal bDown As Boolean) As Long
   
    Dim lReturn As Long
    Dim i As Long
   
    If bDown Then
        For i = shStart.Index + 1 To ActiveWorkbook.Sheets.Count
            If ActiveWorkbook.Sheets(i).Visible Then
                lReturn = i
                Exit For
            End If
        Next i
    Else
        For i = shStart.Index - 1 To 1 Step -1
            If ActiveWorkbook.Sheets(i).Visible Then
                lReturn = i
                Exit For
            End If
        Next i
    End If
   
    NextVisibleSheetIndex = lReturn
   
End Function

That kept the function code cleaner, but I still had to figure out what sheet to pass in. Well, that turned out to be really easy. If It was moving left, I pass in the first sheet in the group.

Sub MoveSheetsUp()
   
    Dim ssh As Sheets
   
    Set ssh = ActiveWindow.SelectedSheets
   
    If ssh(1).Index = FirstVisibleSheetIndex Then
        If Timer - msnLastWrap > msnWRAPBUFFER Then
            gclsAppEvents.AddLog "^%{PGUP}", "MoveSheetsUp"
            ssh.Move , ActiveWorkbook.Sheets(LastVisibleSheetIndex)
        End If
    Else
        ssh.Move ActiveWorkbook.Sheets(NextVisibleSheetIndex(ssh(1), False))
    End If
   
    msnLastWrap = Timer
   
End Sub

And if I’m moving right, I pass in the last sheet.

Sub MoveSheetsDown()
   
    Dim ssh As Sheets
   
    Set ssh = ActiveWindow.SelectedSheets
   
    If ssh(ssh.Count).Index = LastVisibleSheetIndex Then
        If Timer - msnLastWrap > msnWRAPBUFFER Then
            gclsAppEvents.AddLog "^%{PGDN}", "MoveSheetsDown"
            ssh.Move ActiveWorkbook.Sheets(FirstVisibleSheetIndex)
        End If
    Else
        ssh.Move , ActiveWorkbook.Sheets(NextVisibleSheetIndex(ssh(ssh.Count), True))
    End If
   
    msnLastWrap = Timer
   
End Sub

Moving Sheets within the Workbook

Moving sheets within a workbook is one of those things that’s just easier with a mouse. You can click on a sheet, drag it, and the little arrow tells you where it will land. If you hold down the Ctrl key while you do it, you’re copying.

If you’re more of a keyboard guy (ahem), you probably already know that you can use Alt+HOM (Home > Format > Move or Copy Sheet…) to get the Move or Copy dialog. Of course it’s under Format. I mean, really, that’s the natural place for it. Home > Format is the Insert Tab of dropdowns, if you get my meaning.

That dialog defaults to the same workbook, so you’re moving within the workbook by default. Back in 2009, I took over the Move or Copy dialog as I am wont to do. That uses Alt+EV – the 2003 method for displaying the Move or Copy dialog. Back then, I settled on using my mouse to move sheets within a workbook and using my simpler dialog for moving them between workbooks. I’m very happy with my dialog box and use it all the time.

Today I decided to make a keyboard shortcut for moving within a workbook. I already have code to wrap while switching worksheets. I decided on Ctrl+Alt+PgUp/PgDn. Just Ctrl is how you navigate from one worksheet to the next. Ctrl+Shift groups worksheets together. So that left me with Ctrl+Alt. After one use, I can honestly say that I’m in love.

First I add my shortcuts to Auto_Open

Application.OnKey "^%{PGUP}", "MoveSheetsUp"
Application.OnKey "^%{PGDN}", "MoveSheetsDown"

and to turn them off in Auto_Close

Application.OnKey "^%{PGUP}"
Application.OnKey "^%{PGDN}"

The code is pretty darn similar to the Wrap Sheets code except that it moves the sheet rather than activating it.

Sub MoveSheetsUp()
   
    If ActiveSheet.Index = FirstVisibleSheetIndex Then
        If Timer - msnLastWrap > msnWRAPBUFFER Then
            gclsAppEvents.AddLog "^+{PGUP}", "MoveSheetsUp"
            ActiveSheet.Move , ActiveWorkbook.Sheets(LastVisibleSheetIndex)
        End If
    Else
        ActiveSheet.Move ActiveWorkbook.Sheets(NextVisibleSheetIndex(False))
    End If
   
    msnLastWrap = Timer
   
End Sub

Sub MoveSheetsDown()
   
    If ActiveSheet.Index = LastVisibleSheetIndex Then
        If Timer - msnLastWrap > msnWRAPBUFFER Then
            gclsAppEvents.AddLog "^+{PGDN}", "MoveSheetsDown"
            ActiveSheet.Move ActiveWorkbook.Sheets(FirstVisibleSheetIndex)
        End If
    Else
        ActiveSheet.Move , ActiveWorkbook.Sheets(NextVisibleSheetIndex(True))
    End If
   
    msnLastWrap = Timer
   
End Sub

If you don’t want to add this code to your PMW (YET!), you can try it out here.

You can download MoveSheetsKeyboard.zip

Todoist vs GoodTodo

Recently some guys I work with started using Todoist. It’s a todo list application with collaboration features. They set me up with an account and I tried it for a month. Here’s what I learned.

I’m sticking with GoodTodo. Sorry for the early spoiler, but since this is an Excel blog, some of you may not care about the details.

I meant to post this earlier, but never got around to it. Then I got an email that GoodTodo is raising their prices. It’s still a good deal to me. The email just reminded me to get off my butt and post my review. Also, my “really inexpensive” comment from six years ago needs to me modified to “moderately inexpensive”.

One thing before I get to the details: I’m only evaluating these applications based on features I care about. I don’t care about collaboration, so Todoist doesn’t get any points from me. If you care about collaboration, well, we’re different then.

Exporting
Remind me never to write mystery novels. I’ve already told you I’m sticking with GoodTodo and now I’m starting with the number one reason why. Anytime I want, I can send all my past, present, and future todos from GoodTodo to my inbox. Not so much with Todoist. Their export feature doesn’t export completed todos nor does it export the comments associated with the todos.

They do have an API. Presumably I could write my own code to get whatever I want, but that was beyond what I was willing to do.

This was a deal breaker for me. It’s my data even if it’s on your server and you shouldn’t restrict my access to it.

Point: GoodTodo

Entering Todos
GoodTodo has a feature where if you send an email to today@goodtodo.com from an email it recognizes, it will create a todo from the email. It supports all kinds of email addresses like tomorrow@, nextfriday@, and even specific dates.

As I’ve used GoodTodo over the years, I literally only send things to “today” and I move them from within the site if I need to. The fact that I don’t use this feature to it’s fullest doesn’t mean it’s not useful. I just means Mark did a lot of email parsing code that I never use. I still email todos quite a bit.

The downside to entering emails is that the subject of the email becomes the title of the todo. It’s pretty rare that I would have titled my todo like that if I were creating it from scratch, particularly if the email originated from someone else. I’m pretty careful with my email subjects, but I’m the exception, not the rule. If the subject is particularly egregious, I can just change the title in GoodTodo. If it’s good enough, I generally don’t.

Todoist does not support emailing to your list. If I had an email that I needed to put on my todo list, I copied the contents and pasted it into a new todo. That’s not as convenient as forwarding, but it honestly wasn’t that bad.

Todoist does support keyboard shortcuts in its web app. I had a twitter back-and-forth with them about how I couldn’t do certain things without the mouse, the result of which was I figured out how to do everything I need with the keyboard. It’s clunky. Things lose focus and, in one case, I have to attempt to close the todo without saving, cancel the close when I get the “save now?” message, and that returns the focus to where I need it. Not great, but workable.

Goodtodo has no keyboard shortcuts. I can navigate fairly well with my normal web page navigating tricks, but there is at least one task where I must use the mouse.

Point: A slight edge to Todoist because I’m a keyboard freak.

Editing Todos
GoodTodo provides a title field and a description field. I use the description field to record what I’ve done and what my next action is. It’s all freeform with no real structure. I have an AutoHotKey so that if I press Ctrl+; it enters the current date wherever I am, not just in Excel. So a todo might look like this:

NA: EM KK for truck revenue files

8/4/2016: Got revenue file and processed them

NA: EM JA for fee structure

8/5/2016: JA says fees will not be ready until Wednesday

Basically it’s a date stamp with something I’ve done (like EM (meaning I emailed someone)) and a bunch of NAs (next actions) for what I need to do next. It works and it’s fine.

Todoist has comments that are time stamped. I didn’t have to put the date next to my action, it was already done. That was pretty handy. I also like the structure of comments being a separate entity from the todo item.

Point: Slight edge to Todoist, but I can live with either one

Completing Todos
When you complete a todo in Todoist, it goes away. And it’s really hard to find after that. It’s there and you can get to it, but it’s not easy.

GoodTodo puts a checkmark next to it and moves it to the bottom of the page. If you want to know what you’ve done today, scroll down. If you want see what you did yesterday, navigate to that page and see what’s checked off.

It probably doesn’t seem like a big deal, but it turned out to be important to me.

Point: GoodTodo

Not Completing Todos
Yes, this does happen from time to time. If I don’t get something done today, GoodTodo moves it to tomorrow sometime after I’ve gone to bed (I think).

Todoist still shows me the todo, but it’s under a heading with the date it was added. It’s a lot of wasted space and clutter. Just because I added a todo on a certain day doesn’t mean it has anything to do with that day. I have four things left on my today todo list and I’m not going to get to any of them. I put them on there in case I did get to them, not because they were due today.

Point: GoodTodo

So I’m sticking with GoodTodo. I’d love to have a todo list app written just for me and maybe someday I will. It will have the rolling forward of GoodTodo, the timestamped comments of Todoist, the keyboard shortcuts of Todoist but slightly better, and most importantly I can export all my data any time.

This Theme Stinks

I found this nice plugin called wp-markdown. It lets you write DDoE comments like Stackoverflow answers, that is, markdown with preview. But it doesn’t work with my 10 year old theme.

I’m sure I’ve needed a new theme for a while. But if you’re like me, and you probably are if you’re reading this, you don’t like change. Now get off my lawn.

So if I switch to a fancy new theme, wp-markdown works. Seriously, try leaving a comment at the end of this post and see how awesome it is.

I don’t know why themes don’t take up the whole screen. I hate it when the words are all squished. I paid for a 22″ monitor and I want to use it!

I’ll leave this new theme up for a few days and see how it goes. In the meantime, I’ll still be looking for a new theme that looks and acts more like the old one.

Unique Entries in Userform Dependent Listboxes

Deepthi commented

…could you please help me tweak the code so that I can make multiple selections in listbox 1 in such a way that the values selected in list box two has all the values applicable for the selections made (listbox) but removes all duplicates?

First, a word about that post. I have used the relationship listbox template exactly zero times. I simply prefer to build my classes from scratch with names that reflect the business objects they represent. But I did reuse the userform and I didn’t change the control names from Parent/Child to Class/Student. I’m conflicted about that, but I’ll get over it.

Let’s say we have some classes and students. A class can have many students and a student can have many classes.

When you select a class, the userform lists the students. If you select more than one class, the userform lists all the student from the selected classes, but each student is listed only once.

Andrew and Payton are only listed once.

There are some significant changes to the code, not the least of which is removing the grandchildren. Also instead of tracking ActiveParent (singular), I now track ActiveClasses (plural) because my top listbox is now multiselect. When my Parent listbox changes, I have to see all the classes that are selected.

Private Sub lbxParents_Change()

    Dim clsClass As CClass
    Dim i As Long

    If Me.lbxParents.ListIndex <> -1 Then
        Set Me.ActiveClasses = New CClasses
        For i = 0 To Me.lbxParents.ListCount - 1
            If Me.lbxParents.Selected(i) Then
                Me.ActiveClasses.Add Me.Classes.ClassByClassName(Me.lbxParents.List(i))
            End If
        Next i
    Else
        Set Me.ActiveClasses = Nothing
    End If

    FillChildren

End Sub

Private Sub FillChildren()

    Me.lbxChildren.Clear

    If Not Me.ActiveClasses Is Nothing Then
        If Me.ActiveClasses.StudentCount > 0 Then
            Me.lbxChildren.List = Me.ActiveClasses.StudentList
            Me.lbxChildren.ListIndex = 0
        End If
    End If


End Sub

To get a unique student list, I use a dictionary object. My favorite thing about dictionaries is returning a zero-based array from the Keys or Items properties.

Public Property Get StudentList() As Variant

    Dim clsClass As CClass
    Dim clsStudent As CStudent
    Dim dcReturn As Scripting.Dictionary

    Set dcReturn = New Scripting.Dictionary
   
    For Each clsClass In Me
        For Each clsStudent In clsClass.Students
            If Not dcReturn.Exists(clsStudent.StudentName) Then
                dcReturn.Add clsStudent.StudentName, clsStudent.StudentName
            End If
        Next clsStudent
    Next clsClass
   
    StudentList = dcReturn.Keys
   
End Property

You can check out the rest of the code in the downloadable file.

You can download ParentChildUserformMulti.zip

Matching Column Widths

Here’s a report for a high volume, low margin product. Because the profit is so much smaller than sales and costs, column D is narrower than columns B and C.

Another example is the following table with names across the top. When the column widths are set to autofit, they all become different widths. Of course that simply won’t do.

Drag Multiple Columns

The first technique, and likely the most common, is to select all the columns and change the width of one of them. That will change the width of all of them. In the below figure, I select the entire columns B through K. It appears that column D is the largest so I select the column divider between D and E and drag it a few pixels to the right, then drag it back.

This changes all the selected columns to the width set for column D.

Well, that’s all I have to say about setting column widths. Of course I’m kidding. Let’s look at some keyboard only methods.

Format Column Widths

Select any cell in column D and click the Column Widths button on Home – Cells – Format (Alt + H + O + W). That will tell you the width of column D.

Make a note of the width and dismiss the dialog box (Esc). Now select cells in every column you want to change. For example, I selected B2:K2. It doesn’t have to be row 2. In fact, it could be multiple rows. All that matters is that every column that you want to change is included in the selection. Because the column widths aren’t the same, the Column Width dialog is empty.

I can type 8.43 in that box (the width of column D that I looked up earlier) and all the columns will be set to that width.

Paste Special

To use this method, select D2 and copy it (Ctrl+C). Next, select B2:K2 and choose Paste Special from the Ribbon (Alt + H + V + S). Choose the Column widths radio button (Alt+w) and click OK (Enter).

Build Your Own

You knew this was coming, didn’t you? Didn’t you? I wrote a macro and assigned it to Ctrl+Shift+W.

Public Sub MatchColumnWidths()
   
    Dim lMax As Double
    Dim rCell As Range
   
    gclsAppEvents.AddLog "^+w", "MatchColumnWidths"
   
    If TypeName(Selection) = "Range" Then
        If Selection.Cells.Count > 1 Then
            'if the first cell is active, set all columns to the biggest column
            If ActiveCell.Address = Selection.Cells(1).Address Then
                For Each rCell In Selection.Rows(1).Cells
                    If rCell.ColumnWidth > lMax Then lMax = rCell.ColumnWidth
                Next rCell
                For Each rCell In Selection.Rows(1).Cells
                    rCell.EntireColumn.ColumnWidth = lMax
                Next rCell
            'if the user selected a particular cell (not the first one), set
            'all columns to the selected column
            Else
                For Each rCell In Selection.Rows(1).Cells
                    rCell.EntireColumn.ColumnWidth = ActiveCell.ColumnWidth
                Next rCell
            End If
        End If
    End If
   
End Sub

Now I can select a range, press Ctrl+Shift+W, and my column widths are set. From the examples above, I select B2:K2, press Ctrl+Shift+W, and all the columns match the largest column (D). If you simply select a range, it will make all the columns the same size as the largest column. If you want to choose a different column, first select the range, then use the Tab key to move to the column you want to mimic.

If you want to mimic the first column, and it’s not the largest, you have to select more than one row and press Enter to move to first the column in the second row.

Cleaning Up My JoinRange Arguments

I’m trying to make my JoinRange function better and I’m failing miserably. A few years ago I added a “macro” argument because I was making so many HTML and Trac tables. I don’t use Trac anymore and I almost never make HTML tables (because I blog so infrequently, I guess). I got rid of that argument. The reason I join ranges most often is to create a big In clause in SQL. Let’s say I have this list of customer IDs and I want to make an In clause.

38
142
146
175
214
217

I’d use JoinRange like

=JoinRange(A2:A7,,"','","('","')")

That’s a freakin’ mess. The second argument is the now-defunct macro argument and is blank. The rest of the arguments are

3rd (delimeter): single quote, comma, single quote
4th (beginning): open paren, single quote
5th (ending): single quote, close paren

and I’d get

('38','142','146','175','214','217')

which I could paste into my SQL statement and roll. I hate typing those arguments. Worse, I hate reading those arguments. It’s pretty hard to read in this blog, but it’s worse in Excel’s formula bar. I thought if I could get rid of the single quotes, it would be cleaner. I rewrote the code to add a Quote argument that would wrap every entry in whatever quotes I supplied.

Public Function JoinRange(rInput As Range, _
    Optional sDelim As String = vbNullString, _
    Optional sLineStart As String = vbNullString, _
    Optional sLineEnd As String = vbNullString, _
    Optional sBlank As String = vbNullString, _
    Optional sQuotes As String = vbNullString) As String
   
    Dim vaCells As Variant
    Dim i As Long, j As Long
    Dim lCnt As Long
    Dim aReturn() As String
   
    vaCells = rInput.Value
    ReDim aReturn(1 To rInput.Cells.Count)
   
    For i = LBound(vaCells, 1) To UBound(vaCells, 1)
        For j = LBound(vaCells, 2) To UBound(vaCells, 2)
            lCnt = lCnt + 1
            If Len(vaCells(i, j)) = 0 Then
                aReturn(lCnt) = sQuotes & sBlank & sQuotes
            Else
                aReturn(lCnt) = sQuotes & vaCells(i, j) & sQuotes
            End If
        Next j
    Next i
   
    JoinRange = sLineStart & Join(aReturn, sDelim) & sLineEnd
   
End Function

Now, my formula looks like this:

=JoinRange(A2:A7,",","(",")",,"'")

I think we can all agree that this is no better than what I had before. I thought the quotes were the problem, but it’s also that I use a comma as the delimiter and it’s the thing that separates the arguments. If I change it to pipe delimited…

=JoinRange(A2:A7,"|","(",")",,"'")

Nope. It’s still a headache to read. Based on the number of comments to this post, I’m pretty sure none of you are using predefined names in your book.xlt file. But I do. And If I’m using a workbook that I created, I could use

=JoinRange(A2:A7,xlCOMMA,"(",")",xlSINGLE)

That’s definitely more readable to me. I guess I need a macro to add those names to any books automatically so I can use them.

Public Sub AddConstantNames()
   
    ActiveWorkbook.Names.Add "xlCOMMA", "="","""
    ActiveWorkbook.Names.Add "xlSPACE", "="" """
    ActiveWorkbook.Names.Add "xlDOUBLE", "="""""
    ActiveWorkbook.Names.Add "xlSINGLE", "=""'"""
    ActiveWorkbook.Names.Add "xlPARENO", "=""("""
    ActiveWorkbook.Names.Add "xlPARENC", "="")"""
    ActiveWorkbook.Names.Add "xlPIPE", "=""|"""
   
End Sub
=JoinRange(A2:A7,xlCOMMA,xlPARENO,xlPARENC,xlSINGLE)

I’m not crazy. I swear this all makes sense in my head. Plus, if you’ve read this far, you’re probably crazy too.