Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text Box Reference (XL97; SR2)

    Is it possible to copy/paste text box objects from workbook A to workbook B and maintain the same reference points from workbook A?

    Thanks,
    John

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Text Box Reference (XL97; SR2)

    John, probably not, but several questions to clarify your problem:

    Is this a textbox on a worksheet or a UserForm?

    Is this a textbox from the Forms toolbar or from the Visual Basic toolbar?

    In what property(s) are you using an Excel reference? LinkedCell or something else?

    Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Box Reference (XL97; SR2)

    Sam,

    The textbox is on a worksheet and was created from the draw toolbar.

    The reference I'm referring to is the exact location (or coordinates) of the texbox object in workbook A.

    Thanks,
    John

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Text Box Reference (XL97; SR2)

    The following code should copy a textbox (named Text Box 1) from the active activesheet to the active sheet of a workbook called Book2, placing it in the same position on the sheet.<pre>Sub CopyObject()
    Dim x As Long, y As Long
    Dim strObject As String
    x = ActiveSheet.Shapes("Text Box 1").Top
    y = ActiveSheet.Shapes("Text Box 1").Left
    ActiveSheet.Shapes("Text Box 1").Copy
    Windows("Book2").ActiveSheet.Activate
    ActiveSheet.Paste
    strObject = Selection.Name
    ActiveSheet.Shapes(strObject).Top = x
    ActiveSheet.Shapes(strObject).Left = y
    End Sub</pre>


    Andrew C

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Text Box Reference (XL97; SR2)

    Wow, I surely misunderstood what you wanted! Too many different textboxes! Here's a macro that will hopefully do what you want. --Sam
    <pre>Option Explicit
    Sub CopyTextBox()
    Dim savLeft As Double, savTop As Double
    Dim savTL As String
    Dim r As Range
    If TypeName(Selection) <> "TextBox" Then
    MsgBox "TextBox must be selected!", vbExclamation
    Exit Sub
    End If
    With Selection
    savLeft = .Left
    savTop = .Top
    savTL = .TopLeftCell.Address
    .Copy
    End With
    Set r = Application.InputBox("Select a cell on the destination sheet." _
    , , , , , , , 8)
    r.Worksheet.Activate
    ActiveSheet.Range(savTL).Select
    ActiveSheet.Paste
    With Selection
    .Left = savLeft
    .Top = savTop
    End With
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •