I want a sheet to be hiden when the file is closed.

I have done the code to unhide it should the user require but because of
this I want it hiden again when the file is closed (if it has been unhiden)

Can any one tell me where/how I write this?

Use the before_close event
Alt+Fll to open VB editor. double click 'This workbook' and paste this in

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Visible = False
End Sub

Mike

"Leanne" wrote:

I want a sheet to be hiden when the file is closed.

I have done the code to unhide it should the user require but because of
this I want it hiden again when the file is closed (if it has been unhiden)

Can any one tell me where/how I write this?

Hi Mike,

Thank you! I knew the 'sheet visible' bit but did not know the before close
event.

Out of interest - what does this do/mean (Cancel As Boolean)


"Mike H" wrote:

Use the before_close event
Alt+Fll to open VB editor. double click 'This workbook' and paste this in

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Visible = False
End Sub

Mike

"Leanne" wrote:

I want a sheet to be hiden when the file is closed.

I have done the code to unhide it should the user require but because of
this I want it hiden again when the file is closed (if it has been unhiden)

Can any one tell me where/how I write this?

I wouldn't hide the sheet when the workbook closes.

After your code hides it, your code will have to save the workbook. If the user
opened the workbook and destroyed 90% of the data and decides to close without
saving, your code just screwed up the workbook.

Instead, I'd hide the worksheet when the workbook opens.

Option Explicit
Sub Auto_Open()
thisworkbook.worksheets("Somesheetname").visible = xlsheethidden
end sub

You could use the workbook_open event if you wanted instead.

Leanne wrote:

I want a sheet to be hiden when the file is closed.

I have done the code to unhide it should the user require but because of
this I want it hiden again when the file is closed (if it has been unhiden)

Can any one tell me where/how I write this?

--

Dave Peterson

Hi Dave,

Thanks for pointing that out - I did not realise it would save it but it
makes sense that it would have to. I will take your advise and change it to
hide when opened.

Curiosity question again (only way I will learn!)

You have writen the code to hide differently that Mike (and what I had from
recording a macro). Is there any difference or is it simply preference?

"Dave Peterson" wrote:

I wouldn't hide the sheet when the workbook closes.

After your code hides it, your code will have to save the workbook. If the user
opened the workbook and destroyed 90% of the data and decides to close without
saving, your code just screwed up the workbook.

Instead, I'd hide the worksheet when the workbook opens.

Option Explicit
Sub Auto_Open()
thisworkbook.worksheets("Somesheetname").visible = xlsheethidden
end sub

You could use the workbook_open event if you wanted instead.

Leanne wrote:

I want a sheet to be hiden when the file is closed.

I have done the code to unhide it should the user require but because of
this I want it hiden again when the file is closed (if it has been unhiden)

Can any one tell me where/how I write this?

--

Dave Peterson

Dave,

I'm not sure i understand your point. The Before close event executes only
when a user has decided to close and does nothing to prevent or require that
user to save even if that user has destroyed or messed up the data. The same
risk of lost data would exist with nothing in the before close event or any
macros at all.

Mike

"Dave Peterson" wrote:

I wouldn't hide the sheet when the workbook closes.

After your code hides it, your code will have to save the workbook. If the user
opened the workbook and destroyed 90% of the data and decides to close without
saving, your code just screwed up the workbook.

Instead, I'd hide the worksheet when the workbook opens.

Option Explicit
Sub Auto_Open()
thisworkbook.worksheets("Somesheetname").visible = xlsheethidden
end sub

You could use the workbook_open event if you wanted instead.

Leanne wrote:

I want a sheet to be hiden when the file is closed.

I have done the code to unhide it should the user require but because of
this I want it hiden again when the file is closed (if it has been unhiden)

Can any one tell me where/how I write this?

--

Dave Peterson

Mike used:
Sheets("Sheet1").Visible = False
and I used:
thisworkbook.worksheets("Somesheetname").visible = xlsheethidden

I used worksheets(). Mike used Sheets(). If the sheet you're hiding is a
worksheet, either will work.

If the sheet you're hiding is a chart sheet or a dialog sheet or an old macro
sheet, then my code won't work.

I qualified my worksheets() by using ThisWorkbook. I like to qualify objects to
make sure I know what object I'm working with.

..Visible can accept 3 different values.
xlsheetvisible (which is equal to -1)
xlsheetveryhidden (which is equal to 2)
xlsheethidden (which is equal to 0)

Mike used False. In this case, Excel's VBA is forgiving and will see that as
0--the same as xlsheethidden.

======
And the code Mike suggested didn't actually do the save. You'd have to include
that if you really wanted it. But even if your code did the save or you forced
the user to do the save, the results might not be what you wanted.



Leanne wrote:

Hi Dave,

Thanks for pointing that out - I did not realise it would save it but it
makes sense that it would have to. I will take your advise and change it to
hide when opened.

Curiosity question again (only way I will learn!)

You have writen the code to hide differently that Mike (and what I had from
recording a macro). Is there any difference or is it simply preference?

"Dave Peterson" wrote:

I wouldn't hide the sheet when the workbook closes.

After your code hides it, your code will have to save the workbook. If the user
opened the workbook and destroyed 90% of the data and decides to close without
saving, your code just screwed up the workbook.

Instead, I'd hide the worksheet when the workbook opens.

Option Explicit
Sub Auto_Open()
thisworkbook.worksheets("Somesheetname").visible = xlsheethidden
end sub

You could use the workbook_open event if you wanted instead.

Leanne wrote:

I want a sheet to be hiden when the file is closed.

I have done the code to unhide it should the user require but because of
this I want it hiden again when the file is closed (if it has been unhiden)

Can any one tell me where/how I write this?

--

Dave Peterson


--

Dave Peterson

Event procedures are a special kind of subroutine... the argument list for
it has a required structure and you can't change it; however, you don't have
to remember what the individual argument lists for the various events are.
When you go to the code window for a worksheet or, as in this case, the
workbook, the drop down to the left in the title bar area of the code window
allows you to select the Worksheet or Workbook (as the case may be). For the
workbook code window, selecting Workbook from that list populates the drop
down on the right side of the title bar area with all of its available
events. In that drop down, you will see the BeforeClose event and if you
select it, it will create the event subroutine header for you. That header
is...

Private Sub Workbook_BeforeClose(Cancel As Boolean)

The Cancel argument (a Boolean, hence it is either True or False) is a
method the event gives you to communicate with the code that triggered the
event. If, inside the BeforeClose event, you set Cancel equal to True, it
tells Excel to not Close. This means that you can create code to exam
certain situations you deem important (for example, having a certain cell
filled in or not) and cancel the close operation the user initiated if those
situations warrant it. Of course, good programming practice would be to
include code that pops a message box up and tells the user his/her close
request is being denied and why.

Rick


"Leanne" wrote in message
...
Hi Mike,

Thank you! I knew the 'sheet visible' bit but did not know the before
close
event.

Out of interest - what does this do/mean (Cancel As Boolean)


"Mike H" wrote:

Use the before_close event
Alt+Fll to open VB editor. double click 'This workbook' and paste this in

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Visible = False
End Sub

Mike

"Leanne" wrote:

I want a sheet to be hiden when the file is closed.

I have done the code to unhide it should the user require but because
of
this I want it hiden again when the file is closed (if it has been
unhiden)

Can any one tell me where/how I write this?

Thank you - there is not much point to knowing these codes if I cant
understand them. Appreciated

"Dave Peterson" wrote:

Mike used:
Sheets("Sheet1").Visible = False
and I used:
thisworkbook.worksheets("Somesheetname").visible = xlsheethidden

I used worksheets(). Mike used Sheets(). If the sheet you're hiding is a
worksheet, either will work.

If the sheet you're hiding is a chart sheet or a dialog sheet or an old macro
sheet, then my code won't work.

I qualified my worksheets() by using ThisWorkbook. I like to qualify objects to
make sure I know what object I'm working with.

..Visible can accept 3 different values.
xlsheetvisible (which is equal to -1)
xlsheetveryhidden (which is equal to 2)
xlsheethidden (which is equal to 0)

Mike used False. In this case, Excel's VBA is forgiving and will see that as
0--the same as xlsheethidden.

======
And the code Mike suggested didn't actually do the save. You'd have to include
that if you really wanted it. But even if your code did the save or you forced
the user to do the save, the results might not be what you wanted.



Leanne wrote:

Hi Dave,

Thanks for pointing that out - I did not realise it would save it but it
makes sense that it would have to. I will take your advise and change it to
hide when opened.

Curiosity question again (only way I will learn!)

You have writen the code to hide differently that Mike (and what I had from
recording a macro). Is there any difference or is it simply preference?

"Dave Peterson" wrote:

I wouldn't hide the sheet when the workbook closes.

After your code hides it, your code will have to save the workbook. If the user
opened the workbook and destroyed 90% of the data and decides to close without
saving, your code just screwed up the workbook.

Instead, I'd hide the worksheet when the workbook opens.

Option Explicit
Sub Auto_Open()
thisworkbook.worksheets("Somesheetname").visible = xlsheethidden
end sub

You could use the workbook_open event if you wanted instead.

Leanne wrote:

I want a sheet to be hiden when the file is closed.

I have done the code to unhide it should the user require but because of
this I want it hiden again when the file is closed (if it has been unhiden)

Can any one tell me where/how I write this?

--

Dave Peterson


--

Dave Peterson

This I did not know. Thanks for this information I will use it in future to
try and answer my questions first.

"Rick Rothstein (MVP - VB)" wrote:

Event procedures are a special kind of subroutine... the argument list for
it has a required structure and you can't change it; however, you don't have
to remember what the individual argument lists for the various events are.
When you go to the code window for a worksheet or, as in this case, the
workbook, the drop down to the left in the title bar area of the code window
allows you to select the Worksheet or Workbook (as the case may be). For the
workbook code window, selecting Workbook from that list populates the drop
down on the right side of the title bar area with all of its available
events. In that drop down, you will see the BeforeClose event and if you
select it, it will create the event subroutine header for you. That header
is...

Private Sub Workbook_BeforeClose(Cancel As Boolean)

The Cancel argument (a Boolean, hence it is either True or False) is a
method the event gives you to communicate with the code that triggered the
event. If, inside the BeforeClose event, you set Cancel equal to True, it
tells Excel to not Close. This means that you can create code to exam
certain situations you deem important (for example, having a certain cell
filled in or not) and cancel the close operation the user initiated if those
situations warrant it. Of course, good programming practice would be to
include code that pops a message box up and tells the user his/her close
request is being denied and why.

Rick


"Leanne" wrote in message
...
Hi Mike,

Thank you! I knew the 'sheet visible' bit but did not know the before
close
event.

Out of interest - what does this do/mean (Cancel As Boolean)


"Mike H" wrote:

Use the before_close event
Alt+Fll to open VB editor. double click 'This workbook' and paste this in

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Visible = False
End Sub

Mike

"Leanne" wrote:

I want a sheet to be hiden when the file is closed.

I have done the code to unhide it should the user require but because
of
this I want it hiden again when the file is closed (if it has been
unhiden)

Can any one tell me where/how I write this?

If your code hides the sheet, but workbook isn't saved, then it's not really
doing much.

So somebody or something will have to save the workbook after the sheet is
hidden.

If you depend on the user to save with the worksheet hidden, then the save may
not happen and the sheet isn't hidden.

If you do the save in code, then you may be saving something that the user
doesn't want to save.

Either way sounds bad to me.

Mike H wrote:

Dave,

I'm not sure i understand your point. The Before close event executes only
when a user has decided to close and does nothing to prevent or require that
user to save even if that user has destroyed or messed up the data. The same
risk of lost data would exist with nothing in the before close event or any
macros at all.

Mike

"Dave Peterson" wrote:

I wouldn't hide the sheet when the workbook closes.

After your code hides it, your code will have to save the workbook. If the user
opened the workbook and destroyed 90% of the data and decides to close without
saving, your code just screwed up the workbook.

Instead, I'd hide the worksheet when the workbook opens.

Option Explicit
Sub Auto_Open()
thisworkbook.worksheets("Somesheetname").visible = xlsheethidden
end sub

You could use the workbook_open event if you wanted instead.

Leanne wrote:

I want a sheet to be hiden when the file is closed.

I have done the code to unhide it should the user require but because of
this I want it hiden again when the file is closed (if it has been unhiden)

Can any one tell me where/how I write this?

--

Dave Peterson


--

Dave Peterson

Dave

A good point I never considered the user cancelling the save and agree
forcing one isn't the answer either so in the workbook open event would be
the better option. Now all we have to do is force the user to enable macros
and we've cracked it.

I know, I think i'll hide all the sheets except one in the before_close or
before_save event and only unhide them when the user enables macros now all
i've got to do is force the user to save <g

Seriously what this actually demonstrates; apart from yours being a better
solution, is that protective measures in Excel are for the discouragement of
the casual user and of little practicable value.


Mike

"Dave Peterson" wrote:

If your code hides the sheet, but workbook isn't saved, then it's not really
doing much.

So somebody or something will have to save the workbook after the sheet is
hidden.

If you depend on the user to save with the worksheet hidden, then the save may
not happen and the sheet isn't hidden.

If you do the save in code, then you may be saving something that the user
doesn't want to save.

Either way sounds bad to me.

Mike H wrote:

Dave,

I'm not sure i understand your point. The Before close event executes only
when a user has decided to close and does nothing to prevent or require that
user to save even if that user has destroyed or messed up the data. The same
risk of lost data would exist with nothing in the before close event or any
macros at all.

Mike

"Dave Peterson" wrote:

I wouldn't hide the sheet when the workbook closes.

After your code hides it, your code will have to save the workbook. If the user
opened the workbook and destroyed 90% of the data and decides to close without
saving, your code just screwed up the workbook.

Instead, I'd hide the worksheet when the workbook opens.

Option Explicit
Sub Auto_Open()
thisworkbook.worksheets("Somesheetname").visible = xlsheethidden
end sub

You could use the workbook_open event if you wanted instead.

Leanne wrote:

I want a sheet to be hiden when the file is closed.

I have done the code to unhide it should the user require but because of
this I want it hiden again when the file is closed (if it has been unhiden)

Can any one tell me where/how I write this?

--

Dave Peterson


--

Dave Peterson

I agree with the disabling macros bit. I don't think you can do much to enforce
it, though.

I remember a thread from long ago that would use the before_save event.

It would hide the worksheets
do the save
and then unhide those worksheets (and mark the file as already .saved)

And in most cases, you have to hope that your co-workers are working with
you--not trying to break things.



Mike H wrote:

Dave

A good point I never considered the user cancelling the save and agree
forcing one isn't the answer either so in the workbook open event would be
the better option. Now all we have to do is force the user to enable macros
and we've cracked it.

I know, I think i'll hide all the sheets except one in the before_close or
before_save event and only unhide them when the user enables macros now all
i've got to do is force the user to save <g

Seriously what this actually demonstrates; apart from yours being a better
solution, is that protective measures in Excel are for the discouragement of
the casual user and of little practicable value.

Mike

"Dave Peterson" wrote:

If your code hides the sheet, but workbook isn't saved, then it's not really
doing much.

So somebody or something will have to save the workbook after the sheet is
hidden.

If you depend on the user to save with the worksheet hidden, then the save may
not happen and the sheet isn't hidden.

If you do the save in code, then you may be saving something that the user
doesn't want to save.

Either way sounds bad to me.

Mike H wrote:

Dave,

I'm not sure i understand your point. The Before close event executes only
when a user has decided to close and does nothing to prevent or require that
user to save even if that user has destroyed or messed up the data. The same
risk of lost data would exist with nothing in the before close event or any
macros at all.

Mike

"Dave Peterson" wrote:

I wouldn't hide the sheet when the workbook closes.

After your code hides it, your code will have to save the workbook. If the user
opened the workbook and destroyed 90% of the data and decides to close without
saving, your code just screwed up the workbook.

Instead, I'd hide the worksheet when the workbook opens.

Option Explicit
Sub Auto_Open()
thisworkbook.worksheets("Somesheetname").visible = xlsheethidden
end sub

You could use the workbook_open event if you wanted instead.

Leanne wrote:

I want a sheet to be hiden when the file is closed.

I have done the code to unhide it should the user require but because of
this I want it hiden again when the file is closed (if it has been unhiden)

Can any one tell me where/how I write this?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson