How to create a macro to automatically fill in a grid

[Hidvig]

I am trying to build a holiday tracker. I need to have a worksheet that, on the first tab, allows the user to fill in details such as:

name:
day:
month:

I will then have 12 tabs with each month of the year.
Each tab will have a grid with 1-31 (or however many days are in that month) at the top and each persons name down the side

I need to write a macro that will fill see the details inserted in the first tab and make a number one appear in the relative grid, so if someone fills in as follows:

name: Matt Smith
day: 5
month: September

a number 1 will appear in the September tab at the grid point where Matt Smith and 5 overlap

I hope this makes sense and that someone can help

Thank you

 

[JLGWhiz]

If by grid you mean you will have a matrix with days of the month across row 1 beginning in cell B1 and the employee names will be listed in column A beginning in cell A2 The something like the following would work. sheets(1) will be the input sheet, with Range B2:P4 being used for input by the employee. This will allow an employee to schedule up to 15 days, if they can accrue that many. Any days scheduled must be entered in consecutive cells, even if there are weekends or other non-work days between vacation dates. The month name for the sheets and the month name entered by the user must be the identical. Something you might want to try, to avoid misspelled names and months, is to put a list of each, which are identical to those of the month schedule sheets,on the input page and have the user copy from those lists to he input range. That way they would always be using the correct data.
So, this code assumes that you have an input page with the input range B2:P4, 12 sheets named each named for a different month of the year, and each of the month sheets with days of the month on row 1 beginning in cell B1, and Names down column A beginning in cell A2. It will do a quality check of the input range to check for missing and uncontiguous entries If no gaps are detected, it will then determine how many entries were made, find the sheet for the first entry, locate the name on that sheet and put a "1" in the day entered by the user. It will repeat that process for the number of entries made.

Sub sche()
Dim sh As Worksheet, nr As Long, dSh As Worksheet, fNm As Range
Set sh = Sheets("Sheet1") 'Edit sheet name
'The next three If...Then statements are quality checks.
    If Application.CountA(sh.Range("B2:P4")) = 0 Then
        MsgBox "There are no entries for schduling"
        Exit Sub
    End If
nr = Application.CountA(sh.Range("B2:P2"))
    If Application.CountA(sh.Range("B2").Resize(1, nr)) <> nr Then
        MsgBox "Non-Sequential entry detected"
        Exit Sub
    End If
    If Application.CountA(sh.Range("B2:P" & nr)) Mod nr <> 0 Then
        MsgBox "All required data not entered for each column"
    End If
'Now we load the monthly sheets. Since different people use different formats for month names, it needs a control.
    For i = 2 To nr + 1
        Set dSh = Sheets(sh.Cells(4, i).Value)
        dy = sh.Cells(3, i).Value
        Set fNm = dSh.Range("A:A").Find(sh.Cells(2, i).Value, LookIn:=xlValues)
            If Not fNm Is Nothing Then
                fNm.Offset(, dy) = 1
           End If
    Next
End Sub

With the input feature, you can schedule more than one employee at a time so long as it does not exceed fifteen days.

 

[Hidvig]

thats fantastic, thank you very much.

Is there a way I could add a line of code in so that I can have a 'book / cancel' function

So if I now have my input page as follows:

name: Matt Smith
day: 5
month: September
Action: (list option to either book or cancel)

Then if the user selects book it continues as before, but if they select cancel, it puts in a 0 instead.

Thank you

 

[JLGWhiz]

This will provide the cancel option. However, you cannot schedule multiple employees unless they all make the same option.
Also, depending on how your system is set up, the zero might not be visible. I put a formatting line in that should avoid that issue, but thought I would mention it anyhow.
Regards, JLG

Sub sched()
Dim sh As Worksheet, nr As Long, dSh As Worksheet, fNm As Range
Dim dy As Long, msg As String
Set sh = Sheets("Sheet1") 'Edit sheet name
'The next three If...Then statements are quality checks.
    If Application.CountA(sh.Range("B2:P4")) = 0 Then
        MsgBox "There are no entries for schduling"
        Exit Sub
    End If
nr = Application.CountA(sh.Range("B2:P2"))
    If Application.CountA(sh.Range("B2").Resize(1, nr)) <> nr Then
        MsgBox "Non-Sequential entry detected"
        Exit Sub
    End If
    If Application.CountA(sh.Range("B2:P" & nr)) Mod nr <> 0 Then
        MsgBox "All required data not entered for each column"
    End If
'Now we load the monthly sheets. Since different people use different formats for month names, it needs a control.
msg = "IF YOU WANT TO BOOK THE DATE, CLICK 'YES', ELSE TO CANCEL, CLICK 'NO'"
ans = MsgBox(msg, vbYesNo + vbExclamation, "CONFIRM")
    For i = 2 To nr + 1
        Set dSh = Sheets(sh.Cells(4, i).Value)
        dy = sh.Cells(3, i).Value
        Set fNm = dSh.Range("A:A").Find(sh.Cells(2, i).Value, LookIn:=xlValues)
            If Not fNm Is Nothing Then  
      If ans = vbYes Then
                        fNm.Offset(, dy) = 1
      ElseIf ans = vbNo Then
   fNm.Offset(, dy).NumberFormat = "@"
   fNm.Offset(, dy) = "0"
      End If
           End If
    Next
End Sub

 

[Hidvig]

You are brilliant, thank you

 

[JLGWhiz]

You're welcome
Regards, JLG

 

[MzColey]

Hi JLG.

Through a Google search, I ran across this old thread, but I'm hoping to gain a little more help with creating a macro similar to Hidvig. I have teaching schedules for over 100 classes that I put onto a day & time grid, but it's REALLY time-consuming. If I were to have a macro on another worksheet, could I have Excel create this automatically, and if so, would any changes or updates to the data worksheet update the grid?

Thank you in advance!