Re: $$Excel-Macros$$ Pasting a button onto multiple sheets
Put this macro in the THISWORKBOOK module. Now when you double click cell a1 in any sheet you will go to sheet1 Change name and cell to suit Private Sub Workbook_SheetBeforeDoubleClick _ (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) If Target = Sh.Range(a1) Then Sheets(sheet1).Select End Sub Don Guillett SalesAid Software dguille...@gmail.com -Original Message- From: Eddie Sent: Wednesday, October 12, 2011 6:45 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Pasting a button onto multiple sheets Hi, I have a workbook with around 100 worksheets. The first sheet acts as a summary/introductory or front end and has links to other sub- sheets. In the first sub-sheet I've created a button containing a macro that returns you to the introduction sheet. What I'd like to do is copy this button onto each of the sub-sheets, however Excel doesnt allow me to paste into multiple sheets. Is there anyway of replicating this button in multiple sheets without having to do this manually for each sheet? Any help very much appreciated. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Pasting a button onto multiple sheets
it is so easy, if you use hyperlink instead of button Divaker On Wed, Oct 12, 2011 at 5:15 PM, Eddie eddiejame...@googlemail.com wrote: Hi, I have a workbook with around 100 worksheets. The first sheet acts as a summary/introductory or front end and has links to other sub- sheets. In the first sub-sheet I've created a button containing a macro that returns you to the introduction sheet. What I'd like to do is copy this button onto each of the sub-sheets, however Excel doesnt allow me to paste into multiple sheets. Is there anyway of replicating this button in multiple sheets without having to do this manually for each sheet? Any help very much appreciated. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Pasting a button onto multiple sheets
I Create a macro to an Index Sheet.. Hope it will be Usable Sub MakeComponentinRunTImeOnSpreadSheet() On Error Resume Next Dim cb As OLEObject n = 5 For i = 1 To 10 Set cb = ActiveSheet.OLEObjects.Add(classtype:=Forms.Checkbox.1) With cb .Top = n .Object.Caption = Do you want to Select Range .LinkedCell = Active.Offset(0, 5).Address .Object.Value = False .Visible = True End With ActiveCell.Offset(1, 0).Activate Set cb = Nothing n = n + 20 Next End Sub Sub MakeMyIndex() Dim intICounter As Integer Dim intTotalSheet As Integer Dim wksIndexAs Worksheet Dim shButtonAs Shape Set wksIndex = Worksheets.Add wksIndex.Name = Index intTotalSheet = ThisWorkbook.Worksheets.Count For intICounter = 1 To intTotalSheet If Worksheets(intICounter).Name wksIndex.Name Then Set shButton = wksIndex.Shapes.AddShape(msoShapeRectangle, wksIndex.Cells(intICounter, 3).Left, wksIndex.Cells(intICounter, 3).Top, wksIndex.Cells(intICounter, 3).Width, wksIndex.Cells(intICounter, 3).Height) wksIndex.Hyperlinks.Add shButton, , ' Worksheets(intICounter).Name '!A1, Click Me, Worksheets(intICounter).Name shButton.TextFrame.Characters.Text = Worksheets(intICounter).Name Set sh = Nothing End If Next wksIndex.Cells(, 3).EntireColumn.ColumnWidth = 16 For intICounter = 1 To intTotalSheet If Worksheets(intICounter).Name wksIndex.Name Then Set shButton = Worksheets(intICounter).Shapes.AddShape(msoShapeRectangle, Worksheets(intICounter).Range(B3).Left, Worksheets(intICounter).Range(B3).Top, _ Worksheets(intICounter).Range(B3).Width, Worksheets(intICounter).Range(B3).Height) Worksheets(intICounter).Columns(3).ColumnWidth = 16 wksIndex.Hyperlinks.Add shButton, , ' wksIndex.Name '!A1, Click Me, Index shButton.TextFrame.Characters.Text = Index Set sh = Nothing End If Next End Sub On Wed, Oct 12, 2011 at 9:03 PM, Divaker Pandey divake...@gmail.com wrote: it is so easy, if you use hyperlink instead of button Divaker On Wed, Oct 12, 2011 at 5:15 PM, Eddie eddiejame...@googlemail.comwrote: Hi, I have a workbook with around 100 worksheets. The first sheet acts as a summary/introductory or front end and has links to other sub- sheets. In the first sub-sheet I've created a button containing a macro that returns you to the introduction sheet. What I'd like to do is copy this button onto each of the sub-sheets, however Excel doesnt allow me to paste into multiple sheets. Is there anyway of replicating this button in multiple sheets without having to do this manually for each sheet? Any help very much appreciated. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Rajan verma +91 9158998701 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Pasting a button onto multiple sheets
please Ignore previouse Mail Sub MakeMyIndex() Dim intICounter As Integer Dim intTotalSheet As Integer Dim wksIndexAs Worksheet Dim shButtonAs Shape Set wksIndex = Worksheets.Add wksIndex.Name = Index intTotalSheet = ThisWorkbook.Worksheets.Count For intICounter = 1 To intTotalSheet If Worksheets(intICounter).Name wksIndex.Name Then Set shButton = wksIndex.Shapes.AddShape(msoShapeRectangle, wksIndex.Cells(intICounter, 3).Left, wksIndex.Cells(intICounter, 3).Top, wksIndex.Cells(intICounter, 3).Width, wksIndex.Cells(intICounter, 3).Height) wksIndex.Hyperlinks.Add shButton, , ' Worksheets(intICounter).Name '!A1, Click Me, Worksheets(intICounter).Name shButton.TextFrame.Characters.Text = Worksheets(intICounter).Name Set sh = Nothing End If Next wksIndex.Cells(, 3).EntireColumn.ColumnWidth = 16 For intICounter = 1 To intTotalSheet If Worksheets(intICounter).Name wksIndex.Name Then Set shButton = Worksheets(intICounter).Shapes.AddShape(msoShapeRectangle, Worksheets(intICounter).Range(B3).Left, Worksheets(intICounter).Range(B3).Top, _ Worksheets(intICounter).Range(B3).Width, Worksheets(intICounter).Range(B3).Height) Worksheets(intICounter).Columns(3).ColumnWidth = 16 wksIndex.Hyperlinks.Add shButton, , ' wksIndex.Name '!A1, Click Me, Index shButton.TextFrame.Characters.Text = Index Set sh = Nothing End If Next End Sub On Wed, Oct 12, 2011 at 9:26 PM, rajan verma rajanverma1...@gmail.comwrote: I Create a macro to an Index Sheet.. Hope it will be Usable Sub MakeComponentinRunTImeOnSpreadSheet() On Error Resume Next Dim cb As OLEObject n = 5 For i = 1 To 10 Set cb = ActiveSheet.OLEObjects.Add(classtype:=Forms.Checkbox.1) With cb .Top = n .Object.Caption = Do you want to Select Range .LinkedCell = Active.Offset(0, 5).Address .Object.Value = False .Visible = True End With ActiveCell.Offset(1, 0).Activate Set cb = Nothing n = n + 20 Next End Sub Sub MakeMyIndex() Dim intICounter As Integer Dim intTotalSheet As Integer Dim wksIndexAs Worksheet Dim shButtonAs Shape Set wksIndex = Worksheets.Add wksIndex.Name = Index intTotalSheet = ThisWorkbook.Worksheets.Count For intICounter = 1 To intTotalSheet If Worksheets(intICounter).Name wksIndex.Name Then Set shButton = wksIndex.Shapes.AddShape(msoShapeRectangle, wksIndex.Cells(intICounter, 3).Left, wksIndex.Cells(intICounter, 3).Top, wksIndex.Cells(intICounter, 3).Width, wksIndex.Cells(intICounter, 3).Height) wksIndex.Hyperlinks.Add shButton, , ' Worksheets(intICounter).Name '!A1, Click Me, Worksheets(intICounter).Name shButton.TextFrame.Characters.Text = Worksheets(intICounter).Name Set sh = Nothing End If Next wksIndex.Cells(, 3).EntireColumn.ColumnWidth = 16 For intICounter = 1 To intTotalSheet If Worksheets(intICounter).Name wksIndex.Name Then Set shButton = Worksheets(intICounter).Shapes.AddShape(msoShapeRectangle, Worksheets(intICounter).Range(B3).Left, Worksheets(intICounter).Range(B3).Top, _ Worksheets(intICounter).Range(B3).Width, Worksheets(intICounter).Range(B3).Height) Worksheets(intICounter).Columns(3).ColumnWidth = 16 wksIndex.Hyperlinks.Add shButton, , ' wksIndex.Name '!A1, Click Me, Index shButton.TextFrame.Characters.Text = Index Set sh = Nothing End If Next End Sub On Wed, Oct 12, 2011 at 9:03 PM, Divaker Pandey divake...@gmail.comwrote: it is so easy, if you use hyperlink instead of button Divaker On Wed, Oct 12, 2011 at 5:15 PM, Eddie eddiejame...@googlemail.comwrote: Hi, I have a workbook with around 100 worksheets. The first sheet acts as a summary/introductory or front end and has links to other sub- sheets. In the first sub-sheet I've created a button containing a macro that returns you to the introduction sheet. What I'd like to do is copy this button onto each of the sub-sheets, however Excel doesnt allow me to paste into multiple sheets. Is there anyway of replicating this button in multiple sheets without having to do this manually for each sheet? Any help very much appreciated. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links :
Re: $$Excel-Macros$$ Pasting a button onto multiple sheets
I tend to agree with Divakar. But in case you'd still want to go with the original request, here's the macro Sub CopyShapesAcross() Dim shp As Shape Dim lngLoop As Long Application.ScreenUpdating = False Set shp = Sheets(1).Shapes(NameOfShape) For lngLoop = 2 To Sheets.Count If Sheets(lngLoop).Type = xlWorksheet Then shp.Copy Sheets(lngLoop).Paste Application.Goto Sheets(lngLoop).Cells(1) With Sheets(lngLoop).Shapes(shp.Name) .Top = shp.Top .Left = shp.Left End With End If Next lngLoop With Application .Goto Sheets(1).Cells(1) .ScreenUpdating = True End With End Sub Regards, Sam Mathai Chacko (GL) On Wed, Oct 12, 2011 at 9:03 PM, Divaker Pandey divake...@gmail.com wrote: it is so easy, if you use hyperlink instead of button Divaker On Wed, Oct 12, 2011 at 5:15 PM, Eddie eddiejame...@googlemail.comwrote: Hi, I have a workbook with around 100 worksheets. The first sheet acts as a summary/introductory or front end and has links to other sub- sheets. In the first sub-sheet I've created a button containing a macro that returns you to the introduction sheet. What I'd like to do is copy this button onto each of the sub-sheets, however Excel doesnt allow me to paste into multiple sheets. Is there anyway of replicating this button in multiple sheets without having to do this manually for each sheet? Any help very much appreciated. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Sam Mathai Chacko -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
