-1

So I have a Workbook which uses an UserForm to create a new record into a table. It also creates a new worksheet tab which takes some of this information to create the name of this new tab.

However, I would like to then hyperlink to this newly created tab but have hit a snag as most support pages require an 'Active cell' which I don't have until I run the userform.

My steps are: -

  1. User fills in userform and clicks submit.
  2. This then does 2 things: - A. A new tab is created which takes the course date, course name and initials of trainer - e.g. 01102000EXEL1MG which would be a unique name for this tab which contains a form. B. I then have an existing tab which contains a table and in this table a new row is created with information from the userform such as course date, course name, trainer and a few other details.
  3. What I need to do is then take the newly created tab name - e.g. 01102000EXEL1MG and create a hyperlink to this tab in my 'Course List' table in the newly created row.

Is this possible?

I have tried the following to no avail: -

Make active cell a hyperlink to newly created tab

https://mindovermetal.org/en/how-to-add-and-hyperlink-new-sheets-with-vba-in-excel/

Code tried so far: -

Option Explicit

Private Sub CommandButtonCompleteBookerInformation_Click()

'Step 1: Once 'Complete Booker Information' Cmd Button selected, start UserForm Data Validation

    '1.1 Course Name not Empty

    If TextBoxCourseName.Text = "" Then
        MsgBox "Please add a course name", vbCritical
        Exit Sub
        
    Else

    '1.2 Course Date in XX/XX/XXXX Format

        If TextBoxCourseDate.Text = "" Then
            MsgBox "Please add the course date in XX/XX/XXXX format", vbCritical
            Exit Sub
    Else
            Dim date_format As String
            date_format = "##/##/####"
                If Not TextBoxCourseDate.Text Like date_format Then
                    MsgBox "Please add the course date in XX/XX/XXXX format", vbCritical
                    Exit Sub
    Else

    '1.3 Course Duration is numeric

                If IsNumeric(TextBoxDurationDays) = False Then
                    MsgBox "Please enter the course duration in Number of Days (numeric)", vbCritical
                    Exit Sub
    
    Else

''''''

'Step 2 End Ifs

    End If
    End If
    End If
    End If


''''''

'Step 3: Take this information and populate the sheet

    '3.1 Get Course Name Info and store in correct cell
    Dim CourseNameValue As String
    CourseNameValue = TextBoxCourseName.Text
    Sheets(Sheets.Count).Range("D3").Value = CourseNameValue

    '3.2 Same with Course Date
    Dim CourseDateValue As String
    CourseDateValue = TextBoxCourseDate.Text
    Sheets(Sheets.Count).Range("D4").Value = CourseDateValue

    '3.3 Same with Course Duration
    Dim CourseDurationValue As String
    CourseDurationValue = TextBoxDurationDays.Text
    Sheets(Sheets.Count).Range("D9").Value = CourseDurationValue

    '3.4 Get Trainer Name and store in correct cell
    
    If OptionTrainerNameAB.Value = True Then
    Sheets(Sheets.Count).Range("D6").Value = "name 1 part a"
    Sheets(Sheets.Count).Range("G6").Value = "name 1 part b"
    
    Else
    
    If OptionTrainerNameCD.Value = True Then
    Sheets(Sheets.Count).Range("D6").Value = "name 2 part a"
    Sheets(Sheets.Count).Range("G6").Value = "name 2 part b"
            
    Else
    
    If TextBoxTrainerNameOther.Text <> "" Then
    Sheets(Sheets.Count).Range("D6").Value = TextBoxTrainerNameOther.Text
    
    Else
    Sheets(Sheets.Count).Range("D6").Value = "Unknown"
    
        End If
        End If
        End If
        
    '3.5 Get Course Location and store in correct cell
    If OptionLocationVC.Value = True Then
    Sheets(Sheets.Count).Range("D7").Value = "Virtual Classroom"
    
    Else
    
    If OptionLocationOnsite.Value = True Then
    Sheets(Sheets.Count).Range("D7").Value = "Onsite"
    
    Else
    
    If OptionLocationSite1.Value = True Then
    Sheets(Sheets.Count).Range("D7").Value = "site 1"
    
    Else
    
    If OptionLocationSite2.Value = True Then
    Sheets(Sheets.Count).Range("D7").Value = "site 2"
    
        End If
        End If
        End If

    '3.6 Get Course Type and store in correct cell
    If OptionCourseTypeCourse1.Value = True Then
    Sheets(Sheets.Count).Range("D8").Value = "Course1"
    
    Else
    
    If OptionCourseTypeCourse2.Value = True Then
    Sheets(Sheets.Count).Range("D8").Value = "Course2"
     
    Else
        
    If OptionCourseTypeOther.Value = True Then
    Sheets(Sheets.Count).Range("D8").Value = "Other Third Party"
    
    Else
    Sheets(Sheets.Count).Range("D8").Value = "Course Type Unknown"

        End If
        End If
        End If
        End If
        
    '3.7 Get Delivery Method and store in correct cell
    If CheckBoxTeamsOrZoomTeams.Value = True Then
    Sheets(Sheets.Count).Range("F8").Value = "Teams"
    
    Else
    
    If CheckBoxTeamsOrZoomZoom.Value = True Then
    Sheets(Sheets.Count).Range("F8").Value = "Zoom"
    
    Else
    
    If CheckBoxTeamsOrZoomNA.Value = True Then
    Sheets(Sheets.Count).Range("F8").Value = "N/A"
    
    Else
    Sheets(Sheets.Count).Range("F8").Value = "Delivery Method Unknown"
    
        End If
        End If
        End If
        
    '3.8 Get Public / Closed and store in correct cell
    
    If CheckBoxPublicOrClosedPublic.Value = True Then
    Sheets(Sheets.Count).Range("H8").Value = "Public"
    
    Else
    
    If CheckBoxPublicOrClosedClosed.Value = True Then
    Sheets(Sheets.Count).Range("H8").Value = "Closed"
    
    Else
    Sheets(Sheets.Count).Range("H8").Value = "Public/Closed Unknown"
    
        End If
        End If

