PDA

View Full Version : [SOLVED] Outta Sight Cell Comments



iwrk4dedpr
11-20-2004, 12:44 AM
All,

Have you ever wondered. Isn't there some other shape for cell comments.

Have you ever said "Ooooooh a rectangle now why can't I be that original."

Well have I think I've got a relatively neat answer.



Many thanks to DHawley of www.ozgrid.com/forum (http://www.ozgrid.com/forum) for some inspiration

(Edit to Post)
1. Reloading current file with updated workbook.

Anne Troy
11-20-2004, 12:57 AM
Hi, Barry!
At the top of every VBAX forum page, on the green bar, is a bunch of links. One of them says "KBase". That's where this should go. :)

When you're done, just post back here, and I'll delete this.

You should only post in THIS forum if you NEED help.

Thanks for contributing!

Ken Puls
11-20-2004, 01:13 AM
:goss: psst! Anne...


Have you seen the amount of code in this one? Also has a userform in it too... I'm not sure it's really all that condusive to the KB layout, unless the VBA window just says: Download the attached! :dunno

It is kind of cool, although I've never felt the need to change my comments shapes personally.

One thing that's weird though is that I bombed on objCmt.Shape.TextFrame.Characters.Font.Size = Me.lbo_FontSize (in the Private Sub cmd_MakeComment_Click() routine)... but only when I first loaded it. It works fine now...:confused:

Barry, is this the workbook that you want to convert to an addin? (as per this post (http://www.vbaexpress.com/forum/showthread.php?t=1341)?)

iwrk4dedpr
11-20-2004, 02:53 AM
kpuls,

Yes, this is the book that I want to EVENTUALLY make into an addin. However, I need to know what kind of things am I doing wrong, what have I not taken into account, ... etc.

Anne Troy
11-20-2004, 02:58 AM
You know, Barry, you can put Dave's logo in your sig too, if you want. :)

Jacob Hilderbrand
11-20-2004, 03:05 AM
It looks very good so far.

iwrk4dedpr
11-20-2004, 03:07 AM
kpuls,


Just checked my file and got same error that you did on the font size. Even though there was a selection it regestered as being blank. I'm channging my file to reference the indexed value from the list.


Also other issue was that if you selected to close the form with the "x" it would also error. Just took care of that as well.


Anne, I had gone to the KB site and began to fill out the form, but like kpuls stated it appears that the KB site was strictly for posting say a routine or two. This is a file that contains a form etc ... so I wasn't sure of where to put it. I also realized that this forum is for questions but wanted to get it out there for some feed back about any problems. If you point me where to go I'll be happy to place this file there.

Zack Barresse
11-20-2004, 03:08 AM
Hey Barry,

Looks nice. :yes

I error out here ...


objCmt.Shape.TextFrame.Characters.Font.Size = Me.lbo_FontSize
(line 40)

iwrk4dedpr
11-20-2004, 03:24 AM
Thanks for all the input, Please keep it comming.

I've updated the file above taking care of some of the errors that you all have reported. My ultimate goal is to make this an AddIn ( not to sell but just to distribute ) so any issues you see with the code or anything else let me know.

If you have suggestions ( when it becomes an addin I see it adding to a menu or making a new menu item ). I'm also wanting to create the ability to allow the user to choose the color of the comment. Does somebody know or have any suggestions on how to create a color picker??

I realize that these functions are all ready present by right clicking a cell comment ( minus the shape ) but I'd like to make it a bit easier. Just by selecting the cell and then calling up a routine that automatically displays options.

Zack Barresse
11-20-2004, 03:30 AM
Okay, a little more on that specific error. It is when you add multiple lines to the text box - use the Enter key, you may want to put some code in there to handle that.

And when you do compile this into an add-in, maybe it would be more advantageous to put an extra option on your right-click menu rather than take over the entire right click option. Just a thought. But I like it! Very nice, and original. Good job!!

Ken Puls
11-20-2004, 10:47 AM
Okay, a little more on that specific error. It is when you add multiple lines to the text box - use the Enter key, you may want to put some code in there to handle that.

And when you do compile this into an add-in, maybe it would be more advantageous to put an extra option on your right-click menu rather than take over the entire right click option. Just a thought. But I like it! Very nice, and original. Good job!!
I've found it errors without multiple lines. I just open it up, right click on the sheet, entered "adj" (no quotes) and clicked okay (left all other defaults). Still errored on the line Zack mentioned.

I agree that it should just be added as a right click item, not take over the entire right click menu... that would drive me nuts (and I'm close enough already!:rofl )

Can you post an updated version so we can continue testing with the most current build?

Cheers,

Richie(UK)
11-20-2004, 11:55 AM
Hi Barry,

Re the colour picker - have a look at JW's page here:
http://www.j-walk.com/ss/excel/tips/tip49.htm

HTH

Richie(UK)
11-20-2004, 01:01 PM
Hi Barry,

OK, I downloaded the workbook to give it a try. Nice job.

I did, however, get the same error referred to above with the line


objCmt.Shape.TextFrame.Characters.Font.Size = Me.lbo_FontSize
on the first use of the form.

If you hard-code a value the routine works the first time too. This prompted me to step-through the code and discover that "lbo_FontSize" is actually equal to "" on the first pass. Now I'm not exactly sure why the Value property isn't set the first time, even though you have stipulated the ListIndex value, but using the ListIndex instead seems to solve the issue. Like this

objCmt.Shape.TextFrame.Characters.Font.Size = Me.lbo_FontSize.List(Me.lbo_FontSize.ListIndex)

HTH

iwrk4dedpr
11-20-2004, 01:20 PM
Richie,

I thought I did just that and then reloaded into the above attachment. I'll check out the attached file.


As far as the right clicking I agree with you. I shouldn't hijack basic xl functions for my purpose. If I ever get this to a final state I'll make it a menu option that is created when the file loads as an AddIn.

iwrk4dedpr
11-20-2004, 01:45 PM
All,


Ok here are the changes that I thought that I had added to the first post.

1. Made Richies suggested changes. Though I all ready had seen that. Not sure why it wasn't working but, hmmmmm?

2. Took care of using the Return key in the text box. Had to remove the linefeed chr(13) character from the text box. Now it displays fine.


3. Took care of using the "x" to close the form error.


4. Still haven't made a menu item. I think that'll be last. But I will not continue to hijack the right click in / when the AddIn.


a) Updated attached file on first posting of thread.

Zack Barresse
11-20-2004, 01:56 PM
Another suggestion I would make is when you do code the add/delete from the right click menu, code the two routines in Standard Modules, then call them upon Workbook_Open and Workbook_BeforeClose. This has another benefit in testing your routines. You can assign them to a Forms Command Button and add or delete it all you want.

Ken Puls
11-20-2004, 04:59 PM
Hi Barry,

I have some code and some opinions for you...

First off, the code. How about this for your menu items (to get you started)

The following code will create a menu item at the very bottom of the cell's right click menu. In a standard module:


Option Explicit
'Change the name of your button here
Const sCaption As String = "Create Cool Comment!"

Sub CellMenu_CreateAdditions()
'Macro purpose: To add an item to the cell (right click) menu
Dim compop As CommandBarButton
'Delete any previous occurance of the menu item
Call CellMenu_RemoveAdditions
'Create the new instance of the menu item
Set compop = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton)
With compop
.BeginGroup = True
.OnAction = "KickOffUF"
.Caption = sCaption
End With
End Sub

Sub KickOffUF()
'Macro pupose: To load the userform
Dim Cancel as boolean
If Range("rngPCommentsActive") And Selection.Cells.Count = 1 Then
frm_pComments.Show
Cancel = frm_pComments.Tag
Unload frm_pComments
End If
End Sub

Sub CellMenu_RemoveAdditions()
'Macro purpose: To remove cell menu additions
'On error in case the button does not exist
On Error Resume Next
Application.CommandBars("Cell").Controls(sCaption).Delete
On ErOption Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Add the cell menu additions
Call CellMenu_RemoveAdditions
End Sub

Private Sub Workbook_Open()
'Remove the cell menu additions
Call CellMenu_CreateAdditions
End Sub

You'll note that I removed the line to set the workbook to read only, as it isn't really necessary when converted to an addin. As mentioned before, the user won't be able to save to it anyway. I also moved the guts of your SheetBeforeRightClick event to the KickOffUF code. This gets you away from the interface hijacking issue.

And now for the rest... A couple of things you may (or not) want to consider for this...
1) I prefer to use the "unload me" command from the userform to get rid of it, rather than doing things when control passes back to the originating sub. I haven't stepped through the code enough to follow the logic behind your approach though, so if you've intentionally avoided that, well.. just avoid this! :D (What the heck does the "tag" stuff do, anyway? I haven't been able to figure that out.)
2) The code above could be modified to remove the existing "insert comment" on the cell menu, and replace it with your own. I've never done it before, though, so that would take a little more research on my part (although I'm sure that some of the others here have accomplished such a feat before!:yes )
3) Why refer to the range of your msoConstants using the range("A65536").End(xlup) method? Since you're coding this, releasing it as an addin, and not actively encouraging the user to add more, why not name the range and just refer to that? It might only save a couple of nano-seconds overall, but its a little cleaner IMHO.
4) Just a thought on the loop (step 5 of cmd_MakeComment_Click) as well... I've never done this myself, but you may be able to use the Find command on this range to improve performance. It could work out faster than the loop.
5) I'd personally rather see the shape options in Alphabetical order, rather than numerical.
6) And finally (for now;) ), I would also prefer to have the default set to visible, just so I can see the comment I just typed, in case I made a mistake. Maybe you could give the user the option to save default settings? One method would be to write all the defaults to a range of cells (column D?) and load them on startup. (Of course, the last line of any save defaults routine should proabably then be ThisWorkbook.Save)

