Force User to enable macro and still save

Hi everyone,


I'm currently doing a timesheet module for work and am struggling with a bit of code.

I have found here a bit of code which is perfect for what I want and need done but unfortunately it's interfering with other part of my code.

I am using the following code to make people enable the macro:

Code:

---

Option Explicit

Const WelcomePage = "Macros"

---

Code:

---

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
'Call customized save routine
Call CustomSave
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
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If
End With
End Sub

---

Code:

---

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Call customized save routine and set workbook's saved property to true
'(To cancel regular saving)
Call CustomSave(SaveAsUI)
Cancel = True

'Turn events back on an set saved property to true
Application.EnableEvents = True
ThisWorkbook.Saved = True
End Sub

---

Code:

---

Private Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
End Sub

---

Code:

---

Private Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
'Turn off screen flashing
Application.ScreenUpdating = False

'Record active worksheet
Set aWs = ActiveSheet

'Hide all sheets
Call HideAllSheets

'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If

'Restore file to where user was
Call ShowAllSheets
aWs.Activate

'Restore screen updates
Application.ScreenUpdating = True
End Sub

---

Code:

---

Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet

Worksheets(WelcomePage).Visible = xlSheetVisible

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws

Worksheets(WelcomePage).Activate
End Sub

---

Code:

---

Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws

Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

---

------


When using this, unfortunately, it's then stopping my worksheet from being saved with the name I want it to have.

When not using the veryhidden code it does it by using the following code:

Code:

---

Public Sub Send_Email()
On Error Resume Next
Application.DisplayAlerts = False
MkDir ("C:\Timesheets\")
ActiveWorkbook.SaveAs "C:\Timesheets\" & Range("A3").Value & " " & Replace(Range("B3"), "/", "-") & ".xlsm"

Dim linsOutlookApp As Object
Dim linsMailItem As Object
Dim lstrFileName As String
Dim lstrOldFileName As String
Dim MailAddress As String
Dim CCMailAddress As String
Dim MailSubject As String
Dim EmailComments As Variant

Sheets("Time Entry").Activate
ActiveWorkbook.Save


If MsgBox("Press OK to send email", vbOKCancel, "Email Confirmation") = vbOK Then


Set linsOutlookApp = CreateObject("Outlook.Application")
linsOutlookApp.Session.Logon
Set linsMailItem = linsOutlookApp.CreateItem(0)

linsMailItem.Attachments.Add ActiveWorkbook.FullName

linsMailItem.Recipients.Add "myemail"

linsMailItem.Subject = "Timesheet for " & Range("A3").Value & " for w/c " & (Range("B3").Value)
linsMailItem.Body = "This file was sent automatically from Excel. " & _
linsMailItem.DeleteAfterSubmit = False
linsMailItem.Send

Else: End
End If
End Sub

---

------

I guess what I'm trying to get out of the help is:

1 - I want to keep using my initial code "Veryhidden" so that the users must enable macros to use this spreadsheet other it will not do what's meant to
2 - when I use the "veryhidden" bit of cade, my spreadsheet is using whatever name the spreadsheet has instead of renaming it according to the user that has input it's name and the date.



Could someone please help me tie these two up?

I need the veryhidden code to let my macro save the spreadsheet so it can then be sent with the right name and date.

Thank you for all your help! :)

Regards,

Andre Bessa

I sense that you are in real danger of tying yourself up in knots if you are not careful.
This all looks a bit too complicated and convoluted to me, so let's take a step back and find out what you are trying to achieve (ignoring all code for the moment)

1. In very broad terms what is the user expected to do?
Inputting time into a worksheet?

2. What is it that you are trying to prevent them from doing?
Amending the structure of the worksheet?
Putting invalid entries into the worksheet?

3. What is your concern about saving the file at the end of the process?
Saving the file as the incorrect name?

Is this close to the mark?

Hi,

Thank you so much for your reply Yondle! :)

1. In very broad terms what is the user expected to do?

The user is expected to open the workbook, enable macros e then fill in a timesheet with their name, date, customer name, time and whether this is chargeable work or not.

Inputting time into a worksheet?

Yes, the end-user will also be inputting time into the timesheet.

2. What is it that you are trying to prevent them from doing?

Previously the users have been inputting worng dates, misspelling their own names and various other things. This macro looks to do a spot check before it then send it to an email box with a reference to the name of the person that has filled this in and the date that they have chosen on the first input cell.

Amending the structure of the worksheet?

No, I can prevent this by protecting the sheet.

Putting invalid entries into the worksheet?

Yes, the above mentioned but more specific than just only dates here and text there...

3. What is your concern about saving the file at the end of the process?

The file needs to be saved so that when the user presses the send button, this contains the name of the person and the date (both taken from the spreadsheet the person filled in)

Saving the file as the incorrect name?

Currently it's not saving the file with any name as the "Veryhidden" code is cancelling regular saving and if I turn that off, the veryhidden code stops working.....

HELP!!! :)

A few ideas for you to consider:

Quick Summary
My understanding is that the same master workbook format is used by lots of different users but it takes a unique name when saved by each user every day. There is a concern over the quality of the input, which in turn may affect the name of the file. In fact it is likely that users frequently over-write the masterfile and keep sending the same filename by email (ouch!)

Suggestions
On opening the file you could prevent access to any sheets (hide them) etc until a button is pressed (unhides them), and this will not work until macros are enabled.
Userforms are the best way to tighten the quality of data entry by multiple users. You can make behaviour more predictable and force ALL the boxes to be completed - but you need to spend a bit of time creating them.
If you do not want to create userforms then you can build a lot of control into the worksheets themselves.
Comments below apply equally to userforms or direct input into worksheets.
Obviate a lot of your difficulties by using the power of Excel to monitor input (and prevent invalid data)

Validate data entry - HOW?
Add a (protected and hidden) Control Sheet in the workbook with key validation data such as
- List of user names
- List of customer names
and use dropdown boxes permitting only these values in cells where input required
(But what if a new customer after workbook sent out: drop-down includes CUSTOMER NOT LISTED - and allow entry in cell below where they can type in any name, same approach would apply to a new employee)
- The date box could default to the current day, or perhaps drop-down with word "Today" (which Excel would interpret as today's date), "Yesterday" and a list of all other recent dates to allow input if not done on correct day - here I would ask user enter the date at the beginning of the input and again further down and put an error message to screen if the 2 answers differ - wakes them up!)
- dropdown options Yes/No in "chargeable " box

Time input
- a bit more difficult to control quality of input here, especially if allocating time between various boxes
- start and finish times better than hours worked
- perhaps some idiot checks such as maximum number of hours etc or force user to confirm anything that looks peculiar based on logic checks

Saving the file
- if the user name has been correctly "validated" and the date made as idiot-proof as possible, then the name that is auto-generated should also be valid (most of the time) and by putting the appropriate code into a "before close" sub - this can be used to force the file to be saved in the manner you want with the name you want. You can also prevent the masterfile name being used to save the file - thus making it impossible to overwrite.

Hopefully that has provided some "food for thought".

Attached is a file which illustrate some of the above (the basic stuff) - nothing fancy.
As I said previously, I would use UserForms but for the moment this will give you an idea of how easy it is to improve accuracy

Controls are exercised via mixture of hidden worksheets, data validation checks, and macros to take user to next step. To avoid confusion you need unique user and customer numbers - duplicate name problems otherwise.

Various input boxes have DropDowns - change a few (I have not cleared them out, but the user would just see blanks to start with)
See what happens when you change Customer to New Customer
See what happens when you select any customer other than New Customer and try to type a name in the new customer box

First time just open the file and just follow the instructions (amend a couple of fields) including closing the file (it will fail to save unless you have a D drive, but there is a message box that tells you where it wants to save to, and you can change the path etc after first run!)

This macro (which acts when workbook is opened every time) hides all sheets except one which leaves the user in no doubt what he must do

Code:

---

Private Sub Workbook_Open()
Worksheets("Welcome").Visible = True
Worksheets("DirectEntry").Visible = False
Worksheets("Control").Visible = False
End Sub

---

This macro controls things when user finishes inputting

Code:

---

Sub CloseFile()
Dim FilePath As String
Dim FileName As String
Dim FullPath As String


With Sheets("DirectEntry")
FileName = .Range("B2").Value & " " & .Range("D4").Value & ".xlsm"
End With


FilePath = "D:\Documents\"
FullPath = FilePath & FileName
MsgBox "File will be saved as " & FullPath
ActiveWorkbook.SaveAs FullPath


Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

---

Note that I have not read all the code, questions, or responses in the entire thread. However, in re, Hidden code...

Why not just put all the code possible* in modules, then protect the Project with a password.

*By using Application level Events, you might put all the code in one or more modules.