Cell name to change name in codes of command buttons
 

The hardest thing about asking for help is to express the problem so
that others can understand it - the issue is so clear in OUR minds that
it's hard to understand how someone else can't grasp it immediately -
and that's not counting the difference in the way folks think.

At any rate, after reading and re-reading your note, it seems to me
that you want to have a _set_ of CommandButtons on your Index worksheet,
and you want the captions of those buttons and the sheets to which
clicking one of the buttons will take you to be specified in a cell in
the A5:E9 range.

If that's the case, then assuming that CommandButton1 is
mapped/linked/paired with the contents of A5 then the following _Click
event code would work to take you to cell A1 of whatever sheet is named
in A5:

Private Sub CommandButton1_Click()
Dim sSheet as string
sSheet = Worksheets("Index").Range("A5").Value
Worksheets(sSheet).Select
Application.GoTo Worksheets(sSheet).Range("A1"), Scroll:=True 'or
false??
End Sub

and you would need similar code (but referencing a different cell on
the Index worksheet, of course) for each of your CommandButtons.

As to updating the command button captions when the sheet names in
A5:E9 change, you're pretty much on track with your code snippet -
except that it appears that

Set myRngToCheck = Me.Range("a5,a6 ")

should be

Set myRngToCheck = Me.Range("a5:e9")

to get the entire range of cells that the sheet names are in.

Also, it's not clear in the step where you actually update the button
caption -

Worksheets("Index").CommandButton1.Caption = Format(Target.Value, "mmmm
dd, yyyy")

you are trying to display the sheet name in a datetime format. It
seems that a simple

Worksheets("Index").CommandButton1.Caption = Target.Text

would be what you needed.

The above changes should get you closer.

Now, for an out-of-the-box idea - you could implement this with one
command button, if you ask your users to select which of the sheets they
want to go to. In other words, have the A5:E9 range visible, instruct
the user to select one of the cells and then click on a single button.
To do that,
the _Click event code would be modified to:

Private Sub CommandButton1_Click()
Dim sSheet as String
Dim sCell as String
Dim Target as Range
Dim myRngToCheck As Range

Set Target = Selection
Set myRngToCheck = Me.Range(A5:E9)

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, myRngToCheck) Is Nothing Then
Exit Sub
End If

sCell = Target.Address(0,0)
sSheet = Target.Text
Worksheets(sSheet).Select
Application.GoTo Worksheets(sSheet).Range(sCell), Scroll:=True 'or
false??
End Sub

Of course, it may not fit what you are doing to make your user go back
to a single range to make the selection, but if it would work, it would
save you a lot of typing!


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112399