CODEWITHSUNDEEP
excelvbaif-statementwith-statement
Jesse
Jesse

Reputation: 7

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 inputbox 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 exist 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 the name it has been given
    Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
    ws.Name = xName
End With

<More code>

Upvotes: 0

Views: 48

Answers (2)

SJR
SJR

Reputation: 23081

Try this. You can turn off errors while you check if the sheet exists. Define the variable after the name has been entered otherwise you will get another error.

Sub x()

Dim ws As Worksheet
Dim xName As String
Dim xSht As worksheet

xName = InputBox("Enter Sheet Name")
If xName = "" Then Exit Sub

On Error Resume Next 'avoids error if sheet doesn't exist
Set xSht = Sheets(xName)
On Error GoTo 0      'turn off error avoidance

If Not xSht Is Nothing Then
    MsgBox "Name already exists"
    Exit Sub
End If

With ThisWorkbook
    Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
    ws.Name = xName
End With

End Sub

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Reverse the order of:

Set xSht = Sheets(xName)

and:

xName = InputBox("Enter Sheet Name")

(there may be other problems)

Upvotes: 1

Related Questions