Need help to add sheet, rename and update in TOC

Hello,

I want to create a database spreadsheet for collecting data.

Tried :
Code 1: I want a button to add new sheet and rename it by using inputbox. (Done by me)
Code 2: the table of contents should be updated with name of the newly added sheet.

Needed Help:
Code 3: Before adding sheet it should check if sheet name already exists. If not add new sheet, else Msgbox (" Sheets exists") (Help needed)
In Code 1: (Imp for me) when the input box is cancelled or left blank and clicked ok, the macros should end without adding sheet, else if the number is entered the sheet should be created.(Help needed)
Also, the patient no entered in the input box should be added in cell.value for cell A2
In Code 2: The table of contents should not contain Main & Main_1 sheet name and the Sr No should be updates with rows filled. Also the cell should be automated once filled.

Patients_Details.xlsm

If somebody could hep me to build such code, it will really help me a lot. And your help is much appreciated.

Attachment 1016

Waiting for replies.

Thanks,

Ketan Bhole

it's no reccomended to attach e-mail address, spam risk, please delete

Code:

---

Sub AddSheet()
Dim ID
ID = Application.InputBox("Please enter the Patient Number.")
NewNames = "Patient No - " & ID
If Not WorksheetExists(NewNames) Then
  Application.ScreenUpdating = False
  With Sheets("Main_1")
    .Visible = True
    .Copy After:=ActiveSheet
    .Visible = False
  End With
  ActiveSheet.Name = NewNames
  Call Create_TOC
  Application.ScreenUpdating = True
Else
  MsgBox " Sheets exists"
End If
End Sub
Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
    On Error Resume Next
    WorksheetExists = (Sheets(WorksheetName).Name <> "")
    On Error GoTo 0
End Function

---

Attachment 1050

Hello Again,

In the attached sheet, a New sheet (Copy of Main_1 Sheet) can be added by clicking on the button, and can be renamed. The new name gets added to the sheet and the same is updated in the table of contents in the Sheet ("Main"). Along with this the Sheet No and Total no of pages also get updated with the name in the next column.

Now my concern is, instead of Sheet no and total no of pages, what I want is the Next schedule date which will be from the new sheet which I will add by clicking button.

The calculation is next schedule date should be depend upon the actual date ie Sheet("Main_1") Row 5.

To explain it further, initially the Followup 1 Schedule date (B4) should appear in TOC Next Date column. Then when the actual date is entered in cell B5, the next date appear in TOC should be the date which is available in cell C4, again when actual date in cell C5 is enter the next date should be the date in cell D4 and so on....

This means, the Table of contents should keep on changing when the actual date is entered in row 5. and if the next date is blank it should be blank.

Also, will it be possible to delete the name of the sheet from the table of contents when the particular sheet is deleted.

Please let me know if you require any kind of information or let me know if this could not happen what other manipulation can we do.

Attachment 1050

Many many thanks in advance.

Hope this will work too.

Regards,
Ketan Bhole