How to specify where to insert the Alt Text of an image.

tharnden7

New Member
Joined
Dec 27, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Is there a way to click on an image in one worksheet and have it insert the Alt Text in another worksheet? Ideally, I would like to select which cell to insert the Alt Text. For example, I have a worksheet filled with images that each have their own Alt Text. I would like to click on any image and be able to choose where I would like the Alt Text to be placed on a different worksheet. Is this possible?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Try this code, which is based largely on this brilliant code by @Jaafar Tribak.


Put all this code in the ThisWorkbook module, then save, close and reopen the workbook.

VBA Code:
Option Explicit

Private WithEvents cmbrs As CommandBars


Private Sub Workbook_Activate()
    Set cmbrs = Application.CommandBars
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set cmbrs = Application.CommandBars
End Sub

Private Sub cmbrs_OnUpdate()
    Call ManipulateImage
End Sub


Private Sub ManipulateImage()

    Dim shp As Shape
    Dim AltTextCell As Range
    
    Set shp = GetShape
    If Not shp Is Nothing Then
        On Error Resume Next
        Set AltTextCell = Application.InputBox("Alt Text = " & shp.AlternativeText & vbLf & vbLf & "Select destination sheet and cell", Title:=shp.Name, Type:=8)
        On Error GoTo 0
        If Not AltTextCell Is Nothing Then
            AltTextCell.Range("A1").Value = shp.AlternativeText
        End If
    End If

End Sub


Private Function GetShape() As Shape

    Static oPreviousShp As Shape
    Dim oCurrentShp As Shape
    
    If TypeName(Selection) = "Picture" Then
        Set oCurrentShp = Application.Selection.ShapeRange.Item(1)
        If oPreviousShp Is oCurrentShp Then
            Set GetShape = Nothing
        Else
            Set oPreviousShp = oCurrentShp
            Set GetShape = oCurrentShp
        End If
    End If
    
End Function
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top