Buttons

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

Hello, I would like to put 100 buttons or so on the top of a sheet and
would like to be able to click on anyone of them so that the action would be
to go down the same sheet or another sheet directly to where that title
appears. Can someone suggest me a way?

Thanks you

Daniel
 
I'm sure there is a better way but in case no one else responds here is what
I came up with.
You will need to know the cell location of your names. ie. if your command
button caption is set to Independance Day and that title is in cell O25 then
do below:
Add your button, right click the button, left click view code. Where the
cursor is flashing(should be Private Sub Commandbutton_Click()) type:
cells(25,15).select
Now when you click the command button named Independance Day you will taken
to cell O25.

Not the best method but it works.
HTH
 
Hi, Daniel,
This code would make buttons on the top of a sheet.
Before run this code, please be sure to add NAMES( from Insert > Names.) to
the ranges where that title
appears.


'-- CODE ----------------------------------------------
Sub MakeButtons()
Dim n As Name
Dim rng As Range
Dim i As Long
For Each n In ThisWorkbook.names
On Error Resume Next
Set rng = Range(n)
If Not Err.Number <> 0 Then
i = i + 1
With Sheets(1).Cells(i, 1)
With .Parent.Buttons.Add(.Left, .Top, .Width, .Height)
.Caption = n.Name
.OnAction = "'GotoRange" & Chr$(34) & rng.Parent.Name &
"!" & _
rng.Address & Chr$(34) & "'"
With .Characters(Start:=1, Length:=Len(n.Name)).Font
.Name = "Verdana"
.Size = 9
End With
End With
End With
End If
Next
Set rng = Nothing
End Sub

Sub GotoRange(ByVal Target)
Application.Goto Range(Target)
End Sub
'-------------------------------------------------------


--
Kind Regards
Colo
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Colo of 'The Road of The Cell Masters' :)

URL:http://www.interq.or.jp/sun/puremis/colo/CellMastersLink.htm
mailto:[email protected]

/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
 
Back
Top