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
