How to create a hyperlink with a userform ? VBA

Question

I have create with many help a code who create with a user form a new sheet with the name of the client and many other information. And in the first page who resume all name client i have made an hyperlink (column C) who send to queue name of the client. But with the userform a error 424 appears.

 Private Sub btnajoutclient_Click()

Dim numFeuilClient As String
Dim prenomFeuilClient As String
Dim telFeuilClient As String
Dim mailFeuilClient As String
Dim AdresseFeuilClient As String
Dim cpFeuilClient As String
Dim villeFeuilClient As String
 'RENDRE LES FEUILLES VISIBLES'
Worksheets(2).Visible = True
Worksheets(3).Visible = True
 'CREER 2 BOITES POUR AVOIR LES INFOS : NOM ET TEL'
numFeuilClient = frmnouveauclient.TextBoxcasenom
prenomFeuilClient = frmnouveauclient.TextBoxprénom
telFeuilClient = frmnouveauclient.TextBoxcasenumérotel
mailFeuilClient = frmnouveauclient.TextBoxcasemail
AdresseFeuilClient = frmnouveauclient.TextBoxcaseadresse
cpFeuilClient = frmnouveauclient.TextBoxcasecodepostal
villeFeuilClient = frmnouveauclient.TextBoxcaseville   
 'freezer lécran
Application.ScreenUpdating = False
'SI PAS DE NOM SAISIE ALORS EXIT'
If numFeuilClient = "" Then
 Worksheets(2).Visible = False
 Worksheets(3).Visible = False
 Exit Sub
End If
'ON SUPPRIME LA ZONE SELECTIONNER LA FEUILLE TYPE'
Sheets("FeuilClient").Range("_zonesuprfinal").ClearContents
Sheets("FeuilClient").Copy after:=Sheets(Sheets.Count)
'RENOMMER LA FEUILLE
   ActiveSheet.Name = numFeuilClient
'ON MET LE NOM ET LE TEL DANS LES CASES SELECTIONEE DE LA FEUILLE CLIENT'
ActiveSheet.Range("_nomclient").Value = numFeuilClient
ActiveSheet.Range("_telclient").Value = telFeuilClient
ActiveSheet.Range("_prenomclient").Value = prenomFeuilClient
ActiveSheet.Range("_mailclient").Value = mailFeuilClient
ActiveSheet.Range("_adresse").Value = AdresseFeuilClient
ActiveSheet.Range("_codepostal").Value = cpFeuilClient
ActiveSheet.Range("_ville").Value = villeFeuilClient
'Aller sur la feuille fichier client
Sheets(1).Activate
'On trouve une case vide et y met le nom sur le fichier client
Feuil3.Range("A1048000").Select
ActiveCell.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = numFeuilClient
'On trouve une case vide et y met le nom sur le tel du client
Sheets("FichierClient").Range("B1048000").Select
ActiveCell.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = telFeuilClient
'Mettre un hyperlien sur le fichierclient
Sheets("FichierClient").Range("C1048000").Select
ActiveCell.End(xlUp).Select
ActiveCell.Offset(1, 0).Select




Hyperlinks.Add Anchor:=ActiveCell, Address:="", _
     SubAddress:="'" & numFeuilClient & "'!A1", TextToDisplay:="Voir Client"



 'ON REND INSIVIBLE LES FEUILLES'
  Worksheets(2).Visible = False
 Worksheets(3).Visible = False
'défreezer l'écran
  Application.ScreenUpdating = True
End Sub

Why should we look to all your code, if you have a single problem with it? This error means that you did not set a specific object. You show us a lot of (useless) code and not specify on which line the error appear... Shell we guess? — FaneDuru, Jun 15 '21 at 19:12
thanks for your answers. I show all the code because i don't know where is the problem. And i imagine it's more easy to andurstand the code and resolve it to see him. — Cédric Coutant, Jun 15 '21 at 19:34
At least, when this error appear? Did you try running the code line by line (using F8) and see on which line the code stops? — FaneDuru, Jun 15 '21 at 19:38
yes, this is the line at the end of thecode with the hyperlink. He can't put the hyperlink for the sheet: Sheets("FichierClient").Range("C1048000").Select ActiveCell.End(xlUp).Select ActiveCell.Offset(1, 0).Select Hyperlinks.Add Anchor:=ActiveCell, Address:="", _ SubAddress:="'" & numFeuilClient & "'!A1", TextToDisplay:="Voir Client" — Cédric Coutant, Jun 15 '21 at 19:42

