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 <[email protected]> wrote:
> it is so easy, if you use hyperlink instead of button
>
> Divaker
>
>
>
> On Wed, Oct 12, 2011 at 5:15 PM, Eddie <[email protected]>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 [email protected]
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> 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 [email protected]
>
> <><><><><><><><><><><><><><><><><><><><><><>
> 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 [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
