How to Make Buttons in Excel for linking different thing on

webking

New Member
Joined
Mar 10, 2002
Messages
21
I have an excel sheet on which i have made different sections containing information about sales , expenses , store etc..Now each section has a Heading showing what that section is for.
But the sections are so manay that it becomes confusing finding the rite section for data entry.
Is it possible that some how i can make Button of each Heading at the start of the sheet which can enable the user to just click the button and he is at the desired section. Like if suppose the Expenses heading is in Cell AA11 the button should be such that when we click the button it takes the user on that cell.
Also there has to be a button which takes the user back to the cells where all the buttons of different sections are present.
Please guide me steps wise.
Thankyou.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
The easiest way is to assign a Named Range to each place you want to go. Then place Command Buttons on the sheet from the CONTROL BOX TOOLBAR. You may then assign a hyperlink to each button (Insert Hyperlink)
 
Upvote 0
I have sometmes used this method successfully - set your headings as range names and then create hyperlinks to them (Insert - hyperlinks) and in reverse also.
 
Upvote 0
On 2002-03-20 14:19, SamS wrote:
I have sometmes used this method successfully - set your headings as range names and then create hyperlinks to them (Insert - hyperlinks) and in reverse also.
unless you have vba code attached to the buttons, there is no need for them, just highlite the text (remember to give them a range name so you can navugate back to them) and use the hyperlink, as the rangename acts as a bookmark.
 
Upvote 0
Thankyou for your replies but
your method is not working. like i want to have a excel sheet which when i open shows the following:-

1) In Cell A1 to A6 it shows Buttons which when pressed takes the user to the desired heading present on the sheet.

it like creating a button on an excel sheet in cell A1 which is when pressed takes the user to say Cell H1.

can we do this in excel.
 
Upvote 0
Put the following code in the code for the sheet that contains your button:

Private Sub CommandButton1_Click()
[h1].Select
End Sub

Repeat as necessary for the number of buttons that you have.
 
Upvote 0
Hi WebKing,

You can achieve what you want without buttons by using an event macro.

In the sheet module place something like the following:

---begin VBA---
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Column = 1 Then
If Target.Row = 1 Then Range("H1").Select
If Target.Row = 2 Then Range("H2").Select
If Target.Row = 3 Then Range("H3").Select
If Target.Row = 4 Then Range("H4").Select
If Target.Row = 5 Then Range("H5").Select
If Target.Row = 6 Then Range("H6").Select
End If

End Sub
---end VBA---

Double clicking on A1:A6 maps to H1:H6. You can still have data, formulae, etc. in the cells, too.

HTH,
Jay


Bye,
Jay
 
Upvote 0
I made a similar spreadsheet. At the top all it has is buttons. You click on the button, then it takes you to the point where you would enter the data. When you are done entering, you click on a button that says either "Home" or "Next". Home naturally takes you to the start of the sheet, and Next takes you to wherever place on the sheet you want to go to is. This is a little time consuming because you have to make a macro for each location that you want to go to, but because I use this thing all day everyday, it makes life much easier.

To do it, first you have to record a macro. Once you hit record, move your scrollbars until you see the window you want for your first data area. Then adjust the zoom to your desired level (remember if others are using this they could have different screen resolutions, so be careful here), then stop the recording. Assign this macro to a control button at the top of your page and repeat as necessary for all of your areas. Make sure you make a Home button in all of your data areas so there is an easy way to get back to the start. Hope this helps.
 
Upvote 0
easier on the eyes....

---begin VBA---
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Column = 1 Then

For i =1 to 6 step 1 then
If Target.Row = i Then Cell(i, "H").Select
Next i

End If

End Sub
---end VBA---
This message was edited by RET79 on 2002-03-21 09:44
 
Upvote 0
RET79, nice tight code but like this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Column = 1 Then

For i = 1 To 6 Step 1
If Target.Row = i Then Cells(i, "H").Select
Next i

End If

End Sub

Yours in EXCELent Frustration
KniteMare
This message was edited by KniteMare on 2002-03-21 11:13
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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