Software

Turn Microsoft Office documents into custom applications with VB

With a little Visual Basic magic, you can take Office documents such as Excel spreadsheets and turn them into applications. Your users won't even realize they're using Office. In this Daily Drill Down, Bruce Maples shows you how.

Did you know it’s possible to build a custom Microsoft Office application in such a way that users won’t know they’re using Office? By building a custom interface and by removing unnecessary menus and toolbars, you can create single purpose tools that are more productive for users. In this Daily Drill Down, I will demonstrate how to design and customize an Excel expense report in this way. I will also explain the Visual Basic (VB) code you’ll need to use so that you can make use of this approach.

Why hide Office?
The primary reason you would want to remove part or all of the Office interface is to create simplicity for your users. While Microsoft’s use of adaptive menus has made the interface less daunting, there are still options included that most users will never need. By removing these options, you can make your customized Office application easier for users to utilize and you’ll increase their productivity. Also, you may want to conceal data or prevent access to features to improve data integrity. For example, you may not want users changing custom formulas.

The sample application I’ll discuss, called the Acme Expense Report, is an example of a stripped-down Excel application. It only requires employees to fill in their expenses, then to print the list. On an enterprise level, the data used might also need to be sent to a SQL Server data warehouse. In that case, you wouldn’t want to give users even the slightest risk of breaking the data setup.

First, make it functional
Before you can close the Office interface, you need to build a new interface. Why? Once you take away toolbars, menus, and even scrollbars, users can’t interact with your application unless you provide some means to do so. Therefore, your first step is to build a functional application that stands on its own.

As with all good application designs, you’ll want to consider what tasks you want your application to perform and how the user will accomplish these tasks. Will you design custom forms or perhaps use standard documents that come with the Office application? Will you provide your own menu bar or toolbar, or will everything be done on the document or form itself?

In the case of the Acme Expense Report, all I want users to be able to do is:
  • Use the data form to supply expense data.
  • Clear the data that had previously been entered—if they want to start over.
  • Preview and print a final report that’s formatted more effectively than the raw data.

I designed the expense report using three worksheets in an Excel spreadsheet. The first worksheet acts as the interface, and the remaining worksheets will contain the expense data (Figure A) and the final (printable) expense report (Figure B). I selected the column headings and first row of our database and named the range Database (as shown in Figure A) so that Excel would always be able to locate it when using the data form.

Figure A
The Database worksheet will contain the data for an expense report.


Figure B
Another worksheet stores a basic report, formatted for printing.


On the printable report shown in Figure B, the Submitted By field is populated by a concatenation formula. This formula references the username from the user interface (which I describe below) as follows:
="Submitted by " & Main!C4 & " on " & TEXT(YEAR(NOW()) &
 "/" & MONTH(NOW()) & "/" & DAY(NOW()),"yyyy/mm/dd")


The user interface allows users to add raw data and print the report without actually using either data worksheet. It contains a title, a text into which the employee would type his or her name, and four command buttons (Figure C).

Figure C
Requiring your employees to use an interface helps protect the data and the report.


I protected the entire worksheet and disabled cell locking for the text box cell labeled Your Name. This leaves the Your Name text box as the only cell on the interface that the user can modify.
To protect an entire worksheet in Excel, click Tools | Protection | Protect Sheet. To disable cell locking for a specific cell, right-click the cell, select Format Cells, select the Protection tab, and clear the Locked check box.
Other than type input into the Your Name text box, users can only click the command buttons. Once you’ve built the interface, it’s time to add functionality.

Programming function buttons
Let’s take a look at how the Enter Data, Clear Data, and Preview Report buttons are programmed. Of the three, the Enter Data button is the easiest to set up. When this button is clicked, the ShowDataForm method is called for the RawData sheet:
Private Sub cmdEnterData_Click()
' show the data form for user to enter data
Sheets("data").ShowDataForm
End Sub


The Clear Data button isn’t difficult to program either. When this button is clicked, it selects a range beginning with A2 (the first cell of the data rows, below the headers) and deletes it.
Private Sub cmdClearData_Click()
' Clear the data out of the Data sheet
Application.ScreenUpdating = False
Sheets("Data").Activate
ActiveSheet.Range("A2").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
ActiveSheet.Range("A2").Select
Sheets("Main").Activate
Application.ScreenUpdating = True
End Sub


