Assigning a formula to a button
Results 1 to 8 of 8

Thread: Assigning a formula to a button
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2019
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Assigning a formula to a button

    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!


  2. #2
    Board Regular
    Join Date
    Mar 2016
    Posts
    177
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Assigning a formula to a button

    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.

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,750
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Assigning a formula to a button

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  4. #4
    Board Regular
    Join Date
    Aug 2019
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assigning a formula to a button

    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?

  5. #5
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,750
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Assigning a formula to a button

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  6. #6
    Board Regular
    Join Date
    Aug 2019
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assigning a formula to a button

    Fantastic! That worked perfectly!

    Thanks!

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,750
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Assigning a formula to a button

    Glad I was able to help you.
    Come back here to Mr. Excel next time you need additional assistance.
    Quote Originally Posted by gaudrco View Post
    Fantastic! That worked perfectly!

    Thanks!
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,750
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Assigning a formula to a button

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •