PDA

View Full Version : Sleeper: Comment location & Open Office



GreenTree
11-16-2008, 10:59 PM
I'm using VBA to create spreadsheets that others will open & use. Some of these users have various versions of Excel, while some use Open Office to view & manipulate the spreadsheets. For the most part, keeping things widely compatible hasn't been too difficult, but I've found one issue that's a doozy: comments.

When I comment a cell, with the comment not "visible" (i.e. not visible full time, but only when the user hovers over it with the cursor), I'm not in control of where the comment shows up. Excel decides, and on the whole that's okay. It may be necessary to scroll a bit to see the entire comment, but it shows up reasonably close to the commented cell. If I make the comment "visible" (i.e. visible full time), I can specify a location for it, but as soon as I make it no longer visible, my specified location is forgotten & it reappears in "the" location -- the one Excel chooses for it. Okay, that's livable. (And, for the number of commented cells I have, full time visible for everything won't work. More screen real estate would be needed for the comments than is used by the rest of the spreadsheet!)

HOWEVER, when I open the same spreadsheet in Open Office Calc to check for compatibility, the comment is FAR, FAR away from the cell. As in, I make it visible, then scroll several Page Down's and a couple screens to the right before I see it. However, when I move the comment to a more reasonable location, I can make it no longer visible, and when I hover over it, it appears in the same spot that I've moved it to, unlike Excel. So, hidden or not, there is a "position" value in Open Office that persists.

It would be *really* nice if the comments I use (many hundreds of them) in the produced spreadsheets were as available to the Open Office users (i.e. they pop-up reasonably near the commented cell, not requiring absurd amounts of scrolling to go see) as they are to the Excel users, but I'm at a loss how to make that happen. Does anyone have experience with Open Office & comments that might shed even a bit of light on this?

Many thanks,

G.T. :banghead: :banghead:

lucas
11-17-2008, 10:09 AM
I don't have open office to test but what if you used data validation to add your comments....

see attached and select the highlighted cell. Go to data-validation and select the input message to see how it is achieved.

GreenTree
11-18-2008, 11:32 AM
Interesting approach! Hadn't considered that avenue, but it certainly seems workable. With just the one cell "commented" this way, the result in Open Office looks exactly like it does in Excel. I'll need to try it with the large grid of cells & see if it still keeps the info right next to the cell in the way that comments don't (in Open Office) when there get to be too many of them. Many thanks!

It appears that there is a limit to the length of the Validation comment, about 11 or 12 lines max (more than that can be entered, though they're not displayed). There will be times that's less than I'd like, although it may be possible to work around it.

GreenTree
11-25-2008, 09:46 PM
When you sort and filter cells which are commented with the Validation comments, the comments will filter appropriately, but they do not sort with the cells. Serious problem, and makes this approach unusable as a replacement for Comments when sorting will be involved.

For example, let's say you have the numbers 1 thru 10 in A2:A11 and other data in B2:C:11. With an autofilter, you hide the odd rows, and you sort based on column A descending. You now have visible 10, 8, 6, 4, and 2 in column A in that order. If you had commented the cells in the A column with their respective values, what you'd see now are the comments 2, 4, 6, 8, and 10, so no cell has its original comment any more!

I don't have any solution to offer, but I figured I'd follow up on this thread in case somebody else has the same issue; at least I can save them the trouble of discovering what I just did.

So my original quandry remains... comments are broken when somebody with Open Office is using the spreadsheet (the comment location doesn't sort when the lines do... if the data on Row 3 gets sorted down to row 300, the comment remains up near row 3, well out of sight for everyone without a monitor taller than you are), and the Validation comments don't sort at all. Sigh. I guess my Open Office users are out of luck here.

Ah, well. Happy Thanksgiving, all!

:turkey:

GreenTree
11-25-2008, 10:21 PM
And one other thing I noticed, playing around with the example file, is that in Open Office the Validation Comments sort with the cells just as you'd hope! So if the spreadsheet is for ONLY an Open Office audience, use Validation comments, but realize that any Excel user who sorts things will see bogus data. If the file is for a user with Excel, use regular comments, knowing that an Open Office user who sorts the file won't be able to see many of them.

:banghead: