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

[Michael1105]

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.

[#2]

@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.

[#3]

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!

[#4]

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".

[#5]

@NikolinoDE 

[#6]

Application.Goto Sheets("Sheet1").Range("C17")

[#7]

Thank you for your reply. I added the code as suggested and when the button is clicked a Run-time error '9': Subscript out of range error is returned.

[#8]

@Michael1105 

This works for me, with simple controls and with active X controls.

If the examples in this file do not work for you either, I recommend that you insert a file (without sensitive data).

You could also send this to me as a private message.

[#9]

@NikolinoDE 

Thank you for your response. I've attached a file of one of our branches and excluded account numbers and member names. I've also placed in cells the box sizes (not as buttons, only as text within the cell) for rerferrence as to what buttons I need for this location.

[#10]

@Michael1105 

Here is your file with the buttons.

I took the liberty of putting you in different types of buttons (with ActiveX control, without, hyperlink, image) so you can choose what type of button you want to add.

Furthermore, I wish you much success with Excel.

Carpe diem

NikolinoDE

So di non sapere niente (Socrate)

[#11]

@NikolinoDE
Thank you so very very much for your help with this. I am most appreciative of your time and knowledge.
I'm sure you do know that you have taught me some skills here that are invaluable to me!
Have a great rest of your day and thank you again! :)