Force user to enable macros

[fcarboni]

Hi,
To organize the colleague holidays into my offices, I want to use one xls file.
The user can type 4 or 8 h in a calendar day only when into her office two people not will be in holiday in the same time.
Ex: quality office 6 people, only the first two gay that reserve a same day can going in holiday, the 3th can't!!.
But in the 1th case VBA run and my control too, in the 2th not, but any case operator can input reserve a holidays day
I try to use the Ken Plus solution: vbaexpress.com/kb/getarticle.php?kb_id=379
but this app conflicts with my checks.
My file is here fcarboni.com/forum/holiday2012.xls, somebody can help me?.
1th sorry for my bad english 2th thank any case
Fabrizio

[mperrah]

I had a project that required macros enabled on open.
I found help here.
They suggested make a sheet that is visible on open that says "If you are reading this your macros are disabled," then instruct how to enable them.
and in vba write the script that hides the sheet on open.
Then if macros are enabled they never see that sheet.


Option Explicit

Private Sub Workbook_Open()
Call CreateMenu

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


Call UnhideSheets

Dim xSheet As Worksheet
For Each xSheet In ThisWorkbook.Sheets
With xSheet
.protect _
DrawingObjects:=.ProtectDrawingObjects, _
Contents:=.ProtectContents, _
UserInterFaceOnly:=True
End With
Next xSheet

With Application
.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" _
And Not Sheet.Name = "library" _
And Not Sheet.Name = "MenuSheet" Then
Sheet.Visible = xlSheetVisible
End If
Next
'
Sheets("Prompt").Visible = xlSheetVeryHidden
'
Application.GoTo Worksheets(1).[a1], True '< Optional
'
Set Sheet = Nothing
ActiveWorkbook.Saved = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Turn off events to prevent unwanted loops
Application.EnableEvents = False
'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
.Calculation = xlCalculationAutomatic

Call DeleteMenu
Call HideSheets

.ScreenUpdating = True
.EnableCancelKey = xlInterrupt
ThisWorkbook.Save
End With
Case Is = vbNo

'Do not save
Case Is = vbCancel

'Set up procedure to cancel close
Cancel = True
End Select
End If
'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
Application.EnableEvents = True
.Saved = True
If Not Workbooks.Count > 1 Then Application.Quit
Else
Application.EnableEvents = True
End If
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

[mperrah]

the sheet titled "Prompt" has this:


(not code, just text on the sheet...
If Opening this file:
You must have macros enabled
(Go to: tools -> macro -> security -> medium
then close and re-open the workbook)
When file re-opens, at prompt click enable macros

[fcarboni]

Mperrah,
your code is like to my,
But into my xls file, I've more controll, and Application.EnableEvents generate conflict!!
if you want you can see the xls, the link download is on my 1th post.

[Aussiebear]

The link download is not in your first post. Go to Go Advanced, scroll down to Manage Attachments and follow the prompts from there.

[fcarboni]

Sorry,
there's the correct link,
I'm a newby and i can't post any link
w w w.fcarboni.com/forum/Cartel1.xlsm

[fcarboni]

nobody can help me??
I think is not normal that one control disable the other.?!?!?!?
www.fcarboni.com/forum/holiday2012.xls (http://www.fcarboni.com/forum/holiday2012.xls)

I'll be crazy!!
the macros that hidden sheet planning and showing sheet macros don't run if the user digit something into planning sheet.
WHY???