Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default Saving in 1 worksheet only

Hi I have asked a couple of questions on here and you've helped greatly,
here's 1 mo

I want my document able to only save on 1 page ("introduction" sheet).

I dont want other people who use the document to be able to save on any of
the other sheets.

Other words, I want them to have to go back to the "Introduction" sheet in
order to save.

Thanks guys
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Saving in 1 worksheet only

Hi,

I assume this is to ensure that this sheet is the one visible on opening. If
so it would be easier to simply select it when the workbook opens.

Alt+f11 to open VB editor double click 'this workbook' and paste this in on
the right

Private Sub Workbook_Open()
Sheets("Introduction").Select
End Sub


Mike


"Mr. Burton" wrote:

Hi I have asked a couple of questions on here and you've helped greatly,
here's 1 mo

I want my document able to only save on 1 page ("introduction" sheet).

I dont want other people who use the document to be able to save on any of
the other sheets.

Other words, I want them to have to go back to the "Introduction" sheet in
order to save.

Thanks guys

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default Saving in 1 worksheet only

1st sheets is an introduction, when macros are enabled it takes you too the
rest of the sheet (that was hidden). People work on the rest of the sheet
then quit, the document then takes the user back to the introduction page
then asks to save.

Problem is if they click save whilst working and dont save again when they
quit at the introduction page, it gets saved in the middle of the sheets that
arnt ment to be shown until macros are enabled at the introduction sheet.

Does that make sense?

I dont want them to be able to save until they get taken back to the
introduction page.

"Mike H" wrote:

Hi,

I assume this is to ensure that this sheet is the one visible on opening. If
so it would be easier to simply select it when the workbook opens.

Alt+f11 to open VB editor double click 'this workbook' and paste this in on
the right

Private Sub Workbook_Open()
Sheets("Introduction").Select
End Sub


Mike


"Mr. Burton" wrote:

Hi I have asked a couple of questions on here and you've helped greatly,
here's 1 mo

I want my document able to only save on 1 page ("introduction" sheet).

I dont want other people who use the document to be able to save on any of
the other sheets.

Other words, I want them to have to go back to the "Introduction" sheet in
order to save.

Thanks guys

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Saving in 1 worksheet only

Try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Introduction").Select
End Sub

Mike

"Mr. Burton" wrote:

1st sheets is an introduction, when macros are enabled it takes you too the
rest of the sheet (that was hidden). People work on the rest of the sheet
then quit, the document then takes the user back to the introduction page
then asks to save.

Problem is if they click save whilst working and dont save again when they
quit at the introduction page, it gets saved in the middle of the sheets that
arnt ment to be shown until macros are enabled at the introduction sheet.

Does that make sense?

I dont want them to be able to save until they get taken back to the
introduction page.

"Mike H" wrote:

Hi,

I assume this is to ensure that this sheet is the one visible on opening. If
so it would be easier to simply select it when the workbook opens.

Alt+f11 to open VB editor double click 'this workbook' and paste this in on
the right

Private Sub Workbook_Open()
Sheets("Introduction").Select
End Sub


Mike


"Mr. Burton" wrote:

Hi I have asked a couple of questions on here and you've helped greatly,
here's 1 mo

I want my document able to only save on 1 page ("introduction" sheet).

I dont want other people who use the document to be able to save on any of
the other sheets.

Other words, I want them to have to go back to the "Introduction" sheet in
order to save.

Thanks guys

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Saving in 1 worksheet only

I would let them save as many times as they wanted but when they close the
workbook, hide the sheetsexcept the Introduction sheet and save in that view
through code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
With ActiveWorkbook
For Each ws In .Worksheets
If ws.Name < "Introduction" Then
ws.Visible = xlVeryHidden
End If
Next ws
.Save
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 13 Oct 2008 05:42:01 -0700, Mr. Burton
wrote:

1st sheets is an introduction, when macros are enabled it takes you too the
rest of the sheet (that was hidden). People work on the rest of the sheet
then quit, the document then takes the user back to the introduction page
then asks to save.

Problem is if they click save whilst working and dont save again when they
quit at the introduction page, it gets saved in the middle of the sheets that
arnt ment to be shown until macros are enabled at the introduction sheet.

Does that make sense?

