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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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 <(E-Mail Removed)>,
"Ardy" <(E-Mail Removed)> 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

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >

Try:

Set cell = Application.Inputbox("Select cell using mouse", Type:=8)

In article <(E-Mail Removed)>,
"Ardy" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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
> > >

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 <(E-Mail Removed)>,
> "Ardy" <(E-Mail Removed)> 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

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
> >
> >

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 <(E-Mail Removed)>,
> > "Ardy" <(E-Mail Removed)> 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

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 <(E-Mail Removed)>,
> > > "Ardy" <(E-Mail Removed)> 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

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 <(E-Mail Removed)>,
> > > > "Ardy" <(E-Mail Removed)> 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