Order for VBA

N

nobbyknownowt

Is there any specific order that VBA should be put? I have a workbook
which I want users not to be able to save. I have code to hide the
sheets and leave a prompt sheet to make users enable macros and i also
have code to not allow saves. They both work individually but when i
put them together however the hide sheet code fails.

Have put the code below
Could someone set me on the correct road please? Thanks
Nobby


Option Explicit


Private Sub Workbook_Open()

With Application
'disable the ESC key
..EnableCancelKey = xlDisabled
..ScreenUpdating = False

Call UnhideSheets

..ScreenUpdating = True
're-enable ESC key
..EnableCancelKey = xlInterrupt
End With

End Sub
'
Private Sub UnhideSheets()
'
Dim Sheet As Object
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next
'
Sheets("Prompt").Visible = xlSheetVeryHidden
'
Set Sheet = Nothing
ActiveWorkbook.Saved = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
..EnableCancelKey = xlDisabled
..ScreenUpdating = False

Call HideSheets

..ScreenUpdating = True
..EnableCancelKey = xlInterrupt
End With
End Sub

Private Sub HideSheets()
'
Dim Sheet As Object '< Includes
worksheets and chartsheets
'
With Sheets("Prompt")
'
'the hiding of the sheets constitutes a change that
generates
'an automatic "Save?" prompt, so IF the book has already
'been saved prior to this point, the next line and the
lines
'relating to .[A100] below bypass the "Save?" dialog...
If ThisWorkbook.Saved = True Then .[A100] = "Saved"
'
..Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
'
If .[A100] = "Saved" Then
..[A100].ClearContents
ThisWorkbook.Save
End If
'
Set Sheet = Nothing
End With
'
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
MsgBox "You cannot save this!"
End Sub
 
B

Bob Phillips

This code worked for me. You need to put it all in the ThosWorkbook code
module.


Private Sub Workbook_Open()

With Application
'disable the ESC key
.EnableCancelKey = xlDisabled
.ScreenUpdating = False

Call UnhideSheets

.ScreenUpdating = True
're-enable ESC key
.EnableCancelKey = xlInterrupt
End With

End Sub
'
Private Sub UnhideSheets()
'
Dim Sheet As Object
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next
'
Sheets("Prompt").Visible = xlSheetVeryHidden
'
Set Sheet = Nothing
ActiveWorkbook.Saved = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False

Call HideSheets

.ScreenUpdating = True
.EnableCancelKey = xlInterrupt
End With
End Sub

Private Sub HideSheets()
'
Dim Sheet As Object '< Includes worksheets and chartsheets
'
With Sheets("Prompt")
'
'the hiding of the sheets constitutes a
'change that generates an automatic "Save?" _
'prompt, so IF the book has already been
'saved prior to this point, the next line and the Lines
'relating to .[A100] below bypass the "Save?" dialog...
If ThisWorkbook.Saved = True Then .[A100] = "Saved"
'
.Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
'
If .[A100] = "Saved" Then
[A100].ClearContents
ThisWorkbook.Save
End If
'
Set Sheet = Nothing
End With
'
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
' Cancel = True
' MsgBox "You cannot save this!"
End Sub




--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



nobbyknownowt said:
Is there any specific order that VBA should be put? I have a workbook
which I want users not to be able to save. I have code to hide the
sheets and leave a prompt sheet to make users enable macros and i also
have code to not allow saves. They both work individually but when i
put them together however the hide sheet code fails.

Have put the code below
Could someone set me on the correct road please? Thanks
Nobby


Option Explicit


Private Sub Workbook_Open()

With Application
'disable the ESC key
EnableCancelKey = xlDisabled
ScreenUpdating = False

Call UnhideSheets

ScreenUpdating = True
're-enable ESC key
EnableCancelKey = xlInterrupt
End With

End Sub
'
Private Sub UnhideSheets()
'
Dim Sheet As Object
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next
'
Sheets("Prompt").Visible = xlSheetVeryHidden
'
Set Sheet = Nothing
ActiveWorkbook.Saved = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
EnableCancelKey = xlDisabled
ScreenUpdating = False

Call HideSheets