That's all I can think of at the moment. I hope some of it is useful to you. :vv

Cheers,

iwrk4dedpr
11-21-2004, 05:10 AM
kpuls,

Thanks for the input. This is exactly the kind of stuff that I need.

1. Thanks for the code suggestions. I'll have a look see later today. I'm not very awake at the moment :snooze. But if you've gotten a menu item on the right click pop up that's awesome.

2. The response to question 1 ( Why am I using the Forms Tag property ) well it's probably a hack but here's the reason. If the cell was set to true ( the form would show ) but what if the user didn't want to put in a cell comment then he/she would press cancel and then the normal shortcut menu would show up. However, if the user did add a comment that was why the right clicked in the first place so why display the short cut menu. It just sends back weather or not to cancel the right click.

3. As far as part 2. I think it would be best to leave the standard insert comment as is. Just add a new Insert Cust Comment item.

4. For question 3. Why not use a fixed range instead of a dynamic range. Well, one of the things that needs to happen is that if someone uses this on a version of XL other than 2000 I want to be able to delete a shape that it can't make or doesn't recognize. ( Code's not written but I don't want the user to keep trying to make a shape that won't work )

5. As far as finding the shape name ( using find instead of a loop ) well let's just say that it shows the level of sophistication in my programming. This is one reason why I'm asking for thoughts and opinions. I don't regularly ( acutally not at all ) have an access ( face to face that is ) with someone who programs in which I can bounce ideas off of or learn from what they've done or are doing. All the coding experience that I have is self taught ( some from forums ) so I've gotten bad habits that show up in my coding. Actually I could get the value by offsetting from A5 the list index of the shape list box.


6. As far as the sort order. Alphabetical would be preferred. I just sorted by the value to verify that there weren't two items with the same value.

7. As far as defaulting the value to set the comment to visible. Not an issue. Easily, remedied. As far as saving a default setting that could be done. I just figured with so few selections that it wasn't neccessary. But with 138 shapes you might always want the 90th and find it a total pain to find it. So that idea has merit as well.


8. Now you stated that you wouldn't set the add in workbook to readonly as the user can't save to it anyway. I agree. If that was my only reason your right. However, it is possible to open more than one instance of XL and if both are trying to open the .xla workbook they conflict. Now I realize that 99% of the users will never encounter this but for that 1% I put it in.


Give me a couple of days and I'll get your improvements added along with credit stated in the code. By the way. Despite now asking questions about this workbook does VBAExpress have a place that you can put project workbooks for people to download??? Dreamboat above stated the the KB section was the place but that appears to be simply for code not a complete workbook? Any thoughts.

Ken Puls
11-21-2004, 10:04 AM
Hi Barry,

I just want to make sure you know I wasn't trying to point out any shortcommings in your programming skills. Just trying to give some alternate ways to imporve the efficiency or ideas. I'm still learning too, and come here because I don't have anyone at work to share this with either! :yes

As for where to place... check this page (http://www.vbaexpress.com/cooltools.htm) out. My guess (although I'd leave confirmation to her,) is that Dreamboat would just love to expand the section of "developed by our members" with more add-ins and such!;)

PS I'm not really after credit either!

Cheers,

Anne Troy
11-21-2004, 10:47 AM
Damn straight!

Ken Puls
11-21-2004, 02:53 PM
Hi Barry,

A couple of things come to mind from your replies, more "Food for thought" than anything, but here they are:

1) With regards to leaving the standard comment on the menu... why? :dunno Here's what I'm thinking. Assume that this is all finished and added to the VBAX cool tools page. You have a user that downloads your add-in (intentionally) because they are interested in it. If your defaults are set to match the standard comments form, why have a duplicate menu item? You could certainly code this to only nuke the default comment menu item when your add-in is loaded, and remove it when your add-in unloads. (Most of that process already exists in the code above... it's just getting the part to delete/re-establish MS's default.) I wouldn't advise this at all if your work didn't entirely match the default's functionality, but it does. I'd be curious to know what others think on this as well.:yes

2) On the point of allowing the user to set the default and read only status... the two could conflict. If you want to store the defaults in worksheet cells, well... you wouldn't be able to save the file.:mkay You could use registry keys to store the defaults, however, and I have some code to do that as well, or there is a KB Entry here (http://www.vbaexpress.com/kb/getarticle.php?kb_id=208). One reason this isn't a preferred method, though, is that if a user uninstalls the add-in, those keys could be left behind if you don't create a reliable procedure to remove them. I'm not a big fan of leaving garbage in a user's registry as it does affect computer performance. (Of course there are several high priced professional programs who don't clean up after themselves, but IMHO that doesn't make it right!):creator:

As I said, just "Food for thought"!:)

PS: Just found BrettDJ's colour picker (http://www.vbaexpress.com/kb/getarticle.php?kb_id=224) in VBAX's own KBase!

Ken Puls
12-13-2004, 10:19 AM
Hey Barry,

Whats the skinny on your project? Are you still making progress? :)

iwrk4dedpr
12-13-2004, 12:57 PM
Still working on it. Just have gotten bogged down with work that feeds the family.

It's still an item I want to complete. I'm using the file currently as posted and I've been having some issues with it. Despite that those that I work with seem to think it's pretty cool. So it's still on the books.



Regards,
Barry


Will post with complete project when it's done.