cosmicsyzygy
05-31-2007, 12:02 PM
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
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