May 25, 2006

Cell Watch Toolbar

I had reason to try making a new toy this week. It's quite similar to Dynamic Comments and I can't help noticing that Jan Karel Pieterse on the Daily Dose of Excel made something that works with a user form that is very interesting (much better code than mine of course).

Anyway, here's what I have done so far. Launch it from the Tools menu at the top of Excel.

Push the little "plus" button to change the Watch Cell (the cell to be watched, just like the Watch Window toolbar, but far more simpler)



The Cell Watch userform pops up showing the active cell's column, row and active sheet. Use the dropdown lists to set yourself withing the activeworkbook and then push one of these four buttons.

  • Column

  • Row

  • Sheet

  • Fixed


  • The active book and sheet you choose will be recorded. Now depending on the choice you made, you can select another sheet or book and far right caption of the toolbar will change accordingly.

    Let's say you selected, "Column" with the active cell in D. No matter which cell you select in whichever sheet or book, the value shown in D for the corresponding row in the recorded sheet and book will show in the toolbar. So selecting J7 will cause the toolbar to show the value in D7 of the recorded sheet and book. Keep in mind you can use this to show values in hidden rows and columns too, use the dropdown lists to select them.

    Row works the same way as Column but this time the row is fixed (plus the sheet and book) Of course, there is nothing to stop you from using the same sheet and/or book as recorded. A possible workaround when you can't see headings at the top? An alternative to freezing panes?

    Sheet shows value of the cell that corresponds with the fixed sheet and book (same cell address).

    Fixed makes everything static, always the same cell in the same sheet and book. But it might come in hand to make sure a certain cell has not changed according to something you just entered.

    Hover the cursor over the caption to see the option you have chosen, the watch cell path and address, then it's formula and format. (This is a litte buggy at times but is handy when okay)



    Two more things. Push the button with the "pencil" icon, you can enter text of your choice into the caption. Push it again and it changes back to normal.



    And finally, push the watch cell caption itself to enter it's value into the clipboard. Just the thing when you want to paste somewhere else, even a different program like Word or Outlook. Have fun.

    Plans exist to make a better version when I have time, the current download link is here.

    Speaking of improvements, I did make a change to New Book Navigator. You can enter your own names for the Highlight shapes using an input box. This means you can select a different group of cells and name all of them "May Sales, 2006". Then when it comes time to remove them, enter the same name and only those highlight shapes will be removed. Very convenient, there's more stuff coming up for this addin too. Get the latest version here.  

    Posted by andrewe at 20:39

    May 17, 2006

    Even More Buttons

    Like I mentioned before, I was planning to add some more buttons to the New Book Navigator toolbar. Actually there is still something I want to add, I will do so if I can write the code sometime.

    Anyway, here's what I added today.

    First there are some new buttons to highlight selected areas and to remove them as the case may be. I have similar options with my other utilities that work in the same way (although the cells are highlighted, the format does not change, instead Autoshapes are used in such a way that the cells can be selected).

    And I added a way to deselect cells. If you keep the Ctrl key depressed, you can select (deselect) multiple areas.



    You might ask why I just don't add these features to my utilities? Well, some may be in future, I'll keep it in mind. But the ones I added to New Book Navigator are ones I use all the time, so I want them handy just like I wanted Rounded Comments next to the other comment options on the right click (Cell) menu. And both of these are rather specialized, for the time being I figured it was better keeping them as separate addins.

    One thing I am learning is it not just about writing code, it's the design that makes things work together.

    The latest version is here.  
    Posted by andrewe at 18:48

    May 11, 2006

    More Buttons

    I am really starting to worry about my Excel addiction.

    I had a couple of days free during the holidays so I rewrote the code for Rounded Comments. And I added a new button called "Comment" (Thanks Hui) that allows you to add comment text to selected cells.

    Values and Formulas only work with non-blank cells too.



    I had a slight fever at the time (Why do I have to catch a cold during my holidays!) so I hope the code is okay, at least my computer hasn't done anything strange yet.

    Download Rounded Comments

    And today I started adding new buttons to New Book Navigator (yet again)

    The new buttons make selecting cells easier. One set allow you to select cells offset to a selection. Hidden and filtered rows / columns are avoided and multiple areas are okay. And the other buttons allow you to select cells to the outer limits of the used range, kind of like Ctrl + the arrow buttons but you can go all the way to the end each time and use multiple areas are okay in this case too.



    Download New Book Navigator

    When I get a chance I'll probably add something else. This week I was working on a game for a friend's son. Both he and I are happy so far :-)

    Is it Thursday already? Time to get packed again, have a good weekend.  
    Posted by andrewe at 20:53