score 0 · Answer 1 · answered Jun 15 '21 at 23:31

Hyperlinks needs to be qualified to a Worksheet

The simple fix is change it to

ActiveSheet.HyperLinks.Add ...

That said, there is a lot of opportunity to improve this code. Consider this

Private Sub btnajoutclient_Click()
    Dim numFeuilClient As String
    Dim prenomFeuilClient As String
    Dim telFeuilClient As String
    Dim mailFeuilClient As String
    Dim AdresseFeuilClient As String
    Dim cpFeuilClient As String
    Dim villeFeuilClient As String
    Dim wsFeuilClient As Worksheet

    With ThisWorkbook ' or ActiveWorkbook or specify a workbook
        'RENDRE LES FEUILLES VISIBLES'
        .Worksheets(2).Visible = True
        .Worksheets(3).Visible = True

         'CREER 2 BOITES POUR AVOIR LES INFOS : NOM ET TEL'
        With frmnouveauclient
            numFeuilClient = .TextBoxcasenom
            'SI PAS DE NOM SAISIE ALORS EXIT'
            If numFeuilClient = vbNullString Then
                GoTo CleanUp
            End If

            prenomFeuilClient = .TextBoxprénom
            telFeuilClient = .TextBoxcasenumérotel
            mailFeuilClient = .TextBoxcasemail
            AdresseFeuilClient = .TextBoxcaseadresse
            cpFeuilClient = .TextBoxcasecodepostal
            villeFeuilClient = .TextBoxcaseville
        End With

        'freezer lécran
        Application.ScreenUpdating = False

        'ON SUPPRIME LA ZONE SELECTIONNER LA FEUILLE TYPE'
        .Worksheets("FeuilClient").Range("_zonesuprfinal").ClearContents
        Set wsFeuilClient = .Worksheets("FeuilClient").Copy(after:=.Sheets(.Sheets.Count))

        'RENOMMER LA FEUILLE
        With wsFeuilClient
            .Name = numFeuilClient

        'ON MET LE NOM ET LE TEL DANS LES CASES SELECTIONEE DE LA FEUILLE CLIENT'
            .Range("_nomclient").Value = numFeuilClient
            .Range("_telclient").Value = telFeuilClient
            .Range("_prenomclient").Value = prenomFeuilClient
            .Range("_mailclient").Value = mailFeuilClient
            .Range("_adresse").Value = AdresseFeuilClient
            .Range("_codepostal").Value = cpFeuilClient
            .Range("_ville").Value = villeFeuilClient
        End With

        'Aller sur la feuille fichier client
        With .Sheets(1)
            'On trouve une case vide et y met le nom sur le fichier client
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = numFeuilClient
        End With
        With .Worksheets("FichierClient")
            'On trouve une case vide et y met le nom sur le tel du client
            .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Value = numFeuilClient
            'Mettre un hyperlien sur le fichierclient
            With .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0)
                .Value = numFeuilClient

                .Worksheet.Hyperlinks.Add Anchor:=.Cells, Address:=vbNullString, _
                    SubAddress:="'" & numFeuilClient & "'!A1", TextToDisplay:="Voir Client"
            End With
        End With

CleanUp:
        'ON REND INSIVIBLE LES FEUILLES'
        .Worksheets(2).Visible = False
        .Worksheets(3).Visible = False
    End With

    'défreezer l'écran
    Application.ScreenUpdating = True
End Sub

thank you for your answers, I will see that. – Cédric Coutant Jun 16 '21 at 16:47 — Cédric Coutant, Jun 16 '21 at 16:47

How to create a hyperlink with a userform ? VBA

1 Answers1