Automating addition of shortcut icons to excel worksheet cells
Posted on 2014-10-30
My excel worksheet contains a column of file addresses
I want to use AUTOMATION to add a graphic short cut to each row, so when I click a shortcut, it opens the file addressed on that row.
I did a manual trial, and recorded the macro below. It causes a shortcut (MyFilename.png) to be positioned within cell B7, and to gain a hyperlink which opens file: 'Support.log".
My MAIN QUESTION: This worksheet will end up as a large catalogue of files. I need to search for files against names or tags, and import and export file details; further - this list of utilities is likely to increase..
Given that requirement, is my intention of using shortcut icons the right way to go about building a directory of links to files? OR: are there better solutions?
My subsidiary QUESTION, is about computing .ScaleWidth and .ScaleHeight, please see below.
Selection.ShapeRange.ScaleWidth 0.4923809159, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.4923809159, msoFalse, msoScaleFromTopLeft
ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:= "Support.log"
.ScaleWidth and .ScaleHeight needed to be changed to 0.7 for graphic MyFileName.png to fill the height of the Excel cell. Can VBA calculate the required values of .ScaleWidth and .ScaleHeight?
I locked the sheet:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True