Order for VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nobbyknownowt - 19 Feb 2007 12:57 GMT

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

Signature

nobbyknownowt

Reply to this Message

Bob Phillips - 19 Feb 2007 19:38 GMT

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

Signature

---
HTH

Bob

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

> 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
[quoted text clipped - 91 lines]
> MsgBox "You cannot save this!"
> End Sub

Reply to this Message

Bob Phillips - 19 Feb 2007 21:14 GMT

Signature

---
HTH

Bob

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

Reply to this Message