Hi

I have a function that I often call from within my code to take me to a
particular sheet.

Public Function Go_To(address As String)
Sheets(address).Select
Range("A1").Select
End Function

I now want to create a button that will call this function, and am at a loss
as to how to do this. Can anyone help? Is it possible to do this?

Here is my code for the button

Sub Button
ActiveSheet.Buttons.Add(350, 0, 72, 72).Select
Selection.Name = "Go_To_Index"
Selection.OnAction = "Call Go_To(""Sheet 1"")" '<-----------Here is my
problem
ActiveSheet.Shapes("Go_To_Index").Select
Selection.Characters.Text = "Go To Index"
With Selection.Characters(Start:=1, Length:=18).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
End With
End Sub


Many Thanks - Grant

Hi Grant
Replace
Selection.OnAction = "Call Go_To(""Sheet 1"")"
with
Go_To("Sheet1")

I would also suggest some slight amendment to your Go_To function :
'Replace Address with SheetName since Address is an existing expression in
VBA
Function Go_To(SheetName As String)
Sheets(SheetName).Select
Range("A1").Select
End Function

HTH
Cordially
Pascal

"Grant Reid" a écrit dans le message de
...
Hi

I have a function that I often call from within my code to take me to a
particular sheet.

Public Function Go_To(address As String)
Sheets(address).Select
Range("A1").Select
End Function

I now want to create a button that will call this function, and am at a
loss
as to how to do this. Can anyone help? Is it possible to do this?

Here is my code for the button

Sub Button
ActiveSheet.Buttons.Add(350, 0, 72, 72).Select
Selection.Name = "Go_To_Index"
Selection.OnAction = "Call Go_To(""Sheet 1"")" '<-----------Here is
my
problem
ActiveSheet.Shapes("Go_To_Index").Select
Selection.Characters.Text = "Go To Index"
With Selection.Characters(Start:=1, Length:=18).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
End With
End Sub


Many Thanks - Grant

Grant
Sorry for the confusion
You should amend the WHOLE line from
Selection.OnAction = "Go_To(""Sheet1"")"
To
Go_To("Sheet1")

HTH
Cordially
Pascal

"Grant Reid" a écrit dans le message de
...
Hi Pascal

I made the suggested changes to my function and also the change to

Selection.OnAction = "Go_To(""Sheet1"")"

When I run the routine I get a '1004' run time error - Unable to set the
OnAction property of the Button class.

If I change it to

Selection.OnAction = Go_To("Sheet1")

I get '438' run time error - Object doesn't support this property or
method

Any ideas? Any help would be much appreciated.

Regards - Grant

Hi Pascal

I made the suggested changes to my function and also the change to

Selection.OnAction = "Go_To(""Sheet1"")"

When I run the routine I get a '1004' run time error - Unable to set the
OnAction property of the Button class.

If I change it to

Selection.OnAction = Go_To("Sheet1")

I get '438' run time error - Object doesn't support this property or method

Any ideas? Any help would be much appreciated.

Regards - Grant

Hi Pascal

OK, have replaced

Selection.OnAction = "Go_To(""Sheet1"")"
with
Go_To("Sheet1")

When I execute my routine from Sheet2, it creates a button on Sheet2 and
then navigates to Sheet1, after which an error occurs saying the item with
the specified name is not found. The error is logical I suppose, because the
button does not exist on Sheet1.

What I'm really trying to accomplish is to be able execute my routine on any
sheet, create the button on that particular sheet and only when the button
is clicked, go to Sheet1.

I can accomplish this using a hyperlink but a button would be a far more
elegant solution. I hope the answer is out there and that someone can help
me.

Many Thanks - Grant

Hi

I'm still battling with this problem and I hope that there is someone out
there who can provide an answer. I have a function that I often call from
within my code to take me to a particular sheet.

Public Function Go_To(SheetName As String)
Sheets(SheetName).Select
Range("A1").Select
End Function

Now I want to run a routine on Sheet2 that creates a button that only when
it is clicked will go to Sheet1, Range A1. Is this possible????

Here is my code to create the button on Sheet2....... Question Marks
indicate the problem area.

Sub Add_Button()
ActiveSheet.Buttons.Add(350, 0, 72, 72).Select
Selection.Name = "Go_To_Sheet1"
Selection.OnAction = ?????????????
ActiveSheet.Shapes("Go_To_Sheet1").Select
Selection.Characters.Text = "Go To Sheet 1"
With Selection.Characters(Start:=1, Length:=18).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
End With
End Sub

Many Thanks - Grant

Hey Grant -

If I understand your problem, I think you can create a separate function
called "GoToSheet1" which doesn't take any arguments, and just calls the
original Go_To function. Then set your OnAction property to "GoToSheet1":

Public Sub GoToSheet1()
Go_To Sheets(1).Name ' or Go_To "Sheet1"
End Sub

Sub Add_Button()
ActiveSheet.Buttons.Add(350, 0, 72, 72).Select
Selection.Name = "Go_To_Sheet1"
Selection.OnAction = "GoToSheet1"
...

Also, you'll want to put all of these functions in a Module, instead of the
code for a particular worksheet, if you haven't already done so. Otherwise
Excel might complain that it can't find your function.

Hope that helps


"Grant Reid" wrote in message
...
Hi

I'm still battling with this problem and I hope that there is someone out
there who can provide an answer. I have a function that I often call from
within my code to take me to a particular sheet.

Public Function Go_To(SheetName As String)
Sheets(SheetName).Select
Range("A1").Select
End Function

Now I want to run a routine on Sheet2 that creates a button that only when
it is clicked will go to Sheet1, Range A1. Is this possible????

Here is my code to create the button on Sheet2....... Question Marks
indicate the problem area.

Sub Add_Button()
ActiveSheet.Buttons.Add(350, 0, 72, 72).Select
Selection.Name = "Go_To_Sheet1"
Selection.OnAction = ?????????????
ActiveSheet.Shapes("Go_To_Sheet1").Select
Selection.Characters.Text = "Go To Sheet 1"
With Selection.Characters(Start:=1, Length:=18).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
End With
End Sub

Many Thanks - Grant

So why not just have the go_to macro go directly to sheet1 and not pass it any
parms at all?

Public Function Go_To
application.goto worksheets("sheet1").range("a1"), scroll:=true
End Function

But if you want to pass it a parm:

Option Explicit
Sub Add_Button()
Dim BTN As Button

On Error Resume Next
ActiveSheet.Buttons("go_to_sheet1").Delete
On Error GoTo 0

Set BTN = ActiveSheet.Buttons.Add(350, 0, 72, 72)

With BTN
.Name = "Go_To_Sheet1"
.Caption = "Go To Sheet 1"
.OnAction = ThisWorkbook.Name & "!'go_to " _
& Chr(34) & "sheet1" & Chr(34) & "'"
With .Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
End With
End Sub


Grant Reid wrote:

Hi Pascal

OK, have replaced

Selection.OnAction = "Go_To(""Sheet1"")"
with
Go_To("Sheet1")

When I execute my routine from Sheet2, it creates a button on Sheet2 and
then navigates to Sheet1, after which an error occurs saying the item with
the specified name is not found. The error is logical I suppose, because the
button does not exist on Sheet1.

What I'm really trying to accomplish is to be able execute my routine on any
sheet, create the button on that particular sheet and only when the button
is clicked, go to Sheet1.

I can accomplish this using a hyperlink but a button would be a far more
elegant solution. I hope the answer is out there and that someone can help
me.

Many Thanks - Grant

--

Dave Peterson