Creating a hyperlink in a spreadsheet with VB RRS feed

  • Question

  • Hi guys and gals,

    I have a work book that asks you for some variables and it creates a form then adds the info to a register.  after the code is complete I need it to create a hyperlink to the page it has just creates (all the info resides in the same work book and the register is the front page looking up the info on the back pages......  ANYWAY the code goes something like this

     pNumber = Application.InputBox( _
        prompt:="Enter variation number - This will be the number of the variation you are up to eg: VAR010, Keep the VAR as capitals please", _
        Title:="Page Number", _

    '   These are the input boxes for the new variation that is made
    '   they will populate parts of the variation

    '   Variation Area
        vArea = Application.InputBox( _
        prompt:="Enter the area for the variation ie: Pos-Str, Pos-Mec, Pos-Ele, Neg-Mec and so on", _
        Title:="Area", _
    '   Variation Status
        vStatus = Application.InputBox( _
        prompt:="Enter the status of your variation ie: Submited, Approved", _
        Title:="Status", _

    '   Description
        vDescription = Application.InputBox( _
        prompt:="Enter short description for your varition", _
        Title:="Description", _

    '   Date Identified
        vDate = Application.InputBox( _
        prompt:="Enter the date your varition was identified - use the format mm-dd-yy please", _
        Title:="Date", _
    '   Service Order
        vSO = Application.InputBox( _
        prompt:="Enter your service order number - If service order number not availible at this time insert NA", _
        Title:="Service Order", _


    '   This part lets you set the components you will have to the variation
    '   Comming Soon



    '   This bit unhides the template and copies it as another sheet in the register
    '   then hides the template again

        Sheets("Copy").Visible = True
        Sheets("Copy").Copy After:=Sheets(3)
        Sheets("Copy (2)").Select
        Sheets("Copy (2)").Name = pNumber
        Sheets("Variation Register").Select

    '   This here bit replaces the lookup in the new row for the new sheet

        Selection.Insert Shift:=xlDown
        Application.CutCopyMode = False
    '   This bit changes the lookup function to lookup the new variation page
        Selection.Replace What:="VAR001", Replacement:=pNumber, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    '   This part changes the data in the new Variation
        ActiveCell = pNumber
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Copy").Visible = False
    '   This will take all the data from the input boxes and put it into the variation
        ActiveCell = vArea
        ActiveCell = vStatus
        ActiveCell = vDescription
        ActiveCell = vDate
        ActiveCell = vSO
    '   This here bit of code sorts the register page

        Sheets("Variation Register").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Sort Key1:=Range("A8"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Key2:=Range("B6") _
            , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
            False, Orientation:=xlTopToBottom
    '   This code will make the hyperlink to the page from the register - Comming Soon

    '    Selection.End(xlDown).Select
    '    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="pNumber", SubAddress:= _

    End Sub

    Well thats my code - as you can see the code should select the last entry and turn the pNumber field into a hyperlink to the page in the work book.  I am confudled and bamboozled and looking at my code you can see why.  Can some one out there help me out please

    Wednesday, September 2, 2009 6:13 AM


  • Hello,

    Do you have any error message received form your code? I suggest when you past your code it's better to made a short version to reproduce your issue, therefore more members here would easy to catch your issue, and you'll resolve your issue more efficient.

    Please try following code, you just need to exchange the value of parameter Address and SubAddress.

     ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
            "pNumber!A1", TextToDisplay:="#pNumber!A1"


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Tim Li Wednesday, September 9, 2009 7:53 AM
    Tuesday, September 8, 2009 6:38 AM