Operating Room Schedule in Excel

sandra312,

Jell may well be correct about Access being the better platform for such an application but the truth is that the learning curve for Access is very steep indeed. It's adopters often use obscure and arcane language which makes the whole process even more difficult. You can get help here on EE on Access but if you don't speak their language it's a waste of time. Personally I had a very poor experience in the help on Access here on EE. However that was a while ago and perhaps the Access experts are now more empathetic.

A few questions and observations:

1. Meantime I need to be clear how you are going to use your workbook. When a week's worth of schedules have been completed, do you re-use Monday's worksheet?

2. I would strongly recommend you not to use the Print-preview mode as the normal mode of use. The column and row headers become unreadable. Apart from that you have had to use unnecessarily large point sizes.

3. To convert your whole workbook into a database with a Userform interface can be done but it would take, most probably, about 1000 lines of code to do it. I have recently written a very similar application for reception desk staff at a police station.  It is not a trivial project and certainly not a 'code snippet' solution. There is also the likelihood that not all the cells would be visible without scrolling the UserForm - much as at present in that not all the cells are visible at any one moment.

4. For the moment I believe Jell's suggestion to keep with what you've got is good advice. Don't bother with Combo boxes - Data Validation is much easier - with dynamic ranges of course.

Converting what you have to a UserForm/Database in Excel goes far beyond the scope of questions on Experts-Exchange, and would need to be on a commercial basis. Meantime let's deal with the suggestions made by Jell.

a. Dynamic ranges:
Define named range SCRUBSTAFFLIST as

=OFFSET('Validation Lists'!$E$2,0,0,COUNTA('Validation Lists'!$E$2:$E$65536),1)

All the other named ranges used in Data Validation dropdown lists need to be re-defined in the same manner.

That will allow you to add names to the lists and for the new names to be added to the Data Validation dropdown lists automatically. Personally I would just add the names to the lists as and when required directly into the lists themselves.

b. I've run out of time, right now, to do the VBA sorting routines. I'll deal with them later today - if Jell hasn't already dealt with it.

Hope that helps.

Patrick

Patrick,
I sent you a note regarding this thread.
Brad


Sandra,
In addition to the excellent advice offered by Jell and Patrick, please consider reducing your font size and using 100% as the Zoom setting. Data validation dropdowns have a fixed size for the text, and it is quite unreadable at 50% Zoom setting. In fact, some people have even asked for a way of making it even bigger than font size you get at 100% Zoom, so I have written macros that temporarily increase the Zoom to 125% when a data validation cell is selected.
Brad

sandra,

I think you are using a sledge hammer to crack a nut. When you create a worksheet try always to stick to a point size of about 12 - plus or minus 2 and with very, very few exceptions don't use anything else. Don't bother messing around with zoom levels - it's just confusing and irritating. It's irritating because things like dropdown lists become illegible. If you want to print a largish worksheet on one sheet of A4 then go to Print Preview/PageSetUp and select the print 1 page wide  x 1 page tall option. That's all you need to do to print it. As soon as you take other options you will get in a mess pretty quickly. I found, for example, your whole workbook almost unusable until I have removed almost every bit of formatting and reduced the font size on every worksheet to 12.

Lastly avoid like the plague, merged cells. They are a total pain and will always cause you trouble. Almost without exception you can achieve the same effect without using merged cells.

Patrick

Sandra,

I have made a start on the VBA for the sorting of the Data Validation lists. The code is below as is the file with the code in it.

1. Make a change to any of the lists with the yellow headers and you will see that the names are sorted automatically. Ensure that there is no gap in the list of names.

2. In future try to create named ranges with MUCH shorter names. Long names are a pain to cope with. For example SCRUBS would have done.

You will see that if you want to extend the automatic sorting process, you will need to add further lines of VBA code. Note not only the range name in the [] square brackets but also the key for the sort - such as Key1:=Range("E3") - which is unique for each named range. The line of code:

    [SCRUBSTAFFLIST].Sort Key1:=Range("E3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

is only ONE line of code. A space followed by an underline allows one to place the code on the next line - for easier reading. The compiler then treats it as one line of code.

To get to and from the VBA Editor - use ALT+F11. By the way you may find easier, when changing/improving the DataValidations worksheet, to disable the worksheet_change sub. To do that just put an x in front of its name - like this:

Sub Xworksheet_change(ByVal target As Range)

When you want to work again - remove the X.

Hope that helps a bit more

Patrick

Sub worksheet_change(ByVal target As Range)
Dim rng As Range
 
Set rng = Sheet1.Cells
 
If Not Intersect(target, rng) Is Nothing Then
    [SCRUBSTAFFLIST].Sort Key1:=Range("E3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    [ANAESTHETICSTAFFLIST].Sort Key1:=Range("F3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
     [PACUSTAFFLIST].Sort Key1:=Range("G3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    [TTSTAFFLIST].Sort Key1:=Range("H3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End If
 
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:

Sandra,

In the code pane for Sheet2 I have placed the VBA as below (file also). What this does is to watch for any change to the date in cell B1 of the Monday worksheet. As soon as you change that then all the worksheet names change automatically. Also all the dates on each sheet are automatically updated as well - that's done with simple formulae in each sheet Tue thru' Sat in cells B1 on each sheet. Try changing the date for next Monday in the Monday sheet - but change it in the editing box beside the fx.

That should at least make it easier for you to create a new workbook each week.

Hope that helps a bit more.

Patrick

Sub worksheet_change(ByVal target As Range)
Dim rng As Range
Dim i As Long
 
Set rng = Sheets(2).[B1]
 
If Not Intersect(rng, target) Is Nothing Then
    For i = 2 To Worksheets.Count
        Sheets(i).Name = Sheets(i).[B1].Text
    Next i
End If
 
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Sandra,
There is a checkbox in the data validation dialog to allow users to type in any value they wish, such as for Agency staff. You'll find this somewhat incongruously named checkbox in the "Error Alert" tab; it is called "Show error alert after invalid data is entered". The correct setting for your purposes is unchecked.

Brad

Sandra,

Attached is the View-modified copy of your Template. I have changed the Zoom to 85%.

Perhaps you are not aware that you can make the same changes to all the worksheets at the same time. I have for example changed the width of column A and all the row heights in one go. To do that, click on the first worksheet tab, hold down SHIFT and click on the last worksheet tab. That selects all the worksheets. After that any change you make on the first worksheet is also made in the other selected worksheets. To 'ungroup' them either click on a worksheet that is not in the selected group or right click on a an selected worksheet tab and select 'ungroup'.

I believe you now need to implement all the changes I have suggested. I have given you examples of what and how to do it/them - namely:

1. Dynamic named ranges
2. Auto-sorted named ranges
3. Automatically dated ranges on each worksheet
4. Automatically named worksheets - according to the dates

You will need to look at each of the implementations carefully and use them one by one. When you have you will find you have a workbook that is much easier to use and to update each week.

I think it's now time for you to close this question.

Patrick