Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
I am trying to create a macro that will delete a cell content and
related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
Dim cell as range
cell = Application.Inputbox("Select cell using mouse2, Type:=8) On Error Resume Next Application.Displayalerts = False Worksheets(cell.Resize(1,10.Value).delete Application.Displayalerts = True On Error Goto 0 cell.ClearContents -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ardy" wrote in message ups.com... I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
One way:
Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
Bob:
Got an error on cell = Application.Inputbox("Select cell using mouse2, Type:=8) and Worksheets(cell.Resize(1,10.Value).delete I wonder why, I got to play with this and see how I can make it work Ardy Bob Phillips wrote: Dim cell as range cell = Application.Inputbox("Select cell using mouse2, Type:=8) On Error Resume Next Application.Displayalerts = False Worksheets(cell.Resize(1,10.Value).delete Application.Displayalerts = True On Error Goto 0 cell.ClearContents -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ardy" wrote in message ups.com... I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
Try:
Set cell = Application.Inputbox("Select cell using mouse", Type:=8) In article .com, "Ardy" wrote: Bob: Got an error on cell = Application.Inputbox("Select cell using mouse2, Type:=8) and Worksheets(cell.Resize(1,10.Value).delete I wonder why, I got to play with this and see how I can make it work Ardy Bob Phillips wrote: Dim cell as range cell = Application.Inputbox("Select cell using mouse2, Type:=8) On Error Resume Next Application.Displayalerts = False Worksheets(cell.Resize(1,10.Value).delete Application.Displayalerts = True On Error Goto 0 cell.ClearContents -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ardy" wrote in message ups.com... I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
This is great this works pritty good, I got to modify the message and
all and all couple of other things to it to make it fit the situation, One thing is that how would you expand on this code and capture the content and use it to delete the respected tab which has the same name as the cell content that we are just abt to delete. Ardy JE McGimpsey wrote: One way: Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
How do I rate, I can't fine any link as to rate the individual.
Ardy John Bundy (remove) wrote: Create a button and attach this, when you select a cell with a name it will delete the associated cell and clear the cell Private Sub CommandButton1_Click() Sheets(ActiveCell.Value).Delete ActiveCell = "" End Sub -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
JE.
I also get a Run Time Error"424', Object Required when pressing cancel. Ardy Ardy wrote: This is great this works pritty good, I got to modify the message and all and all couple of other things to it to make it fit the situation, One thing is that how would you expand on this code and capture the content and use it to delete the respected tab which has the same name as the cell content that we are just abt to delete. Ardy JE McGimpsey wrote: One way: Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
How about this minor modification to JE's code:
Dim rResult As Range Do on error resume next Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) on error goto 0 If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents Ardy wrote: JE. I also get a Run Time Error"424', Object Required when pressing cancel. Ardy Ardy wrote: This is great this works pritty good, I got to modify the message and all and all couple of other things to it to make it fit the situation, One thing is that how would you expand on this code and capture the content and use it to delete the respected tab which has the same name as the cell content that we are just abt to delete. Ardy JE McGimpsey wrote: One way: Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
Great it works and no error on cancel.
I have two questions and a request. Q1. Dose the variable rResult holds the cell number or content? i.e A3(#A#3) or the content "Some Text" Q2. Explain the (1) after rResult. What function dose it serves what is it doing Request: Assuming that the cell we are clearing the content (rResult(1).ClearContents) has more information in front of it C3:BH3 how would one clear those. The confusing part for me is how to capture the initial cell id and make the related range to clear. Example: The user picks Cell A2 that has the content "Some Text" the current code dose perfect it clears it and deletes the tab that has the "Some Text". Now visualize there are more related information in cell C3:BH3 that also needs to be cleared. Ardy Dave Peterson wrote: How about this minor modification to JE's code: Dim rResult As Range Do on error resume next Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) on error goto 0 If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents Ardy wrote: JE. I also get a Run Time Error"424', Object Required when pressing cancel. Ardy Ardy wrote: This is great this works pritty good, I got to modify the message and all and all couple of other things to it to make it fit the situation, One thing is that how would you expand on this code and capture the content and use it to delete the respected tab which has the same name as the cell content that we are just abt to delete. Ardy JE McGimpsey wrote: One way: Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
1) Neither. rResult is an object variable that holds the range/cell
object. 2) It is possible to return a multi-cell range. rRange(1) refers to the first cell in rRange - or the only cell if rRange consists of only one cell. Request: Add this line after rResult(1).ClearContents rResult.Offset(1, 2).Resize(1, 58).ClearContents In article .com, "Ardy" wrote: Q1. Dose the variable rResult holds the cell number or content? i.e A3(#A#3) or the content "Some Text" Q2. Explain the (1) after rResult. What function dose it serves what is it doing Request: Assuming that the cell we are clearing the content (rResult(1).ClearContents) has more information in front of it C3:BH3 how would one clear those. The confusing part for me is how to capture the initial cell id and make the related range to clear. Example: The user picks Cell A2 that has the content "Some Text" the current code dose perfect it clears it and deletes the tab that has the "Some Text". Now visualize there are more related information in cell C3:BH3 that also needs to be cleared. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
rResult is a variable that holds a reference to that range.
rResult is an object that has lots of properties. It's more complex than say a variable declared as a Long (just a whole number). You can get to the value. If rResult is a single cell: Msgbox rResult.value or its address: msgbox rResult.address or lots more things Put your cursor on "Range" in this line" Dim rResult as Range and hit F1 and you'll be taken to VBA's help for Range objects. rResult(1) will refer to the first cell in that range. The user can select lots of cells. JE wants to just use the first in his code. And one of the properties that a range has is .Offset() and another is ..Resize(). rResult(1).offset(0,1) will stay on the same row (0) and go one column to the right. rResult(1).offset(0,1).resize(1,59) will "move" one cell to the right and resize that range to 1 row by 59 columns So rResult(1).offset(0,1).resize(1,59).clearcontents or avoiding the offset() rResult(1).resize(1,60).clearcontents (I think that A:BH is 60 columns--check my work before you trust it!) Ardy wrote: Great it works and no error on cancel. I have two questions and a request. Q1. Dose the variable rResult holds the cell number or content? i.e A3(#A#3) or the content "Some Text" Q2. Explain the (1) after rResult. What function dose it serves what is it doing Request: Assuming that the cell we are clearing the content (rResult(1).ClearContents) has more information in front of it C3:BH3 how would one clear those. The confusing part for me is how to capture the initial cell id and make the related range to clear. Example: The user picks Cell A2 that has the content "Some Text" the current code dose perfect it clears it and deletes the tab that has the "Some Text". Now visualize there are more related information in cell C3:BH3 that also needs to be cleared. Ardy Dave Peterson wrote: How about this minor modification to JE's code: Dim rResult As Range Do on error resume next Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) on error goto 0 If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents Ardy wrote: JE. I also get a Run Time Error"424', Object Required when pressing cancel. Ardy Ardy wrote: This is great this works pritty good, I got to modify the message and all and all couple of other things to it to make it fit the situation, One thing is that how would you expand on this code and capture the content and use it to delete the respected tab which has the same name as the cell content that we are just abt to delete. Ardy JE McGimpsey wrote: One way: Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
Thank You So much it is putting it to perspective a bit........;)
Ardy Dave Peterson wrote: rResult is a variable that holds a reference to that range. rResult is an object that has lots of properties. It's more complex than say a variable declared as a Long (just a whole number). You can get to the value. If rResult is a single cell: Msgbox rResult.value or its address: msgbox rResult.address or lots more things Put your cursor on "Range" in this line" Dim rResult as Range and hit F1 and you'll be taken to VBA's help for Range objects. rResult(1) will refer to the first cell in that range. The user can select lots of cells. JE wants to just use the first in his code. And one of the properties that a range has is .Offset() and another is .Resize(). rResult(1).offset(0,1) will stay on the same row (0) and go one column to the right. rResult(1).offset(0,1).resize(1,59) will "move" one cell to the right and resize that range to 1 row by 59 columns So rResult(1).offset(0,1).resize(1,59).clearcontents or avoiding the offset() rResult(1).resize(1,60).clearcontents (I think that A:BH is 60 columns--check my work before you trust it!) Ardy wrote: Great it works and no error on cancel. I have two questions and a request. Q1. Dose the variable rResult holds the cell number or content? i.e A3(#A#3) or the content "Some Text" Q2. Explain the (1) after rResult. What function dose it serves what is it doing Request: Assuming that the cell we are clearing the content (rResult(1).ClearContents) has more information in front of it C3:BH3 how would one clear those. The confusing part for me is how to capture the initial cell id and make the related range to clear. Example: The user picks Cell A2 that has the content "Some Text" the current code dose perfect it clears it and deletes the tab that has the "Some Text". Now visualize there are more related information in cell C3:BH3 that also needs to be cleared. Ardy Dave Peterson wrote: How about this minor modification to JE's code: Dim rResult As Range Do on error resume next Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) on error goto 0 If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents Ardy wrote: JE. I also get a Run Time Error"424', Object Required when pressing cancel. Ardy Ardy wrote: This is great this works pritty good, I got to modify the message and all and all couple of other things to it to make it fit the situation, One thing is that how would you expand on this code and capture the content and use it to delete the respected tab which has the same name as the cell content that we are just abt to delete. Ardy JE McGimpsey wrote: One way: Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
Watch out for what I wrote. I didn't notice that the data was on two different
rows. You'll want to change that .offset() to come down and move to the right. Ardy wrote: Thank You So much it is putting it to perspective a bit........;) Ardy Dave Peterson wrote: rResult is a variable that holds a reference to that range. rResult is an object that has lots of properties. It's more complex than say a variable declared as a Long (just a whole number). You can get to the value. If rResult is a single cell: Msgbox rResult.value or its address: msgbox rResult.address or lots more things Put your cursor on "Range" in this line" Dim rResult as Range and hit F1 and you'll be taken to VBA's help for Range objects. rResult(1) will refer to the first cell in that range. The user can select lots of cells. JE wants to just use the first in his code. And one of the properties that a range has is .Offset() and another is .Resize(). rResult(1).offset(0,1) will stay on the same row (0) and go one column to the right. rResult(1).offset(0,1).resize(1,59) will "move" one cell to the right and resize that range to 1 row by 59 columns So rResult(1).offset(0,1).resize(1,59).clearcontents or avoiding the offset() rResult(1).resize(1,60).clearcontents (I think that A:BH is 60 columns--check my work before you trust it!) Ardy wrote: Great it works and no error on cancel. I have two questions and a request. Q1. Dose the variable rResult holds the cell number or content? i.e A3(#A#3) or the content "Some Text" Q2. Explain the (1) after rResult. What function dose it serves what is it doing Request: Assuming that the cell we are clearing the content (rResult(1).ClearContents) has more information in front of it C3:BH3 how would one clear those. The confusing part for me is how to capture the initial cell id and make the related range to clear. Example: The user picks Cell A2 that has the content "Some Text" the current code dose perfect it clears it and deletes the tab that has the "Some Text". Now visualize there are more related information in cell C3:BH3 that also needs to be cleared. Ardy Dave Peterson wrote: How about this minor modification to JE's code: Dim rResult As Range Do on error resume next Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) on error goto 0 If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents Ardy wrote: JE. I also get a Run Time Error"424', Object Required when pressing cancel. Ardy Ardy wrote: This is great this works pritty good, I got to modify the message and all and all couple of other things to it to make it fit the situation, One thing is that how would you expand on this code and capture the content and use it to delete the respected tab which has the same name as the cell content that we are just abt to delete. Ardy JE McGimpsey wrote: One way: Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
After you wrote your explanation I made the modification, to accomedate
for that. You know dave I am not a programmer by trait. I have been able to write Dave Peterson wrote: Watch out for what I wrote. I didn't notice that the data was on two different rows. You'll want to change that .offset() to come down and move to the right. Ardy wrote: Thank You So much it is putting it to perspective a bit........;) Ardy Dave Peterson wrote: rResult is a variable that holds a reference to that range. rResult is an object that has lots of properties. It's more complex than say a variable declared as a Long (just a whole number). You can get to the value. If rResult is a single cell: Msgbox rResult.value or its address: msgbox rResult.address or lots more things Put your cursor on "Range" in this line" Dim rResult as Range and hit F1 and you'll be taken to VBA's help for Range objects. rResult(1) will refer to the first cell in that range. The user can select lots of cells. JE wants to just use the first in his code. And one of the properties that a range has is .Offset() and another is .Resize(). rResult(1).offset(0,1) will stay on the same row (0) and go one column to the right. rResult(1).offset(0,1).resize(1,59) will "move" one cell to the right and resize that range to 1 row by 59 columns So rResult(1).offset(0,1).resize(1,59).clearcontents or avoiding the offset() rResult(1).resize(1,60).clearcontents (I think that A:BH is 60 columns--check my work before you trust it!) Ardy wrote: Great it works and no error on cancel. I have two questions and a request. Q1. Dose the variable rResult holds the cell number or content? i.e A3(#A#3) or the content "Some Text" Q2. Explain the (1) after rResult. What function dose it serves what is it doing Request: Assuming that the cell we are clearing the content (rResult(1).ClearContents) has more information in front of it C3:BH3 how would one clear those. The confusing part for me is how to capture the initial cell id and make the related range to clear. Example: The user picks Cell A2 that has the content "Some Text" the current code dose perfect it clears it and deletes the tab that has the "Some Text". Now visualize there are more related information in cell C3:BH3 that also needs to be cleared. Ardy Dave Peterson wrote: How about this minor modification to JE's code: Dim rResult As Range Do on error resume next Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) on error goto 0 If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents Ardy wrote: JE. I also get a Run Time Error"424', Object Required when pressing cancel. Ardy Ardy wrote: This is great this works pritty good, I got to modify the message and all and all couple of other things to it to make it fit the situation, One thing is that how would you expand on this code and capture the content and use it to delete the respected tab which has the same name as the cell content that we are just abt to delete. Ardy JE McGimpsey wrote: One way: Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
Glad you fixed it!
Ardy wrote: After you wrote your explanation I made the modification, to accomedate for that. You know dave I am not a programmer by trait. I have been able to write Dave Peterson wrote: Watch out for what I wrote. I didn't notice that the data was on two different rows. You'll want to change that .offset() to come down and move to the right. Ardy wrote: Thank You So much it is putting it to perspective a bit........;) Ardy Dave Peterson wrote: rResult is a variable that holds a reference to that range. rResult is an object that has lots of properties. It's more complex than say a variable declared as a Long (just a whole number). You can get to the value. If rResult is a single cell: Msgbox rResult.value or its address: msgbox rResult.address or lots more things Put your cursor on "Range" in this line" Dim rResult as Range and hit F1 and you'll be taken to VBA's help for Range objects. rResult(1) will refer to the first cell in that range. The user can select lots of cells. JE wants to just use the first in his code. And one of the properties that a range has is .Offset() and another is .Resize(). rResult(1).offset(0,1) will stay on the same row (0) and go one column to the right. rResult(1).offset(0,1).resize(1,59) will "move" one cell to the right and resize that range to 1 row by 59 columns So rResult(1).offset(0,1).resize(1,59).clearcontents or avoiding the offset() rResult(1).resize(1,60).clearcontents (I think that A:BH is 60 columns--check my work before you trust it!) Ardy wrote: Great it works and no error on cancel. I have two questions and a request. Q1. Dose the variable rResult holds the cell number or content? i.e A3(#A#3) or the content "Some Text" Q2. Explain the (1) after rResult. What function dose it serves what is it doing Request: Assuming that the cell we are clearing the content (rResult(1).ClearContents) has more information in front of it C3:BH3 how would one clear those. The confusing part for me is how to capture the initial cell id and make the related range to clear. Example: The user picks Cell A2 that has the content "Some Text" the current code dose perfect it clears it and deletes the tab that has the "Some Text". Now visualize there are more related information in cell C3:BH3 that also needs to be cleared. Ardy Dave Peterson wrote: How about this minor modification to JE's code: Dim rResult As Range Do on error resume next Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) on error goto 0 If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents Ardy wrote: JE. I also get a Run Time Error"424', Object Required when pressing cancel. Ardy Ardy wrote: This is great this works pritty good, I got to modify the message and all and all couple of other things to it to make it fit the situation, One thing is that how would you expand on this code and capture the content and use it to delete the respected tab which has the same name as the cell content that we are just abt to delete. Ardy JE McGimpsey wrote: One way: Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
Dave:
I don't want to over extend my welcome, but I also am really looking for reversal of this code too. What I mean is that if the user wants to add a student. Now this is a bit tricky. Because lets say in column A starting at A2 the names start, Assume we have continuous names in range A2:A20. Now with the help of previous code the user is able to delete lets say one of the names (A10). Now if we want to add a name it would be nice to start from bottom where ever that is in this case A20 come up and find our null(A10) and insert the name there. Once we insert the name, the macro will create a tab for the inserted name by copying a hidden tab called "Template" and make a link from the name to the newly created tab. I probably can peace mill the copying and linking, but I am stump on the finding the null, and what if there is no gap I assume there is an else in there to make it go to the bottom of the list. Ardy Dave Peterson wrote: Glad you fixed it! Ardy wrote: After you wrote your explanation I made the modification, to accomedate for that. You know dave I am not a programmer by trait. I have been able to write Dave Peterson wrote: Watch out for what I wrote. I didn't notice that the data was on two different rows. You'll want to change that .offset() to come down and move to the right. Ardy wrote: Thank You So much it is putting it to perspective a bit........;) Ardy Dave Peterson wrote: rResult is a variable that holds a reference to that range. rResult is an object that has lots of properties. It's more complex than say a variable declared as a Long (just a whole number). You can get to the value. If rResult is a single cell: Msgbox rResult.value or its address: msgbox rResult.address or lots more things Put your cursor on "Range" in this line" Dim rResult as Range and hit F1 and you'll be taken to VBA's help for Range objects. rResult(1) will refer to the first cell in that range. The user can select lots of cells. JE wants to just use the first in his code. And one of the properties that a range has is .Offset() and another is .Resize(). rResult(1).offset(0,1) will stay on the same row (0) and go one column to the right. rResult(1).offset(0,1).resize(1,59) will "move" one cell to the right and resize that range to 1 row by 59 columns So rResult(1).offset(0,1).resize(1,59).clearcontents or avoiding the offset() rResult(1).resize(1,60).clearcontents (I think that A:BH is 60 columns--check my work before you trust it!) Ardy wrote: Great it works and no error on cancel. I have two questions and a request. Q1. Dose the variable rResult holds the cell number or content? i.e A3(#A#3) or the content "Some Text" Q2. Explain the (1) after rResult. What function dose it serves what is it doing Request: Assuming that the cell we are clearing the content (rResult(1).ClearContents) has more information in front of it C3:BH3 how would one clear those. The confusing part for me is how to capture the initial cell id and make the related range to clear. Example: The user picks Cell A2 that has the content "Some Text" the current code dose perfect it clears it and deletes the tab that has the "Some Text". Now visualize there are more related information in cell C3:BH3 that also needs to be cleared. Ardy Dave Peterson wrote: How about this minor modification to JE's code: Dim rResult As Range Do on error resume next Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) on error goto 0 If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents Ardy wrote: JE. I also get a Run Time Error"424', Object Required when pressing cancel. Ardy Ardy wrote: This is great this works pritty good, I got to modify the message and all and all couple of other things to it to make it fit the situation, One thing is that how would you expand on this code and capture the content and use it to delete the respected tab which has the same name as the cell content that we are just abt to delete. Ardy JE McGimpsey wrote: One way: Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
First, one of the things JE's suggested code was to clearcontents of the cell
whose worksheet was deleted. I'd change this line: rResult(1).ClearContents to rResult(1).entirerow.delete to delete the whole row. Then there would be no gaps in the data. And any new name could be added to the bottom of the list. And I like to use the =hyperlink() worksheet function in an adjacent cell (another reason to delete the entire row!). The worksheet function =hyperlink() will react nicely when/if you change the name of that worksheet. A lot of things could go wrong with this type of thing. The user could enter the name of a worksheet that already exists--or could enter an invalid name (wrong characters like slashes, colons... or too many characters). I made some assumptions. You'll have to change them if I guessed wrong. Option Explicit Sub AddName() Dim myNewName As String Dim ActWks As Worksheet Dim NewWks As Worksheet Dim DestCell As Range myNewName = Trim(InputBox(Prompt:="Enter a new name")) If myNewName = "" Then Exit Sub End If Set ActWks = ActiveSheet With ActWks Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With Application.ScreenUpdating = False With Worksheets("template") .Visible = xlSheetVisible .Copy after:=Worksheets(Worksheets.Count) Set NewWks = ActiveSheet .Visible = xlSheetHidden End With On Error Resume Next NewWks.Name = myNewName If Err.Number < 0 Then MsgBox "Error renaming the new sheet from: " _ & NewWks.Name & " to: " & myNewName & vbLf _ & "Could be an invalid name or a sheet" _ & " by that name already exists!" _ & vbLf & vbLf & "Entered Name NOT Used--Worksheet Name Added!!!" Err.Clear End If 'put the name entered so the hyperlink works????? DestCell.Value = NewWks.Name DestCell.Offset(0, 1).Formula _ = "=HYPERLINK(""#""&CELL(""address"",INDIRECT(""'""& " _ & DestCell.Address(0, 0) & "&""'!A1"")),""Click Me"")" ActWks.Activate Application.ScreenUpdating = True End Sub Ardy wrote: Dave: I don't want to over extend my welcome, but I also am really looking for reversal of this code too. What I mean is that if the user wants to add a student. Now this is a bit tricky. Because lets say in column A starting at A2 the names start, Assume we have continuous names in range A2:A20. Now with the help of previous code the user is able to delete lets say one of the names (A10). Now if we want to add a name it would be nice to start from bottom where ever that is in this case A20 come up and find our null(A10) and insert the name there. Once we insert the name, the macro will create a tab for the inserted name by copying a hidden tab called "Template" and make a link from the name to the newly created tab. I probably can peace mill the copying and linking, but I am stump on the finding the null, and what if there is no gap I assume there is an else in there to make it go to the bottom of the list. Ardy Dave Peterson wrote: Glad you fixed it! Ardy wrote: After you wrote your explanation I made the modification, to accomedate for that. You know dave I am not a programmer by trait. I have been able to write Dave Peterson wrote: Watch out for what I wrote. I didn't notice that the data was on two different rows. You'll want to change that .offset() to come down and move to the right. Ardy wrote: Thank You So much it is putting it to perspective a bit........;) Ardy Dave Peterson wrote: rResult is a variable that holds a reference to that range. rResult is an object that has lots of properties. It's more complex than say a variable declared as a Long (just a whole number). You can get to the value. If rResult is a single cell: Msgbox rResult.value or its address: msgbox rResult.address or lots more things Put your cursor on "Range" in this line" Dim rResult as Range and hit F1 and you'll be taken to VBA's help for Range objects. rResult(1) will refer to the first cell in that range. The user can select lots of cells. JE wants to just use the first in his code. And one of the properties that a range has is .Offset() and another is .Resize(). rResult(1).offset(0,1) will stay on the same row (0) and go one column to the right. rResult(1).offset(0,1).resize(1,59) will "move" one cell to the right and resize that range to 1 row by 59 columns So rResult(1).offset(0,1).resize(1,59).clearcontents or avoiding the offset() rResult(1).resize(1,60).clearcontents (I think that A:BH is 60 columns--check my work before you trust it!) Ardy wrote: Great it works and no error on cancel. I have two questions and a request. Q1. Dose the variable rResult holds the cell number or content? i.e A3(#A#3) or the content "Some Text" Q2. Explain the (1) after rResult. What function dose it serves what is it doing Request: Assuming that the cell we are clearing the content (rResult(1).ClearContents) has more information in front of it C3:BH3 how would one clear those. The confusing part for me is how to capture the initial cell id and make the related range to clear. Example: The user picks Cell A2 that has the content "Some Text" the current code dose perfect it clears it and deletes the tab that has the "Some Text". Now visualize there are more related information in cell C3:BH3 that also needs to be cleared. Ardy Dave Peterson wrote: How about this minor modification to JE's code: Dim rResult As Range Do on error resume next Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) on error goto 0 If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents Ardy wrote: JE. I also get a Run Time Error"424', Object Required when pressing cancel. Ardy Ardy wrote: This is great this works pritty good, I got to modify the message and all and all couple of other things to it to make it fit the situation, One thing is that how would you expand on this code and capture the content and use it to delete the respected tab which has the same name as the cell content that we are just abt to delete. Ardy JE McGimpsey wrote: One way: Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
I really appreciate your help on this, Like I said I am not a
programmer, So I am using this project to learn a bit and at the same time help my son's school in gathering data, please allow some time for me to munch on what you have said and wrote so I can digest, There are dependencies between couple of tabs so I am not sure that if I delete the row that would mess things up in other places. As a programmer I would love to get your opinion of what I have done so far, can take this on off line and e-mail you my spread sheet so you can see the scope and sort of give me some tips............ if not I understand. Ardy Dave Peterson wrote: First, one of the things JE's suggested code was to clearcontents of the cell whose worksheet was deleted. I'd change this line: rResult(1).ClearContents to rResult(1).entirerow.delete to delete the whole row. Then there would be no gaps in the data. And any new name could be added to the bottom of the list. And I like to use the =hyperlink() worksheet function in an adjacent cell (another reason to delete the entire row!). The worksheet function =hyperlink() will react nicely when/if you change the name of that worksheet. A lot of things could go wrong with this type of thing. The user could enter the name of a worksheet that already exists--or could enter an invalid name (wrong characters like slashes, colons... or too many characters). I made some assumptions. You'll have to change them if I guessed wrong. Option Explicit Sub AddName() Dim myNewName As String Dim ActWks As Worksheet Dim NewWks As Worksheet Dim DestCell As Range myNewName = Trim(InputBox(Prompt:="Enter a new name")) If myNewName = "" Then Exit Sub End If Set ActWks = ActiveSheet With ActWks Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With Application.ScreenUpdating = False With Worksheets("template") .Visible = xlSheetVisible .Copy after:=Worksheets(Worksheets.Count) Set NewWks = ActiveSheet .Visible = xlSheetHidden End With On Error Resume Next NewWks.Name = myNewName If Err.Number < 0 Then MsgBox "Error renaming the new sheet from: " _ & NewWks.Name & " to: " & myNewName & vbLf _ & "Could be an invalid name or a sheet" _ & " by that name already exists!" _ & vbLf & vbLf & "Entered Name NOT Used--Worksheet Name Added!!!" Err.Clear End If 'put the name entered so the hyperlink works????? DestCell.Value = NewWks.Name DestCell.Offset(0, 1).Formula _ = "=HYPERLINK(""#""&CELL(""address"",INDIRECT(""'""& " _ & DestCell.Address(0, 0) & "&""'!A1"")),""Click Me"")" ActWks.Activate Application.ScreenUpdating = True End Sub Ardy wrote: Dave: I don't want to over extend my welcome, but I also am really looking for reversal of this code too. What I mean is that if the user wants to add a student. Now this is a bit tricky. Because lets say in column A starting at A2 the names start, Assume we have continuous names in range A2:A20. Now with the help of previous code the user is able to delete lets say one of the names (A10). Now if we want to add a name it would be nice to start from bottom where ever that is in this case A20 come up and find our null(A10) and insert the name there. Once we insert the name, the macro will create a tab for the inserted name by copying a hidden tab called "Template" and make a link from the name to the newly created tab. I probably can peace mill the copying and linking, but I am stump on the finding the null, and what if there is no gap I assume there is an else in there to make it go to the bottom of the list. Ardy Dave Peterson wrote: Glad you fixed it! Ardy wrote: After you wrote your explanation I made the modification, to accomedate for that. You know dave I am not a programmer by trait. I have been able to write Dave Peterson wrote: Watch out for what I wrote. I didn't notice that the data was on two different rows. You'll want to change that .offset() to come down and move to the right. Ardy wrote: Thank You So much it is putting it to perspective a bit........;) Ardy Dave Peterson wrote: rResult is a variable that holds a reference to that range. rResult is an object that has lots of properties. It's more complex than say a variable declared as a Long (just a whole number). You can get to the value. If rResult is a single cell: Msgbox rResult.value or its address: msgbox rResult.address or lots more things Put your cursor on "Range" in this line" Dim rResult as Range and hit F1 and you'll be taken to VBA's help for Range objects. rResult(1) will refer to the first cell in that range. The user can select lots of cells. JE wants to just use the first in his code. And one of the properties that a range has is .Offset() and another is .Resize(). rResult(1).offset(0,1) will stay on the same row (0) and go one column to the right. rResult(1).offset(0,1).resize(1,59) will "move" one cell to the right and resize that range to 1 row by 59 columns So rResult(1).offset(0,1).resize(1,59).clearcontents or avoiding the offset() rResult(1).resize(1,60).clearcontents (I think that A:BH is 60 columns--check my work before you trust it!) Ardy wrote: Great it works and no error on cancel. I have two questions and a request. Q1. Dose the variable rResult holds the cell number or content? i.e A3(#A#3) or the content "Some Text" Q2. Explain the (1) after rResult. What function dose it serves what is it doing Request: Assuming that the cell we are clearing the content (rResult(1).ClearContents) has more information in front of it C3:BH3 how would one clear those. The confusing part for me is how to capture the initial cell id and make the related range to clear. Example: The user picks Cell A2 that has the content "Some Text" the current code dose perfect it clears it and deletes the tab that has the "Some Text". Now visualize there are more related information in cell C3:BH3 that also needs to be cleared. Ardy Dave Peterson wrote: How about this minor modification to JE's code: Dim rResult As Range Do on error resume next Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) on error goto 0 If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents Ardy wrote: JE. I also get a Run Time Error"424', Object Required when pressing cancel. Ardy Ardy wrote: This is great this works pritty good, I got to modify the message and all and all couple of other things to it to make it fit the situation, One thing is that how would you expand on this code and capture the content and use it to delete the respected tab which has the same name as the cell content that we are just abt to delete. Ardy JE McGimpsey wrote: One way: Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
If you have specific questions, it's better to post in the newsgroups.
But I don't like opening workbooks from others. Sorry. Ardy wrote: I really appreciate your help on this, Like I said I am not a programmer, So I am using this project to learn a bit and at the same time help my son's school in gathering data, please allow some time for me to munch on what you have said and wrote so I can digest, There are dependencies between couple of tabs so I am not sure that if I delete the row that would mess things up in other places. As a programmer I would love to get your opinion of what I have done so far, can take this on off line and e-mail you my spread sheet so you can see the scope and sort of give me some tips............ if not I understand. Ardy Dave Peterson wrote: First, one of the things JE's suggested code was to clearcontents of the cell whose worksheet was deleted. I'd change this line: rResult(1).ClearContents to rResult(1).entirerow.delete to delete the whole row. Then there would be no gaps in the data. And any new name could be added to the bottom of the list. And I like to use the =hyperlink() worksheet function in an adjacent cell (another reason to delete the entire row!). The worksheet function =hyperlink() will react nicely when/if you change the name of that worksheet. A lot of things could go wrong with this type of thing. The user could enter the name of a worksheet that already exists--or could enter an invalid name (wrong characters like slashes, colons... or too many characters). I made some assumptions. You'll have to change them if I guessed wrong. Option Explicit Sub AddName() Dim myNewName As String Dim ActWks As Worksheet Dim NewWks As Worksheet Dim DestCell As Range myNewName = Trim(InputBox(Prompt:="Enter a new name")) If myNewName = "" Then Exit Sub End If Set ActWks = ActiveSheet With ActWks Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With Application.ScreenUpdating = False With Worksheets("template") .Visible = xlSheetVisible .Copy after:=Worksheets(Worksheets.Count) Set NewWks = ActiveSheet .Visible = xlSheetHidden End With On Error Resume Next NewWks.Name = myNewName If Err.Number < 0 Then MsgBox "Error renaming the new sheet from: " _ & NewWks.Name & " to: " & myNewName & vbLf _ & "Could be an invalid name or a sheet" _ & " by that name already exists!" _ & vbLf & vbLf & "Entered Name NOT Used--Worksheet Name Added!!!" Err.Clear End If 'put the name entered so the hyperlink works????? DestCell.Value = NewWks.Name DestCell.Offset(0, 1).Formula _ = "=HYPERLINK(""#""&CELL(""address"",INDIRECT(""'""& " _ & DestCell.Address(0, 0) & "&""'!A1"")),""Click Me"")" ActWks.Activate Application.ScreenUpdating = True End Sub Ardy wrote: Dave: I don't want to over extend my welcome, but I also am really looking for reversal of this code too. What I mean is that if the user wants to add a student. Now this is a bit tricky. Because lets say in column A starting at A2 the names start, Assume we have continuous names in range A2:A20. Now with the help of previous code the user is able to delete lets say one of the names (A10). Now if we want to add a name it would be nice to start from bottom where ever that is in this case A20 come up and find our null(A10) and insert the name there. Once we insert the name, the macro will create a tab for the inserted name by copying a hidden tab called "Template" and make a link from the name to the newly created tab. I probably can peace mill the copying and linking, but I am stump on the finding the null, and what if there is no gap I assume there is an else in there to make it go to the bottom of the list. Ardy Dave Peterson wrote: Glad you fixed it! Ardy wrote: After you wrote your explanation I made the modification, to accomedate for that. You know dave I am not a programmer by trait. I have been able to write Dave Peterson wrote: Watch out for what I wrote. I didn't notice that the data was on two different rows. You'll want to change that .offset() to come down and move to the right. Ardy wrote: Thank You So much it is putting it to perspective a bit........;) Ardy Dave Peterson wrote: rResult is a variable that holds a reference to that range. rResult is an object that has lots of properties. It's more complex than say a variable declared as a Long (just a whole number). You can get to the value. If rResult is a single cell: Msgbox rResult.value or its address: msgbox rResult.address or lots more things Put your cursor on "Range" in this line" Dim rResult as Range and hit F1 and you'll be taken to VBA's help for Range objects. rResult(1) will refer to the first cell in that range. The user can select lots of cells. JE wants to just use the first in his code. And one of the properties that a range has is .Offset() and another is .Resize(). rResult(1).offset(0,1) will stay on the same row (0) and go one column to the right. rResult(1).offset(0,1).resize(1,59) will "move" one cell to the right and resize that range to 1 row by 59 columns So rResult(1).offset(0,1).resize(1,59).clearcontents or avoiding the offset() rResult(1).resize(1,60).clearcontents (I think that A:BH is 60 columns--check my work before you trust it!) Ardy wrote: Great it works and no error on cancel. I have two questions and a request. Q1. Dose the variable rResult holds the cell number or content? i.e A3(#A#3) or the content "Some Text" Q2. Explain the (1) after rResult. What function dose it serves what is it doing Request: Assuming that the cell we are clearing the content (rResult(1).ClearContents) has more information in front of it C3:BH3 how would one clear those. The confusing part for me is how to capture the initial cell id and make the related range to clear. Example: The user picks Cell A2 that has the content "Some Text" the current code dose perfect it clears it and deletes the tab that has the "Some Text". Now visualize there are more related information in cell C3:BH3 that also needs to be cleared. Ardy Dave Peterson wrote: How about this minor modification to JE's code: Dim rResult As Range Do on error resume next Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) on error goto 0 If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents Ardy wrote: JE. I also get a Run Time Error"424', Object Required when pressing cancel. Ardy Ardy wrote: This is great this works pritty good, I got to modify the message and all and all couple of other things to it to make it fit the situation, One thing is that how would you expand on this code and capture the content and use it to delete the respected tab which has the same name as the cell content that we are just abt to delete. Ardy JE McGimpsey wrote: One way: Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Function:
I Undrestand......:)
Ardy Dave Peterson wrote: If you have specific questions, it's better to post in the newsgroups. But I don't like opening workbooks from others. Sorry. Ardy wrote: I really appreciate your help on this, Like I said I am not a programmer, So I am using this project to learn a bit and at the same time help my son's school in gathering data, please allow some time for me to munch on what you have said and wrote so I can digest, There are dependencies between couple of tabs so I am not sure that if I delete the row that would mess things up in other places. As a programmer I would love to get your opinion of what I have done so far, can take this on off line and e-mail you my spread sheet so you can see the scope and sort of give me some tips............ if not I understand. Ardy Dave Peterson wrote: First, one of the things JE's suggested code was to clearcontents of the cell whose worksheet was deleted. I'd change this line: rResult(1).ClearContents to rResult(1).entirerow.delete to delete the whole row. Then there would be no gaps in the data. And any new name could be added to the bottom of the list. And I like to use the =hyperlink() worksheet function in an adjacent cell (another reason to delete the entire row!). The worksheet function =hyperlink() will react nicely when/if you change the name of that worksheet. A lot of things could go wrong with this type of thing. The user could enter the name of a worksheet that already exists--or could enter an invalid name (wrong characters like slashes, colons... or too many characters). I made some assumptions. You'll have to change them if I guessed wrong. Option Explicit Sub AddName() Dim myNewName As String Dim ActWks As Worksheet Dim NewWks As Worksheet Dim DestCell As Range myNewName = Trim(InputBox(Prompt:="Enter a new name")) If myNewName = "" Then Exit Sub End If Set ActWks = ActiveSheet With ActWks Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With Application.ScreenUpdating = False With Worksheets("template") .Visible = xlSheetVisible .Copy after:=Worksheets(Worksheets.Count) Set NewWks = ActiveSheet .Visible = xlSheetHidden End With On Error Resume Next NewWks.Name = myNewName If Err.Number < 0 Then MsgBox "Error renaming the new sheet from: " _ & NewWks.Name & " to: " & myNewName & vbLf _ & "Could be an invalid name or a sheet" _ & " by that name already exists!" _ & vbLf & vbLf & "Entered Name NOT Used--Worksheet Name Added!!!" Err.Clear End If 'put the name entered so the hyperlink works????? DestCell.Value = NewWks.Name DestCell.Offset(0, 1).Formula _ = "=HYPERLINK(""#""&CELL(""address"",INDIRECT(""'""& " _ & DestCell.Address(0, 0) & "&""'!A1"")),""Click Me"")" ActWks.Activate Application.ScreenUpdating = True End Sub Ardy wrote: Dave: I don't want to over extend my welcome, but I also am really looking for reversal of this code too. What I mean is that if the user wants to add a student. Now this is a bit tricky. Because lets say in column A starting at A2 the names start, Assume we have continuous names in range A2:A20. Now with the help of previous code the user is able to delete lets say one of the names (A10). Now if we want to add a name it would be nice to start from bottom where ever that is in this case A20 come up and find our null(A10) and insert the name there. Once we insert the name, the macro will create a tab for the inserted name by copying a hidden tab called "Template" and make a link from the name to the newly created tab. I probably can peace mill the copying and linking, but I am stump on the finding the null, and what if there is no gap I assume there is an else in there to make it go to the bottom of the list. Ardy Dave Peterson wrote: Glad you fixed it! Ardy wrote: After you wrote your explanation I made the modification, to accomedate for that. You know dave I am not a programmer by trait. I have been able to write Dave Peterson wrote: Watch out for what I wrote. I didn't notice that the data was on two different rows. You'll want to change that .offset() to come down and move to the right. Ardy wrote: Thank You So much it is putting it to perspective a bit........;) Ardy Dave Peterson wrote: rResult is a variable that holds a reference to that range. rResult is an object that has lots of properties. It's more complex than say a variable declared as a Long (just a whole number). You can get to the value. If rResult is a single cell: Msgbox rResult.value or its address: msgbox rResult.address or lots more things Put your cursor on "Range" in this line" Dim rResult as Range and hit F1 and you'll be taken to VBA's help for Range objects. rResult(1) will refer to the first cell in that range. The user can select lots of cells. JE wants to just use the first in his code. And one of the properties that a range has is .Offset() and another is .Resize(). rResult(1).offset(0,1) will stay on the same row (0) and go one column to the right. rResult(1).offset(0,1).resize(1,59) will "move" one cell to the right and resize that range to 1 row by 59 columns So rResult(1).offset(0,1).resize(1,59).clearcontents or avoiding the offset() rResult(1).resize(1,60).clearcontents (I think that A:BH is 60 columns--check my work before you trust it!) Ardy wrote: Great it works and no error on cancel. I have two questions and a request. Q1. Dose the variable rResult holds the cell number or content? i.e A3(#A#3) or the content "Some Text" Q2. Explain the (1) after rResult. What function dose it serves what is it doing Request: Assuming that the cell we are clearing the content (rResult(1).ClearContents) has more information in front of it C3:BH3 how would one clear those. The confusing part for me is how to capture the initial cell id and make the related range to clear. Example: The user picks Cell A2 that has the content "Some Text" the current code dose perfect it clears it and deletes the tab that has the "Some Text". Now visualize there are more related information in cell C3:BH3 that also needs to be cleared. Ardy Dave Peterson wrote: How about this minor modification to JE's code: Dim rResult As Range Do on error resume next Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) on error goto 0 If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents Ardy wrote: JE. I also get a Run Time Error"424', Object Required when pressing cancel. Ardy Ardy wrote: This is great this works pritty good, I got to modify the message and all and all couple of other things to it to make it fit the situation, One thing is that how would you expand on this code and capture the content and use it to delete the respected tab which has the same name as the cell content that we are just abt to delete. Ardy JE McGimpsey wrote: One way: Dim rResult As Range Do Set rResult = Application.InputBox( _ Prompt:="Select cell to clear: ", _ Title:="Clear cell and delete sheet", _ Type:=8) If rResult Is Nothing Then Exit Sub 'User cancelled Loop Until Not Intersect(rResult(1), Range("A2:A100")) Is Nothing On Error Resume Next Application.DisplayAlerts = False Sheets(rResult(1).Text).Delete Application.DisplayAlerts = True On Error GoTo 0 rResult(1).ClearContents In article . com, "Ardy" wrote: I am trying to create a macro that will delete a cell content and related tab by picking the cell. i.e. We have bunch of names in column A starting at A2 in tab "Roster". Each name in "Roster" has its own respected Tab. The macro or Code once activated will have the user to pick the cell that has the name to be deleted, clears the content, but before clearing will capture the name in a variable that will be used to find the respected tab and delete the tab as well. Any help on this is appreciated. Ardy -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if function to delete row | Excel Worksheet Functions | |||
How do I delete a column that has been used for a function? | Excel Worksheet Functions | |||
Delete Cell Function | Excel Worksheet Functions | |||
function to delete rows | Excel Programming | |||
Macro/Function to delete......... | Excel Programming |