Please help with my code
 

I have the following code:

Elseif Activeworkbook.sheets.names = "Sheet 1" Then
Sheets("Sheet 1").Delete

Basically, I have Workbook A and Workbook B. In Workbook A, I created a
user form with various functionalities, and the code above is part of the
user form. When I open Workbook A in the same instance as Workbook B, the
user form will open on the Workbook B. When I click on any button on the
user form, the code will execute on Workbook B.

The purpose of the code above is to check whether "Sheet 1" exists in
Workbook B. If exist, delete "Sheet 1". I am having problem with the code
(Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then) to check whether "Sheet
1" exists.

Can someone help me with the code? Below is the structure of my entire code:

Private Sub cmdTest_Click()
If ....... Then
.......
Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then
Sheets("Sheet 1").Delete
Else
........
End If
End Sub

Thanks.

Please help with my code
 

I am entirely unclear what the 'if then else' structure is intended to do? If
I wanted to delete a sheet I would be inclined to do something like this

application.displayalerts = false
on error resume next
Activeworkbook.Sheets("Sheet1").Delete
on error goto 0
application.displayalerts = true
--
HTH...

Jim Thomlinson


"AccessHelp" wrote:

I have the following code:

Elseif Activeworkbook.sheets.names = "Sheet 1" Then
Sheets("Sheet 1").Delete

Basically, I have Workbook A and Workbook B. In Workbook A, I created a
user form with various functionalities, and the code above is part of the
user form. When I open Workbook A in the same instance as Workbook B, the
user form will open on the Workbook B. When I click on any button on the
user form, the code will execute on Workbook B.

The purpose of the code above is to check whether "Sheet 1" exists in
Workbook B. If exist, delete "Sheet 1". I am having problem with the code
(Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then) to check whether "Sheet
1" exists.

Can someone help me with the code? Below is the structure of my entire code:

Private Sub cmdTest_Click()
If ....... Then
.......
Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then
Sheets("Sheet 1").Delete
Else
........
End If
End Sub

Thanks.

Please help with my code
 

Hi Jim,

Thanks for your response. Basically, my code is checking two things before
the main code is executed. That is why, you are seeing 3 layers of "if then"
statements.

The first layer (If ..... Then) is checking whether the workbook is
protected. If protected, the code will stop and message the users to
unprotect the workbook.

The second layer (Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then) is
checking whether the sheet "Sheet 1" exists. If exist, delete it and proceed
to the next layer (the main code).

The next layer (Else ...... End If) is the main code to execute on Workbook
B.

Please help with my code
 

Why check for it's existence. Just try to delete it. If it gets deleted then
it must have existed. If not then no harm done... Your logic won't work
"exist, delete it and proceed to the next layer (the main code)." because it
will not proceed to the else section. Once the elseif is executed that entire
coding structure is done.


If -protected- then
-have the user unprotect
else
application.displayalerts = false
on error resume next
Activeworkbook.Sheets("Sheet1").Delete
on error goto 0
application.displayalerts = true
-do your main layer stuff
--
HTH...

Jim Thomlinson


"AccessHelp" wrote:

Hi Jim,

Thanks for your response. Basically, my code is checking two things before
the main code is executed. That is why, you are seeing 3 layers of "if then"
statements.

The first layer (If ..... Then) is checking whether the workbook is
protected. If protected, the code will stop and message the users to
unprotect the workbook.

The second layer (Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then) is
checking whether the sheet "Sheet 1" exists. If exist, delete it and proceed
to the next layer (the main code).

The next layer (Else ...... End If) is the main code to execute on Workbook
B.

How can I check whether the sheet "Sheet 1" exists in Workbook B? Do you
think I can check the existence and at the same, retain my current code
structure?

Thanks.

"Jim Thomlinson" wrote:

I am entirely unclear what the 'if then else' structure is intended to do? If
I wanted to delete a sheet I would be inclined to do something like this

