Notebook 0001: .xls macro

Whole week last week I was working on the Employee Survey Result module to be distributed to the managers. The information would be obtained by the managers from this module will be utilized, as a component, in their business plans for the year 2009.

Of course, as usual, I won't release anything until I am satisfied with its look and usability. From a normal excel file, I inserted several VB scripts and looping formulas to execute a little automation to the file, to make calculation for each data filter.

Here's the look of the module.

Main Page


Navigation Page


Data Page


Graph Page


All Questions Comparison Page



I am not 100% satisfied it my work because I wasn't able to execute 100% automation in all the pages and components of the module. But I guess 80.00% complete execution of my thoughts is already enough to serve the purpose of this module.

Also, I am still on beginner's level when it comes to VB scripting and macro manipulation of excel. Hopefully someday I can master this. I still have a lot of things to learn.

The first kernel of script I've written in VB script editor of excel is this:

Private Sub CommandButton2_Click()
Sheets("MainPage1").Select
End Sub

Now, I can do this (as I have employed them in the recent module):

Sub auto_open()

Worksheets("Open1").Select
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHeadings = False
Application.DisplayFullScreen = True
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
Worksheets("Open1").Select
Application.CommandBars.Item(1).Controls("Tools").Enabled = False
Application.CommandBars.Item(1).Controls("File").Enabled = False
Application.CommandBars.Item(1).Controls("Format").Enabled = False
Application.CommandBars.Item(1).Controls("Edit").Enabled = False
Application.CommandBars.Item(1).Controls("View").Enabled = False
Application.CommandBars.Item(1).Controls("Insert").Enabled = False
Application.CommandBars.Item(1).Controls("Data").Enabled = False

End Sub

Sub auto_close()

ActiveWindow.DisplayWorkbookTabs = True
ActiveWindow.DisplayHeadings = True
Application.DisplayFullScreen = False
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
Application.CommandBars.Item(1).Controls("Tools").Enabled = True
Application.CommandBars.Item(1).Controls("File").Enabled = True
Application.CommandBars.Item(1).Controls("Format").Enabled = True
Application.CommandBars.Item(1).Controls("Edit").Enabled = True
Application.CommandBars.Item(1).Controls("View").Enabled = True
Application.CommandBars.Item(1).Controls("Insert").Enabled = True
Application.CommandBars.Item(1).Controls("Data").Enabled = True

ThisWorkbook.Saved = True

Application.Quit

End Sub

These two kernels will hide or deactivate certain parts of Excel program. It will give you a full screen view of the spreadsheet as well as protecting some editing features contained in the toolbars and command bars.

For the log-in page, I employed the kernel below for password validation.

Private Sub CommandButton1_Click()
If Sheets("MainAnchor").Cells(6, 3).Value = "Yes" Then
Sheets("MainPage1").Select
Else
MsgBox "You have entered an invalid password"
End If
End Sub

I will find time to execute the 100% of my thoughts on the module. There are a lot of things I want to do but I cannot execute them because of my limited knowledge on VB Script/Macro.

** Side note: Pow and I had a short discussion on the GUI of Vista as well as some information on this topic. Hopefully, on of these days we can expound our discussion in this topic.