Excel2003 XPsp2
I have a le worksheet with more than 75 comments.
If you are running a macro they do not display when you hover over them.
Is there a way to make the comments visable?

"Oldjay" wrote:
Excel2003 XPsp2
I have a le worksheet with more than 75 comments.
If you are running a macro they do not display when you hover over them.
Is there a way to make the comments visible?

Perhaps ..
Click Tools Options View tab
Under "Comments" section
Check "Comment indicator only" OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

hi
a suggestion.
you could add code to your macro to unhide your comments while the macro
runs then add code at the end of the macro to hide the comments again
from Debra Dalbleish's site contextures
Sub ShowSheetComments()
'shows all comments on the active sheet
Dim c As Comment

For Each c In ActiveSheet.Comments
c.Visible = True
Next

End Sub
http://www.contextures.com/xlcomments03.html#Show

regards
FSt1

"Oldjay" wrote:

Excel2003 XPsp2
I have a le worksheet with more than 75 comments.
If you are running a macro they do not display when you hover over them.
Is there a way to make the comments visable?

Two two solutions shown do not address the problem. If I am not running a
macro the comments are only displayed when I hover over them. This is what I
want to do. When a macro is running they are not displayed

"FSt1" wrote:

hi
a suggestion.
you could add code to your macro to unhide your comments while the macro
runs then add code at the end of the macro to hide the comments again
from Debra Dalbleish's site contextures
Sub ShowSheetComments()
'shows all comments on the active sheet
Dim c As Comment

For Each c In ActiveSheet.Comments
c.Visible = True
Next

End Sub
http://www.contextures.com/xlcomments03.html#Show

regards
FSt1

"Oldjay" wrote:

Excel2003 XPsp2
I have a le worksheet with more than 75 comments.
If you are running a macro they do not display when you hover over them.
Is there a way to make the comments visable?

.. When a macro is running they are not displayed

But shouldn't you just wait until the macro completes? No different from
pressing F9 where you have manual calc set, and awaiting the completion of
re-calcs.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

While a macro is running you cannot "hover".


Gord Dibben MS Excel MVP

On Fri, 8 Feb 2008 14:51:01 -0800, Oldjay
wrote:

Two two solutions shown do not address the problem. If I am not running a
macro the comments are only displayed when I hover over them. This is what I
want to do. When a macro is running they are not displayed

"FSt1" wrote:

hi
a suggestion.
you could add code to your macro to unhide your comments while the macro
runs then add code at the end of the macro to hide the comments again
from Debra Dalbleish's site contextures
Sub ShowSheetComments()
'shows all comments on the active sheet
Dim c As Comment

For Each c In ActiveSheet.Comments
c.Visible = True
Next

End Sub
http://www.contextures.com/xlcomments03.html#Show

regards
FSt1

"Oldjay" wrote:

Excel2003 XPsp2
I have a le worksheet with more than 75 comments.
If you are running a macro they do not display when you hover over them.
Is there a way to make the comments visable?

It looks like hovering is out of the question when a macro is running,
but while this macro is running a cell's comment becomes visible when
it is made the active cell and invisible when you deactivate it by
clicking elsewhere. I was even able to add a comment to a cell while
the macro was running. Interestingly the user name that always
prefaces a comment was not there when I added the comment while the
macro was running...

Public Sub Hover_Test()
Dim K As Long, strAddress As String
Do While K < 100000
Range("H1").Value = K
K = K + 1
strAddress = ActiveCell.Address
DoEvents
On Error Resume Next
If ActiveCell.Address < strAddress Then
Range(strAddress).Comment.Visible = False
Else: ActiveCell.Comment.Visible = True
strAddress = ActiveCell.Address
End If
Loop
End Sub

Ken Johnson

The macro has pause commands. You have to click on a special cell to continue

"Max" wrote:

.. When a macro is running they are not displayed

But shouldn't you just wait until the macro completes? No different from
pressing F9 where you have manual calc set, and awaiting the completion of
re-calcs.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

On my machine this does not happen. The comment does not appear when the cell
is selected or "hovered"

Here is the pause function

