A Challenge - Adding Buttons to an Excel Worksheet

[Lothdyn]

Hi Everyone,

I've got a challenge I've been trying to work through today, and any ideas would be welcome. I have the following code:

1) Create a list of all the sheets in a workbook


Sub ListSheets()
Dim Mysheet As Object

ActiveSheet.Range("A1").Activate

For Each Mysheet In Sheets
ActiveCell.Value = Mysheet.Name
ActiveCell.Offset(1, 0).Activate
Next Mysheet


End Sub

2) Create buttons and code to jump to all the worksheets in the book, on the active sheet


Sub AddSheetAndButtons2()
Dim Code As String, Count As Integer
Dim NewButton As OLEObject
Dim NewSheet As Worksheet
Dim Mysheet As Object, LinkToSheet As String, NextLine As Integer

ActiveSheet.Range("A1").Activate

Do Until ActiveCell.Value = ""

Count = Count + 1
LinkToSheet = ActiveCell.Value

'Add a new button
Set NewButton = ActiveSheet.OLEObjects.Add("Forms.CommandButton.1")

NewButton.Name = "CB" & Count

With NewButton
.Left = 10
.Top = 28 * Count
.Width = 150
.Height = 25
.Object.Caption = LinkToSheet
End With

'Add Code
Code = ""
Code = "Private Sub CommandButton" & Count & "_Click()" & vbCrLf
Code = Code & vbTab & "ActiveWorkbook.Sheets(" & Chr(34) & LinkToSheet & Chr(34) & ").Activate" & vbCrLf
Code = Code & "End Sub"

'Insert code into module
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
NextLine = .CountOfLines + 4
.InsertLines NextLine, Code
End With

ActiveCell.Offset(1, 0).Activate

Loop

End Sub

Unfortunately after the workbook creates the first button succesfully. I get a subscript out of range error when I identify the VBComponents index.

I'm hoping someone out there can suggest where I'm going wrong.

Thanks for your help

Lothdyn

EDIT:

I just realised, a version of Excel would likely help. I'm trying to get this working in Excel 2000 (or failing that 2004).