Button Selection OnAction property

[Grant Reid]

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

 

[papou]

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

 

[papou]

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]

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

 

[Grant Reid]

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

 

[Grant Reid]

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

 

[Mark Thorpe]

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

 

[Dave Peterson]

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