''''''

 'Step 4: Add a new row to the "Course List" table
 
    Dim CourseListTable As ListObject
    Set CourseListTable = Sheets("Course List").ListObjects("CourseList")
    Dim AddedRow As ListRow
    Set AddedRow = CourseListTable.ListRows.Add
    With AddedRow

''''''
    
'Step 5: Take the date information from the userform and convert into an actual date (as recognised by excel)

    Dim CourseDateValueAsDate As Date
    
    CourseDateValueAsDate = CDate(TextBoxCourseDate.Text)
    
''''''

'Step 6: Add the Course Date (as Date), Name, Location, Trainer and Public or Closed Status to the "Course List" Table new row
 
    Dim initials As String
        
        .Range(1) = CourseDateValueAsDate
        .Range(2) = CourseNameValue
        .Range(3) = Sheets(Sheets.Count).Range("D7").Value
        .Range(4) = Sheets(Sheets.Count).Range("D6").Value
        .Range(5) = Sheets(Sheets.Count).Range("G6").Value
        
    initials = Left(Sheets(Sheets.Count).Range("D6").Value, 1) & Left(Sheets(Sheets.Count).Range("G6").Value, 1)
        
        .Range(6) = initials
        .Range(7) = .Range(4).Value & " " & .Range(5).Value
        .Range(8) = Sheets(Sheets.Count).Range("H8").Value

''''''

'Step 7: Change the name of the Tab to be Date, Course Code & Trainer Initials
    
    'Remove / from course date
    
    Dim CourseDate As String
    Dim Result As String
    
    CourseDate = TextBoxCourseDate.Text
    Result = Replace(CourseDate, "/", "")
    
    'Name tab 

    Dim ReferenceCode As String
    
    ReferenceCode = Result & CourseNameValue & initials
    
    link = Left(ReferenceCode, 31)
    
    Sheets(Sheets.Count).Name = link
    
    .Range(9) = link

'Step 8: Make the course name a Hyperlink that links to the worksheet



End With
  
''''''

'Step 9: Then sort the "CourseList" Table by date order.

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    
    Set ws = Sheets("Course List")
    Set tbl = ws.ListObjects("CourseList")
    Set rng = Range("CourseList[Course Date]")
    
    With tbl.Sort
    
       .SortFields.Clear
       .SortFields.Add Key:=rng, SortOn:=xlSortOnValues, Order:=xlAscending
       .Header = xlYes
       .Apply

    End With


'Step 10: When all correct call the next Sub.

Unload Me

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Michael Greenberry
  • 1
  • 1

1 Answers1

0

You rarely need to activate anything to use it in code.
Obligatory post on that: How to avoid using Select in Excel VBA.

That said, you can create the sheet however you like and then reference it without the need to activate it:

Sub Test()

    Dim NewSht As Worksheet
    Set NewSht = ThisWorkbook.Worksheets.Add
    NewSht.Name = "01102000EXEL1MG"
    
    With ThisWorkbook.Worksheets("Sheet1")
        'Add the Hyperlink to A1 in Sheet1.
        'You'll need to update the location to your "Course List" table.
        'You may also want to update the SubAddress A1 location to somewhere more
        'relevant within the new sheet.
        .Hyperlinks.Add Anchor:=.Range("A1"), _
                        Address:="", _
                        SubAddress:="'" & NewSht.Name & "'!A1", _
                        TextToDisplay:=NewSht.Name
                        
        'Adding a new sheet makes the new sheet active.
        'This makes Sheet1 active again.  The code runs fine
        'without it, you'd just need to manually select Sheet1 when code is finished.
        .Activate
                        
    End With
    
End Sub

Further reading:
With...End With block

Dharman
  • 30,962
  • 25
  • 85
  • 135
Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • That is great and will try part of this code with my file. I won't have the worksheet name at the point you mention so will have to work on a workaround for that part. I will also look to add some code later. – Michael Greenberry Aug 03 '22 at 15:23