I have a workbook which contains 40+ sheets that is filled out by our vendors
and returned to us. Not all sheets are used every time and I would like to
hide the unused sheets before submitting to document control.
Every sheet has information on it as there are cell references on each sheet
to basic job information filled out on sheet one.
I was thinking about a macro or VBA that runs behind a hidden command button
that would determine if certain fields on each sheet were filled or empty,
and hide the sheets that had the empty fields.
All of the sheets are protected, so obviously the code would have to
unprotect the sheets before hiding.

I know what end result I would like, but€¦
I have no idea where to start!

try this idea
Sub hideif()
For Each ws In Worksheets
If ws.Name < "Sheet1" Then
If Len(ws.Range("a1")) < 1 Then ws.Visible = False
End If
Next
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"suznal" wrote in message
...
I have a workbook which contains 40+ sheets that is filled out by our
vendors
and returned to us. Not all sheets are used every time and I would like to
hide the unused sheets before submitting to document control.
Every sheet has information on it as there are cell references on each
sheet
to basic job information filled out on sheet one.
I was thinking about a macro or VBA that runs behind a hidden command
button
that would determine if certain fields on each sheet were filled or empty,
and hide the sheets that had the empty fields.
All of the sheets are protected, so obviously the code would have to
unprotect the sheets before hiding.

I know what end result I would like, but€¦
I have no idea where to start!

We can start from he-

Sub sonic()
'Scroll through worksheets
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
If Range("A1").Value = "" Then ws.Visible = False
Next ws
End Sub

This will loop through all worksheets and hide them if A1 is empty. Two
points. It will fail if A1 is empty in all sheets because you can't hide all
sheets and 2 you don't have to unprotect a protected sheet to hide it.

Mike

"suznal" wrote:

I have a workbook which contains 40+ sheets that is filled out by our vendors
and returned to us. Not all sheets are used every time and I would like to
hide the unused sheets before submitting to document control.
Every sheet has information on it as there are cell references on each sheet
to basic job information filled out on sheet one.
I was thinking about a macro or VBA that runs behind a hidden command button
that would determine if certain fields on each sheet were filled or empty,
and hide the sheets that had the empty fields.
All of the sheets are protected, so obviously the code would have to
unprotect the sheets before hiding.

I know what end result I would like, but€¦
I have no idea where to start!

Selections are NOT necessary.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike H" wrote in message
...
We can start from he-

Sub sonic()
'Scroll through worksheets
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
If Range("A1").Value = "" Then ws.Visible = False
Next ws
End Sub

This will loop through all worksheets and hide them if A1 is empty. Two
points. It will fail if A1 is empty in all sheets because you can't hide
all
sheets and 2 you don't have to unprotect a protected sheet to hide it.

Mike

"suznal" wrote:

I have a workbook which contains 40+ sheets that is filled out by our
vendors
and returned to us. Not all sheets are used every time and I would like
to
hide the unused sheets before submitting to document control.
Every sheet has information on it as there are cell references on each
sheet
to basic job information filled out on sheet one.
I was thinking about a macro or VBA that runs behind a hidden command
button
that would determine if certain fields on each sheet were filled or
empty,
and hide the sheets that had the empty fields.
All of the sheets are protected, so obviously the code would have to
unprotect the sheets before hiding.

I know what end result I would like, but€¦
I have no idea where to start!