[XL-2007] Utiliser la macro pour créer un lien vers une fiche créée

[beowulf97]

Bonjour à tous,

voilà je vais vous expliquer mon problème actuel :

J'ai un fichier me permettant de créer des "fiches clients", j'entre le nom, je sélectionne ensuite un paramètre dans une liste déroulante et ensuite en utilisant le bouton "update", grâce à la macro, je créée la fiche client associée (du même nom).

Ce que je souhaiterai faire :
Je souhaiterai, en plus de créer la fiche client ( ce que j'ai réussi, à faire ), rendre le nom du client de ma première page cliquable (en faire un lien vers la page client créée au lieu de le laisser en tant que seul nom ).

Voici le code de la macro que j'utilise :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
 
 
Sub Macro()
'
' Macro Update File
'
    Dim n As Integer, nI As Integer, m As Integer, Rep As Integer, Lang As Integer
    Dim IType As String, WName As String, MName As String, IName As String, Prompt1(1 To 2) As String, _
        Title(1 To 2) As String, WPrompt(1 To 2) As String, WTitle(1 To 2) As String
    Dim IPre As Variant
    Dim Control As Boolean
'
    WPrompt(1) = "Ouvrez d'abord le fichier 'Master Commissioning sheets.xls' SVP."
    WPrompt(2) = "Please open first the 'Master Commissioning sheets.xls' file."
    Prompt1(1) = "Etes-vous sûre de vouloir effacer la fiche de MES supprimée ?"
    Prompt1(2) = "Are you sure you want to delete the removed commissioning sheet ?"
    WTitle(1) = "Ouverture Master"
    WTitle(2) = "Master Opening"
    Title(1) = "Suuprimer une fiche de mise en service"
    Title(2) = "Delete commissioning sheet"
    MName = "Master Commissioning sheets.xls"
    Control = True
    n = 0
    nI = 0
    NNspec = 0
'
    WName = ActiveWorkbook.Name
    If (Workbooks(WName).Worksheets(1).Cells(1, 16).Value = "FR") Then
        Lang = 1
    Else
        Lang = 2
    End If
'
    For n = 1 To Workbooks.Count
        If (Workbooks(n).Name = MName) Then
            Exit For
        End If
    Next
    If (n > Workbooks.Count) Then
        Rep = MsgBox(WPrompt(Lang), vbOKOnly, WTitle(Lang))
    Else
        Application.ScreenUpdating = False
        Workbooks(WName).Activate
        Worksheets("Instruments & Equipments List").Activate
        Do While Control
            nI = nI + 1
            If (Trim(Workbooks(WName).Worksheets(1).Cells(8 + nI, 1).Value) = "") Then
                Control = False
            End If
        Loop
        nI = nI - 1
'
        For n = 1 To nI
            IType = Workbooks(WName).Worksheets(1).Cells(8 + n, 14).Value
            If (Workbooks(WName).Worksheets(1).Cells(7 + n, 14).Value = "?") Then
                If (n = 1) Then
                    IPre = 1
                Else
                    For m = (7 + n) To 8 Step -1
                        If (Workbooks(WName).Worksheets(1).Cells(m, 14).Value <> "?") Then
                            IPre = Workbooks(WName).Worksheets(1).Cells(m, 1).Value
                            Exit For
                        End If
                    Next
                    If (m < 8) Then
                        IPre = 1
                    End If
                End If
            Else
                IPre = Workbooks(WName).Worksheets(1).Cells(7 + n, 1).Value
            End If
            IName = Workbooks(WName).Worksheets(1).Cells(8 + n, 1).Value
            For m = 2 To Workbooks(WName).Worksheets.Count
                If (IName = Workbooks(WName).Worksheets(m).Name) Then
                    Exit For
                End If
            Next
            If (m > Workbooks(WName).Worksheets.Count) Then
                For m = 1 To Workbooks(MName).Worksheets.Count
                    If (Workbooks(MName).Worksheets(m).Name = IType) Then
                        Exit For
                    End If
                Next
                If (m <= Workbooks(MName).Worksheets.Count) Then
                    Windows(MName).Activate
                    Sheets(IType).Select
                    If (n = 1) Then
                        Sheets(IType).Copy After:=Workbooks(WName).Sheets(1)
                    Else
                        Sheets(IType).Copy After:=Workbooks(WName).Sheets(IPre)
                    End If
                    Windows(WName).Activate
                    ActiveSheet.Name = IName
                    Cells(6, 3).Formula = "='" & Worksheets(1).Name & "'!$D$2"
                    Cells(7, 3).Formula = "='" & Worksheets(1).Name & "'!$D$3"
                    Cells(8, 3).Formula = "='" & Worksheets(1).Name & "'!$D$4"
                    Cells(12, 3).Formula = "='" & Worksheets(1).Name & "'!$B$" & (8 + n)
                    Cells(12, 8).Formula = "='" & Worksheets(1).Name & "'!$A$" & (8 + n)
                    Cells(12, 12).Formula = "='" & Worksheets(1).Name & "'!$L$" & (8 + n)
                    Cells(13, 3).Formula = "='" & Worksheets(1).Name & "'!$D$" & (8 + n)
                    Sheets(1).Activate
                Else
                    Workbooks(WName).Worksheets(1).Cells(8 + n, 14).Value = "?"
                End If
            Else
                For m = 1 To Workbooks(MName).Worksheets.Count
                    If (Workbooks(MName).Worksheets(m).Name = IType) Then
                        Exit For
                    End If
                Next
                If (m > Workbooks(MName).Worksheets.Count) Then
                    Workbooks(WName).Worksheets(1).Cells(8 + n, 14).Value = "?"
                End If
                If (n = 1) Then
                    Worksheets(IName).Move After:=Worksheets(1)
                Else
                    Worksheets(IName).Move After:=Worksheets(IPre)
                End If
            End If
        Next
'
        Control = False
        For m = 2 To Workbooks(WName).Worksheets.Count
            For n = 1 To nI
                If (Workbooks(WName).Worksheets(1).Cells(8 + n, 1).Value = Workbooks(WName).Worksheets(m).Name) Then
                    Exit For
                End If
            Next
            If (n > nI) Then
                If Not Control Then
                    Rep = MsgBox(Prompt1(Lang), vbYesNo + vbDefaultButton2, Title(Lang))
                    Control = True
                End If
                If (Rep = 6) Then
                    Workbooks(WName).Worksheets(m).Select
                    Application.DisplayAlerts = False
                    ActiveWindow.SelectedSheets.Delete
                    Application.DisplayAlerts = False
                Else
                    Exit For
                End If
            End If
        Next
'
        Application.ScreenUpdating = True
        Workbooks(WName).Worksheets(1).Activate
'
    End If
'
End Sub
'
Sub SelectCode(Row As Integer, Col As Integer)
'
'Macro Select Sheet Code
'
    Dim n As Integer, m As Integer, p As Integer, nCode As Integer, Rep As Integer, Spec As Integer
    Dim CodeTitle() As String, Prompt1 As String, Prompt2 As String, Prompt3 As String, MName As String, _
        Sort(1 To 2) As String
'
    Prompt1 = "Please open first 'Master Commissioning sheets.xls' file"
    Prompt2 = "Select the Instrument/Equipment code"
    Prompt3 = "Code unknown!" & Chr(13) & "Select the Instrument/Equipment code from the list below"
    MName = "Master Commissioning sheets.xls"
'
    For n = 1 To Workbooks.Count
        If (Workbooks(n).Name = MName) Then
            Exit For
        End If
    Next
    If (n > Workbooks.Count) Then
        Rep = MsgBox(Prompt1, vbOKOnly, "Open Master Workbook")
    Else
        nCode = Workbooks(MName).Worksheets.Count
        ReDim CodeTitle(nCode, 2)
        For n = 1 To nCode
            CodeTitle(n, 1) = Workbooks(MName).Worksheets(n).Name
            CodeTitle(n, 2) = Workbooks(MName).Worksheets(n).Cells(11, 1).Value
        Next
'
        For n = 2 To nCode
            For m = 1 To n
                If (CodeTitle(n, 1) < CodeTitle(m, 1)) Then
                    Sort(1) = CodeTitle(n, 1)
                    Sort(2) = CodeTitle(n, 2)
                    For p = n - 1 To m Step -1
                        CodeTitle(p + 1, 1) = CodeTitle(p, 1)
                        CodeTitle(p + 1, 2) = CodeTitle(p, 2)
                    Next
                    CodeTitle(m, 1) = Sort(1)
                    CodeTitle(m, 2) = Sort(2)
                    Exit For
                End If
            Next
        Next
'
'
        With UserForm1
            .Caption = "Code Selection"
            If ((Trim(ActiveSheet.Cells(Row, Col).Value) = "?") Or (Trim(ActiveSheet.Cells(Row, Col).Value) = "")) Then
                .Label1.Caption = Prompt2
            Else
                For n = 1 To nCode
                    If (Trim(ActiveSheet.Cells(Row, Col).Value) = CodeTitle(n, 1)) Then
                        Exit For
                    End If
                Next
                If (n <= nCode) Then
                    Spec = n - 1
                    .Label1.Caption = ""
                Else
                    Spec = 0
                    .Label1.Caption = Prompt3
                End If
            End If
            .ComboBox1.Clear
            For n = 1 To nCode
                .ComboBox1.AddItem CodeTitle(n, 1) & " : " & CodeTitle(n, 2)
            Next
            .ComboBox1.ListIndex = Spec
            .Show
            If (.Tag = "1") Then
                ActiveSheet.Cells(Row, Col).Value = CodeTitle(.ComboBox1.ListIndex + 1, 1)
            End If
        End With
    End If
'
End Sub

Je précise que le nom des clients est affiché dans la colonne "A" à partir de "A9" inclus.

Voilà j'espère que vous pourrez m'aider.

Merci d'avance

[beowulf97]

J'ai pensé utiliser une formule pour que les noms des clients soient automatiquement des liens, et qu'ensuite avec la macro actuelle, la fiche soit créée et donc que le lien fonctionne..

Ou créer une autre colonne nommée "lien" et donc au lieu de mettre à jour le nom du client pour en faire un lien, Créer directement le lien dans la colonne approppriée..

Mais je ne sais faire aucun des deux :s