July 28, 2005

Comments Made Simple(r)

Doug Glancy kindly suggested making my code run more like a regular userform in the comments of my last post.

So this is what I came up with. Enter the text as before by pushing the OK button.



Then choose to add another line or continue by inserting the comment.



You can then choose to keep the comment visible or hide it from view. With a bit of practice you can enter lines and comments quite quickly. A shortcut would be a really good idea if you use it a lot.

Here's the new code.

Sub AddComment()
    On Error Resume Next
    Dim cmtMsg As String, nwLne As String, LneCnt As Long, Dscn As Long
    If TypeName(Selection) <> "Range" Then Exit Sub
AddLine:
    nwLne = InputBox("Enter line " & LneCnt + 1 & " of your comment text." & vbNewLine & vbNewLine & _
                "Leave blank or push Cancel to exit.", "Please write your comment")
    If LneCnt > 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg = nwLne
    LneCnt = LneCnt + 1
    If nwLne = "" Then
        Exit Sub
    Else
        Dscn = MsgBox("Do you want to add another line? " & vbNewLine & vbNewLine _
                & "Push No to insert the comment.", vbYesNo, "Add another line?")
        If Dscn = vbYes Then GoTo AddLine
        Application.ScreenUpdating = False
        With ActiveCell
                .ClearComments
                .AddComment
                With .Comment
                    .Visible = True
                    .Shape.AutoShapeType = msoShapeRoundedRectangle
                    .Shape.Shadow.Visible = msoFalse
                    .Shape.Select True
                    .Text Text:=cmtMsg
                    With Selection.ShapeRange
                        .Shadow.Visible = msoFalse
                        .ScaleHeight 0.3, msoFalse, msoScaleFromTopLeft
                        .Adjustments.Item(1) = 0.25
                    End With
                .Shape.TextFrame.Autosize = True
                Dscn = MsgBox("Do you want the comment to remain visible? ", _
                    vbYesNo, "Keep comment visible?")
                If Dscn = vbNo Then .Visible = False
            End With
            .Select
        End With
        Application.ScreenUpdating = True
    End If
End Sub



Hmm...me like :-)

Update: Andy Pope sent me a file to show an even better job can be done with a Multiline Textbox. There's no need to push buttons to add new lines, just use the Enter key. This also has a great advantage in that you see all of the text, not just one line at a time) When finished you can enter the text, by pushing Tab, Enter (This enables the Ok button to insert the comment)

Here's a picture of it in action.



You can download his file here.

More good code for me to study. Thanks also to Jon Peltier for suggesting the same thing.
  

Posted by andrewe at 19:55

July 27, 2005

Comments Made Simple

This is an idea I had this morning at work. Instead of of all the hassle that usually goes with writing and then resizing comments, wouldn't it be nice to do everything in just one go?

This code inserts a comment and allows you to enter the multi-lined text like using a typewriter. Enter one line at a time and it's resized at the end automatically.

Sub AddComment()
    On Error Resume Next
    Dim cmtMsg As String, nwLne As String, LneCnt As Long, shCmt As Long
    If TypeName(Selection) <> "Range" Then Exit Sub
AddLine:
    nwLne = InputBox("Text Line: " & LneCnt + 1 & vbNewLine & vbNewLine & _
                "New Text Lines are added automatically." & vbNewLine & _
                "Leave blank or push ""Cancel"" to exit.", "Please write your comment")
    If LneCnt > 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg = nwLne
    LneCnt = LneCnt + 1
    If nwLne <> "" Then GoTo AddLine
    cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
    Application.ScreenUpdating = False
    With ActiveCell
        .ClearComments
        .AddComment
        With .Comment
            .Visible = True
            .Shape.AutoShapeType = msoShapeRoundedRectangle
            .Shape.Shadow.Visible = msoFalse
            .Shape.Select True
            .Text Text:=cmtMsg
            If cmtMsg <> "" Then .Shape.TextFrame.AutoSize = True
            shCmt = MsgBox("Do you want the comment to remain visible? ", _
                    vbYesNo, "Keep comment visible?")
            If shCmt = vbNo Then .Visible = False
            End With
        .Select
    End With
    Application.ScreenUpdating = True
End Sub

Works like this...



And looks like this...



Put it in your Personal Workbook (standard module) and add a macro button to a menu or toolbar to make it easier still :-)  
Posted by andrewe at 20:03

July 24, 2005

Keyboard Shortcuts

Keyboard shorcuts save a lot of time. Here are some of the more common ones (they can also be used in a lot of other software other than Excel)

