VBA copy textbox from one workbook and paste

D

daveyc18

Well-known Member
Joined
Feb 11, 2013
Messages
707
Office Version
  1. 365
  2. 2010
trying to copy a textbox (activesheet.shapes.range(text box) from another workbook to another and retain the formatting

I mean, the recording does do the job, but it's not dynamic as it actually records the actual text in the box and this text will vary month to month
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
this is the ugly code i get

Code: 
 Windows("December FY2021 HC.xlsx").Activate
    ActiveSheet.Shapes.Range(Array("TextBox 2")).Select


    Windows("December 2020-21 Hardclose.xlsx").Activate
    ActiveSheet.Shapes.Range(Array("TextBox 2")).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        "Key Trends" & Chr(13) & "Key Trend" & Chr(13) & "- For December YTD  Forecast, Comms has a pressure (overspending) of $111111M ($111111M in pressures from XXXX and $11111M in pressures from XXXXX expense). " & Chr(13) & "Risks:" & Chr(13) & "1. None Trends, Risks & Mitigation PlansKey Trend- For December YTD  Forecast, Comms has a pressure (overspending) o" & _
        "f $0M ($0M in pressures fromXXXXX and $0.M in pressures from XXXXxexpense). Risks:None" & Chr(13) & "" & _
        ""
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 37). _
        ParagraphFormat
        .BaselineAlignment = msoBaselineAlignAuto
        .FirstLineIndent = 0
        .HangingPunctuation = msoTrue
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 11).Font
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(12, 25).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(37, 1).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 10
        .Name = "+mn-lt"
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(38, 10).ParagraphFormat _
        .FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(38, 9).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Italic = msoFalse
        .Name = "+mn-lt"
        .UnderlineStyle = msoUnderlineSingleLine
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(47, 1).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 10
        .Name = "+mn-lt"
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(48, 182). _
        ParagraphFormat
        .BaselineAlignment = msoBaselineAlignAuto
        .FirstLineIndent = 0
        .HangingPunctuation = msoTrue
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(48, 2).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Italic = msoTrue
        .Name = "+mn-lt"
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(50, 179).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Italic = msoFalse
        .Name = "+mn-lt"
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(229, 1).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 10
        .Name = "+mn-lt"
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(230, 7). _
        ParagraphFormat
        .BaselineAlignment = msoBaselineAlignAuto
        .FirstLineIndent = 0
        .HangingPunctuation = msoTrue
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(230, 6).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Italic = msoFalse
        .Name = "+mn-lt"
        .UnderlineStyle = msoUnderlineSingleLine
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(236, 1).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 10
        .Name = "+mn-lt"
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(237, 274). _
        ParagraphFormat.FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(237, 3).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Italic = msoFalse
        .Name = "+mn-lt"
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(240, 270).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Italic = msoFalse
        .Name = "+mn-lt"
        .UnderlineStyle = msoUnderlineSingleLine
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(510, 1).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 10
        .Name = "+mn-lt"
    End With
 
Upvote 0
Code: 
Sheets("comms").Activate
ActiveSheet.Shapes("Text Box 2").Select
a = Selection.Characters.Text


ThisWorkbook.Sheets("comms").Activate
ActiveSheet.Shapes("Text Box 2").Select
Selection.Characters.Text = a

this works, bu tonly for the first 255 characters it looks like
 
Upvote 0
anybody ?

in summary, I literally want to copy one textbook to another workbook with all the formatting
 
Upvote 0
Thanks for providing information about your solution.

However, for the future please take note of #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!

If you do cross-post in the future and also provide links to the threads in other forums where you have posted the question, then there shouldn’t be a problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Vlookup that copies formatting?
Replies
3
Views
330
Eric W
Eric W
P
  • Solved
Textbox BackColor Problem
Replies
5
Views
234
rlv01
R
P
  • Solved
Checking Textbox For Specific Text Entry
Replies
4
Views
197
Pacman52
P
N
  • Question
Automating textbox updates in PP using VBA to read excel data
Replies
0
Views
434
noobslayer252
N
H
  • Question
VBA Project, Copy enabled labels and textboxes from a userform to another userform
Replies
0
Views
186
HosEsf
H

Forum statistics

Threads
1,214,646
Messages
6,120,716
Members
448,985
Latest member
chocbudda

Share this page

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