PDA

View Full Version : Force users to classify documents before close



rajagopal
04-12-2012, 03:06 AM
Hi,

System should force users to classify the excel documents as Internal, Public, Confidential before they close.

1. I want the classification option to be provided as radio button in alert message box
2. User cannot close the message box unless they choose any option
3. If the user closes the inner (x) close button also, the macro should trigger
4. When user classify a excel today as Internal and open the excel tomorrow and close (with or without making changes), then also the alert message box should show.

Simon Lloyd
04-12-2012, 06:05 AM
And how far have you got with all this work yourself?

rajagopal
04-12-2012, 06:13 AM
UserForm:


Private Sub btnSubmit_Click()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Changing footer in " & ws.Name
With ws.PageSetup
If rdoHighlyRestricted.Value = True Then
.LeftFooter = rdoHighlyRestricted.Caption
ElseIf rdoRestricted.Value = True Then
.LeftFooter = rdoRestricted.Caption
ElseIf rdoInternal.Value = True Then
.LeftFooter = rdoInternal.Caption
ElseIf rdoPublic.Value = True Then
.LeftFooter = rdoInternal.Caption
End If
End With
Next ws
Set ws = Nothing
Application.StatusBar = False
Unload FrmClassify
MsgBox "Active Document Classified sucessfully", vbInformation + vbOKOnly

End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
Cancel = True
'MsgBox "The X is disabled, please use a button on the form.", vbCritical
End If
End Sub
*************************************************************************** *********
ThisWorkbook


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim iReply As Integer
iReply = MsgBox("Have you classified the active document?? ", vbQuestion + vbYesNo, "Classify")
If iReply = vbNo Then
'FrmClassify.Repaint

FrmClassify.Show
End If
End Sub

rajagopal
04-17-2012, 04:31 AM
Hi,

Can anyone pls help?

rajagopal
04-19-2012, 11:22 PM
Is there any way by which we can execute a macro across excel without having a macro in temp file using add in?