Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Lounger
    Join Date
    Dec 2002
    Location
    South Australia, Australia
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to add record and sort (Excel 97)

    Hello

    I have almost completed my spreadsheet ready to distribute to someone who is not conversant with Excel. I am trying to make it as easy as possible to use.

    What I would like to happen is:-

    When a new staff member is added - they are entered once and are placed in all worksheets and then the sheets are automatically sorted into alphabetical order.

    For me this is difficult, but I am sure someone will know how.

    Regards
    Kerry

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to add record and sort (Excel 97)

    Hi Kerry

    I've had a look at your attached workbook and have a couple of questions regarding how you wish to maintain the workbook.

    When you add a new staff member, do you really want the person's name to appear on all worksheets, or just those from the current month onwards?

    I notice you have already made use of a hidden sheet for Entitlements. My general suggestion is to use another hidden (or maybe Very Hidden) sheet to hold all the information, including staff member names, that is duplicated throughout the monthly sheets. The latter sheets could then use references to the hidden sheet, rather than duplicated values, to display the staff member names etc. This means only one copy of the data, securely stored, without the possibility of inconsistencies.

    With regards to adding a new staff member, my suggestion is via a UserForm, whose associated code could be used to store the new staff member details in the hidden sheet, then update & sort the references in the relevant monthly sheets, also nominated on the UserForm.

    Is this the kind of approach you had in mind?

    Alan

  3. #3
    New Lounger
    Join Date
    Dec 2002
    Location
    South Australia, Australia
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to add record and sort (Excel 97)

    Hello Alan

    Tthanks for the prompt reply.

    You raise some good ideas.

    1. I like the very hidden worksheet idea
    2. I also like the user Form idea too
    3. Yes - they should be inserted in the month sheet that they start. However, I guess this will cause problems in the accrual sheet. Maybe you can figure that out?

    I know the whole accrual formula is a lacking in finite accuracy, but at this stage, I cant think of anyway around it. One problem at a time!

    Thanks

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to add record and sort (Excel 97)

    I don't think the UserForm/VeryHidden sheet combination would be too hard to code. The majority of the work might be in a rework of the workbook, inserting references in place of staff name values. I'd imagine this too could be done as a programatic search/replace though, without too much effort.

    I didn't pay much attention to the accrual sheet I must admit. The easiest way might be to add new staff members to all sheets, as you first suggested, but force-fill appropriate columns with zeros, or whatever is appropriate for the accrual sheet to work properly.

    Are you OK with doing the coding for these suggestions. I may have time later to nut out something rough if not.

    Alan

  5. #5
    New Lounger
    Join Date
    Dec 2002
    Location
    South Australia, Australia
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to add record and sort (Excel 97)

    Alan I am not really able to do such coding, so your assistance would be greatly appreciated, if you can find the time.

    Kind Regards
    Kerry

  6. #6
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to add record and sort (Excel 97)

    Alan:

    What is a "Very Hidden" sheet, please? Can't find an explanation either in Excel Help or on a search on the lounge. How does it differ from a Hidden sheet?

    Many thanks, Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to add record and sort (Excel 97)

    When you hide a worksheet via Format - sheet - hide, excel will display the "unhide" item if you go to format - sheet . This is a "dead-giveaway" that there are hidden worksheets.

    In VB editor (alt-F11) you can display the properties window (F4). If you select a worksheet, from the "project explorer" window, you can see that the possible values for the visible property are "xlSHeetVisible", "xlSheetHidden", and xlVeryHidden". Selecting the "very hidden" will not display the "unhide" in the format - sheet menu (unless you have other hidden sheets).

    This can also be changed via code by directly modifying the property
    <pre>Sheets(1).Visible = xlVeryHidden</pre>

    Steve

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to add record and sort (Excel 97)

    Tony,

    Activate the Visual Basic Editor (Alt+F11)
    Make sure the Project Explorer is visible (Ctrl+R)
    Expand your workbook until you see the worksheets under Microsoft Excel Objects.
    Select one of the worksheets.
    Make sure that Properties are visible (F4)
    Look at the Visible property. It has 3 possible values:
    -1 = xlSheetVisible
    0 = xlSheetHidden
    2 = xlSheetVeryHidden
    If you set this property to 2 = xlSheetVeryHidden, the user won't be able to unhide it from Excel itself (Format | Sheet | Unhide...).
    The only way to unhide it is in the Visual Basic Editor or in VBA code:
    Worksheets("Sheet37").Visible = xlSheetVisible.

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to add record and sort (Excel 97)

    I'd be willing to have a go & post it up, Kerry. Mind you, I'm nowhere near the expertise of some of the geniuses here, but I have done similar coding to your situation myself. No doubt my solution will invite many "patches" <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, but then again, I guess that's what the lounge is all about. Hopefully I'll get something up tomorrow. (It was my birthday celebration tonight, so I'll forgive myself in advance for any broken promises.)

    Alan

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to add record and sort (Excel 97)

    Hi Tony

    I can't expand much on the explanations given by Hans and Steve, except to say that I was alerted to their "existence" (by Hans I think) when I asked about the equivalent of Word's Document Variables in spreadsheets. I have found them to be rather useful (read sneaky) in workbooks distributed to users who tend to "fiddle" and consequently break the spreadsheet(s). You can use them from VBA code to automatically reset certain values for instance, triggered by on open or close events.

    Alan

  11. #11
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to add record and sort (Excel 97)

    Thanks guys - always ready to learn new ways to confound the users!

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  12. #12
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Macro to add record and sort (Excel 97)

    Alan You are very kind. If you dont feel confident to assist here, I wonder if one of the Moderators might see this post and help. Trouble is I dont know how to get this question back in the loop.

    I hope you are having a great birthday !

    Kerry <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to add record and sort (Excel 97)

    I would tend to revamp the spreadsheet rather than write code to try to maintain IDENTICAL lists in over a dozen different sheets.

    Why don't you make ONE big sheet (transposed) with names in the columns and dates in the rows (this would conceivably allow for over 179 YEARS worth of daily data!).

    You could include a column for each name, (I would orient the text vertically so the columns do not to be as wide, but that's me), one for YEAR, MONTH&YEAR, and DATE (month,day&Year). Freeze the panes and create an autofilter. WIth the autofilter you can essentially get each INDIVIDUAL sheet you had before without doing any work but filtering!

    Having all separate Year, month/year, and day/month/year will help make filtering easier if you want ot filter on particular years or just to get certain months in a year. [you can do it with custom filter but it is more cumbersome]

    You should be able to get your output by using a Pivot table based on this sheet.

    To add names just insert a new column and add the name in 1 place. No complicated macros, easy to modify and maintain, filters are pretty user-friendly and user-understandable.

    Statistical info is easier to get from this database (subtotals, Dfunctions, Advanced filtering, pivot tables, etc) are all relatively straightforward if the dataset is designed properly.

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Steve

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to add record and sort (Excel 97)

    Hi Steve

    I'd be interested in more details on your proposed method. I'm unfamiliar with some of the things you're suggesting, but I think your overall aim is to be able to generate "views" of the data, using the filtering techniques you mention. It does seem much more logical to have all the data on a single sheet, but I can't visualise the arrangement.

    I've attached what I think might be a (incomplete) starting point, based on my interpretation of your method. I can see that the data is now essentially one big flat file, but how to move from this point is new territory for me. Autofilter, pivot table output are features I've never had to deal with. I guess there might still be room for a macro/UserForm to automate inserting a new name in the correct location, since Kerry says the user is not too conversant with Excel.

    I'll try to pursue this further, along the lines you propose.

    Alan

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to add record and sort (Excel 97)

    Some suggestions to get you started: Note many of these are MY preferences, so take them or leave them. Others might have better ideas, take what you want from the others and make it your own!

    Surnames and First names:
    If you format the cells and rotate the text it is easier to read and the row width is narrower. SLect row 1 and 2, format - cells - alignment tab, 90 degrees.

    Row 2 needs "headers". for the Year, Month, date. instead of the merged cells for the autofilter to work.

    Col C should have the FULL DATE: 4/1/2003, 4/2/2003, 4/3/2003, etc

    Note for col C you could enter in the 1st 2 dates and then higlight those cells, select the BOTTOM RIGHT CORNER [cursor will be a plus (+)] and drag it down to fill (you will get a "tool tip" on the date you are on.

    All col B should be filled with the Month and Year (4/2003, 5/2003, etc) the "day will default to 1, that is fine). You want all april 2003 to use the same day!


    For col B you could fill with formula starting in row 3 and copying it down.
    =DATE(YEAR(C3),MONTH(C3),1)

    Format as "mmm-yy" or something similar to display month/year. Copy and paste-special values.

    Put a YEAR in every row in column A, use the formula:
    =year(C3). Copy and paste-special values.

    If you do NOT want them visible (when they are the same as the cell above) you can hide with conditional formatting. Select the cells in COl A and col b. click on format - conditional format, select "formula is" in the left pull down and enter (no quotes):
    "=A4=A3"
    Click <format> , Font - tab, select color pull down and choose WHITE (or whatever the background color is). Click <ok> twice

    Select cell E3 and pick windows - freeze panes to leave the names at top and dates at left no matter where you scroll to.

    Add the data filter. Insert a row between first and last names. Datafilter works on the top row in the region. Hide this row. Select one of the date cells in col C (it doesn't matter which and pick data - filter - autofilter) This will add "little pulldown arrows" to each column in row 3.

    These arrows are the "magic" of data filter. Each one has a unique list of everything in that column. You can select an item and the list will hide everything not matching what you select. Select the pull down in column B and pick Apr-03 and ONLY April 2003 will be shown, all other will be hidden. Multiple "filters" can be selected. Each one will "filter" the data another step.

    Create a total and Subtotal row:
    Insert 3 rows above row 1 (row 1 will become row 4)
    In Cell C1 enter TOTAL of All Data and "Align right"
    In cell D1 enter:
    =SUM(D7x) where x is the last row of your data
    Copy this formula from D1 to AO1 (or whatever the last column is)
    This is the sum of the data for each person.

    In C2 enter something like Total of Visible data and "align right". In Cell D2 enter:
    =SUBTOTAL(9,D7x) where x is the last row of your data
    Copy this formula from D2 to AO2 (or whatever the last column is)
    This is the sum of the data for each person that is visible

    Now when the data is filtered for a particular month (or other filter, the SUBTOTAL will reflect JUST the visible data)

    TO insert new names, will require removing the autofilter (data filter-autofilter) inserting a column where desired, copying the formulas in row 1 and 2 then readding the datafilter. This could be done via a macro if desired (something like this PRELIMINARY, not tested a alot)

    <pre>Sub InsertNewName()
    Dim sNewName As String
    Dim iCol As Integer
    Range("a6").AutoFilter
    sNewName = InputBox("New Name to add?")
    'NameList is a defined name of D6:AO6 but will expand
    iCol = WorksheetFunction.Match(sNewName, Range("namelist"))

    Range("NameList").Cells(iCol + 1).EntireColumn.Insert
    Range("NameList").Cells(iCol + 1).FormulaR1C1 = sNewName

    Range("D14").Copy (Range("NameList").Cells(iCol + 1).Offset(-5, 0))
    Application.CutCopyMode = False
    Range("a6").AutoFilter
    End Sub</pre>


    Hope this gets you started, post back if you have further questions
    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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