How to prevent excel from relocating or changing active sheet to newly created sheet after creating a new sheet via function button

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
    MsgBox """" & sheetName & """ was successfully removed!"
End Sub

>Solution :

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


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

Leave a Reply