• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Copy textboxes on excel sheet to another sheet at the same position

I have a workbook with multiple sheets. On the first sheet, there are many textboxes and commandbuttons. These controls should be copied to the other sheets on exactly the same position. Please help.
0
Frans_Truyens
Asked:
Frans_Truyens
  • 2
  • 2
1 Solution
 
KimputerCommented:
Sub Move_controls()
 Dim sh As Shape
 Dim T, L As Integer

 Sheets("Sheet1").Select
     For Each sh In ActiveSheet.Shapes
     If sh.Type <> msoComment Then
         T = sh.Top
         L = sh.Left
         
         For Each Sheet In ActiveWorkbook.Sheets
            If Sheet.Name <> "Sheet1" Then
                 sh.Copy
                    Sheet.Select
                        ActiveSheet.PasteSpecial Link:=False, DisplayAsIcon:=False
                sh.Select
                Selection.Top = T
                Selection.Left = L
         
            End If
         Next
     End If

     Sheets("Sheet1").Select
     Next
 End Sub

Open in new window


Requires sheet1 to be named "Sheet1" (if not, change the code, in the obvious 2 spots)
The results on the other sheets looks the same, but is not 100% the same (it's more of a picture than a real control). So if you still need to edit the labels, command name, etc, this solution is not for you.
If no editing is needed, it could be useful.
0
 
Frans_TruyensAuthor Commented:
I am going to try it.
0
 
KimputerCommented:
sorry, error resolved (control format, i.e. it's still commandbutton or textbox in the other sheets)

Sub Move_controls()
 Dim sh As Shape
 Dim T, L As Integer

 Sheets("Sheet1").Select
     For Each sh In ActiveSheet.Shapes
     If sh.Type <> msoComment Then
        MsgBox sh.FormControlType
        
         T = sh.Top
         L = sh.Left
         
         For Each Sheet In ActiveWorkbook.Sheets
            If Sheet.Name <> "Sheet1" Then
                 sh.Copy
                    Sheet.Select
                        ActiveSheet.Paste
                sh.Select
                Selection.Top = T
                Selection.Left = L
         
            End If
         Next
     End If

     Sheets("Sheet1").Select
     Next
 End Sub
 

Open in new window

0
 
Frans_TruyensAuthor Commented:
Thanks, this solves my problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now