application.displayalerts = false
on error resume next
Activeworkbook.Sheets("Sheet1").Delete
on error goto 0
application.displayalerts = true
--
HTH...

Jim Thomlinson


"AccessHelp" wrote:

I have the following code:

Elseif Activeworkbook.sheets.names = "Sheet 1" Then
Sheets("Sheet 1").Delete

Basically, I have Workbook A and Workbook B. In Workbook A, I created a
user form with various functionalities, and the code above is part of the
user form. When I open Workbook A in the same instance as Workbook B, the
user form will open on the Workbook B. When I click on any button on the
user form, the code will execute on Workbook B.

The purpose of the code above is to check whether "Sheet 1" exists in
Workbook B. If exist, delete "Sheet 1". I am having problem with the code
(Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then) to check whether "Sheet
1" exists.

Can someone help me with the code? Below is the structure of my entire code:

Private Sub cmdTest_Click()
If ....... Then
.......
Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then
Sheets("Sheet 1").Delete
Else
........
End If
End Sub

Thanks.

Please help with my code
 

Unless you or another user has purposely changed the sheet name, it will most
likely be Sheet1 as opposed to Sheet 1. To delete a sheet in a workbook
other than the active workbook, you need to declare the workbook object like:

Elseif Activeworkbook.sheets.names = "Sheet 1" Then
Workbooks(2).Sheets("Sheet 1").Delete
Of Course if Workbooks(2) is the active workbook then the declaration is
unecessary.

"AccessHelp" wrote:

Hi Jim,

Thanks for your response. Basically, my code is checking two things before
the main code is executed. That is why, you are seeing 3 layers of "if then"
statements.

The first layer (If ..... Then) is checking whether the workbook is
protected. If protected, the code will stop and message the users to
unprotect the workbook.

The second layer (Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then) is
checking whether the sheet "Sheet 1" exists. If exist, delete it and proceed
to the next layer (the main code).

The next layer (Else ...... End If) is the main code to execute on Workbook
B.

How can I check whether the sheet "Sheet 1" exists in Workbook B? Do you
think I can check the existence and at the same, retain my current code
structure?

Thanks.

"Jim Thomlinson" wrote:

I am entirely unclear what the 'if then else' structure is intended to do? If
I wanted to delete a sheet I would be inclined to do something like this

application.displayalerts = false
on error resume next
Activeworkbook.Sheets("Sheet1").Delete
on error goto 0
application.displayalerts = true
--
HTH...

Jim Thomlinson


"AccessHelp" wrote:

I have the following code:

Elseif Activeworkbook.sheets.names = "Sheet 1" Then
Sheets("Sheet 1").Delete

Basically, I have Workbook A and Workbook B. In Workbook A, I created a
user form with various functionalities, and the code above is part of the
user form. When I open Workbook A in the same instance as Workbook B, the
user form will open on the Workbook B. When I click on any button on the
user form, the code will execute on Workbook B.

The purpose of the code above is to check whether "Sheet 1" exists in
Workbook B. If exist, delete "Sheet 1". I am having problem with the code
(Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then) to check whether "Sheet
1" exists.

Can someone help me with the code? Below is the structure of my entire code:

Private Sub cmdTest_Click()
If ....... Then
.......
Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then
Sheets("Sheet 1").Delete
Else
........
End If
End Sub

Thanks.

Please help with my code
 

Hi Jim,

Yes, you are right. My logic won't work. Why didn't I catch that?

You code works perfectly the way I wanted.

By the way, how do I check to see the sheet "Sheet 1" exists? I am just
asking to gain some knowledge. When I tried 'Activeworkbook.sheets.names =
"Sheet 1"', I got an error on member and highlighted the code "Names".

Thank you again very much for your help.


"Jim Thomlinson" wrote:

Why check for it's existence. Just try to delete it. If it gets deleted then
it must have existed. If not then no harm done... Your logic won't work
"exist, delete it and proceed to the next layer (the main code)." because it
will not proceed to the else section. Once the elseif is executed that entire
coding structure is done.


