Help with Macro for Zooming when cells have Comments?
 

I'm using Debra's macro for autozooming when a cell has a drop down
menu...

http://www.contextures.com/xlDataVal08.html#AllCells

I'm not a VBasic expert. But throwing caution to the wind, I tried to
use the same technique to zoom when a cell has a Comment [as an
alternative to changing comment font settings in the Control Panel
(Display, Appearance, Tool Tip).] But it's not working. Here's the
macro. Can anyone offer help to get this working?

Private Sub Worksheet_SelectionComment(ByVal Target As Range)
Dim rngDV As Range
Application.EnableEvents = False
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0
If rngDV Is Nothing Then Exit Sub
If Intersect(Target, rngDV) Is Nothing Then
ActiveWindow.Zoom = 62
Else
ActiveWindow.Zoom = 100
End If
Application.EnableEvents = True
End Sub

The only things I changed from Debra's macro we

1. the subroutine title
(from Worksheet_SelectionChange to Worksheet_SelectionCcomment)

and

2. the SpecialCells type
(from xlCellTypeAllValidation to xlCellTypeComments)

Both macros are in the same Visual Basic code window.

One thing I don't understand is that when I click in the original
Validation macro, the two pulldowns at the top of the Code Window say:

Worksheet and SelectionChange

When I click in the new Comments macro the two pulldowns say:

(General) and Worksheet_SelectionComment

Maybe this is a clue to my problem?

Help with Macro for Zooming when cells have Comments?
 

You can change the name of the Sub Procedure..

However.. I assume it originally was Worksheet_SelectionChange
This is the name for an EVENT handler. These events are predefined.
and can be run from object modules only.

Open a code pane for an OBJECT module (like a worksheet or thisworkbook)
Check the dropdowns in the top of the codepane.
In the LEFT dropdown you can select those objects that support events.
In the RIGHT dropdown you can select the relevant events for the object.


Unless the combination name OBJECT_EVENT corresponds to a existing event
name it will never be triggered.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(scutchen) wrote:

I'm using Debra's macro for autozooming when a cell has a drop down
menu...

http://www.contextures.com/xlDataVal08.html#AllCells

I'm not a VBasic expert. But throwing caution to the wind, I tried to
use the same technique to zoom when a cell has a Comment [as an
alternative to changing comment font settings in the Control Panel
(Display, Appearance, Tool Tip).] But it's not working. Here's the
macro. Can anyone offer help to get this working?

Private Sub Worksheet_SelectionComment(ByVal Target As Range)
Dim rngDV As Range
Application.EnableEvents = False
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0
If rngDV Is Nothing Then Exit Sub
If Intersect(Target, rngDV) Is Nothing Then
ActiveWindow.Zoom = 62
Else
ActiveWindow.Zoom = 100
End If
Application.EnableEvents = True
End Sub

The only things I changed from Debra's macro we

1. the subroutine title
(from Worksheet_SelectionChange to Worksheet_SelectionCcomment)

and

2. the SpecialCells type
(from xlCellTypeAllValidation to xlCellTypeComments)

Both macros are in the same Visual Basic code window.

One thing I don't understand is that when I click in the original
Validation macro, the two pulldowns at the top of the Code Window say:

Worksheet and SelectionChange

When I click in the new Comments macro the two pulldowns say:

(General) and Worksheet_SelectionComment

Maybe this is a clue to my problem?