Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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
    Sheets(sheetName).Delete
    MsgBox """" & sheetName & """ was successfully removed!"
End Sub

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading