+ Reply to Thread
Results 1 to 7 of 7

Macro for calendar like comment

  1. #1
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Macro for calendar like comment

    I'm looking to create a macro so that when you type E into a cell it automatically opens up a comment box for editing with the some pre-written text in the comment box all in bold like:

    Event:

    Date:

    Location:

    I modified a macro which can do this in cell a1 to give some ideas of format I'm looking for:
    Please Login or Register  to view this content.
    The above code really only shows the format of the comment box I'm looking for. The way the macro is to work is best demonstrated by a macro I found on another forum which actually opens up the comment to be edited and the comment is deleted when the cell value is deleted. I'm looking for a combination of the two but I can't seem to isolate the code I'm looking for also I only want the macro to run when the letter 'e' is written in the cell.

    Thanks
    Please Login or Register  to view this content.
    Last edited by Leith Ross; 05-09-2012 at 03:01 PM. Reason: Added Code Tags

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro for calendar like comment

    Hello pauld.ccvw,

    Welcome to the Forum!

    Here is the revised macro code.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro for calendar like comment

    Thanks for that, although I have a few teething issues.
    When I type in 'E' the comment box links with the cell below rather than than the cell with the E in it.
    Also I should have asked if there's a way to make the comment box longer, say 120x rather than 100x so there is space for a line of text under location.

    Also before I was running a macro to turn anything in lowercase to uppercase, I'm thinking these two codes are clashing because they have the same title (both Private Sub Worksheet_Change(ByVal Target As Range)), although changing the title doesn't seem to help

    Please Login or Register  to view this content.
    Thanks

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro for calendar like comment

    Hello pauld.ccvw,

    Try this updated version and let me know the results.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro for calendar like comment

    The comment box size and font is perfect thanks. It also works with the other code which forces uppercase which just makes the spreadsheet look neater.

    The only problem seems to be is that the comment box doesn't open automatically when I type in the cell value but I have to click back on the cell again.

    I've removed the line [Cmnt.Visible = True] so that the comment is visible when inputting in the cell but then hidden until hover over the cell.

    Is there a way, like on the second code on the first post where deleting the cell value (i.e. E ) will also delete the comment?

    I'm also looking to add a code to do similar thing with other letters such as PM for Planning Meeting. I know enough how to go in and edit that code to do what I want in that circumstance but I don't understand what causes the 'ambiguous name detected' other than it is a repeat name of a previous code and don't know what parameters I can use to change that name (e.g. adding a number such as Worsksheet_SeclectionChange1 won't help).

    Thanks for your help it has been great.

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro for calendar like comment

    Hello Paul.ccvw,

    This change will delete the comment, if there is one, when the cell is cleared.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro for calendar like comment

    I'm still having the problem that when I type the text in the cell and press enter nothing happens until I select the cell again, then the comment box appears. Is there a way that the comment can appear as soon as E is typed in to cell and enter is pressed? Aside from that it is absolutely perfect.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1