Thanks for the clarifications.
My recommendations would be:-
1) Forget about the individual buttons for the Time Now and Arrival Time columns and use the double click event (I'll show you what to do shortly).
2) Create separate sheets for Transfers and Discharges.
3) Once patients are discharged or transferred, archive their full data in a separate sheet (and call it "Archive" I suppose) and delete their data from the Admissions sheet. Deleting "used" data will bring data of current patients to the top of the Admissions list. You will be then able to access the full history of archived patients (sounds a bit impersonal!) if needed through an archive search which we can create.
4) Personally, I haven't got much time for userforms as data still needs to be placed in the relevant input sheet regardless. I just consider userforms as an unnecessary step requiring more code. However, if you still like the idea of userforms, have a look at Trevor Easton's site:
Trevor is extremely good at this sort of thing.
5) In relation to the clock that you have in the Admissions sheet, I would ditch the code (even though it it very neat and compact) and simply place the following formula in cell C2:-
The date will then perpetually update and appear in this cell.
Then in cell C3, place the following formula:=
then format the cell for time only. The time will then perpetually update and appear in this cell.
However, should you prefer to keep the clock code, then assign the code to a Workbook_Open event. The time and date will then update each time the work book is opened.
Now, for the double click event code (test this first in the dummy copy of your work book):-
Right click on the Admissions tab and from the menu that appears, select "view code". In the big white field that appears, paste the following code:-
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lr As Long
Dim cell As Range
lr = Range("B" & Rows.Count).End(xlUp).Row
For Each cell In Range("B6:B" & lr, "G6:G" & lr)
If ActiveCell.Column = 2 Or ActiveCell.Column = 7 Then
ActiveCell.Value = Format(Time, "hh:mm")
Every time that you double click on a cell in Column B (from B6 down) or a cell in Column G (from G6 down), the time will appear in that cell.
Let us know your thoughts.