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 = Name.Range("sheet1 to sheet 7") 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

 

[plingy]

Hi Jimbaygrant,

Why not use a userform.
Open the VB Editor and choose the menu [Insert][Userform].
You can put the textbox and "OK" and "Cancel" buttons on it and do your checks before the form closes.
It might be more difficult if you're not used to event programming but it's the way to do it.

Good Luck