Preventing Worksheet Deletion without workbook protection

I have a Excel project where there is a workbook with “required” worksheets (users cannot delete them) but users can add / modify other sheets.  So the workbook structure cannot be protected, yet I have to make sure these required worksheets are not deleted.

I am using Excel 2010, which does not have a Worksheet Delete event.  This does exist in Excel 2013, but even that event does not have a Cancel method.

So I needed to develop a solution to handle this issue.  Here are two methods.  The first is fairly complex, but it works.  But then I discovered method 2, which is much cleaner and simpler.  The method 1 approach may still have some value, particularly if you need to create a customized worksheet delete event.

Method 1 – Complex method

Create a customized worksheet delete event

The basic approach is to first recognize if a worksheet has been deleted, basically creating a customized WorksheetDelete Event.  This is done by counting the number of worksheets in the SheetDeactivate Event (storing that value in a global variable) and then performing the same count in the SheetActivate Event.  If the count has decreased, then you know the user deleted a worksheet.  If this worksheet is not allowed to be deleted, then it must be restored.  This could be useful for special processing after a worksheet is deleted.

 
 

Private Sub Workbook_SheetDeactivate(ByVal ws As Object)


'** iWksCountBefore was declared as a global variable    
iWksCountBefore = ActiveWorkbook.Sheets.Count
Private Sub Workbook_SheetActivate(ByVal ws As Object)

iWksCountAfter = ActiveWorkbook.Sheets.Count
If iWksCountAfter < iWksCountBefore Then
'**   The worksheet was deleted - do something
Else
End If
Exit Sub

ErrorHandling:

End Sub

Ok – that part was simple.  The problem was what to do if the user was not allowed to delete that particular worksheet.  How do you restore it?

Restoring the Worksheet

In order to restore the worksheet, some preliminary steps need to be taken.

  1. Back up the worksheet in the WorksheetDeactivate Event. I found that copying the worksheet to another worksheet in the workbook ultimately created issues with Range Name. So instead I copy the worksheet to a temporary workbook.
  2. Store all of the named ranges into a global array. We will need this later
  3. If the “protected” worksheet was deleted, recognize that in the WorksheetActivate Event and copy back the backed up worksheet to this workbook.  (Prior to restoring the worksheet, you need to turn off events (Application.EnableEvents = False) so the restoration does not kick off another Sheet Activate / Deactivate event causing an endless loop)
  4. When the worksheet from the temporary workbook is copied, range names can still refer back to the temp file. In order to clean this up, you need to do 3 things:
    1. Delete all range names in the workbook
    2. Recreate the range names by using the stored Named Range data in the global array (see step 2)
    3. Prior to restoring the worksheet, you need to turn off events (Application.EnableEvents = False) so the restoration does not kick off another Sheet Activate / Deactivate event causing an endless loop
  5. Delete the temporary file

This is a lot of code, kind of clunky but it works.  Go to the bottom of this entry to see the code.

Method 2 – Much more elegant with a lot less code

(credit for this idea must be given to Jan Karel Pieterse – see link:  http://datapigtechnologies.com/blog/index.php/prevent-worksheet-delete-without-workbook-protection/#respond )

In Excel 2013, there is a worksheet delete event but it does not have a cancel method.  In earlier versions, there is no worksheet delete event at all.  In Excel 2010 (and I assume earlier versions), the events occur in the following order for adding and deleting a worksheet:

  1. Worksheet added (runs immediately before the Workbook_SheetDeactivate event)
  2. Workbook_SheetDeactivate Event called
  3. Worksheet Deleted (runs immediately after Workbook_SheetDeactivate but before Workbook_SheetActivate)
  4. Workbook_SheetActivate Event called

Remember, we have Workbook protection off so the user can add worksheets.  This occurs before the Workbook_SheetDeactivate event.  But what about worksheets that a user deletes?  Let’s assume a user tries to delete a required worksheet.  If we can turn Workbook protection on in the Workbook_SheetDeactivate event, the Worksheet Delete cannot occur.  After we have protected the sheet from being deleted, we then need to turn Worksheet Protection off.

So here is the solution.

 
 
Private Sub Workbook_SheetDeactivate (ByVal ws as object)
‘** set the condition here that determines if this particular worksheet is to
‘** be protected from deletion and run the following code if true.
‘  ……….

‘** Step 1:  Turn on protection
ThisWorkbook.Protect, True
‘** Step 2:  Call sub that turns off protection using the Application.OnTime method (I do not yet ‘**understand why this works and a direct sub or function call does not work)
‘**
Application.OnTime Now, “ThisWorkbook.UnprotectBook
End Sub


Public Sub UnprotectBook()
     ThisWorkbook.Unprotect
End Sub

What is happening here?

Step 1 – turns on Workbook protection

Step 2 – calls the UnprotectBook sub.  But before this sub is run, Excel attempts the worksheet delete.  Since the workbook is now protected, the worksheet delete fails and the user gets the system message:

Workbook is protected and cannot be changed

(can anyone figure out how to replace this with a custom message?)

The SheetActivate event is never run because the worksheet deletion did not occur so focus remains on the current worksheet.

Step 3 – now the UnprotectBook sub is run and workbook  protection is turned off.

A much simpler solution.

Method 1 Code

Here is the code for Method 1

Private Sub Workbook_SheetDeactivate(ByVal ws As Object)

Call StoreAllNamedRanges(ActiveWorkbook)
Call CopyWorksheetAsTemp(ws)
iWksCountBefore = ActiveWorkbook.Sheets.Count
Application.EnableEvents = True

End Sub

Private Sub xWorkbook_SheetActivate(ByVal ws As Object)
Dim xlWB_Scorecard As Workbook
Dim xlWB_Temp As Workbook
Dim xlWS_Temp As Worksheet
Dim sCurrentSheetName As String
'** The parameter bDeleteWorksheetProtection" determines if this code will run
'** It is set to false while it is developed.  This is used as a global variable
'** since the range name is lost if the admin worksheet is deleted
Set xlWB_Scorecard = ActiveWorkbook
'MsgBox "activate"

iWksCountAfter = ActiveWorkbook.Sheets.Count

'** If the worksheet count decreased, then a worksheet was deleted
If iWksCountAfter < iWksCountBefore Then
Set xlWB_Temp = Workbooks.Open("Temp1")
Set xlWS_Temp = xlWB_Temp.Worksheets(1)

xlWB_Scorecard.Activate

'** This assumes you want to prevent this worksheet deletion - you need code here to determine that

'** Before copy back the Admin worksheet - delete all the range names / copy the worksheet / then add
'**  the range names back
Call ClearAllNamedRanges(ThisWorkbook)

Application.EnableEvents = False
xlWS_Temp.Copy ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Application.EnableEvents = True

'** The range names need to be cleared again after copying in the Temp worksheet
Call ClearAllNamedRanges(ThisWorkbook)
Call LoadAllNamedRanges(ThisWorkbook)

xlWB_Temp.Close savechanges:=False

Else

End If
If IsFile("Temp1.xlsm") Then
Kill "Temp1.xlsm"
Else
End If

Application.EnableEvents = True
End Sub

'*************

Public Function StoreAllNamedRanges(xlWB As Workbook) As String()

'**  This function stores all named ranges in a workbook into an array
'**  The array is two dimensional -
'**        Dimension 1 - the identifier of the named range
'**        Dimension 2 - the attribute of the array where:
'**         1=name        2= refersto  3= Scope (wb or ws)  4= comment
'************************************************************************

Dim iRangeCount As Integer
Dim i As Integer
Dim rngName As Name

'Set wb = ActiveWorkbook
iRangeCount = xlWB.Names.Count
ReDim sAllRanges(1 To iRangeCount, 1 To 4)

For Each rngName In xlWB.Names
i = i + 1
sAllRanges(i, 1) = rngName.Name
sAllRanges(i, 2) = rngName.RefersTo
If TypeOf rngName.Parent Is Workbook Then
sAllRanges(i, 3) = "wb"
Else
sAllRanges(i, 3) = "ws"
End If
sAllRanges(i, 4) = rngName.Comment
Next rngName

End Function

'***********

Public Sub CopyWorksheetAsTemp(xlWSSource As Worksheet)
'** This sub copies the current scorecard worksheet to a new worksheet
'** and retains the existing data

'enable events are turned off during this process
Dim sMsg As String
Dim iRet As Integer

Application.EnableEvents = False
xlWSSource.Copy
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs "Temp1", FileFormat:=52
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

Public Function ClearAllNamedRanges(xlWB As Workbook) As String()
'**  This function clears all named ranges in a workbook
'************************************************************************

Dim rngName As Name

For Each rngName In xlWB.Names
rngName.Delete
Next rngName

End Function

'********

Public Function LoadAllNamedRanges(xlWB As Workbook) As String()

'**  This function load all named ranges from an array into the specified workbook
'**  The array is two dimensional -
'**        Dimension 1 - the identifier of the named range
'**        Dimension 2 - the attribute of the array where:
'**         1=name        2= refersto  3= Scope (wb or ws)  4= comment
'************************************************************************

Dim iRangeCount As Integer
Dim i As Integer
Dim xlWS As Worksheet
Dim iPosition As Integer
Dim sWorksheet As String

Set xlWS = xlWB.ActiveSheet

'Set wb = ActiveWorkbook

For i = 1 To UBound(sAllRanges, 1)
If sAllRanges(i, 3) = "wb" Then
xlWB.Names.Add Name:=sAllRanges(i, 1), RefersTo:=sAllRanges(i, 2)
Else
'Determine the worksheet for the range
iPosition = InStr(1, sAllRanges(i, 2), "!")
sWorksheet = Mid(sAllRanges(i, 2), 2, iPosition - 2)
'** Replace the apostrophe found in worksheets that have spaces with a null space
sWorksheet = Replace(sWorksheet, "'", "")
Set xlWS = xlWB.Sheets(sWorksheet)

xlWS.Names.Add Name:=sAllRanges(i, 1), RefersTo:=sAllRanges(i, 2)
End If
Next i

End Function

Advertisements
Posted in VBA Code | Leave a comment

Copying Worksheets to Other Workbooks – Shapes causing unwanted links

I have a project that copies a series of worksheets from other files to a consolidation workbook.  After the consolidation workbook is saved and closed, when it is reopened, the following message appears:

This workbook contains links to other data sources     with Update / Don’t Update buttons.

This message is confusing to the users and if they attempt to do an Update, the source file may no longer be available.  So this is what I did:

I determined that there are no links in formulas or in data verification.  I realized that the source worksheets use various Shapes as buttons.  They have a link to the macro.  It appears in Excel 2010, when these worksheets are copied with the shapes with macros, the consolidation workbook creates a link to the source workbook to find the macro.

I was not able to find a way to stop this link from being created, so at the end of the vb code that copies that source file to the consolidation target, I put in a piece of code that does the following:

  • Loops through each worksheet of the consolidation workbook
  • Finds each shape in each of the worksheets
  • Determines if the shape runs a macro
  • If the shape runs a macro (the OnAction option is not null), remove the file reference and update the OnAction for the shape so it only references the local macro.

Here is the code:


Public Sub FixShapes()

Dim ws As Worksheet
Dim shp As Shape
Dim iPosition As Integer
Dim sNewOnAction As String

For Each ws In Worksheets
    For Each shp In ws.Shapes
            If IsNull(shp.OnAction) Then
            Else
                If InStr(1, shp.OnAction, &quot;!&quot;) &gt; 0 Then
                    iPosition = InStr(1, shp.OnAction, &quot;!&quot;) + 1
                    sNewOnAction = Mid(shp.OnAction, iPosition)
                    shp.OnAction = sNewOnAction
                Else
                End If
            End If
    Next shp
Next ws

End Sub



Posted in Excel Tips, VBA Code | Leave a comment

Copying Worksheets from one Workbook to Another – Error 1004

I was working on a project using Excel 2010  where I had to copy entire worksheets from one workbook to another.  I was doing this through some vba code.  When I attempted to save the target file, I would get an error.  The error message was vba 1004 — with a message of:

Errors were detected while saving ‘c:\filefolder\filename.xlsx’.  Microsoft Excel may be able to save the file by removing or repairing some features.

It took quite a bit of research to figure out what was going on.  Luckily, I stumbled onto this KB from Microsoft.  It appears this is caused by a known bug.  You cannot save a workbook that contains an image that was copied from another workbook in Excel 2010 – unless a patch has been applied to Excel.  Here is the link to the KB:

http://support.microsoft.com/kb/2597034

Hope this saves someone some time.

Posted in Excel Tips, VBA Code | Leave a comment

Add Sequence Number to a Table

There are times you want each record to have a unique identifier.  This function will add a unique sequence number to each record in any table.  It assumes you have a field in the target table that will store the sequence numbers.

Public Function AddSequenceToTable(sTableName As String, sFieldName As String, Optional iStartValue As Long)
‘Purpose: Adds a sequence number starting from 1 to the designated table and field
CalledFrom:
‘Calls  : basErrorHandler.ErrorHandler()
‘Assumes: sTable is the target table for the sequence number.  sFieldName is the target fieldname of that table.
‘Written: MM
‘Updated:
‘Attribu: na
‘Returns: True/False indicating success.
‘——————————————————————
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
 
Set db = CurrentDb()
Set rs = db.OpenRecordset(sTableName)
 
If IsNull(iStartValue) Then
    iStartValue = 0
Else
End If
Do While Not rs.EOF
    iStartValue = iStartValue + 1
   With rs
        .Edit
            .Fields(sFieldName).Value = iStartValue
        .Update
        .MoveNext
    End With
Loop
 On Error GoTo ErrorHandling:
 ExitProc:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
  Exit Function
 ErrorHandling:
  Select Case Err.Number
    Case Else
        Call ErrorHandler(“frm.BasicUtils”, Err.Number, Err.Description)
        GoTo ExitProc
  End Select
 End Function
Aside | Posted on by | Tagged , | 1 Comment

VBA Code: IsFile — Does a file exist?

Here is some code to determine if a file exists.  Requires reference to Microsoft Scripting Runtime.  (Error handling is commented out).

Function IsFile(sFileName) As Boolean
‘—————————————————————–
‘Purpose: Checks for the existence of a file based on path/name passed in.

‘CalledFrom: many
‘Calls: basErrorHandler.ErrorHandler(), basDeclares.adh_accFileExists()
‘Assumes: na
‘Returns: True/False indicating True = Yes False = No.
‘——————————————————————

‘Test for the existance of a file
‘Must include full path in sFileName
On Error GoTo ErrorHandling
Dim fs As Object

Set fs = CreateObject(“Scripting.FileSystemObject”)

If fs.FileExists(sFileName) Then
     IsFile = True
Else
     IsFile = False
End If

ExitProc:
Exit Function

ErrorHandling:
‘ Call ErrorHandler(“basUtils.IsFile”, Err.Number, Err.Description)
GoTo ExitProc

End Function

Posted in VBA Code | Tagged | Leave a comment

Importing Excel into Access. Sometimes Access is too smart for its own good.

Ever try to import an Excel file into Access and find that some records were not imported because they didn’t match the field type (text, integer)?  Access tries to predict the field type based on the data it sees and sometimes gets it wrong.  This often occurs with Account Number fields which are sometimes numeric and sometimes text.  Access thinks it has a numeric field, but then in record 500, it encounters an Account Number with an alpha character – and that record ends up in the Error file.

One solution is to first convert the Excel file to a csv and then perform the import.  But here again, sometimes Access creates the same problem for itself (and you!).

Here’s a solution that will always work.  The code below does the following:

Function:  ConvertCellsToText – (this requires that you pass parameters that define the Excel application, workbook and worksheet).

1)      Determines the used part of the worksheet