'This module contains the implementation of the mythical Pause'
'function. It works hand-in-hand with code contained in the
'target workbook's change function.

'To use this function, you must call it in code like this:

'Pause "A1" <- Where we include the target cell's reference

'In addition, you must include the following code
'in the target workbook. Copy it and paste it into the
'workbook removing the left most comment quotes.

'---------- COPY THE FOLLOWING CODE INTO THE VBA WORKSHEET ----------
'Option Explicit

'Private Sub Worksheet_Change(ByVal Target As Range)
' 'This is the all important worksheet code
' 'It contains a simple If statement that compares
' 'the selected cell reference in the global variable
' 'to that of the cell just modified.
' 'If they match, we will clear the global
' 'toggle flag releasing any pause that may be pending

' If Target = Range(strTargetCell) Then
' flgToggle = False
' End If
'End Sub
'-------------- END THE WORKBOOK CODE --------------

'Operation: What happens is that we set the toggle flag to true
'causing our Do..Loop to loop infinately until the flag is
'cleared. The function Paused is just a nice package that makes
'things easier to read. You MUST include the DoEvents keyword
'in the Do..Loop or the program will lock-up. (Or appear to
'do so!) The ONLY way the flag will get cleared
'is in the target workbooks Change event handler.
'If you wanted to release the Pause with something else like a command
button or something,
'just clear the flag within the object "Click" event.

'Troubleshooting: If the macro won't advance past a pause,
'check to make sure that data in the target cell has
'changed. It MUST be changed to fire the event properly.
'You can't put a "default" value in the cell then just let
'the user accept it without changing it.
'If you do that, it won't fire the workbook's change event.

Public Sub Pause(TargetCell As String)
'This implements a pause in code.
'We set the toggle flag and the cell reference in the
'global variable then waits until the flag is cleared.
'The flag MUST be cleared by code in the workbook's
'Change event. See Code Above for example.

'Check the flgPauseActive flag to see if we are already in a pause
If flgPauseActive Then
MsgBox "WARNING! The system is already paused waiting for a change
in cell " & TargetCell & ". Please clear that pause first.", vbCritical +
vbOKOnly, "PROGRAMMING ERROR"
Exit Sub
Else
flgPauseActive = True
End If

'We need to check and make sure we were actually passed a
'cell reference. If we are not, the thing goes on ANY modified cell.
If Len(TargetCell) = 0 Then
MsgBox "No target cell was passed to the pause routine. Please check
the calling function and confirm that a cell is being passed as the first
parameter of the call.", vbCritical + vbOKOnly, "Failed to pass parameter!"
Exit Sub
End If

'Set our global variables
flgToggle = True
strTargetCell = TargetCell
Application.ScreenUpdating = True
Application.DisplayAlerts = True
'Do our pause
Do
DoEvents
Loop Until Not flgToggle
'Reset the pause active flag
flgPauseActive = False
'And return control to the calling routine
End Sub

"Ken Johnson" wrote:

It looks like hovering is out of the question when a macro is running,
but while this macro is running a cell's comment becomes visible when
it is made the active cell and invisible when you deactivate it by
clicking elsewhere. I was even able to add a comment to a cell while
the macro was running. Interestingly the user name that always
prefaces a comment was not there when I added the comment while the
macro was running...

Public Sub Hover_Test()
Dim K As Long, strAddress As String
Do While K < 100000
Range("H1").Value = K
K = K + 1
strAddress = ActiveCell.Address
DoEvents
On Error Resume Next
If ActiveCell.Address < strAddress Then
Range(strAddress).Comment.Visible = False
Else: ActiveCell.Comment.Visible = True
strAddress = ActiveCell.Address
End If
Loop
End Sub

Ken Johnson

I kept on getting an error in the event code so I just commented it
out.
When I called the code below the active cell's comment appeared.



Public Sub Pause(TargetCell As String)
Dim strAddress As String 'added line 1
'This implements a pause in code.
'We set the toggle flag and the cell reference in the
'global variable then waits until the flag is cleared.
'The flag MUST be cleared by code in the workbook's
'Change event. See Code Above for example.

