How to store data from a daily input form in a database?

[MattJC7]

Hey all,

I work at a hospital and we have a daily workload spreadsheet that each team in the department completes daily to allocate staff. We then store the data by pressing submit which uses the code below that I worked up.
My issue is that this all depends on the wards remaining on the row number as they were first placed on and I’m not sure I can guarantee that as we have 7 teams with a lot of people who may tinker.
FYI each team has a separate version of the sheet and filters out wards they don’t need to see.
Thanks.

Sub data_input()

Dim String1 As String
String1 = "CDU"
Dim String2 As String
String2 = "DCU"
Dim String3 As String
String3 = "ED"
Dim String4 As String
String4 = "ITU"

'etc for each ward

'Input CDU data
Sheet1.Select
Range("C11:J11").Select
Selection.Copy
Cells(1, 1).Select

Sheets([String1]).Visible = True
Sheets([String1]).Select
Range("B" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste

Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.Value = Date

Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With

Cells(1, 1).Select
Sheets([String1]).Visible = False

End Sub

 

[dmt32]

Hi,
If the teams have access to your workbook over the hospitals corporate network & clearly your hospital allows you to use macros, then one approach to consider would be creating template copies of the workbook for each team & have these write their submissions to a master (database) workbook.

If need further guidance place copy of the workbook with anonymous data using MrExcels XL2BB addin would be helpful

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

Dave

 

[MattJC7]

Hi,
If the teams have access to your workbook over the hospitals corporate network & clearly your hospital allows you to use macros, then one approach to consider would be creating template copies of the workbook for each team & have these write their submissions to a master (database) workbook.

If need further guidance place copy of the workbook with anonymous data using MrExcels XL2BB addin would be helpful

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

Dave

Hi Dave

Thanks for this. Yes I’d be keen to have a central database but we use a shared network drive (not cloud), so I was concerned that as teams often will be submitting around the same time - would this lead to any errors? I suppose the chances of submissions at the exact same time are minimal but my copying/pasting code takes a few seconds to submit - are there better ways?

I’ll work out the xl2bb soon and post what I have, you’d be helping massively.

 

[dmt32]

Submissions at exact same time can be managed - I created a time sheet application on same basis for charity my daughter worked for 10 years or so ago where 250 submissions each week were being entered without any issue. Any better way? Access database but some organisations do not have corporate licence or those with right skills to provide ongoing support for a "home brew" application which is why many go for Excel

I am surprised you can use macro in your unit, my wife worked in NICU for 40 years and anything other standard workbooks created on work desktops were not allowed.

Happy to assist if I can but I am am getting rusty.

Dave