+ Reply to Thread
Results 1 to 3 of 3

How to alter a recorded macro to work better

  1. #1
    Registered User
    Join Date
    07-07-2009
    Location
    Louisiana, United States
    MS-Off Ver
    Excel 2007
    Posts
    19

    Question How to alter a recorded macro to work better

    I have recorded the following macro to add a picture to the comment field for a cell. However, I notice that if I delete the initial comment and re-run the macro I get errors on every line of code.

    Why is that?

    How can this macro be altered so that I will give me the option to insert a picture no matter which cell I have selected?


    Sub AddPictureToCommentBox()
    '
    ' AddPictureToCommentBox Macro
    '
    Range("B18").Select
    Range("B18").AddComment
    Range("B18").Comment.Visible = False
    Range("B18").Comment.Text Text:="" & Chr(10) & ""
    Selection.ShapeRange.Fill.Transparency = 0#
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 255)
    Selection.ShapeRange.Fill.BackColor.RGB = RGB(255, 255, 225)
    Selection.ShapeRange.Fill.UserPicture _
    "C:\Users\Tony\Pictures\Becca's Art\My Birthday Parrot b 4-16-09.png"

    ----------------------------------------------------------------------------------------------------

    I came across this macro which looks like it should do what I want but doesn't go any further than to add a comment box - there is no prompt to select an image. so essentially i'm left with an empty message box.

    Sub AddPictureToComment()
    Dim rng As Range
    Dim shp As Comment

    Set rng = ActiveCell

    If Not rng.Comment Is Nothing Then
    rng.Comment.Delete
    End If

    If rng.Text <> "" Then
    Set shp = rng.AddComment("")
    shp.Shape.Fill.UserPicture rng.Text

    End If

    End Sub
    Last edited by antonymiller; 07-14-2009 at 02:16 AM. Reason: Additional information

  2. #2
    Registered User
    Join Date
    07-07-2009
    Location
    Louisiana, United States
    MS-Off Ver
    Excel 2007
    Posts
    19

    Solved

    Option Explicit



    Const ImgFileFormat = "Image Files (*.bmp;*.gif;*.tif;*.jpg;*.jpeg)," & _

    "*bmp;*gif;*.tif;*.jpg;*.jpeg"



    Sub AddPicturesToComments()

    Dim HasCom

    Dim Pict As String

    Dim Ans As Integer



    Set HasCom = ActiveCell.Comment

    If Not HasCom Is Nothing Then ActiveCell.Comment.Delete

    Set HasCom = Nothing



    GetPict:

    Pict = Application.GetOpenFilename(ImgFileFormat)

    'Note you can load in, almost any file format

    If Pict = "False" Then End



    Ans = MsgBox("Open : " & Pict, vbYesNo + vbExclamation, "Use this Picture?")

    If Ans = vbNo Then GoTo GetPict



    With ActiveCell

    .AddComment

    .Comment.Visible = False

    .Comment.Shape.Fill.Transparency = 0#

    .Comment.Shape.Fill.UserPicture Pict

    End With



    End Sub

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: How to alter a recorded macro to work better

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1