added function to shortcut menu showing up multiple times

[noisepoet]

I have created a calendar user form in VBA that will enter a selected date into a cell. I've added this to the right mouse click shortcut menu with some success, but not complete success. Here's the problem:

Each time I restart Excel, the shortcut menu displays an additional added function. Meaning, the first time I added the calendar to the shortcut menu, it was listed on the menu once. But when I exited out of Excel and started it up again, the shortcut menu displayed two of the new function. In my attempts to fix this, I now have eight calendar functions added to my shortcut menu. Additionally, only the most recently added one works properly. The previous added functions provide an error message stating that the macro cannot be found.

Can you help me with this?

 

[Tubig]

calendar

I have a calendar addin that I got a while ago. It embeds itself into the shell within excel. I don't remember where I downloaded it, but I can send you the addin and directions. Send me a PM if interested.

 

[HalfAce]

Without seeing your code, this is sort of a guess but here's something you can try.
First of all, disable the code that loads up your new menu item so you don't get any more instances of it.
Then include something like this in your ThisWorkbook module.

Private Sub Workbook_Deactivate()
Application.CommandBars("Cell").Reset
End Sub

and activate that.
Then give a right click somewhere to see if they're gone.

If all is cool after that then go ahead & reactivate the code that loads up the new menu item. (and do whatever it is you're doing to execute it.) If I'm right, you should only have one instance of it now and each time you deactivate the workbook it'll go away, so the next time your code to load it up is executed, it'll be the only instance of it.
(By the way, what triggers the code to execute that loads the calendar in?)

 

[noisepoet]

Thank you. Your suggestion worked perfectly. Regarding your question about what triggers the code to execute, I'm not sure I understand the question. This is actually my first VBA experience. I found the information for this user form online, and after coding it three different times, that was as far as I could get with it.

Now that I'm able to place a date into a cell with this calendar, do you know how to format the date so that it will show up in the cell's comments instead of in the actual cell?

Thank you.

 

[HalfAce]

Boy, for a first time vba experience you jumped right in there didn't you.

What I meant by 'what triggers the code' is - what makes the code fire off and add this
menu item to your right click menu? (whatever it is you're doing to execute it.)

As for editing the code to insert the date as a comment instead of a cell value, can we
see the code you're using now to enter it into the cell?

 

[noisepoet]

Private Sub Workbook_Deactivate()
Application.CommandBars("Cell").Reset
End Sub

Private Sub Workbook_Open()
Dim NewControl As CommandBarControl
Application.OnKey "+^{C}", "ThisWorkbook.OpenCalendar"
Set NewControl = Application.CommandBars("Cell").Controls.Add
With NewControl
.Caption = "Insert Date"
.OnAction = "Module1.OpenCalendar"
.BeginGroup = True
End With
End Sub

 

[HalfAce]

Oh, ok it's the workbook open event that's executing your menu item add code. That's good.
Although, something you might want to consider is this.
Right now, the menu item gets added when the workbook opens.
The menu item gets cleared out of there when you deactivate the workbook. (ie, activate - or open - another workbook.). . . which means when you switch back over to this workbook your Insert Date item will no longer appear on your right click menu. (See what I mean?)

Now you have a couple of options here.

1) Move the code in your workbook open event to the workbook activate event instead. (Having the code in your activate & deactivate events will make the menu item available whenever you're in this particular workbook, and not when you're in any other workbook.)

Option 2) OR - you can move the code in your workbook deactivate event into the workbook close event. Having the code in your open & close events will make the Insert Date menu item available in all workbooks that are (or get) opened while this workbook is open.

Now, having said all that, the code I was really looking for is the code you’re using to put the date into the cell so we can edit that to make it a comment in the cell instead of a value.

Here are a couple examples of how you can add a comment to a cell using vba.
The first one simply enters the date into a cell (which you’re already doing) and then inserts a comment into the same cell, using the value of the cell for the comment text, and then deletes the value in the cell. (Note, you’ll have to play with the column width to accommodate whatever format you’re using for the date in the cell.)

Sub CommentDemo1()
With [A1]
  .Value = Date
  .ClearComments
  .AddComment
  .Comment.Visible = False
  .Comment.Text Text:=.Text
  .ClearContents
End With
End Sub

This second one will simply insert a comment with the current date without using any cells, and therefore doesn't require anything formatting wise.

Sub CommentDemo2()
  With [A2]
    .ClearComments
    .AddComment
    .Comment.Visible = False
    .Comment.Text Text:=Chr(34) & Date & Chr(34)
    .Comment.Text Text:=Left(.Comment.Text, Len(.Comment.Text) - 1)
    .Comment.Text Text:=Right(.Comment.Text, Len(.Comment.Text) - 1)
  End With
End Sub

Can either of those be adapted to suit your needs?

 

[noisepoet]

formatting date as comments using VBA

Thank you very much for your help. Reading your advice regarding relocating my code from workbook_open to workbook_activate made me aware of some VBA fundamentals that I wasn't aware of.

The second code concerning formatting comments fits my needs more. I feel like I'm really close to working it out. I just have a couple of questions, if you don't mind:

The line reading ".ClearComments" - if I don't include that, will that allow new comments to be added to older comments? I'm using this program to track daily performance of funds and indexes, and being able to see an entire month's history in the comments is more useful to me than replacing old comments with new. I guess I would also need to know how to ensure that the information I enter into the cell will also show up along with the comments.

Also, when I go to Workbook_Open and enter the subroutine you were kind enough to provide me with, the pull-down menus change from Workbook_Open to General_CommentDemo2. I think that this might have something to do with the desired effect not taking place. My intention is to integrate it into this as an add-in, so at this point, wouldn't it be better to be able to assign the CommentDemo2 code to Workbook instead of to General? If so, how can I get around Excel automatically pushing my code from Workbook to General.

Thank you once again.

 

[HalfAce]

The code for CommentDemo2 does not go into the ThisWorkbook module, it needs to go into a standard module instead. Only the Workbook_Open (or activate) and Workbook_Close (or deactivate - whichever ones you chose to go with) should be in your ThisWorkbook module.

The line reading ".ClearComments" - if I don't include that, will that allow new comments to be added to older comments?

No, if there's a comment in a cell and you try to add a comment it will generate an error.

We can however add to the existing comment as you've indicated.
This brings up a few questions.
What text would you like to add as the new comment (besides the date)? - Just the current value of the cell?
How large will these comments end up being? (There should be a way to resize them but I'll have to dig around a bit to find that.)

 

[noisepoet]

Thank you once again. I've got everything except for the comments happening. When you say that the comments code needs to be put in the standard module, do you mean under Personal.xls? I don't see anything listed "standard module." Maybe you mean module 1? I should mention that my purpose is to format this as an add-in, once it executes properly (I would have mentioned this before, but I didn't exactly know what an add-in was).

The comments would need to hold as many as one entry per business day, listing the date and the value of the cell (cell is formatted as % with 2 decimals).

I ordered Walkenbach's Dummies book. I'm hoping this will help me further. Someone loaned his tome to me, but I think I'd be a lot better off with smaller bites at this point. It's great information, just too much for my little head at this point.