Macro to add record and sort (Excel 97)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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” , 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

Reply | Quote

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

Reply | Quote

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

Steve

Reply | Quote

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

Reply | Quote

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 , Font – tab, select color pull down and choose WHITE (or whatever the background color is). Click 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(D7:Dx) 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,D7:Dx) 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)

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("D1:D4").Copy (Range("NameList").Cells(iCol + 1).Offset(-5, 0))
    Application.CutCopyMode = False
    Range("a6").AutoFilter
 End Sub

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

Reply | Quote

Hi Steve

All great stuff! Just the kind of info I need. I’m going to try to continue to develop this, even though it looks like Kerry needs a deliverable tomorrow. It’s a side of XL I haven’t dabbled in, and looks like the perfect project to start on.

many thanks

Alan

Reply | Quote

Hi Alan

Wow this has become a far bigger job than I anticipated. While I am thrilled with the fact that you want to help, I dont want you to have to remake my spreadsheet. I am too close to getting this out to change it completely now. I will have a look at Steves suggestions later when I have more time. I have to issue this tomorrow, warts and all.

I can cope with showing the user how to manually putting in names and sorting at this stage.

Thanks All. I will get back to you if I have any further questions.

Kerry

Reply | Quote

Hi Kerry

Sorry nothing was forthcoming for you deadline. I didn’t try to take the path I’d originally proposed, because I felt that Steve’s suggestions represented a much better solution. Also, many of the XL projects I port have to be designed so that “operators” can’t easily “break” the application by fiddling around. Your target user seems to be the one who will end up having sole use/control of the app, or at least the “administrator rights” role. This being the case, the hidden data approach is probably not relevant.

I do want to continue with this as a learning project though, so will post up what I come up with. I also think that it might still be appropriate to automate the “Add New Staff Member” function through a UserForm, so that might be of later use to you.

Remember too, that most projects are delivered in stages, so there’s no harm in telling your client that this can be an ongoing development (if that suits you of course!). I’ll post something back when I’ve got it up… now that the fog of birthday celebrations is lifting!

Alan

Reply | Quote

One other “suggestion”. The data (in Kerry’s original dataset at least) seems that it might have many “blanks” on many days and in many people’s columns. The datatables in the original set had ONLY numbers for CERTAIN people.

In this type of condition, another setup is the ELIMINATION of all the columns of people!
Have Year, Month/year, and date as columns. Then have a column for “person” (some type of unique ID, usually whihc is “linked” to a database with more detail), then the column for the numbers. Only enter the date, person, value when a NEW set is needed. You can then filter on month and person and get that persons value for the month. A Pivot table is also much easier to setup.

Steve

Reply | Quote

Hi Alan

It is fine with me if you want to press ahead Alan, however, I need to point out to you some of the factors to keep in mind about how it will be utilised.

The way it is set up with a sheet for each month and the names in the column is actually deliberate. If you look to the right side you will see that staff all have a location and a classification. We want to be able to filter on these fields. The purpose being that we would want to know at a glance if there are any clashes with leave for staff in one location or to see which admin staff are on leave for that particular month.

Also I feel it is more pleasing to the eye to look from left to right and graphically see who is on leave.

So you may want to keep this in mind with your modifications.

Kind Regards

Kerry

Reply | Quote

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.

Reply | Quote

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

Sheets(1).Visible = xlVeryHidden

Steve

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Tony.

Reply | Quote