Big Picture - Combining files & using worksheets to control
Big Picture - Combining files & using worksheets to control
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Big Picture - Combining files & using worksheets to control

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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....



    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  5. #5
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com