Search & Destroy
 

Always wanted to use that phrase.

Anyways, I have an odd question here. I have a macro that generates a sheet
with numbers, hides it and then creates a plot based off of those numbers in
the hidden sheet. It didn't dawn on me before now that this might be bad. I
just noticed I had about 10 hidden sheets that served no purpose.
I now need a way to remove the hidden sheets if someone removes the graph
that uses that data. Any ideas?

Thanks - Ben H.

Search & Destroy
 

Hi Ben,

In your sheet generation code, name the hidden sheet and, before creating
the new sheet, delete the named sheet. Something, perhaps like:

'=============
Public Sub Tester()
Dim SH As Worksheet

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets("MyHiddenSheet").Delete
Application.DisplayAlerts = True
On Error GoTo 0

Set SH = ActiveWorkbook.Worksheets.Add
With SH
.Name = "MyHiddenSheet"
.Visible = xlSheetHidden
'your data code
End With

End Sub
'<<=============

Alternatively, dependending upon your requirements, create the hidden sheet
only if it does not already exist, and change the sheet's data when and as
required.


---
Regards,
Norman



"Ben H" wrote in message
...
Always wanted to use that phrase.

Anyways, I have an odd question here. I have a macro that generates a
sheet
with numbers, hides it and then creates a plot based off of those numbers
in
the hidden sheet. It didn't dawn on me before now that this might be bad.
I
just noticed I had about 10 hidden sheets that served no purpose.
I now need a way to remove the hidden sheets if someone removes the graph
that uses that data. Any ideas?

Thanks - Ben H.

Search & Destroy
 

Norman

The way you explained the method won't work. It won't work because I go
through and delete the chart and sheet with a name before I regenerate the
numbers for that chart and sheet with that same name.

What happens is that Chart "A" links to Sheet "A". The name "A" could be
anything. The user then could delete Chart "A" from the workbook but this
won't catch Sheet "A" since Sheet "A" is hidden. So how do I find Sheet "A"
when the name varies and also when Chart "A" is gone? Is there a way to
search if a cell is linked? Any other ideas?


Thanks - Ben H.

"Norman Jones" wrote:

Hi Ben,

In your sheet generation code, name the hidden sheet and, before creating
the new sheet, delete the named sheet. Something, perhaps like:

'=============
Public Sub Tester()
Dim SH As Worksheet

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets("MyHiddenSheet").Delete
Application.DisplayAlerts = True
On Error GoTo 0

Set SH = ActiveWorkbook.Worksheets.Add
With SH
.Name = "MyHiddenSheet"
.Visible = xlSheetHidden
'your data code
End With

End Sub
'<<=============

Alternatively, dependending upon your requirements, create the hidden sheet
only if it does not already exist, and change the sheet's data when and as
required.


---
Regards,
Norman



"Ben H" wrote in message
...
Always wanted to use that phrase.

Anyways, I have an odd question here. I have a macro that generates a
sheet
with numbers, hides it and then creates a plot based off of those numbers
in
the hidden sheet. It didn't dawn on me before now that this might be bad.
I
just noticed I had about 10 hidden sheets that served no purpose.
I now need a way to remove the hidden sheets if someone removes the graph
that uses that data. Any ideas?

Thanks - Ben H.