How to Make Buttons in Excel for linking different thing on
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How to Make Buttons in Excel for linking different thing on

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    Mar 2002
    Location
    Atlanta
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Keaau, Hawaii
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

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
  •  

 

 
DMCA.com