2)      Converts every single cell in the used part of the worksheet to text by placing an apostrophe in front of each cell.

Typically, I run this using the following process:

  1. Convert the “txt” file using the ConvertCellsToTxt function.
  2. Copy the original Excel file, giving it a prefix of “txt”.  Before this is done, I check to see if that file already exists (see Function:  IsFile and ExtractFolderNameFromPath).  If it does exist, it is deleted.
  3. Import the “txt” Excel file using the standard Transferspreadsheet function.
  4. Delete the “txt” Excel file.

The downside to this process is that it can take minutes to process very large spreadsheets.  Realize that if you are importing a spreadsheet with 50,000 rows and 20 columns, the ConvertCellsToText function has to convert 1,000,000 cells.

The upside is:  No more import errors!

Here is the code for the ConvertCellsToText function (I have commented out my error handling routine):

Public Function ConvertCellsToText(xlAPP As Excel.Application, xlWB As Excel.Workbook, xlWS As Excel.Worksheet)
‘—————————————————————–
‘Purpose: Converts every cell on the passed in spreadsheet to text by placing an apostrophe (‘) at
‘ the beginning of each cell. This ensures that there will be no errors when the excel
‘ sheet is imported into an Access table (Access may think that a column is numeric and 
‘ when it encounters text, it creates an import error and the record is ignored.
‘ The file is not saved after the changes are made
‘CalledFrom:
‘Calls : basErrorHandler.ErrorHandler()
‘Assumes: Workbook/worksheet is passed to the subroutine

‘Returns: True/False indicating success.
‘——————————————————————
On Error GoTo ErrorHandling:
Dim iLastRow As Long
Dim iLastColumn As Long
Dim vExcelSheet As Variant
Dim iCol As Integer
Dim iRow As Long

iLastRow = xlWB.ActiveSheet.UsedRange.Rows.Count

iLastColumn = xlWB.ActiveSheet.UsedRange.Columns.Count

vExcelSheet = xlWS.UsedRange.Value
For iRow = 1 To UBound(vExcelSheet, 1)
     For iCol = 1 To UBound(vExcelSheet, 2)

     If IsError(vExcelSheet(iRow, iCol)) Then
           xlWS.Range(ColumnLetter(iCol) & iRow).Value = “‘”
     Else
          xlWS.Range(ColumnLetter(iCol) & iRow).Value = “‘” & vExcelSheet(iRow, iCol)
     End If

    Next iCol
Next iRow

ExitProc:
DoCmd.SetWarnings True
Exit Function

ErrorHandling:

‘Call ErrorHandler(“basUtils.ConvertCellsToText”, Err.Number, Err.Description)
GoTo ExitProc

End Function

Aside | Posted on by | Tagged , | Leave a comment

Keys to a Successful Enterprise Resource Planning (ERP) Systems Implementation — Are you installing a new ERP system? Read our top ten (well, actually eleven) keys to a successful Enterprise Resource Planning Implementation project.

 

1.    Establish Clear Business Objectives.

A large-scale systems implementation affects the entire business.  It is a very expensive undertaking that should ultimately pay off with reduced costs, new capabilities to meet market requirements and / or the creation of new revenue opportunities.  But this usually occurs with considerable business disruption – most people don’t react well to change.

A successful implementation will minimize business disruption.  This is accomplished when every manager understands the goals of this new system and understands his responsibility to meet these goals. This can only occur with clear measurable and achievable business objectives that are established at the start of the project.

Large-scale projects that are linked to technical objectives – (for example, replacement of an old system with a new one because the vendor went out of business) typically are troubled if middle management does not truly see the benefit of the new system.

2.    Gain the Commitment of Senior Management.

A project that changes the entire culture of a business cannot succeed without the 100% backing of senior management.  Middle management and supervisors are most affected by a new system – therefore their buy in to the new system is critical.  But they don’t buy in if they do not feel that senior management is solidly behind the project.  Projects of this sort extend over a long period of time.  Commitment from senior management must be visible and continual.

3.    Staff Properly

Large projects need dedicated personnel with the appropriate skill sets.  The people on the project team must be relieved of their regular day to day duties.

A common mistake is to expect programmers to do the work of system or business analysts.  That’s like having your carpenter design a skyscraper.

4.    Don’t Underestimate the Data Conversion Effort

ERP projects are often replacing aged systems.  Part of the systems replacement justification is to improve the quality and integrity of the data.  Data conversion is divided into two parts: current (open orders, receivables, payables, etc.) and historical data.  Data in the older system must be mapped to the new system.  This takes planning, software development and often cleanup of inconsistent data.

This part of the project is very often ignored in project planning or the complexity of the effort is grossly underestimated.

5.    Provide Proper Tools for the Project Team

Don’t forget the little things to make a project team work effectively.  A proper work environment, telephones, voice mail, email will allow the team to communicate well and be productive.  But don’t treat the project team significantly differently than the rest of the employees – or there will be a “we” vs. “them” in the company.

6.    Communicate.  And then Communicate Again

Generate excitement.  Tell people what is happening and when it is happening.  And then tell them again.

If the project runs into problems, let people know what the problem is and what actions are being taken to get back on track.  The worst thing for an organization is to have rumors running the project.

7.    Start at the Top

Focus on the top of the information chain first – the financial data.  This data pervades every activity of the enterprise.  Assuming a new General Ledger and Financial Reporting system is part of the project, a new chart of accounts should be one of the first activities to be addressed.

8.    Big Bang? Or the Modular Approach?

An ERP implementation is usually comprised of several integrated modules.  A major decision to be made very early is how these modules are going to be implemented, one at a time, in a few groups or all at once – “Big Bang”.

The Big Bang approach has the advantage of reducing the number of interfaces that have to be written to existing systems.  But it comes at a high cost and significantly increases project risk.  When multiple modules are implemented all at once, there is simply more opportunity for things to go wrong.  It takes thorough planning and testing.  More resources are required, therefore more sophisticated project management is also required.

If the project can be separated into a modular approach without seriously affecting the rollout schedule, give it high consideration.

9.    Don’t Delay Critical Reporting

Many projects bump up against deadlines.  As they do, projects tasks are prioritized and some are determined to be unnecessary for day 1.  Frequently, the delayed task is a report – the common logic is: “It’s a monthly report.  We can get it done during the first month.”

Don’t fall into this trap.  Other problems always occur during the first month of operation, and then the key reports are put off further.  Now you have a real problem.

If critical reports or other key project components are not ready, delay the implementation.

10. Train – then Train Again

Training is one of the critical components of the implementation.  Get key users involved early.  Establish a train the trainer program.  Spend a little extra on a good technical writer and / or professional trainer.  The dividends are tremendous.

Standardized training offered by the software vendor may not be enough.  The software vendor’s training focuses on mechanics.  The more important training is related to the process changes that have occurred as a result of the new software.

Do training of users shortly before the implementation.  Establish post-implementation training sessions as well.  This will reinforce the training – and also highlight problem areas early.

11. Test – Then Test Again

Don’t assume that your vendor has tested the software, therefore you don’t have to.  You must make sure that the software supports all the types of transactions you process.  Testing is time consuming and requires a clear understanding of all the business processes.  Testing scenarios should be written and exercised with a clear test plan.  You must make sure that not only does the software function, but is it doing what you want it to do.

 

Posted in IT Strategy/Planning | Leave a comment