jrc1378


I have a workbook with the following sheets:

Display (Sheet1)

Data (Sheet2)

Images (Sheet4)

My sub is built to read reference data from Data! that tells it to copy a certain picture from Images! and paste it onto Display! at a certain location. It also grabs a label name from Data! and puts it to the left of the image just pasted.

All of this works fine, but I also need to change the name of the pasted image from "Picture 201" or whatever to a name pulled from a cell on Data!. In addition, I need to add alt text and a hyperlink to the pasted image based on variables from Data! cells. I've tried a number of Shapes and Pictures methods but nothing seems to work - I either get illegal object usage, or just no errors but no output either. Here's the code with comments throughout:

Code Snippet

Sub InitDrawTwo()

' Declaration of Variables

Dim dRange As Range, pRange As Range, DD As Integer, namePicture As String
Dim displaySheet As Range

' Show a window stating Please Wait

UserForm3.Show (0)

' Turn off screen redraws for faster performance

Application.ScreenUpdating = False

' Read through rows of data

For DD = 3 To 20

' Pull Named Range listed in first reference cell

Set dRange = Sheet2.Range("V" & DD)

' Pull cell reference listed in corresponding reference cell

Set pRange = Sheet2.Range("W" & DD)

' Pull name to label picture with

namePicture = Sheet2.Range("P" & DD)

' Go to Named Range and copy the picture from there

Sheet4.Activate
Sheet4.Range(dRange).Select
Selection.Copy


' Go to the Display sheet and paste the copied image into the cell referenced
' Somewhere in here I need to change the pasted pictures name from
' "Picture 201" or whatever to the value in namePicture. I also need to add
' an alt text filled with data pulled from other cells and a hyperlink
' to another cell in the workbook.

displaySheet.Activate
displaySheet.Range(pRange).Select
ActiveSheet.Paste
Application.CutCopyMode = False

' Put the picture name in the cell one over to the left

ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = namePicture


Next

' Turn on Screen redraws to be safe

Application.ScreenUpdating = True

' Hide the Please Wait window

UserForm3.Hide

' Set focus back to Display page to view final drawing

Sheet1.Range("A1").Select

' Publish the completed display pages to a web page

initWebSave

End Sub

I would love any help I can get with this. Thanks! Oh, please ignore the Dim displaySheet - that's for later use.




Re: Adding alt text, name, and hyperlink to pasted images from another worksheet.

Andy Pope


Hi,

You can use the fact that the pasted shape will have an indexed that matches the count of shapes. I've shown how to get the name and set alternative text. You should be able to incorporate the technique into the hyperlink.
Use the macro recorder to help if necessary.

' Go to the Display sheet and paste the copied image into the cell referenced
' Somewhere in here I need to change the pasted pictures name from
' "Picture 201" or whatever to the value in namePicture. I also need to add
' an alt text filled with data pulled from other cells and a hyperlink
' to another cell in the workbook.

displaySheet.Activate
displaySheet.Range(pRange).Select
ActiveSheet.Paste
Application.CutCopyMode = False

namePicture = displaySheet.shapes(displaySheet.shapes.count).name
displaySheet.shapes(displaySheet.shapes.count).alternativetext = "Alternative Text"

' Put the picture name in the cell one over to the left

ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = namePicture







Re: Adding alt text, name, and hyperlink to pasted images from another worksheet.

jrc1378

That worked great for changing the name and Alt Text of the images on the fly:

Code Snippet
' Set the AltText and Name to match the server network name

Sheet1.Shapes(Sheet1.Shapes.Count).Name = sRange & "-" & namePicture

Sheet1.Shapes(Sheet1.Shapes.Count).AlternativeText = namePicture

' Format picture by calling washPicture() from module3

washPicture

' Put the picture name in the cell one over to the left and hyperlink it


ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = namePicture
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
namePicture, TextToDisplay:=namePicture

But the only way I could get a hyperlink to work was by applying it to the label off to the left of the picture - I get errors every time I try to apply it to the image. Also, though the Alt Text does get set, it doesn't work when I hover over the image on the saved web page.

Thoughts






Re: Adding alt text, name, and hyperlink to pasted images from another worksheet.

jrc1378

got it fixed, thanks



Re: Adding alt text, name, and hyperlink to pasted images from another worksheet.

ajliaks

Hi guys,

How can I get the name of the Shape when I click on it. something like:

ShapeName = ActiveSheet.Shapes.Name

Thanks in advance,

Aldo.






Re: Adding alt text, name, and hyperlink to pasted images from another worksheet.

Andy Pope

You need Application.Caller

Also answered in your other post