Adding and naming new excel sheets and checking for errors

[Jimbaygrant]

Hi,

I'm very new to VBA and am trying to write a simple command on a template I'm putting together. I will have one sheet (sheet 8) as my template (hidden to begin with) and want to replicate that sheet the amount of times the user specifies in 'NUMCLASS'-1 as sheet8 will also be renamed.

i) Firstly I want an inputbox to pop up for each new created sheet for the user to name the sheet and after this check if:

ii) they pressed cancel then exit the sub competely

iii) they never entered anything then a message box telling them "You didn't enter a class of business", they press ok and we go back to (i)

iv) they entered a sheet name which already exists, in which case a new input box pops up telling them to enter a new unique name and we repeat from (ii) again.

Here's what I've got so far:

Sub Generate_Sheets_Click()

Sheets("Sheet8").Visible = True

Sheet8.Select

'names the first templated sheet and checks for errors
reTry1:
ActiveSheet.Name = InputBox("Please enter the first class of business", "Sheet Naming", "Please enter a unique title for each class of business")

retry2:
If InputBox(ans) = 0 Then MsgBox "Cancel button pressed": Exit Sub
If ActiveSheet.Name = vbNullStr And InputBox(ActiveSheet.Name) > 0 Then MsgBox "You didn't enter a class of business": GoTo reTry1
If ActiveSheet.Name = Name.Range("sheet1 to sheet 7") Then InputBox("Your class of business was not unique, Please enter a unique name","Sheet Naming")then GoTO retry2


'names each new sheet and checks for errors NUMCLASS-1 number of times
Dim i As Integer
For i = 1 To Sheet4.Range("NUMCLASS").Value - 1

Sheet8.Copy After:=Worksheets(Worksheets.Count)

retry3:
ActiveSheet.Name = InputBox("Please enter the next class of business", "Sheet Naming", "Please enter a unique title for each class of business")

retry4:
If InputBox(ActiveSheet.Name) = 0 Then MsgBox "Cancel button pressed": Exit Sub
If ActiveSheet.Name = vbNullStr And InputBox(ans) > 0 Then MsgBox "You didn't enter a class of business": GoTo retry2
If ActiveSheet.Name Already Exists Then InputBox("Your class of business was not unique, Please enter a unique name","Sheet Naming") then GoTO retry4

Next

End Sub

It's the last 'If' in the lists of 3 that I have no idea how to write and I suspect the code could be tidied up.

Any help would be greatly appreciated.

Thanks in advance.

Jimbaygrant

 

[ousoonerjoe]

The VBA forum may be better suited for this type of request: forum707

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.