Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
Shold be very grateful for hints / solution on this small problems.
A part of the code:
Sheets.Add
ActiveSheet.Name = "Test"
Sheets("Test").Move After:=Sheets(3)
I have tried with following but ...
Sheets.Add
If Error Then GoTo bugs
bugs: ActiveSheet.Name = InputBox("Give name.")
GoTo back
ActiveSheet.Name = "Test"
back: ActiveSheet.Move After:=Sheets(3)
Best regards Joakim Björnberg
| Check out our Excel VBA Resources | ||||
 |
 |
 |
 |
 |
Hi Joakim
Try this;
Sub AddSheet()
Dim ActNm As String
With ActiveWorkbook.Sheets
.Add after:=Worksheets(Worksheets.Count)
End With
ActNm = ActiveSheet.Name
On Error Resume Next
ActiveSheet.Name = "Test"
NoName: If Err.Number = 1004 Then ActiveSheet.Name = InputBox("Give name.")
If ActiveSheet.Name = ActNm Then GoTo NoName
On Error GoTo 0
End Sub
What it does
- Adds sheet after the last sheet
which defaults to sheet(index number) eg Sheet4
This is stored in Variable ActNm so as to have a
comparison.
- If sheet exists (eg 1st time running Test doesn't exist, after that it does, so you get
an error 1004.) then error 1004 generated and
you are asked to input new name.
New name is comarped to ActNm (usually Sheetx)
if = then ask again if sheetname not valid ask
again.
Ivan