ENABLE FILE SAVE & REMAIN IN THE SAME WORKSHEET

[cosmicsyzygy]

Hello There

I am having a slight problem with my VBA code and welcome any suggestios...

Application Info:
Window XP Pro with SP2
Microsoft Excel 2003 SP2

Scenario:
I want to force users to enable macros when using the spreadsheet.
I need to disable the save as option.

Spreadsheet Details:
2 worksheet in the spreadsheet.
Sheet 1 contains a message advising the user to enable macros to view file in the event thy have opted to disable them.
Sheet 2 contains my spreadsheet data where users are required to input data in specific cells.What's Working Right Now:
The code is forcing the user to exit Excel and re-open and enable macros.
The save as function is disabled.What I Need Help With!!
After inputting data in Sheet2, I save the file. The file saves, however, it then goes to Sheet1 (sheet that contain instructions to enable macros) and Sheet2 with updated data cannot be viewed or selected as it seems to be hidden. At this point, I have to exit Excel & re-open the file to view/print the changes on Sheet2.

I would like to save the changes and remain in the same worksheet as I may want to print or add more data without having to exit and re-open the file.


Private Const dsWarningSheet As String = "sheet1" 'Enter name of the Entry/Warning Page
Private Sub Workbook_Beforesave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For Each ds In ActiveWorkbook.Sheets
If LCase(dsWarningSheet) = LCase(ds.Name) Then
ds.Visible = True
Else: ds.Visible = xlVeryHidden
End If
Next
If SaveAsUI Then
Cancel = True
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal ds As Object, ByVal Target As Excel.Range)
If LCase(ds.Name) = LCase(dsWarningSheet) Then
For Each ds In ActiveWorkbook.Sheets
ds.Visible = True
Next
ActiveSheet.Visible = xlVeryHidden
End If
If SaveAsUI Then
Cancel = True
End If
End Sub

Private Sub workbook_open()
Sheets(dsWarningSheet).Select
For Each ds In ActiveWorkbook.Sheets
ds.Visible = True
Next
ActiveSheet.Visible = xlVeryHidden
End Sub

Thanks in advance!
CosmicSyzygy

[lucas]

Hi,
at a glance it looks like your hiding everything except sheet 1 when a save is executed:
Private Sub Workbook_Beforesave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For Each ds In ActiveWorkbook.Sheets
If LCase(dsWarningSheet) = LCase(ds.Name) Then
ds.Visible = True
Else: ds.Visible = xlVeryHidden
End If
Next
If SaveAsUI Then
Cancel = True
End If
End Sub


I think....I would move this code to a workbook beforeclose event and do away with the before save event....

[unmarkedhelicopter]

I don't see what your selection change code is doing that you need done :bug:
Your before save code is nearly right, if it IS SaveAsUI then you can set cancel and exit sub.
If it is not you need to :-
1. record which sheet you are on
2. hide 'some' sheets
3. save workbook
4. unhide 'some' sheets
5. activate the sheet recorded in 1.

[unmarkedhelicopter]

No, if you do that and they save then the next time they open the book and disable macros you get full access.

[cosmicsyzygy]

I don't see what your selection change code is doing that you need done :bug:
Your before save code is nearly right, if it IS SaveAsUI then you can set cancel and exit sub.
If it is not you need to :-
1. record which sheet you are on
2. hide 'some' sheets
3. save workbook
4. unhide 'some' sheets
5. activate the sheet recorded in 1.
Hi

Thanks for responding. I need to be able to save the changes made on sheet2. Once I save changes, I need to stay in sheet2 as sheet1 is only an instruction sheet notifying the user to re-open the file enabling macros and contains no specific data. At this time, when I save the data entered in sheet2, it saves and the cursor is on sheet1. If I click sheet1 after 10 seconds, sheet2 displays. What I want is for it to save the updates and remain on the same sheet (sheet2) until the user chooses to exit the file.

Thanks
Cosmicsyzygy

[unmarkedhelicopter]

Yeah ...
That's what you said the first time so I told you how to do it ...
... do you want me to write the code for you as well ?

[cosmicsyzygy]

Sure I could use the help. I am trying to get this working..... as you can see I am not having any luck. I am a novice with VB so I can use all the help available.

Thanks
CosmicSyzygy