Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming |