delete worksheets if cell value...

Joxu

New Member
Joined
Nov 19, 2009
Messages
21
Hi.

Sorry for long story... and rally english :)

I try to create next one.
When opening Excel file and if macros are disabled, user get message " let me run". All anothers worksheets are then very hidden.
If user are enabled macros then he will see several worksheets, exception those what are setted xlsheetveryhidden.
but, before that I read with code environ username and application organization name. That text values will stay on worksheet "environ", cells A2 and A3. From those cells I take with function left result to cells C2 and C3. Wanted results stays in E2 and E3. On cells F2 and F3, I use simple if function to check if answers are similar as I wanted. If not then F5 have 0.
So far it is easy. But when worksheet environ cell F5 are zero I want to delete all worksheets on that file. I have tried many different codes but my skills seems to be too poor.
All those I want to run without any display alerts and without screenupdate flasing. And run those from Private sub workbook open.
Later on when its work it will be saved as template.
was this too long question??
 

Joxu

New Member
Joined
Nov 19, 2009
Messages
21
Hi.

Sorry for long story... and rally english :)

I try to create next one.
When opening Excel file and if macros are disabled, user get message " let me run". All anothers worksheets are then very hidden.
If user are enabled macros then he will see several worksheets, exception those what are setted xlsheetveryhidden.
but, before that I read with code environ username and application organization name. That text values will stay on worksheet "environ", cells A2 and A3. From those cells I take with function left result to cells C2 and C3. Wanted results stays in E2 and E3. On cells F2 and F3, I use simple if function to check if answers are similar as I wanted. If not then F5 have 0.
So far it is easy. But when worksheet environ cell F5 are zero I want to delete all worksheets on that file. I have tried many different codes but my skills seems to be too poor.
All those I want to run without any display alerts and without screenupdate flasing. And run those from Private sub workbook open.
Later on when its work it will be saved as template.
was this too long question??
This almost work
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Dim myCount 'This line of code is optional
Dim i 'This line of code is optional
On Error Resume Next
myCount = Application.Sheets.Count
Sheets(1).Visible = True
Range("A1").Select
For i = 2 To myCount
Sheets(i).Visible = xlVeryHidden
If i = myCount Then
End If
Next i
ActiveWorkbook.Save
Application.ScreenUpdating = True

End Sub
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Worksheets("environ").Activate
Worksheets("environ").Range("a2").Value = Environ("username")
Worksheets("environ").Range("a3").Value = Application.OrganizationName
Dim ws As Worksheet

If Range("f5").Value = 0 Then

Sheets("environ").Activate
Columns("a:bz").Select
Cells.Clear
Range("a1").Select
Application.DisplayAlerts = False
Sheets("X").Delete
Sheets("XX").Delete
Sheets("XXX").Delete
Sheets("XXXX").Delete
Sheets("pricebook").Delete

Application.DisplayAlerts = True
Else

'For Each ws In Worksheets
Sheets("XX").Visible



If ws.Name = "pricebook" Then
ws.Visible = Visible
End If

Next


Sheets("environ").Activate
ActiveWindow.SelectedSheets.Delete

Application.ScreenUpdating = True

End If
End Sub
 

Forum statistics

Threads
1,082,286
Messages
5,364,292
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top