How to Make Buttons in Excel for linking different thing on

webking · Mar 20th, 2002, 03:03 PM

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.

lenze · Mar 20th, 2002, 03:17 PM

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)

SamS · Mar 20th, 2002, 03:19 PM

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.

SamS · Mar 20th, 2002, 03:44 PM

Quote:

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.

webking · Mar 20th, 2002, 04:39 PM

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.

Al Chara · Mar 20th, 2002, 04:55 PM

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.

Jay Petrulis · Mar 20th, 2002, 07:01 PM

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

cjones88 · Mar 21st, 2002, 08:53 AM

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.

RET79 · Mar 21st, 2002, 10:43 AM

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 ]

KniteMare · Mar 21st, 2002, 12:12 PM

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 ]

Mar 20th, 2002, 04:55 PM	#6
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	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. __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 20th, 2002, 03:03 PM	#1
webking New Member Join Date: Mar 2002 Posts: 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.

Mar 20th, 2002, 03:17 PM	#2
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	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)

Mar 20th, 2002, 03:19 PM	#3
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	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.

Mar 20th, 2002, 04:39 PM	#5
webking New Member Join Date: Mar 2002 Posts: 21	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.

Mar 20th, 2002, 07:01 PM	#7
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	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

Mar 21st, 2002, 08:53 AM	#8
cjones88 New Member Join Date: Mar 2002 Location: Atlanta Posts: 23	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.

Mar 21st, 2002, 10:43 AM	#9
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	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 ]

Mar 21st, 2002, 12:12 PM	#10
KniteMare Board Regular Join Date: Mar 2002 Location: Hilo, Hawaii Posts: 240	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 ]