How to prevent excel from relocating or changing active sheet to newly created sheet after creating-CodePudding

I have created a button that would create a new sheet which works just fine. However, when I created a new sheet with the function, it relocates or redirect me to that new sheet which make. I also have a delete button in which it just accepts the sheet name and delete it instantly with no redirection or relocating. Is there a way to prevent the redirecting from happening? I am still a beginner so if I am doing something wrong, pls kindly correct me! Thanks in advance.

Here is the code.

Option Explicit

Public sheetName As Variant

Sub AddSheet()
    On Error Resume Next
    sheetName = InputBox("New Sheet Name", "Prototype 01")
    If sheetName = "" Then
        MsgBox "Sheet name cannot be empty!"
        Exit Sub
    End If
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = sheetName
    MsgBox "" & sheetName & " was successfully created!"
End Sub

Sub DeleteSheet()
    On Error Resume Next
    sheetName = InputBox("Sheet Name", "Prototype 01")
    If sheetName = "" Then Exit Sub
    Sheets(sheetName).Delete
    MsgBox """" & sheetName & """ was successfully removed!"
End Sub

CodePudding user response：

Yo can switch sheets via Worksheet.Activate function of vba.

Sheets("YourSheetName").Activate

Once you create the new sheet, add this code to return back to your original sheet.