CODEWITHSUNDEEP
vbaexcelhyperlink
Jon0311
Jon0311

Reputation: 61

VBA - hyperlink to new sheet; naming problems

I have a worksheet with an input box (for a company name) that creates a new tab and a new entry in a table with a hyperlink to the new sheet. The hyperlink works, but only if there's no space in the name (i.e if the name is "Company"). The hyperlink works fine, but if the name is "New Company" the hyperlink doesn't work. I can manually edit the link after the fact to make it work again, but I'm hoping to find a solution to the problem here. Example of the code is attached below.

Sub NewCompany()

Dim strName As String

Dim strLink As String

'get the name

    'InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
    strName = InputBox("Enter Company Name.", "NAME COLLECTOR")
    'Exit sub if Cancel button used or no text entered
    If strName = vbNullString Then Exit Sub

    MsgBox "Creating Tab " & strName

'create new row

    Rows("4:4").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

'put Company name in column A

    Cells(4, 1).Value = strName

' create new tab, input name in new tab cell, rename new tab

    Sheets("Blank").Select
    Application.Run "BLPLinkReset"
    Sheets("Blank").Select
    Application.CutCopyMode = False
    Sheets("Blank").Copy After:=Sheets(5)
    Application.Run "BLPLinkReset"
    Sheets("Blank (2)").Select
    Application.Run "BLPLinkReset"
    Cells(3, 3).Value = strName
    Sheets("Blank (2)").Name = strName
    Sheets("Home").Select
    Application.Run "BLPLinkReset"
    Range("B4").Select
    Application.CutCopyMode = False

'create hyperlink to new tab

    strLink = strName & "!A1"
    Range("B4").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        strLink, TextToDisplay:=strName
    Range("A4").Select

End Sub

In conclusion, the hyperlink works when the company name doesn't have a space in it, but I'd like find a way to change the code to make it work with a space in the company name. Any help would be much appreciated.

Upvotes: 2

Views: 488

Answers (1)

jkpieterse
jkpieterse

Reputation: 2956

Change this line:

strLink = strName & "!A1"

to

strLink = "'" & strName & "'!A1"

Upvotes: 1

Related Questions