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:
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 |
#4
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 |
#5
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 |
#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:
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 |
#8
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 |
#9
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 |
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 |