'Check the flgPauseActive flag to see if we are already in a pause
If flgPauseActive Then
MsgBox "WARNING! The system is already paused waiting for a
change in cell " & TargetCell & ". Please clear that pause first.",
vbCritical + vbOKOnly, "PROGRAMMING ERROR"
Exit Sub
Else
flgPauseActive = True
End If

'We need to check and make sure we were actually passed a
'cell reference. If we are not, the thing goes on ANY modified
cell.
If Len(TargetCell) = 0 Then
MsgBox "No target cell was passed to the pause routine. Please
check the calling function and confirm that a cell is being passed as
the first parameter of the call.", vbCritical + vbOKOnly, "Failed to
pass parameter!"
Exit Sub
End If

'Set our global variables
flgToggle = True
strTargetCell = TargetCell
Application.ScreenUpdating = True
Application.DisplayAlerts = True
'Do our pause
Do
strAddress = ActiveCell.Address
DoEvents
On Error Resume Next 'added line 2
If ActiveCell.Address < strAddress Then 'added line 3
Range(strAddress).Comment.Visible = False 'added
line 4
Else: ActiveCell.Comment.Visible = True 'added line 5
strAddress = ActiveCell.Address 'added line 6
End If 'added line 7
Loop Until Not flgToggle
'Reset the pause active flag
flgPauseActive = False
'And return control to the calling routine
End Sub

Ken Johnson

Ken
I added the code. I didn't get any compile errors but I still can't see the
comments. Is there any chance of sending you the file. so that you can look
at it?

oldjay

"Ken Johnson" wrote:

I kept on getting an error in the event code so I just commented it
out.
When I called the code below the active cell's comment appeared.



Public Sub Pause(TargetCell As String)
Dim strAddress As String 'added line 1
'This implements a pause in code.
'We set the toggle flag and the cell reference in the
'global variable then waits until the flag is cleared.
'The flag MUST be cleared by code in the workbook's
'Change event. See Code Above for example.

'Check the flgPauseActive flag to see if we are already in a pause
If flgPauseActive Then
MsgBox "WARNING! The system is already paused waiting for a
change in cell " & TargetCell & ". Please clear that pause first.",
vbCritical + vbOKOnly, "PROGRAMMING ERROR"
Exit Sub
Else
flgPauseActive = True
End If

'We need to check and make sure we were actually passed a
'cell reference. If we are not, the thing goes on ANY modified
cell.
If Len(TargetCell) = 0 Then
MsgBox "No target cell was passed to the pause routine. Please
check the calling function and confirm that a cell is being passed as
the first parameter of the call.", vbCritical + vbOKOnly, "Failed to
pass parameter!"
Exit Sub
End If

'Set our global variables
flgToggle = True
strTargetCell = TargetCell
Application.ScreenUpdating = True
Application.DisplayAlerts = True
'Do our pause
Do
strAddress = ActiveCell.Address
DoEvents
On Error Resume Next 'added line 2
If ActiveCell.Address < strAddress Then 'added line 3
Range(strAddress).Comment.Visible = False 'added
line 4
Else: ActiveCell.Comment.Visible = True 'added line 5
strAddress = ActiveCell.Address 'added line 6
End If 'added line 7
Loop Until Not flgToggle
'Reset the pause active flag
flgPauseActive = False
'And return control to the calling routine
End Sub

Ken Johnson

Hi Oldjay,

I'll have a go but I must admit I did have trouble understanding what
it all does. It will be interesting seeing the whole thing though.

You know how to get my email from my profile?

Ken Johnson

No

"Ken Johnson" wrote:

Hi Oldjay,

I'll have a go but I must admit I did have trouble understanding what
it all does. It will be interesting seeing the whole thing though.

You know how to get my email from my profile?

Ken Johnson

Did you get the workbook?

"Oldjay" wrote:

No

"Ken Johnson" wrote:

Hi Oldjay,

I'll have a go but I must admit I did have trouble understanding what
it all does. It will be interesting seeing the whole thing though.

You know how to get my email from my profile?

Ken Johnson