+ Reply to Thread
Results 1 to 8 of 8

Assign a Macro to a cell

  1. #1
    Registered User
    Join Date
    01-28-2011
    Location
    phila, pa
    MS-Off Ver
    Excel 2003
    Posts
    14

    Assign a Macro to a cell

    Hello, How do I assign a Macro to a cell that will execute the “Insert, Object, Create from File (tab), Display as icon, Browse”. And then let a person choose their own file to insert.

    I checked that website from another thread that VBA Noob suggested:

    http://www.cpearson.com/excel/Events.aspx

    and found the Object Browser in the VBA Editor. Then I found “File Search” in the Classes list. But I’m not sure what to choose from the “Members of File Search” list. Can you help? Am I even in the ballpark? Thanks, db
    Last edited by DABatPENN; 08-10-2011 at 09:47 AM.

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

    Re: Assign a Macro to a cell

    Worksheet event code is stored on a worksheet module. To add it to your worksheet, do the following:

    Copy the code
    Select the worksheet in which you the code to run
    Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
    Where the cursor is flashing, choose Edit | Paste
    Hope that helps.

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

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    01-28-2011
    Location
    phila, pa
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Assign a Macro to a cell

    I did i miss something? What is the code to copy?

  4. #4
    Registered User
    Join Date
    01-28-2011
    Location
    phila, pa
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Assign a Macro to a cell

    maybe the question is: How do i open the module to find the event code to copy. OR do i need to record the “Insert, Object, Create from File (tab), Display as icon, Browse” event first and then copy that code? db

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

    Re: Assign a Macro to a cell

    The code is whatever you write or want to copy from a web site. I was only posting basic instructions how to access the worksheet event code. You need to provide the code. Yo seemed to be asking how to assign it to the cell, not how to write the code.

    If you haven't got the code then start with recording the macro

  6. #6
    Registered User
    Join Date
    01-28-2011
    Location
    phila, pa
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Assign a Macro to a cell

    The macro will not let me stop recording while the “insert object” window is open. So I completed the operation. The below macro inserts an icon for a pdf file from the directory Acrobat Drawings on the “I:” drive. Is there a line of code I can insert into the macro that will stop it for operator input to select different files? If not, then maybe a macro is not what I need.

    Sub Insert_Object()
    '
    ' Insert_Object Macro
    ' Macro recorded 8/4/2011 by dabillyk
    '

    '
    ActiveSheet.OLEObjects.Add(Filename:= _
    "I:\Acrobat Drawings\183-trq12 Rev -.pdf", Link:=False, DisplayAsIcon:= _
    False).Select
    Selection.ShapeRange.IncrementLeft 2.25
    Selection.ShapeRange.IncrementTop 28.5
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "I:\Acrobat Drawings\183-trq12 Rev -.pdf"
    ActiveCell.Select
    End Sub

  7. #7
    Registered User
    Join Date
    01-28-2011
    Location
    phila, pa
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Assign a Macro to a cell

    It wasn't a macro i needed. I ended up using =HYPERLINK(CONCATENATE(H101,B103))

    how do i mark it [solved]?
    Last edited by DABatPENN; 08-10-2011 at 08:51 AM. Reason: solved

  8. #8
    Registered User
    Join Date
    01-28-2011
    Location
    phila, pa
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Assign a Macro to a cell

    i got it...

+ 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