please Ignore previouse Mail
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:26 PM, rajan verma <rajanverma1...@gmail.com>wrote:
> 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
>
--
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
