Groups

    Techy Hel: Excel .. Further to

    my help from a couple of excel wizz's on here - cheers lads - I now need to link 300 pages (numbered sheets 1 - 300) into a front screen for easy access by the end user. (i NEED to use EXCEL as thats the software he's started it in)a

    Whats the best way of going about this? thought about using macros,but then nto sure how many macros I can have per workbook, and to create 300 macro's is gonna take some time.

    4 Comments

    You may have to do a little refining of the code but this will essentially get you heading in the right direction. Create a command button on the front page called "Go to Page" or whatever label you want to give it. Change the command buttons object name to cmdGoTo and copy the code below into it.

    Private Sub cmdGoto_Click()
    'Declare variables required
    Dim objwb As Workbook
    Dim intPgCount As Integer
    Dim intGotoPage As Integer

    On Error GoTo ERR_HANDLER

    'Set object variables
    Set objwb = Application.ActiveWorkbook
    intPgCount = objwb.Sheets.Count

    'Capture page number by prompting user
    intGotoPage = InputBox("Enter Page Number to go to : Between 1 and " & intPgCount, "GoTo Page")

    'Make sure the value entered is a valid sheet number
    If intGotoPage intPgCount Then
    MsgBox "Error. Invalid Page Number"
    Exit Sub
    End If

    'If we have got this far then we have a valid page number. Activate the requested page number.
    objwb.Sheets(intGotoPage).Activate

    'Explicit Exit sub
    Exit Sub

    ERR_HANDLER:
    'Exception handling. Generic.
    MsgBox "Error Number " & Err.Number & " : " & Err.Description & ". Further Action Cancelled."
    objwb.Sheets(1).Activate
    Exit Sub

    End Sub

    Original Poster

    huh


    where the hell do I do that? lol

    Disco;3757377

    huhwhere the hell do I do that? lol



    On the menu, click VIEW / TOOLBARS / CONTROL BOX, and a row of icons will appear, on that row there will be a little grey rectangle shape (this is the command button control). When on your front page, click on the grey rectangle, your cursor will change to a cross, this means you have to draw your button on your front page. Once thats done, right click on the button you've just drawn and then click properties. At the top of the box that appears you will see (name), to the right of that you will see commandbutton1, change the commandbutton1 to cmdGoTo. Close the properties window, right click on your button again then click VIEW CODE. Copy and paste the code from my original thread into that module.

    Once thats done, go back to your front page. Look for the design mode icon (its like a ruler, a pencil on it and a triangle). click this to go between design and run mode. on run mode you should be able to click the button you've drawn and it will then ask you to go to a page number.

    You can also test the code after you've pasted it into the sheet module by pressing F8 and stepping thru the code line by line.

    Original Poster

    sounds complicated like! lol

    also, i would like to "create new customer" so when the button is selected, its create a new page etc -- i guess this is easy enough
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Jaffa quake as McVitie's cuts pack sizes - BBC News34
      2. miikeyblue and shabbird's (but mostly shabbird's) Tuesday night pub quiz!563836
      3. Cheer me up please?813
      4. Your favourite member of the Labour shadow cabinet is .....79

      See more discussions