Forum Discussion

Michael1105's avatar
Michael1105
Brass Contributor
Jul 01, 2022

Form Control Buttons to Take me to a specific areas of a worksheet.

Hello,

 

I'm hoping someone can help.  

I'm looking to place forms control buttons, that when clicked, will take the viewer to an area of a worksheet.

For example:  I have a large report that I send to my branch managers listing their safe deposit box inventories.  These lists have multiple box sizes and to view this data scrolling has to take place to find them, which is cumbersome and time consuming.

I'm looking to place buttons at the top of the worksheet that read the box sizes (i.e. 3x5, 3x10, etc.) and when clicked the curser will move to the cell with the title of the box size.

I've tried to search this here and on YouTube but can't seem to find how to code the buttons, nor could I figure it out by reviewing the "Format Control" menu within the control button.

Could someone let me know if this can be accomplished and how to do this?  

I appreciate any help you can lend.

 

 

developer
Macros and VBA

10 Replies

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jul 02, 2022

    Michael1105 

    Here are three small VBA examples that you can use, depending on what you intend to do.

     

    ‘ 1. Button with VBA & Hyperlink:
Private Sub CommandButton1_Click()
ActiveWorkbook.FollowHyperlink Address:="https://www.Nikolino.de", NewWindow:=True
End Sub


‘ 2. Button with VBA form control
Sub ButtonClick ()
Worksheets("Sheet1").Select
End Sub


‘ 3. Button with VBA Active X control
Private Sub CommandButton1_Click()
Application.Goto Sheets("Sheet1").Range("C25")
End Sub

     

    As a VBA alternative, formatting a cell to look like a button and then pasting the hyperlink into the cell would be the most elegant solution and save resources. But that also depends on your plans.

     

    Hope I was able to help you :).

     

    NikolinoDE

    I know I don't know anything (Socrates)

    Was the answer useful? Mark them as helpful!

    This will help all forum participants.

    • Michael1105's avatar
      Michael1105
      Brass Contributor
      Jul 21, 2022
      Hello NikolinoDE,

      I tried applying the 3rd option to my worksheet because it seems to be the best option for what I want to accomplish. However, it doesn't seem to be working. What I want to do is be to click a button located at the top of the worksheet that will take me directly to a section of the worksheet that the button refers to. I've added a screen shot of what I'm looking to do. I was able to add the code to the button with no errors, however, the button doesn't do anything. I've also included he VBA coding to the button for reference. Also, when I tried renaming the button "10x10", I was given an error message: Not a legal object name: "10x10".
    • Michael1105's avatar
      Michael1105
      Brass Contributor
      Jul 21, 2022
      Hello, NikolinoDE,

      I apologize for the delay in responding. I haven't had a chance to review the information but I will try what you've suggested today.
      Thank you so very much for your help!

Resources