Snasui Excel Forum

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        ChangePicInComment
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Application.DisplayCommentIndicator = xlCommentAndIndicator
    Else
        Application.DisplayCommentIndicator = xlCommentIndicatorOnly
    End If
End Sub

Code: Select all

Sub ChangePicInComment()
    Dim PicName As String
    On Error Resume Next
    PicName = Range("A1")
       Range("A1").Comment.Shape.Select
       Selection.ShapeRange.Fill.UserPicture "D:\" & PicName & ".jpg"
    Range("A1").Select
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1:$A$10" Then
        ChangePicInComment
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$1:$A$10" Then
        Application.DisplayCommentIndicator = xlCommentAndIndicator
    Else
        Application.DisplayCommentIndicator = xlCommentIndicatorOnly
    End If
End Sub

Code: Select all

Sub ChangePicInComment()
    Dim PicName As String
    On Error Resume Next
    PicName = Range("A1:A10")
       Range("A1:A10").Comment.Shape.Select
       Selection.ShapeRange.Fill.UserPicture "D:\Menu\" & PicName & ".jpg"
    Range("A1:A10").Select
End Sub

Code: Select all

Sub AddPics()
'
' AddPic# Macro
'

'
For Each Cell In Selection
    ThisPicture = "D:\Menu\" & Cell.Value & ".jpg"
    With Cell.AddComment
        .Shape.Fill.UserPicture ThisPicture
        .Shape.Fill.Transparency = 0#
        .Shape.Line.Weight = 0.75
        .Shape.Line.DashStyle = msoLineSolid
        .Shape.Line.Style = msoLineSingle
        .Shape.Line.Transparency = 0#
        .Shape.Line.Visible = msoTrue
        .Shape.Line.ForeColor.RGB = RGB(0, 0, 0)
        .Shape.Line.BackColor.RGB = RGB(255, 255, 255)
        .Shape.Fill.Visible = msoTrue
        .Shape.Fill.ForeColor.RGB = RGB(255, 255, 255)
        .Shape.Fill.BackColor.RGB = RGB(255, 255, 225)
        .Shape.LockAspectRatio = msoFalse
        .Shape.Height = 50.25
        .Shape.Width = 57.75
        ActiveCell.Comment.Visible = False
    End With
Next Cell
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    ChangePicInComment
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.DisplayCommentIndicator = xlCommentIndicatorOnly
    If Not Target(1).Comment Is Nothing Then
        Target(1).Comment.Visible = True
    End If
End Sub

Sub ChangePicInComment()
    Dim PicName As String
    On Error Resume Next
    PicName = Selection(1).Value
    Selection(1).Comment.Shape.Select
    Selection.ShapeRange.Fill.UserPicture "D:\Menu\" & PicName & ".jpg"
    Selection(1).Select
End Sub

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static oldCell As Range
    On Error Resume Next
    oldCell.Comment.Select.Visible = False
    Target(1).Comment.Select.Visible = Ture
    Set oldCell = Target(1)
    Application.DisplayCommentIndicator = xlNoIndicator 'Change this line
    If Target(1).Value = "" Then
        Target(1).Comment.Hidden = Ture
    ElseIf Not Target(1).Comment Is Nothing Then
        Target(1).Comment.Visible = True
    End If
End Sub

Code: Select all

Sub MyMacro()
	Sheet1.Unprotect Password:="Secret"
		'YOUR CODE
	Sheet1.Protect Password:="Secret"
End Sub