I'm a big fan of this forum as it has helped me with coding more times than I can count, but I think I've finally found a question that hasn't been asked yet.
Scenario: I have an excel workbook with a large number of worksheets and each sheet has a number of shapes on them with associated text. I need to translate all this text, so I was looking to do it the easiest way possible. I was able to get the following code to work to copy all the shape text into one worksheet. Worked like a charm!
I have a lovely worksheet that has all the text in every shape. What I want to do now is input the french in the cell next to it and then clicking the next command button, it runs through the shapes and replaces the text with the french. I thought it would be as simple as inverting the initial code, but it has proven more tricky than that. Here is what I have:
It doesn't seem to do anything.
If someone can solve this for me I'd be forever grateful! Thank you in advance!
Scenario: I have an excel workbook with a large number of worksheets and each sheet has a number of shapes on them with associated text. I need to translate all this text, so I was looking to do it the easiest way possible. I was able to get the following code to work to copy all the shape text into one worksheet. Worked like a charm!
VBA Code:
Private Sub CommandButton1_Click()
Dim WS_Count As Integer
Dim I As Integer
Dim shp As Shape
Dim strText As String
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
For I = 1 To WS_Count
For Each shp In Worksheets(I).Shapes
On Error Resume Next
strText = shp.TextFrame.Characters.Text
If Err.Number > 0 Then
Err.Clear
Else
If Len(strText) > 0 Then
Cells(Me.Rows.Count, 1).End(xlUp).Offset(1).Value = strText
End If
End If
Next
Next I
End Sub
I have a lovely worksheet that has all the text in every shape. What I want to do now is input the french in the cell next to it and then clicking the next command button, it runs through the shapes and replaces the text with the french. I thought it would be as simple as inverting the initial code, but it has proven more tricky than that. Here is what I have:
VBA Code:
Private Sub CommandButton2_Click()
Dim WS_Count As Integer
Dim I As Integer
Dim cell As Integer
Dim shp As Shape
Dim strText As String
cell = 2
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
For I = 1 To WS_Count
For Each shp In Worksheets(I).Shapes
On Error Resume Next
If Err.Number > 0 Then
Err.Clear
Else
If Len(strText) > 0 Then
strText = Me.Cells(2, cell).Value
shp.TextFrame.Characters.Text = strText
cell = cell + 1
End If
End If
Next
Next I
End Sub
It doesn't seem to do anything.
If someone can solve this for me I'd be forever grateful! Thank you in advance!
Last edited by a moderator: