Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot destroy Word Object in Excel | Excel Programming | |||
Search and destroy | Excel Programming | |||
Destroy command button with a Macro? | Excel Discussion (Misc queries) | |||
How to make VBA code to self-destroy itself? | Excel Programming | |||
Seek and destroy | Excel Programming |