Push both keys at the same time -

  • Ctrl + A    Select All

  • Ctrl + C    Copy

  • Ctrl + X    Cut

  • Ctrl + V    Paste

  • Ctrl + B    Toggle Bold Font

  • Ctrl + I    Toggle Italic Font

  • Ctrl + U    Toggle Underline


  • There's a few more you can refer to here. (Scroll to the bottom)

    DIY Shorcuts
    You can also make your own shortcuts to work with macros. Here are some very simple ones that I find useful. (Paste Values is already available as a toolbar button as well as Paste Formats, and all of them are available from the Paste Special dialog box, accessed by Edit, Paste Special. I've just found using keyboard shortcuts are a lot more convenient to use in this case)

    Placed in a Personal Workbook standard module so you can use them all the time.

    Sub PasteFormulas()
    On Error Resume Next
        Selection.PasteSpecial Paste:=xlFormulas
    End Sub

    Sub PasteValues()
    On Error Resume Next
        Selection.PasteSpecial Paste:=xlValues
    End Sub

    Sub PasteComments()
    On Error Resume Next
        Selection.PasteSpecial Paste:=xlComments
    End Sub

    Then return to Excel and go to Tools, Macro, Macros, select the macro you want, push Options, assign your preferred shortcut keys, push Okay, then Cancel.

    The shortcut keys I use are as follows.

  • Ctrl + Shift + F    Paste Formulas

  • Ctrl + Shift + V    Paste Values

  • Ctrl + Shift + C    Paste Comments


  • Don't forget something must already be copied to the Clipcoard (Ctrl + C) for these to work.

    Note. I haven't inluded "Application.CutCopyMode = False" (this clears the Clipboard) which means you can continue pasting just like using the regular paste (Clipboard) button.  
    Posted by andrewe at 15:30

    July 20, 2005

    More Excel Toys

    Here are a couple of new add-ins I made over the last week.

    Excel Calendar
    I was disappointed with the slow calculation time of my Calendar Toolbar so I decided to make something with a userform instead.



    This calendar uses variables instead of relying on formulas within cells. I've tested it at my job to see if any speed was lost when working with very large files. I'm happy to say that the code seems to run a lot faster. A valuable lesson learned. (If you look closely, you'll see that it uses a simplified version of my worksheet calendar formula but written in VBA)

    Also, I'd like to thank Colo for his suggestion of using a Class module to avoid using the same code over and over again for each button. It's things like this that keep everything nice and simple, not too mention a good experience for me to practice with a new way of coding :-)

    Here's the download. It's just a date picker at the moment, I might add more features later on.

    Excel Calculator
    This too uses variables only. It wasn't too hard using worksheet formulas in cells, using variables was a bit more difficult but I got there in the end. Same story with the speed, it works a lot faster.



    Here's the download, I hope you find it useful.  
    Posted by andrewe at 22:03

    July 09, 2005

    New Calendar Toolbar

    I decided to add a few more features to my Calendar Toolbar.

    This version allows to enter years via a text box and select months with a dropdown list. This makes it easy when you want to navigate fast. Otherwise just use the arrow buttons to move one year or month at a time as before.



    You'll also notice some buttons have symbols instead of numbers (I hope there won't be any font problems using English Excel) The triangle mark shows today, the circle marks can be added or removed if you want to use reminders or show holidays. It's a bit basic but will have to do until I design something better ;-)

    The download link is here. Don't forget to read the updated read me file.
      
    Posted by andrewe at 23:34

    July 04, 2005

    Calendar Toolbar

    Okay, who saw this coming? I thought about making this last night...well, why avoid the temptation? Excel should be fun.



    Then again maybe this add-in making thing really is becoming an obsession...at least my code seems to be getting a little better ;-)

    Here's the download link. Hope it comes in handy.  
    Posted by andrewe at 21:39

    July 03, 2005

    Instant Calendar

    One of the most amazing formulas I've ever seen is this one.

    As posted by John Walkenbach (aka J-Walk) on Dick Kusleika's Daily Dose of Excel, Ugly Formulas...

    Enter as array formula in 6 rows x 7 columns (Push Ctrl, Shift and Enter simultaneously) You may have to format as days by right-clicking the selection, choosing Format Cells, Number, Custom, entering "d" and pushing OK.

    =IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))<>MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(NOW()),MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)



    I couldn't believe this when I first saw it. A monthly calendar using just one formula!?

    Well, it so happens I made a perpetual calendar early last year (pre-blogging days). It used LOOKUP to refer to a table in which each month's days where placed. It was a matter of selecting the correct month, then hiding days that exceed the last day of the month.

    Anyway, I had a close look at it last week and thought...yeah, I can make this into just one formula too, so here it is...

    No formatting is required and it can be entered pushing Enter and Ctrl but it must be entered into range A1:G6. (You can get around this by subtracting ROW() and COLUMN() so that they both equal 1 for the top left cell, so for Cell C25 this would mean ROW() becomes ROW()-24 and COLUMN becomes COLUMN()-2)

    =IF(IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-(COLUMN()+((ROW())*7)-7)>=1,"",SUM((COLUMN()+((ROW())*7)-7)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1)))+1)>DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0)),"",IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-(COLUMN()+((ROW())*7)-7)>=1,"",SUM((COLUMN()+((ROW())*7)-7)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1)))+1))

    or you can use the second half of the formula,

    =IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-(COLUMN()+((ROW())*7)-7)>=1,"",SUM((COLUMN()+((ROW())*7)-7)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1)))+1)

    and then use Conditional Formatting as below (format the font color to match the background)



    Hmm, not as good but not too bad considering it wasn't my intention to make a one formula calendar in the first place. Anyway, the moral of this story is...what you thought was impossible then, might be possible now...anyone can learn some new stuff, so make the most of it ;-)

    P.S. I made a simple perpetual calendar sheet to download here. Don't forget to check out Mark Wielgus's AutomateExcel blog too about the same subject. Seems that everyone's doing it :-)

    Update: Here's a shorter version I wrote after some prompting on my Japanese blog.

    =IF(OR(COLUMN()+ROW()*7-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-6<1,COLUMN()+ROW()*7-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-6>DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0))),"",COLUMN()+ROW()*7-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-6)

    Adjusting it a lot easier too. ;-)  
    Posted by andrewe at 17:36