ScreenUpdating = True
EnableCancelKey = xlInterrupt
End With
End Sub

Private Sub HideSheets()
'
Dim Sheet As Object '< Includes
worksheets and chartsheets
'
With Sheets("Prompt")
'
'the hiding of the sheets constitutes a change that
generates
'an automatic "Save?" prompt, so IF the book has already
'been saved prior to this point, the next line and the
lines
'relating to .[A100] below bypass the "Save?" dialog...
If ThisWorkbook.Saved = True Then .[A100] = "Saved"
'
Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
'
If .[A100] = "Saved" Then
[A100].ClearContents
ThisWorkbook.Save
End If
'
Set Sheet = Nothing
End With
'
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
MsgBox "You cannot save this!"
End Sub
Click to expand...
 
B

Bob Phillips

This code worked for me. You need to put it all in the ThosWorkbook code
module.


Private Sub Workbook_Open()

With Application
'disable the ESC key
.EnableCancelKey = xlDisabled
.ScreenUpdating = False

Call UnhideSheets

.ScreenUpdating = True
're-enable ESC key
.EnableCancelKey = xlInterrupt
End With

End Sub
'
Private Sub UnhideSheets()
'
Dim Sheet As Object
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next
'
Sheets("Prompt").Visible = xlSheetVeryHidden
'
Set Sheet = Nothing
ActiveWorkbook.Saved = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False

Call HideSheets

.ScreenUpdating = True
.EnableCancelKey = xlInterrupt
End With
End Sub

Private Sub HideSheets()
'
Dim Sheet As Object '< Includes worksheets and chartsheets
'
With Sheets("Prompt")
'
'the hiding of the sheets constitutes a
'change that generates an automatic "Save?" _
'prompt, so IF the book has already been
'saved prior to this point, the next line and the Lines
'relating to .[A100] below bypass the "Save?" dialog...
If ThisWorkbook.Saved = True Then .[A100] = "Saved"
'
.Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
'
If .[A100] = "Saved" Then
[A100].ClearContents
ThisWorkbook.Save
End If
'
Set Sheet = Nothing
End With
'
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
' Cancel = True
' MsgBox "You cannot save this!"
End Sub




--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



nobbyknownowt said:
Is there any specific order that VBA should be put? I have a workbook
which I want users not to be able to save. I have code to hide the
sheets and leave a prompt sheet to make users enable macros and i also
have code to not allow saves. They both work individually but when i
put them together however the hide sheet code fails.

Have put the code below
Could someone set me on the correct road please? Thanks
Nobby


Option Explicit


Private Sub Workbook_Open()

With Application
'disable the ESC key
EnableCancelKey = xlDisabled
ScreenUpdating = False

Call UnhideSheets

ScreenUpdating = True
're-enable ESC key
EnableCancelKey = xlInterrupt
End With

End Sub
'
Private Sub UnhideSheets()
'
Dim Sheet As Object
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next
'
Sheets("Prompt").Visible = xlSheetVeryHidden
'
Set Sheet = Nothing
ActiveWorkbook.Saved = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
EnableCancelKey = xlDisabled
ScreenUpdating = False

Call HideSheets

ScreenUpdating = True
EnableCancelKey = xlInterrupt
End With
End Sub

Private Sub HideSheets()
'
Dim Sheet As Object '< Includes
worksheets and chartsheets
'
With Sheets("Prompt")
'
'the hiding of the sheets constitutes a change that
generates
'an automatic "Save?" prompt, so IF the book has already
'been saved prior to this point, the next line and the
lines
'relating to .[A100] below bypass the "Save?" dialog...
If ThisWorkbook.Saved = True Then .[A100] = "Saved"
'
Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
'
If .[A100] = "Saved" Then
[A100].ClearContents
ThisWorkbook.Save
End If
'
Set Sheet = Nothing
End With
'
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
MsgBox "You cannot save this!"
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Making a macro only work on certain files 2
VBA Coding Help for Beginner 0
Word macro that can call cells out of Excel for a find and replace 0
VBA word change keys 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Error message 4
VBA for Dependents shortcut menu 0
Unhide sheet, copy and rename new sheets from list, rehide sheet 8

Top