Before Save VBA Problem

Hannah24

New Member
Joined
Jan 11, 2019
Messages
8
Hi,

I have written the before save VBA which prevents someone from saving the workbook when certain cells are left blank.

I want to use this as a template and to save the workbook with the cells blank however, when any other user opens and tries to save, I want my message to appear and them to be unable to save. How do I amend my VBA to allow me to save as the editor ONLY?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If WorksheetFunction.CountA(Worksheets("Sheet1").Range("E1,D7,C5,D11,X4,D15")) < 6 Then
MsgBox "Workbook will not be saved unless" & vbCrLf & _
"all required fields have been filled in!"
Cancel = True
End If
End Sub

Is anyone able to assit?

Many thanks,

Hannah
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
How do I amend my VBA to allow me to save as the editor ONLY?

You can protect your workbook with a password.
I use this to never allow a user to save even when pressing the red x at the top right
this is used in my "ThisWorkbook" Module

VBA Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Password As String
Dim EnteredPassword As String

Password = "BlakeSkate"
EnteredPassword = InputBox("Enter password to save changes")
If EnteredPassword <> Password Then
Cancel = True
MsgBox ("Password incorrect, file not saved")
End If

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    ThisWorkbook.Saved = True

End Sub
 

Hannah24

New Member
Joined
Jan 11, 2019
Messages
8
Hi @DataBlake,

Thank you for this, but I do not believe this is exactly what I am looking for.

My workbook is a Template with fields that I need individuals to complete. The VBA I have written is to ensure that certain (mandatory) cells are completed before they save.

The VBA I have written works as expected, as in the document cannot be saved with those cells remaining blank. However, it is working little too well, as it will not let me save the template with those cells blank. I (as the editor) need to be able to save the template with those cells remaining blank, so that when other users open to complete it, the cells are originally blank and the VBA will work if they leave them blank, when they themselves try to save.

Does this make sense?

Thanks,

Hannah
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
I (as the editor) need to be able to save the template with those cells remaining blank

ah that makes sense. then you can make use of a shortcut like this:

VBA Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
if Application.UserName = "Your User Name" then goto exx


If WorksheetFunction.CountA(Worksheets("Sheet1").Range("E1,D7,C5,D11,X4,D15")) < 6 Then
MsgBox "Workbook will not be saved unless" & vbCrLf & _
"all required fields have been filled in!"
Cancel = True
End If

exx:
End Sub

if you need to determine what your application username is then use this macro standalone

VBA Code:
Sub Appl_UserNamel()
    MsgBox Application.UserName
End Sub
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

or maybe

VBA Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If WorksheetFunction.CountA(Worksheets("Sheet1").Range("E1,D7,C5,D11,X4,D15")) < 6 Then
MsgBox "Workbook will not be saved unless" & vbCrLf & _
"all required fields have been filled in!"
Cancel = True
End If

if Application.UserName = "Your User Name" then Cancel = False
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,697
Messages
5,637,873
Members
416,986
Latest member
zmartee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top