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 wksIndex As Worksheet Dim shButton As 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.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 > -- 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