I dont want them to be able to save until they get taken back to the
introduction page.

"Mike H" wrote:

Hi,

I assume this is to ensure that this sheet is the one visible on opening. If
so it would be easier to simply select it when the workbook opens.

Alt+f11 to open VB editor double click 'this workbook' and paste this in on
the right

Private Sub Workbook_Open()
Sheets("Introduction").Select
End Sub


Mike


"Mr. Burton" wrote:

Hi I have asked a couple of questions on here and you've helped greatly,
here's 1 mo

I want my document able to only save on 1 page ("introduction" sheet).

I dont want other people who use the document to be able to save on any of
the other sheets.

Other words, I want them to have to go back to the "Introduction" sheet in
order to save.

Thanks guys




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default Saving in 1 worksheet only

I need people to be able to quit without saving. I have the very hidden
sheets setup as when ppl enter data into an empty cell it locks the cell so
other ppl can not tamper with it.
If some 1 makes a mistake and doesnt want the infomation they entered they
must close the document without saving.
So they need to be able to close without saving, therefore an auto save
feature when they close would not work.
My problem is if they save whilst viewing the very hidden sheets, then close
without saving, when the document is re-opened it goes straight to the hidden
sheets without enabling macros so the auto cell locking macro will not run.

"Gord Dibben" wrote:

I would let them save as many times as they wanted but when they close the
workbook, hide the sheetsexcept the Introduction sheet and save in that view
through code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
With ActiveWorkbook
For Each ws In .Worksheets
If ws.Name < "Introduction" Then
ws.Visible = xlVeryHidden
End If
Next ws
.Save
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 13 Oct 2008 05:42:01 -0700, Mr. Burton
wrote:

1st sheets is an introduction, when macros are enabled it takes you too the
rest of the sheet (that was hidden). People work on the rest of the sheet
then quit, the document then takes the user back to the introduction page
then asks to save.

Problem is if they click save whilst working and dont save again when they
quit at the introduction page, it gets saved in the middle of the sheets that
arnt ment to be shown until macros are enabled at the introduction sheet.

Does that make sense?

I dont want them to be able to save until they get taken back to the
introduction page.

"Mike H" wrote:

Hi,

I assume this is to ensure that this sheet is the one visible on opening. If
so it would be easier to simply select it when the workbook opens.

Alt+f11 to open VB editor double click 'this workbook' and paste this in on
the right

Private Sub Workbook_Open()
Sheets("Introduction").Select
End Sub


Mike


"Mr. Burton" wrote:

Hi I have asked a couple of questions on here and you've helped greatly,
here's 1 mo

I want my document able to only save on 1 page ("introduction" sheet).

I dont want other people who use the document to be able to save on any of
the other sheets.

Other words, I want them to have to go back to the "Introduction" sheet in
order to save.

Thanks guys



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default Saving in 1 worksheet only

Hi, I tried the last Macro and it didnt disable saving. I want them to have
the option to save when quiting but thats all.

"Mr. Burton" wrote:

I need people to be able to quit without saving. I have the very hidden
sheets setup as when ppl enter data into an empty cell it locks the cell so
other ppl can not tamper with it.
If some 1 makes a mistake and doesnt want the infomation they entered they
must close the document without saving.
So they need to be able to close without saving, therefore an auto save
feature when they close would not work.
My problem is if they save whilst viewing the very hidden sheets, then close
without saving, when the document is re-opened it goes straight to the hidden
sheets without enabling macros so the auto cell locking macro will not run.

"Gord Dibben" wrote:

I would let them save as many times as they wanted but when they close the
workbook, hide the sheetsexcept the Introduction sheet and save in that view
through code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
With ActiveWorkbook
For Each ws In .Worksheets
If ws.Name < "Introduction" Then
ws.Visible = xlVeryHidden
End If
Next ws
.Save
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 13 Oct 2008 05:42:01 -0700, Mr. Burton
wrote:

1st sheets is an introduction, when macros are enabled it takes you too the
rest of the sheet (that was hidden). People work on the rest of the sheet
then quit, the document then takes the user back to the introduction page
then asks to save.

Problem is if they click save whilst working and dont save again when they
quit at the introduction page, it gets saved in the middle of the sheets that
arnt ment to be shown until macros are enabled at the introduction sheet.

Does that make sense?

I dont want them to be able to save until they get taken back to the
introduction page.

"Mike H" wrote:

Hi,

I assume this is to ensure that this sheet is the one visible on opening. If
so it would be easier to simply select it when the workbook opens.

Alt+f11 to open VB editor double click 'this workbook' and paste this in on
the right

Private Sub Workbook_Open()
Sheets("Introduction").Select
End Sub


Mike


"Mr. Burton" wrote:

Hi I have asked a couple of questions on here and you've helped greatly,
here's 1 mo

I want my document able to only save on 1 page ("introduction" sheet).

I dont want other people who use the document to be able to save on any of
the other sheets.

Other words, I want them to have to go back to the "Introduction" sheet in
order to save.

Thanks guys



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Saving in 1 worksheet only

I guess I don't fully understand the requirements and why it matters if they
save before closing as long as the Before_Close event hides the sheets when
they do close.

Isn't the idea to make sure that when they close the sheets get hidden?

You don't want them to be able to save before closing, right?

But you want them to be able to save when closing, right?

Also have the option to close without saving, right?

And have the sheets hidden when they close, whether or not they save, right?

Maybe you should hide the sheets except for the introduction sheet when the
workbook opens then it wouldn't matter in what state they saved or closed.

Private Sub Workbook_Open()
Dim ws As Worksheet
With ActiveWorkbook
For Each ws In .Worksheets
If ws.Name < "Introduction" Then
ws.Visible = xlVeryHidden
End If
Next ws
End With
End Sub


Gord


On Fri, 17 Oct 2008 05:59:00 -0700, Mr. Burton
wrote:

Hi, I tried the last Macro and it didnt disable saving. I want them to have
the option to save when quiting but thats all.

"Mr. Burton" wrote:

I need people to be able to quit without saving. I have the very hidden
sheets setup as when ppl enter data into an empty cell it locks the cell so
other ppl can not tamper with it.
If some 1 makes a mistake and doesnt want the infomation they entered they
must close the document without saving.
So they need to be able to close without saving, therefore an auto save
feature when they close would not work.
My problem is if they save whilst viewing the very hidden sheets, then close
without saving, when the document is re-opened it goes straight to the hidden
sheets without enabling macros so the auto cell locking macro will not run.

"Gord Dibben" wrote:

I would let them save as many times as they wanted but when they close the
workbook, hide the sheetsexcept the Introduction sheet and save in that view
through code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
With ActiveWorkbook
For Each ws In .Worksheets
If ws.Name < "Introduction" Then
ws.Visible = xlVeryHidden
End If
Next ws
.Save
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 13 Oct 2008 05:42:01 -0700, Mr. Burton
wrote:

1st sheets is an introduction, when macros are enabled it takes you too the
rest of the sheet (that was hidden). People work on the rest of the sheet
then quit, the document then takes the user back to the introduction page
then asks to save.

Problem is if they click save whilst working and dont save again when they
quit at the introduction page, it gets saved in the middle of the sheets that
arnt ment to be shown until macros are enabled at the introduction sheet.

Does that make sense?

I dont want them to be able to save until they get taken back to the
introduction page.

"Mike H" wrote:

Hi,

I assume this is to ensure that this sheet is the one visible on opening. If
so it would be easier to simply select it when the workbook opens.

Alt+f11 to open VB editor double click 'this workbook' and paste this in on
the right

Private Sub Workbook_Open()
Sheets("Introduction").Select
End Sub


Mike


"Mr. Burton" wrote:

Hi I have asked a couple of questions on here and you've helped greatly,
here's 1 mo

I want my document able to only save on 1 page ("introduction" sheet).

I dont want other people who use the document to be able to save on any of
the other sheets.

Other words, I want them to have to go back to the "Introduction" sheet in
order to save.

Thanks guys



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default Saving in 1 worksheet only

Would it be best if i send you the file and tell you the problem then you
could suggest some ideas.

Its not that complicated, just trickly to explain.

"Gord Dibben" wrote:

I guess I don't fully understand the requirements and why it matters if they
save before closing as long as the Before_Close event hides the sheets when
they do close.

Isn't the idea to make sure that when they close the sheets get hidden?

You don't want them to be able to save before closing, right?

But you want them to be able to save when closing, right?

Also have the option to close without saving, right?

And have the sheets hidden when they close, whether or not they save, right?

Maybe you should hide the sheets except for the introduction sheet when the
workbook opens then it wouldn't matter in what state they saved or closed.

Private Sub Workbook_Open()
Dim ws As Worksheet
With ActiveWorkbook
For Each ws In .Worksheets
If ws.Name < "Introduction" Then
ws.Visible = xlVeryHidden
End If
Next ws
End With
End Sub


Gord


On Fri, 17 Oct 2008 05:59:00 -0700, Mr. Burton
wrote:

Hi, I tried the last Macro and it didnt disable saving. I want them to have
the option to save when quiting but thats all.

"Mr. Burton" wrote:

I need people to be able to quit without saving. I have the very hidden
sheets setup as when ppl enter data into an empty cell it locks the cell so
other ppl can not tamper with it.
If some 1 makes a mistake and doesnt want the infomation they entered they
must close the document without saving.
So they need to be able to close without saving, therefore an auto save
feature when they close would not work.
My problem is if they save whilst viewing the very hidden sheets, then close
without saving, when the document is re-opened it goes straight to the hidden
sheets without enabling macros so the auto cell locking macro will not run.

"Gord Dibben" wrote:

I would let them save as many times as they wanted but when they close the
workbook, hide the sheetsexcept the Introduction sheet and save in that view
through code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
With ActiveWorkbook
For Each ws In .Worksheets
If ws.Name < "Introduction" Then
ws.Visible = xlVeryHidden
End If
Next ws
.Save
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 13 Oct 2008 05:42:01 -0700, Mr. Burton
wrote:

1st sheets is an introduction, when macros are enabled it takes you too the
rest of the sheet (that was hidden). People work on the rest of the sheet
then quit, the document then takes the user back to the introduction page
then asks to save.

Problem is if they click save whilst working and dont save again when they
quit at the introduction page, it gets saved in the middle of the sheets that
arnt ment to be shown until macros are enabled at the introduction sheet.

Does that make sense?

I dont want them to be able to save until they get taken back to the
introduction page.

"Mike H" wrote:

Hi,

I assume this is to ensure that this sheet is the one visible on opening. If
so it would be easier to simply select it when the workbook opens.

Alt+f11 to open VB editor double click 'this workbook' and paste this in on
the right

Private Sub Workbook_Open()
Sheets("Introduction").Select
End Sub


Mike


"Mr. Burton" wrote:

Hi I have asked a couple of questions on here and you've helped greatly,
here's 1 mo

I want my document able to only save on 1 page ("introduction" sheet).

I dont want other people who use the document to be able to save on any of
the other sheets.

Other words, I want them to have to go back to the "Introduction" sheet in
order to save.

Thanks guys




  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Saving in 1 worksheet only

Send it on to my email

Change the AT and DOT


Gord

On Fri, 17 Oct 2008 12:37:02 -0700, Mr. Burton
wrote:

Would it be best if i send you the file and tell you the problem then you
could suggest some ideas.

Its not that complicated, just trickly to explain.

"Gord Dibben" wrote:

I guess I don't fully understand the requirements and why it matters if they
save before closing as long as the Before_Close event hides the sheets when
they do close.

Isn't the idea to make sure that when they close the sheets get hidden?

You don't want them to be able to save before closing, right?

But you want them to be able to save when closing, right?

Also have the option to close without saving, right?

And have the sheets hidden when they close, whether or not they save, right?

Maybe you should hide the sheets except for the introduction sheet when the
workbook opens then it wouldn't matter in what state they saved or closed.

Private Sub Workbook_Open()
Dim ws As Worksheet
With ActiveWorkbook
For Each ws In .Worksheets
If ws.Name < "Introduction" Then
ws.Visible = xlVeryHidden
End If
Next ws
End With
End Sub


Gord


On Fri, 17 Oct 2008 05:59:00 -0700, Mr. Burton
wrote:

Hi, I tried the last Macro and it didnt disable saving. I want them to have
the option to save when quiting but thats all.

"Mr. Burton" wrote:

I need people to be able to quit without saving. I have the very hidden
sheets setup as when ppl enter data into an empty cell it locks the cell so
other ppl can not tamper with it.
If some 1 makes a mistake and doesnt want the infomation they entered they
must close the document without saving.
So they need to be able to close without saving, therefore an auto save
feature when they close would not work.
My problem is if they save whilst viewing the very hidden sheets, then close
without saving, when the document is re-opened it goes straight to the hidden
sheets without enabling macros so the auto cell locking macro will not run.

"Gord Dibben" wrote:

I would let them save as many times as they wanted but when they close the
workbook, hide the sheetsexcept the Introduction sheet and save in that view
through code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
With ActiveWorkbook
For Each ws In .Worksheets
If ws.Name < "Introduction" Then
ws.Visible = xlVeryHidden
End If
Next ws
.Save
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 13 Oct 2008 05:42:01 -0700, Mr. Burton
wrote:

1st sheets is an introduction, when macros are enabled it takes you too the
rest of the sheet (that was hidden). People work on the rest of the sheet
then quit, the document then takes the user back to the introduction page
then asks to save.

Problem is if they click save whilst working and dont save again when they
quit at the introduction page, it gets saved in the middle of the sheets that
arnt ment to be shown until macros are enabled at the introduction sheet.

Does that make sense?

I dont want them to be able to save until they get taken back to the
introduction page.

"Mike H" wrote:

Hi,

I assume this is to ensure that this sheet is the one visible on opening. If
so it would be easier to simply select it when the workbook opens.

Alt+f11 to open VB editor double click 'this workbook' and paste this in on
the right

Private Sub Workbook_Open()
Sheets("Introduction").Select
End Sub


Mike


"Mr. Burton" wrote:

Hi I have asked a couple of questions on here and you've helped greatly,
here's 1 mo

I want my document able to only save on 1 page ("introduction" sheet).

I dont want other people who use the document to be able to save on any of
the other sheets.

Other words, I want them to have to go back to the "Introduction" sheet in
order to save.

Thanks guys







  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default Saving in 1 worksheet only

Hi, here is my document (Office 2007 Only).

http://www.savefile.com/files/1848919

They have been made to book out computer and laptop suites.
Trouble is, some people delete others booked sessions and put their own in.

So I have a macro that locks a cell after data is entered into the cell. As
the sheet is protected anybody trying to delete it afterwards cannot.

Trouble was they would have to enable macros to use the macro. That's like
asking a thief to leave his address when he robs a bank.
So the important sheets are veryhidden & I have another macro that unhides
them when you enable macros. Therefore they cannot access the other sheets
until macros are enabled.

My last problem is that if they enable macros then save when they on the
unhidden sheets & save, then quit without saving they document stays on the
veryhidden sheets. Which is not good as the macros are not enabled.

Gord has tried but been unable to assist and could some other kind soul have
a look.Thanks

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default Saving in 1 worksheet only

I had a reply to a different post "Disabling saving" by "Alan Moseley" that
solved this problem.

Try this. In the VBA editor add a new Class Module called Class1. Insert
the following code into it:-

Dim WithEvents MyExcel As Application
Dim booAllowSave As Boolean
Private Sub Class_Initialize()
Set MyExcel = Application
booAllowSave = False
End Sub
Private Sub Class_Terminate()
Set MyExcel = Nothing
End Sub
Private Sub MyExcel_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
booAllowSave = False
End Sub
Private Sub MyExcel_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As Boolean, Cancel As Boolean)
If booAllowSave = False Then
Cancel = True
End If
End Sub

Now insert a new module and add the following code into it:-

Dim MyClass As Class1
Public Sub Auto_Open()

This works perfectly great work Alan!!!!

And thanks to every1 else who has helpout too.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Saving a Worksheet Sibbs Excel Discussion (Misc queries) 3 February 4th 08 02:39 PM
Saving a worksheet JONESAROB Excel Discussion (Misc queries) 2 August 3rd 06 07:59 PM
Saving Worksheet Bobby28 Excel Discussion (Misc queries) 2 April 20th 05 09:55 PM
worksheet saving DYeomans Excel Discussion (Misc queries) 1 April 20th 05 08:04 AM
Saving a Worksheet GoBucks Excel Discussion (Misc queries) 3 January 27th 05 08:37 PM


All times are GMT +1. The time now is 03:08 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"