Big Picture - Combining files & using worksheets to control

[pilot]

I maintain personnel information in 3 workbooks with some commonalities. To simplify maintenance and make this easier to share with other users, many of whom are Excel neophytes, I am considering combining everything into one wb and using topical worksheets to control what is visible.

Currently, each wb has a ws named with the employee's first name. Items tracked include absence & attendance codes and notes, employee information, salary history and leave usage. As an example, I'm thinking of creating a control worksheet named Leave that would be blank but house code to hide all worksheets except those containing leave records (and the other control worksheets).

This is more of a "big picture" question than a detailed "how do I", but is there a better way of going about this?

[ This Message was edited by: pilot on 2002-04-03 06:56 ]

[Damon Ostrander]

Hi Pilot,

This could be done, but in order to really protect each employee's information from others would require a lot of attention to detail. My initial reaction on reading what you want to do was "fear and trepidation", even as an experienced VBA programmer. I think it would be easier to maintain security if you use separate workbooks for each employee. I assume each employee has a password for accessing his/her data? With separate workbooks, the operating system's file access permission system could be used to protect each employee's data from others.

Another immediate reaction was that this is something that might be better accomplished via a real database application, such as Access.

[pilot]

This is for my own use only, not to be accessed by my employees. I wouldn't have to worry about lots of others having password access. The other users I referred to are other managers in the organkzation who want to use my files as templates for tracking their own people. You're right, Access would probably be better but I've done very little in that program. The reason all employees are in one wb is to make it simpler on me, and to make summary and comparison worksheets easier. Appreciate your comments.

[Jack in the UK]

Hi

To add, any security in Excel is weak and huys like me take much fun ripping the codes, passwords and protection apart just for the shear fun of it and trying to find what you hide,, even if nothing at all really, tyhsi advanced users can do in a few minutes, Excel i love but security is all but not there. BE CAREFUL....

[Damon Ostrander]

Hi again Pilot,

Since you are not concerned with security, a single workbook is probably a pretty good way to keep all the information in one place, as you say. If you want to hide information just for the sake of giving each user only the information he/she needs without distractions, this is easy to do. As an example of the type of thing you can do, if each user will be accessing the file from their own computer, you can access their names (either their NT login name or their MS Office registered name) as a basis for deciding which sheets to make visible so that it looks to each user like a file that contains information of interest only to him/her. It can also be set up so that the hidden sheets do not show up on the Format -> Sheet -> Hide/Unhide menu and can only be hidden/unhidden by an interface you provide, such as a button on a worksheet or a custom userform, etc. This is done from VBA by assigning the worksheet's Visible property the value xlVeryHidden.

Happy computing.

Damon

[pilot]

Just a couple more clarifying comments. On security, I know Excel is one of "the weakest links". The file(s) reside on my personal space on our network so it is invisible to anyone else (who hasn't cracked our network, and its security is quite high). Given, the network admin folks can see and invade if they really wanted. That's a risk I'm willing to accept. Other users, especially my employees, would never be accessing this file(s). Any hiding of sheets would only be to make navigation easier for myself. In other words, when posting leave that has been taken, I don't need to see salary history.

[Joe Was]

You may want to try the built-in Excel Data Form for managing an Excel database. On the pull-down menu it is Data-Form...

The code below will work with a table that is on the same sheet as the navagation menu. I use buttons and Photo objects that act as buttons to activate the code below. For your data table I start in AA1 with cells labled as text and lable the fields (AA1,AB1,AC1...)then load the table with at least one data element in AA2 (A dummy like: "First Record" or 1, anything will work.) this sets the table for the data-form defalts. The Data-Form will use your column lables as the forms field lables. It all works very slick. The code below uses the Sendkeys command to activate or use the Data-Form.

Note Data forms can display a maximum of 32 fields at one time. You can only have one Data-List per sheet, but you may have many sheets in a workbook. JSW

Sub DBForm()
'Macro made; 12/13/2001, By: Joe Was.
'Keyboard Shortcut: Ctrl+b.
'Runs the DataBase input Form.

'Calibrate screen for scroll to DataBase-Data-Table.
Sheets("Menu").Select
Range("A1").Select
ActiveWindow.SmallScroll ToRight:=13

'Select first DataBase record.
Range("AA2").Select

'Clean Screen and show Default Data Form.
ActiveWindow.ScrollColumn = 1
SendKeys "%do"

End Sub


Sub myExit()
'Exit the database form.
SendKeys "%fx"
End Sub


Sub myClose()
'Close the datbase form.
SendKeys "%fc:"
End Sub


Sub myPrint1()
'Print the data table.
Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Msg = "Print Data Now?" ' Define message.
Style = vbOKCancel ' Define buttons.
Title = "Ready to Print?" ' Define title.
'Help = "DEMO.HLP" ' Define Help file.
'Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then ' User chose Yes.
Columns("AA:AI").Select
ActiveSheet.PageSetup.PrintArea = "$AA:$AI"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.ScrollColumn = 1
Else ' User chose No.
GoTo Can
End If
End
Can:

End Sub

Sub mySave()
'Save this workbook.
ActiveWorkbook.Save
End Sub

Sub VDBT()
'Move to the data table.
Range("AA2").Select
End Sub

Sub MainView()
'Return to the navagation menu.
ActiveWindow.ScrollColumn = 1
End Sub



[ This Message was edited by: Joe Was on 2002-04-04 06:25 ]