Making comments show - momentarily

[Roger on Excel]

I have comments in cells to help guide users.

I would like to have the comments pop-up momentarily when a sheet is opened
in a workbook. Say - for a second or two.

Also, it would be great if this would only happen during the first 5 mins
the workbook is open - to stop them becoming annoying...

I there a way to do this?

Thanks,

Roger

 

[Nayab]

I have comments in cells to help guide users.

I would like to have the comments pop-up momentarily when a sheet is opened
in a workbook. Say - for a second or two.

Also, it would be great if this would only happen during the first 5 mins
the workbook is open - to stop them becoming annoying...

I there a way to do this?

Thanks,

Roger

U may need to write the code to show the comments within the Autpen
module. Something of the following nature may help

Sub Autpen()
'
Sheets(1).Range("A2").Select
ActiveCell.Comment.Visible = True
Application.ScreenUpdating = True
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveCell.Comment.Visible = False
End Sub

 

[Peter T]

Only lightly tested (I haven't even tried save/close/reopen) but have a go
with this

' in ThisWorkbook module

Private Sub Workbook_Activate()
ShowHideComments True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ShowHideComments
End Sub

Private Sub Workbook_Deactivate()
ShowHideComments
End Sub

Private Sub Workbook_Open()
gdtOpened = Now
ShowHideComments True
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ShowHideComments True
End Sub

''''' end Thisworkbook module

'' in a normal module
Public gdtOpened As Date
Public gdtHideTime As Date

Sub ShowHideComments(Optional bShow As Boolean)

If Now > gdtOpened + TimeSerial(0, 1, 0) Then bShow = False

If bShow And TypeName(ActiveSheet) <> "Chart" Then

If gdtHideTime > 0 Then
Application.OnTime gdtHideTime, "ShowHideComments", , False
End If

If Application.DisplayCommentIndicator <> xlCommentAndIndicator Then
Application.DisplayCommentIndicator = xlCommentAndIndicator
End If

gdtHideTime = Now + TimeSerial(0, 0, 2)
Application.OnTime gdtHideTime, "ShowHideComments"

ElseIf bShow = False And Application.DisplayCommentIndicator _
<> xlCommentIndicatorOnly Then
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
gdtHideTime = 0
End If
End Sub

'' end code in normal module

Change TimeSerial(0, 1, 0) and TimeSerial(0, 0, 2) to suit, as written all
will revert to normal after one minute (you asked for 5) and within that
time comments should display for two seconds.

Regards,
Peter T

 

[Roger on Excel]

Perter,

This is excellent. Thankyou so much for your time.

Best regards,

Roger