Note that the code turns off ScreenUpdating before running anything else and turns it back on before the routine ends. Therefore, the user never sees the application select and clear the data. The next to last step:
Sheets (“Main”).Activate

selects the Main worksheet, so the user sees only the Main worksheet throughout the entire procedure.
If you’ve ever programmed Excel VBA, you may be wondering why I used the Sheet.Activate and Range.Select methods instead of the Cells method. For this demonstration, I wanted to show the ScreenUpdating property in action.
The Preview Report button code is slightly more complicated. It involves three main actions:
  • Delete any data that’s been copied to the Report worksheet by previously clicking the Preview Report button.
  • Copy the data from the RawData worksheet and insert it into the Report worksheet.
  • Display the worksheet in Print Preview mode.

Here’s how the code looks:
Private Sub cmdPreviewReport_Click()
' Turn off screen updating while we move the data.
Application.ScreenUpdating = False

' Rip out existing data before putting in new.
' Have to do this every time, in case they entered something new.

Sheets("Report").Activate
If Sheets("Report").Range("A6") > 0 Then
Sheets("Report").Range("A6").Select
    ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("A6").Select
End If

' Now copy the data from the Data sheet to the Report sheet.
Sheets("Data").Activate
ActiveSheet.Range("A2").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Report").Activate
ActiveSheet.Range("A6").Select
Selection.Insert Shift:=xlDown

' Turn on screen updating.
'Application.ScreenUpdating = True

' Now preview the sheet.
Sheets("Main").Activate
Sheets("Report").PrintPreview
Application.ScreenUpdating = True

End Sub

The final button on the Acme Expense Report is called Reset. This button allows you, the developer, to turn the normal Excel environment back on when you are working with the workbook. Because its code resembles other programming I’ll discuss, we’ll examine the code for the Reset button below.

Removing the Excel interface
Once you have created a working application, the last step is to remove the Excel interface that you don’t want the user to access. If you make your workbook into a template, then users will be able to copy and use it to create new expense reports without changing the original. In addition, you can store the template on the network to make it more easily accessible.

While it isn’t hard to turn off the majority of the interface, the only way to change the main menu bar is by substituting another one—you must have at least one menu bar active in the application. Your menu bar doesn’t have to have anything on it; it can function as a placeholder. That’s how I programmed the expense report.

Your code also needs to return to the original interface when it closes. Overall, this procedure is easy, except for the steps involved in returning the main menu bar. This part of the procedure requires deleting the menu bar that was added earlier, which will cause the standard menu bar to reappear.

To run these two code segments—the first when starting up and the second when closing—use the Workbook_Open and Workbook_BeforeClose macros. These macros automatically run when the workbook is opened and just before it is closed. Here are the two procedures:
Private Sub Workbook_Open()
Application.Caption = "Acme Expense Reporting"
Application.ScreenUpdating = False

' Turn off everything.
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.DisplayFormulaBar = False

' If there are any other command bars visible,
' make sure they are turned off as well.
Dim barTemp As CommandBar
For Each barTemp In Application.CommandBars
    If barTemp.Visible And _
    barTemp.Type <> msoBarTypeMenuBar Then
        barTemp.Visible = False
    End If
Next

' Make double-sure Main is the active window.
Sheets("Main").Activate

With ActiveWindow
    .DisplayGridlines = False
    .DisplayHeadings = False
    .DisplayHorizontalScrollBar = False
    .DisplayVerticalScrollBar = False
    .DisplayWorkbookTabs = False
End With

' Make our menubar the only one available.
Dim oldMbar As CommandBar
Dim newMbar As CommandBar
Set oldMbar = Application.CommandBars.ActiveMenuBar
Set newMbar = Application.CommandBars.Add _
(Name:="MyAppMenu", Position:=msoBarTop, _
MenuBar:=True, temporary:=True)
With newMbar
    .Visible = True
    .Protection = msoBarNoMove
    .Protection = msoBarNoCustomize
End With

