Ajout d'un onglet

[winslow]

Bonjour le forum,
J'arrive très bien à rajouter un onglet et à le nommer. Mais je cherche le code qui me préviendrait si le nom que je choisi pour nommer ma nouvelle sheet est déjà le nom d'une sheet existente.
Dans mon exemple, j'utilise un textbox dans lequel l'utilisateur tape le nom de la sheet.


Private Sub CommandButton1_Click()
For n = 1 To Worksheets.Count

If Worksheets(n).Name = TextBox1.Value Then GoTo fini
Sheets.Add.Name = TextBox1.Value
end if
Next n
fini:
Dim Msg, Style, Title, Response
Msg = "You can not rename a sheet to the same name as another sheet"
Style = vbOKOnly + vbInformation + vbDefaultButton2
Title = "Check Sheet Name"
Response = MsgBox(Msg, Style, Title)
SheetName.Hide
End Sub

merci d'avance
David

 

[PascalXLD]

Re : Ajout d'un onglet

Bonjour

essaie sainsi

Private Sub CommandButton1_Click()
Dim Msg, Style, Title, Response

For n = 1 To Worksheets.Count

If Worksheets(n).Name = TextBox1.Value Then
Msg = "You can not rename a sheet to the same name as another sheet"
Style = vbOKOnly + vbInformation + vbDefaultButton2
Title = "Check Sheet Name"
Response = MsgBox(Msg, Style, Title)
exit sub
end if
Next n

Sheets.Add.Name = TextBox1.Value

End Sub

 

[winslow]

Re : Ajout d'un onglet

Ok J'ai trouvé l'erreur.

Private Sub CommandButton1_Click()
For n = 1 To Worksheets.Count
Sheets("DashBoard").Cells(1, 11).Value = " " & TextBox1.Value
If Worksheets(n).Name = Right(Sheets("DashBoard").Cells(1, 11).Value, 8) Then
GoTo fini
End If
Next n
Sheets.Add.Name = TextBox1.Value
Exit Sub
fini:
Dim Msg, Style, Title, Response
Msg = "You can not rename a sheet to the same name as another sheet"
Style = vbOKOnly + vbInformation + vbDefaultButton2
Title = "Check Sheet Name"
Response = MsgBox(Msg, Style, Title)
SheetName.Hide

End Sub

 

[winslow]

Re : Ajout d'un onglet

Merci Pascal,
Ta réponse me semble plus simple..

 

[pierrejean]

Re : Ajout d'un onglet

bonjour winslow

A tester

Private Sub CommandButton1_Click()
For n = 1 To Worksheets.Count
If Worksheets(n).Name = TextBox1.Value Then exist=true
Next n
if exist then
Dim Msg, Style, Title, Response
Msg = "You can not rename a sheet to the same name as another sheet"
Style = vbOKOnly + vbInformation + vbDefaultButton2
Title = "Check Sheet Name"
Response = MsgBox(Msg, Style, Title)
SheetName.Hide
else
Sheets.add.name=Textbox1.Value
end if
End Sub

suis passablement apres la bataille !!!

Salut a tous