Assigning a formula to a button

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
Hi,


I have a working formula in Excel that allows me tohyperlink to other cells within my worksheet by clicking a specific cell. Iwould like to assign that formula to a button because frankly, using a buttonlooks much more professional than clicking a cell. This is the formula that Iwant to assign to the button:

=HYPERLINK("#"&CHOOSE(MATCH($B$2,{"Apple","Banana","Pear","Grape"},0),"D6","D57","D108","D159"),"GO")



Any ideas?


Thanks!

 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Not sure what column your hyperlink is in, but in the code below I randomly chose "C". Change it to your real column.

Modify your formula to remove the display value:
=HYPERLINK("#"&CHOOSE(MATCH($B$2,{"Apple","Banana","Pear","Grape"},0),"D6","D57,"D108","D159"))
This will cause the CHOOSE result to show instead.

Create a new macro in a module (not sheet code) with this code:
Code:
Sub followHyperlink(i As Integer)
    Range(Right(Range("C" & i).Value, Len(Range("C" & i).Value) - 1)).Select
End Sub

On your sheet, add a form control button on the same row as the hyperlink. (Developer tab->Insert->Form Controls Button)
Resize to fit on row.
Right click button and assign macro.
In the Macro Name, type the following surrounded by single quotes: macroName + Space + row number of hyperlink cell reference

E.g. for button on row 3:
Code:
'followHyperlink 3'

Hide your hyperlink column (C in my example above).

Add new button for each row.
 
Upvote 0
Try this script in your button:
Code:
Private Sub CommandButton1_Click()
'Modified  8/16/2019  5:47:15 PM  EDT
    Select Case Range("B2").Value
        Case "Apple"
            Range("D6").Select
        Case "Banana"
            Range("D57").Select
        Case "Pear"
            Range("D108").Select
        Case "Grape"
            Range("D159").Select
    End Select
End Sub
 
Upvote 0
My Aswer Is This,

This did exactly what I wanted it to do except for one small thing. When I click the command button for this code, the cell comes into view on the spreadsheet but it does not show at the top of the screen. Ideally, I would like for the cell to be the upper-most cell all the way to the left. Instead, the cell comes into view all the way to the left (which is good) but halfway down the viewing screen.

Is there a way to correct this?
 
Upvote 0
Try this:
Code:
Private Sub CommandButton1_Click()
'Modified  8/19/2019  11:08:32 AM  EDT
    Select Case Range("B2").Value
        Case "Apple"
            Application.Goto Range("D6"), Scroll:=True
        Case "Banana"
            Application.Goto Range("D57"), Scroll:=True
        Case "Pear"
           Application.Goto Range("D108"), Scroll:=True
        Case "Grape"
            Application.Goto Range("D159"), Scroll:=True
    End Select
    
End Sub
 
Upvote 0
If your interested you could use this script which would not require clicking a Button.
The script runs when you enter a value in Range("B2"

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/19/2019  12:03:30 PM  EDT
If Target.Address = Range("B2").Address Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    
    Select Case Target.Value
        Case "Apple"
            Application.Goto Range("D6"), Scroll:=True
        Case "Banana"
            Application.Goto Range("D57"), Scroll:=True
        Case "Pear"
           Application.Goto Range("D108"), Scroll:=True
        Case "Grape"
            Application.Goto Range("D159"), Scroll:=True
    End Select
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top