mercredi 3 juillet 2019

How to incorporate a with statement with an if statement?

I've been working on a code to have a message box pop up that you can type in the name of a sheet and then it will create a new sheet at the end of all the other ones with that name.

With the help of googling I was able to make a code that pops up an input box where I write a name that I want for the new sheet and with a with-statement it creates it at the end of all my other sheets. What I am having problems with is the if-statement, which should say that if this name does not exist continue running the code and if the name does exists exit sub (don't continue processing the codes beneath). I got the "If Not xSht Is Nothing Then" to work by writing it as "If xSht Is Nothing Then", but that creates two if statements, so the first part gets completely nullified. I also tried writing it as an else statement, but then it jumps over the msgbox and still creates a new sheet because of the with statement below.

Dim ws As Worksheet
Dim xName As String
Dim xSht As Object

Set xSht = Sheets(xName)

xName = InputBox("Enter Sheet Name")
If xName = "" Then Exit Sub
If Not xSht Is Nothing Then
    MsgBox ("Name already exists")
Exit Sub
End If

With ThisWorkbook   'Adds new sheet with name it's been given
    Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
    ws.Name = xName
End With

<More code>

Aucun commentaire:

Enregistrer un commentaire