I am creating a simple envelope budgeting sheet. On my first sheet, I
would like to have a list of all the other sheets ('envelopes') and
their balance. Each 'envelope' sheet has its current balance in cell
A7 and I would like to access that as well.

So my first sheet would have something like:

8 | 9
D <name of second sheet| value of A7 on second sheet
E <name of third sheet | value of A7 on third sheet
....

If possible, I would like this to be automatic so when I add another
envelope sheet, its name and A7 value would show up on the first sheet.

Finally, would it be possible to detect a double click on the sheet
name and
then switch to that sheet?

Thanks in advance for any help.

This will add hyperlinks on sheet1 to each of the following sheets, 2, 3, 4,
etc. The hyperlink text will be the sheet name, the cell under it will be
the value from cell A7 on that sheet. The sheet names will begin on sheet1,
range D8 and continue for each column to the right for as many sheets you
have.

Sub test()
Dim i As Long
Dim DataClmn As Long
Dim ShtName As String

DataClmn = 4
For i = 2 To Worksheets.Count
ShtName = Worksheets(i).Name

With Worksheets(1)
.Hyperlinks.Add Anchor:=.Cells(8, DataClmn), _
Address:="", _
SubAddress:=ShtName & "!A7", _
ScreenTip:="", _
TextToDisplay:=ShtName & ""
.Cells(9, DataClmn).Value = Worksheets(i).Range("A7").Value
End With
DataClmn = DataClmn + 1

Next i
End Sub

Mike F
wrote in message
ups.com...
I am creating a simple envelope budgeting sheet. On my first sheet, I
would like to have a list of all the other sheets ('envelopes') and
their balance. Each 'envelope' sheet has its current balance in cell
A7 and I would like to access that as well.

So my first sheet would have something like:

8 | 9
D <name of second sheet| value of A7 on second sheet
E <name of third sheet | value of A7 on third sheet
...

If possible, I would like this to be automatic so when I add another
envelope sheet, its name and A7 value would show up on the first sheet.

Finally, would it be possible to detect a double click on the sheet
name and
then switch to that sheet?

Thanks in advance for any help.

Works great. The only problem I am haing is with sheet names with
spaces or '-' in the name.

Can I use sheet names with spaces?

Thanks.

Yes spaces and hyphens are OK in sheet names. Could you describe the exact
problem you are having?

Mike F
wrote in message
oups.com...
Works great. The only problem I am haing is with sheet names with
spaces or '-' in the name.

Can I use sheet names with spaces?

Thanks.

Also sheet names that are a date like Sep-05 were a problem when displayed
on sheet1. Excel used it's date recognition feature to screw it up. Here is
the modified code that should work with any sheet name.

Dim i As Long
Dim DataClmn As Long
Dim ShtName As String

DataClmn = 4
For i = 2 To Worksheets.Count
ShtName = Worksheets(i).Name

With Worksheets(1)
.Hyperlinks.Add Anchor:=.Cells(8, DataClmn), _
Address:="", _
SubAddress:="'" & ShtName & "'!A7", _
ScreenTip:="", _
TextToDisplay:="'" & ShtName
.Cells(9, DataClmn).Value = Worksheets(i).Range("A7").Value
End With
DataClmn = DataClmn + 1

Next i
End Sub

Mike F


"Mike Fogleman" wrote in message
...
Yes spaces and hyphens are OK in sheet names. Could you describe the exact
problem you are having?

Mike F
wrote in message
oups.com...
Works great. The only problem I am haing is with sheet names with
spaces or '-' in the name.

Can I use sheet names with spaces?

Thanks.

Thanks!