Follow Cell pointer

[Jan Karel Pieterse]

Duh. Odd that it does not error for me!
I updated the attachement of the previous message.

[HansV]

Thanks! It's a very versatile tool now.

[Rudi]

Wow...this add-in is going from strength to strength!! Great work to all who contributed to Jan Karels original add-in.

It makes you wonder what else can be modified/tweaked and added??

[HansV]

It makes you wonder what else can be modified/tweaked and added??

Features should only be added if they're useful, not just because you can...

(A useful tweak would be to make the arrows semi-transparent, but that's probably difficult)

[Jan Karel Pieterse]

It can be done, but not with the arrow we used now. It would involve using a different drawing object altogether and set it's fill to a transparency percentage. E.g. a rectangle would do.
Up to you guys to try <g>.

[HansV]

I think the add-in is fine as it is now...

[HansV]

It can be done, but not with the arrow we used now.

In fact, we can - it turned out to be much easier than I expected:

1. Add a line to the modGlobals module:

Public Const ARROWTRANSPARENCY As Single = 0.5

You can change the value 0.5 of course - it varies from 0.0 = fully opaque to 1.0 = fully transparent (invisible).

2. Add a line for each of the line shapes in clsAppEvents:

Code: Select all

        With moPtr1
            .Line.EndArrowheadStyle = ARROWSTYLE 'Diamond
            .Line.EndArrowheadLength = ARROWLENGTH 'Short
            .Line.EndArrowheadWidth = ARROWWIDTH 'Narrow
            .Line.Weight = ARROWWEIGHT ' 1
            .Line.ForeColor.RGB = ARROWCOLOR
            .Line.Transparency = ARROWTRANSPARENCY    ' *** new line ***
            .Name = GetNameForCellPointers()
            .PrintObject = False
        End With

and similar for moPtr2.

[HansV]

And here is a version with the option to switch between opaque and 50% transparent added to the format dialog.

I also added code to prevent the arrows from marking the workbook as changed. (In the original version, you'd be asked whether you wanted to save changes when you closed a workbook, even if you hadn't changed anything; this was because the pointer arrows had been added/removed)

FollowCellPointer.zip

(The digital signature has been lost again.)

[Rudi]

Hans, as usual...some wonderful additives to the add-in. Thanks for this (version: 00-one-million-and-one.123)

[Jan Karel Pieterse]

Nice. Odd you can't do this through the UI!
I would change the transparency setting to a box where you can simply enter the percentage though, more versatile.
No time today...

[HansV]

New version attached.
The dialog now has a scroll bar for transparency.
I also added some extra options for the arrow length, width and style and for the line weight.

FollowCellPointer.zip

[Rudi]

Very nice Hans.

Jan Karel... You should consider selling this add-in to Microsoft! It can make you a VERY rich man!!!

PS: Accept no less than $12,500,000. (Nowadays they are a little skimpish!)

[HansV]

It's a fun add-in, but I don't think it is really suitable for general use - as noted in this thread, the arrows make it more difficult to select a cell to the left of, or straight above the active cell by clicking. And the disabling of Undo (a general side effect of running VBA code) may be too high a price to pay.

[Jan Karel Pieterse]

I agree with Hans, it is nice and I'll probably include it on my downloads page, but it is not a cash cow