If -protected- then
-have the user unprotect
else
application.displayalerts = false
on error resume next
Activeworkbook.Sheets("Sheet1").Delete
on error goto 0
application.displayalerts = true
-do your main layer stuff
--
HTH...

Jim Thomlinson


"AccessHelp" wrote:

Hi Jim,

Thanks for your response. Basically, my code is checking two things before
the main code is executed. That is why, you are seeing 3 layers of "if then"
statements.

The first layer (If ..... Then) is checking whether the workbook is
protected. If protected, the code will stop and message the users to
unprotect the workbook.

The second layer (Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then) is
checking whether the sheet "Sheet 1" exists. If exist, delete it and proceed
to the next layer (the main code).

The next layer (Else ...... End If) is the main code to execute on Workbook
B.

How can I check whether the sheet "Sheet 1" exists in Workbook B? Do you
think I can check the existence and at the same, retain my current code
structure?

Thanks.

"Jim Thomlinson" wrote:

I am entirely unclear what the 'if then else' structure is intended to do? If
I wanted to delete a sheet I would be inclined to do something like this

application.displayalerts = false
on error resume next
Activeworkbook.Sheets("Sheet1").Delete
on error goto 0
application.displayalerts = true
--
HTH...

Jim Thomlinson


"AccessHelp" wrote:

I have the following code:

Elseif Activeworkbook.sheets.names = "Sheet 1" Then
Sheets("Sheet 1").Delete

Basically, I have Workbook A and Workbook B. In Workbook A, I created a
user form with various functionalities, and the code above is part of the
user form. When I open Workbook A in the same instance as Workbook B, the
user form will open on the Workbook B. When I click on any button on the
user form, the code will execute on Workbook B.

The purpose of the code above is to check whether "Sheet 1" exists in
Workbook B. If exist, delete "Sheet 1". I am having problem with the code
(Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then) to check whether "Sheet
1" exists.

Can someone help me with the code? Below is the structure of my entire code:

Private Sub cmdTest_Click()
If ....... Then
.......
Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then
Sheets("Sheet 1").Delete
Else
........
End If
End Sub

Thanks.

Please help with my code
 

Hi JLGWhiz,

Thanks for your response. As you mentioned in your message, if you want to
delete a worksheet in a workbook other than the active workbook, you
reference "Workbook(2)". How is VB recognizes which workbook when there are
more than 2 workbooks open in the same Excel instance?

Thanks.

"JLGWhiz" wrote:

Unless you or another user has purposely changed the sheet name, it will most
likely be Sheet1 as opposed to Sheet 1. To delete a sheet in a workbook
other than the active workbook, you need to declare the workbook object like:

Elseif Activeworkbook.sheets.names = "Sheet 1" Then
Workbooks(2).Sheets("Sheet 1").Delete
Of Course if Workbooks(2) is the active workbook then the declaration is
unecessary.

"AccessHelp" wrote:

Hi Jim,

Thanks for your response. Basically, my code is checking two things before
the main code is executed. That is why, you are seeing 3 layers of "if then"
statements.

The first layer (If ..... Then) is checking whether the workbook is
protected. If protected, the code will stop and message the users to
unprotect the workbook.

The second layer (Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then) is
checking whether the sheet "Sheet 1" exists. If exist, delete it and proceed
to the next layer (the main code).

The next layer (Else ...... End If) is the main code to execute on Workbook
B.

How can I check whether the sheet "Sheet 1" exists in Workbook B? Do you
think I can check the existence and at the same, retain my current code
structure?

Thanks.

"Jim Thomlinson" wrote:

I am entirely unclear what the 'if then else' structure is intended to do? If
I wanted to delete a sheet I would be inclined to do something like this

application.displayalerts = false
on error resume next
Activeworkbook.Sheets("Sheet1").Delete
on error goto 0
application.displayalerts = true
--
HTH...

