Updating text in shape using data entered in a textbox in userform.

[ashni]

HII!!
I have a shape and it has this text in the shape:-

ID: 1648280
Title: NR Analysis and report for colon 2016_12
Completed work: 7
Remaining work: 3

Now i Have a userform which appears after selecting the shape. and it has one text box with label completed work and one button - Save.

Now when i enter the value in the textbox and save it the shape should update to following text:-

ID: 1648280
Title: NR Analysis and report for colon 2016_12
Completed work: '​value user enters in UserForm1 textbox'
Remaining work: 0

How do i do it?

 

[ashni]

We can do it by using Split And Replace function on the Text.

 

[ashni]

using split, left and replace function:-

Private Sub CommandButton1_Click()
Dim FrameText   As String
Dim ReplaceText As Variant
Dim newText     As String
Dim x           As Integer
Dim NEWSTRING As String
'ActiveSheet.Shapes.Range(Array("Rectangle 10")).Select
FrameText = Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text
ReplaceText = Split(FrameText, Chr(10))
'Debug.Print ReplaceText(2)
newText = "Completed Work: " & CInt(UserForm1.TextBox1.Text)
FrameText = Replace(FrameText, ReplaceText(2), newText)
NEWSTRING = Left(FrameText, Len(FrameText) - 2)
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = NEWSTRING & "0"
Range("A1").Select
Unload Me
End Sub