Best way to program tooltips in VBA

Hi,
I wrote a VBA macro that reads machine downtimes from a database and visualises this information in a calendar.
Each cell in the excel calendar represents a 2 hour shift. When a cel is clicked a tooltip appears with detailed information.
This works fine but performance is bad. Before adding the 'selection.Validation' command the performance was good.
So the problem is this statement.
Are there better ways to do this ?

Code : coloring each cell and defining the tooltip :

                            Range(Cells(iRow, iColumn), Cells(iRow, iColumn)).Select 'Volledige performantie
                            With Selection.Interior
                                .Pattern = lPattern
                                .PatternColorIndex = xlAutomatic
                                .Color = xlNone 'Verwijder kleur
                                .TintAndShade = 0
                                .PatternTintAndShade = 0
                            End With
                            
                            With Selection.Validation
                                .Delete
                                .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
                                    :=xlBetween
                                .IgnoreBlank = True
                                .InCellDropdown = True
                                .InputTitle = Worksheets(sSheet).Cells(iRow, 1).Value
                                .ErrorTitle = ""
                                .InputMessage = sComment
                                .ErrorMessage = ""
                                .ShowInput = True
                                .ShowError = True
                            End With

Deleted. Double post.

I tried the following ==>

Worksheets(sSheet).Range(Cells(iRow, iColumn), Cells(iRow, iColumn)).AddComment = "Test"

But without success. My problem is that I want to use row and columnnumber and not for example 'A5'.

@ MarkSt

Welcome to the forum, please use Code Tags when posting code. (Forum Rule#3)

Why not add the information as a comment ? (Opmerking)

That way you only need to hoover over a cell to get the information.

Try this.

    With Worksheets(sSheet)
        If .Cells(iRow, iColumn).Comment Is Nothing Then
            .Cells(iRow, iColumn).AddComment.Text "Test"
        Else
            .Cells(iRow, iColumn).Comment.Text "Test2"
        End If
    End With

From Debra Dalgleish.

Sub CoverCommentIndicator()
'www.contextures.com\xlcomments03.html
Dim ws As Worksheet
Dim cmt As Comment
Dim rngCmt As Range
Dim shpCmt As Shape
Dim shpW As Double 'shape width
Dim shpH As Double 'shape height
Set ws = ActiveSheet
shpW = 5
shpH = 5
On Error Resume Next
For Each cmt In ws.Comments
    Set rngCmt = cmt.Parent
    sq = Right("000000" & Hex(Range(cmt.Parent.Address(0, 0)).Interior.Color), 6)
        With rngCmt
        Set shpCmt = ws.Shapes.AddShape(msoShapeRightTriangle, _
                        rngCmt.Offset(0, 1).Left - shpW, .Top, shpW, shpH)
        End With
    With shpCmt
        .Flip msoFlipVertical
        .Flip msoFlipHorizontal
        .Fill.ForeColor.RGB = RGB(Val("&h" & Right(sq, 2)), Val("&h" & Mid(sq, 3, 2)), Val("&h" & Left(sq, 2)))
        .Fill.Visible = msoTrue
        .Fill.Solid
        .Line.Visible = msoFalse
    End With
Next cmt
End Sub

Sub RemoveIndicatorShapes()
'www.contextures.com\xlcomments03.html
Dim ws As Worksheet
Dim shp As Shape
Set ws = ActiveSheet
For Each shp In ws.Shapes
If Not shp.TopLeftCell.Comment Is Nothing Then
  If shp.AutoShapeType = _
    msoShapeRightTriangle Then
    shp.Delete
  End If
End If
Next shp
End Sub

You wanted the red triangles gone so ....

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Depending where the detailed information was coming from ("scomment" in the original post suggests that it's retreived from somewhere) you could use a textbox.
Being a textbox you can have scrollbars so wouldn't need to worry about sizing or comments becoming distanced from their cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With TextBox1
    'align to cell
    .Top = Target.Top + Target.Height
    .Left = Target.Left + Target.Width
    
    'see if any details
    .text = scomment
    .Visible = Len(scomment)
End With
End Sub