Application.ScreenUpdating = True
End Sub
============================================
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Caption = ""
' Delete our custom menu.
Application.CommandBars("MyAppMenu").Delete

' Reset everything.
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.DisplayFormulaBar = True

With ActiveWindow
    .DisplayGridlines = True
    .DisplayHeadings = True
    .DisplayHorizontalScrollBar = True
    .DisplayVerticalScrollBar = True
    .DisplayWorkbookTabs = True
End With

Application.ScreenUpdating = True
End Sub

Reset button code
Remember the Reset button? I held off discussing the code for it until now because it’s similar to the BeforeClose code shown above, with the addition of a password. Here’s the Reset button code:
Private Sub cmdReset_Click()
' First check to see if this person is authorized.
If InputBox("Enter password:", "Authorization") <> "Kilroy" Then
    MsgBox "Sorry - only authorized personnel may do this."
    Exit Sub
End If

' Got this far -- must be okay!
' Make sure screen updating is turned on.
Application.ScreenUpdating = True

' Delete our custom menu.
Application.CommandBars("MyAppMenu").Delete

' Reset everything to basics.
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Formatting").Visible = True
With ActiveWindow
    .DisplayGridlines = True
    .DisplayHeadings = True
    .DisplayHorizontalScrollBar = True
    .DisplayVerticalScrollBar = True
    .DisplayWorkbookTabs = True
End With
Application.DisplayFormulaBar = True

End Sub

With the interface created and menus and toolbars dumbed down, the Acme Expense Report appears in Excel (Figure D).

Figure D
The Excel application turns a multipurpose program into an easy-to-use single-purpose application.


If this were an actual application that I planned to distribute, I would enhance the code. For example, I would add error checking and create a menu bar that included the same functionality as the buttons. Further, in the Workbook_Open procedure, I would save everything we changed to global variables before I changed them, and then I would read those variables in the BeforeClose procedure so I could put the environment back exactly as it originally existed.

Finally, instead of using the generic data form that’s included in Excel, I would create my own data form so I could perform data validation. I won’t go into detail on any of these enhancements—they will make great practice exercises for you to experiment with.

Uses for Microsoft Office custom applications
You can make use of these Microsoft Office techniques with a large number of applications. For example, if your accounting system made available a full object model to Visual Basic, you could build a robust Time And Expense system using Excel as your front end. If you work at a law firm, you could create a front end on Word that would automate many law documents, and the users could utilize these documents without knowing they were using Word. Onscreen forms, teacher-grading systems, press release systems—the range of applications is only limited by your imagination. Think of all the Office applications as completely flexible environments with powerful data manipulation engines, and you’ll be on your way to putting the power of Office to work for you.
To learn more about how Visual Basic can help you create useful client-side tools, read the following TechProGuild features:The authors and editors have taken care in preparation of the content contained herein but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.
3 comments
Scott Marshburn
Scott Marshburn

"Hello I found this article while doing a web search and find it very informative and have followed the steps provided. However it was written in 2001 since then there have ben upgrades to excel. I was wandering If someone could help me with modifying the code for Removing the Excel interface "

Slayer_
Slayer_

Except for the cell protection, but the intelisense should tell you what you need to know about the differences.

CharlieSpencer
CharlieSpencer

Try reposting this in the 'Q&A' forum. The 'Discussion' forum is for matters of general discussion, not specific problems in search of a solution. The 'Water Cooler' is for non-technical discussions. You can submit a question to 'Q&A' here: http://www.techrepublic.com/forum/questions/post?tag=mantle_skin;content There are TR members who specifically seek out problems in need of a solution. Although there is some overlap between the forums, you'll find more of those members in 'Q&A' than in 'Discussions' or 'Water Cooler'. Be sure to use the voting buttons to provide your feedback. Voting a '+' does not necessarily mean that a given response contained the complete solution to your problem, but that it served to guide you toward it. This is intended to serve as an aid to those who may in the future have a problem similar to yours. If they have a ready source of reference available, perhaps won't need to repeat questions previously asked and answered. If a post did contain the solution to your problem, you can also close the question by marking the helpful post as "The Answer".

Editor's Picks