Jim Thomlinson


"AccessHelp" wrote:

I have the following code:

Elseif Activeworkbook.sheets.names = "Sheet 1" Then
Sheets("Sheet 1").Delete

Basically, I have Workbook A and Workbook B. In Workbook A, I created a
user form with various functionalities, and the code above is part of the
user form. When I open Workbook A in the same instance as Workbook B, the
user form will open on the Workbook B. When I click on any button on the
user form, the code will execute on Workbook B.

The purpose of the code above is to check whether "Sheet 1" exists in
Workbook B. If exist, delete "Sheet 1". I am having problem with the code
(Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then) to check whether "Sheet
1" exists.

Can someone help me with the code? Below is the structure of my entire code:

Private Sub cmdTest_Click()
If ....... Then
.......
Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then
Sheets("Sheet 1").Delete
Else
........
End If
End Sub

Thanks.

Please help with my code
 

Here is a function that returns true or false depending on whether the sheet
exists or not...

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function
--
HTH...

Jim Thomlinson


"AccessHelp" wrote:

Hi Jim,

Yes, you are right. My logic won't work. Why didn't I catch that?

You code works perfectly the way I wanted.

By the way, how do I check to see the sheet "Sheet 1" exists? I am just
asking to gain some knowledge. When I tried 'Activeworkbook.sheets.names =
"Sheet 1"', I got an error on member and highlighted the code "Names".

Thank you again very much for your help.


"Jim Thomlinson" wrote:

Why check for it's existence. Just try to delete it. If it gets deleted then
it must have existed. If not then no harm done... Your logic won't work
"exist, delete it and proceed to the next layer (the main code)." because it
will not proceed to the else section. Once the elseif is executed that entire
coding structure is done.


If -protected- then
-have the user unprotect
else
application.displayalerts = false
on error resume next
Activeworkbook.Sheets("Sheet1").Delete
on error goto 0
application.displayalerts = true
-do your main layer stuff
--
HTH...

Jim Thomlinson


"AccessHelp" wrote:

Hi Jim,

Thanks for your response. Basically, my code is checking two things before
the main code is executed. That is why, you are seeing 3 layers of "if then"
statements.

The first layer (If ..... Then) is checking whether the workbook is
protected. If protected, the code will stop and message the users to
unprotect the workbook.

The second layer (Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then) is
checking whether the sheet "Sheet 1" exists. If exist, delete it and proceed
to the next layer (the main code).

The next layer (Else ...... End If) is the main code to execute on Workbook
B.

How can I check whether the sheet "Sheet 1" exists in Workbook B? Do you
think I can check the existence and at the same, retain my current code
structure?

Thanks.

"Jim Thomlinson" wrote:

I am entirely unclear what the 'if then else' structure is intended to do? If
I wanted to delete a sheet I would be inclined to do something like this

application.displayalerts = false
on error resume next
Activeworkbook.Sheets("Sheet1").Delete
on error goto 0
application.displayalerts = true
--
HTH...

Jim Thomlinson


"AccessHelp" wrote:

I have the following code:

Elseif Activeworkbook.sheets.names = "Sheet 1" Then
Sheets("Sheet 1").Delete

Basically, I have Workbook A and Workbook B. In Workbook A, I created a
user form with various functionalities, and the code above is part of the
user form. When I open Workbook A in the same instance as Workbook B, the
user form will open on the Workbook B. When I click on any button on the
user form, the code will execute on Workbook B.

The purpose of the code above is to check whether "Sheet 1" exists in
Workbook B. If exist, delete "Sheet 1". I am having problem with the code
(Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then) to check whether "Sheet
1" exists.

Can someone help me with the code? Below is the structure of my entire code:

Private Sub cmdTest_Click()
If ....... Then
.......
Elseif Activeworkbook.Sheets.Names = "Sheet 1" Then
Sheets("Sheet 1").Delete
Else
........
End If
End Sub

Thanks.