Need a Form to log Appointments

[rkol297]

I have all my team member's name in column B and starting in column E row 1 is the dates spanning a year of time. I want a form that I can type a team members name and the date in and the form will find the corresponding cell and place a 1 in that cell as seen below.

10/8/14|10/9/14|10/10/14|10/11/14|10/12/14 etc.....
Jane 1
JOE 1
Dave 1 1
Steve 1 1
Mary 1 1 1

How do I do this?

 

[bertie]

Press Alt+F11 to open the vba editor.
Click Insert =UserForm

From the Toolbox select and place a ComboBox on the UserForm.
Right click the ComboBox, select Properties, and give it the Name=cboNames. This will hold the Team Member Names.

From the Toollbox select and place a TextBox on the UserForm.
Right click, select Properties and give the Name=txtDate. This will hold the user entered date.

From the toolbox select and draw a CommandButton on the UserForm.
Name=cmdClose
Caption=Close.

Draw a send CommandButton on the form.
Name =cmdOK
Caption=OK

Right click the form and select ViewCode.

The first piece of code is the UserForm_Initialize event. We use this to populate the Team Member names in the ComboBox

Private Sub UserForm_Initialize()
   Dim rw As Long 'number of Team members
   
   'populate the combo box
   With Sheets("Sheet1")
      'get the number of entries in column B
      rw = .Range("B" & .Rows.Count).End(xlUp).Row
      Me.cboNames.List = .Range("B2:B" & rw).Value
   End With
End Sub

The code behind the Close button unloads the form.

Private Sub cmdClose_Click()
   Unload Me
End Sub

The code behind the OK button:
Does simple validation to determine if user selected a Team Member from the ComboBox?
Does some simple validation to determine if user entered a valid date?
Evaluate the output row using the ListIndex of the ComboBox.
Evaluate the output column by, subtracting user date (dteEnd) from the date in E1 (dteStart)
Uses Cells(row, Column) to output 1 to Sheet1.

Private Sub cmdOk_Click()
   Dim rw As Long
   Dim col As Long
   Dim dteStart As Date
   Dim dteEnd As Date
   
   'did the user select a team member?
   If Me.cboNames.ListIndex < 0 Then
      MsgBox "No Team Member selected, ending!"
      Exit Sub
   End If
   
   'did the user enter a date?
   With Me.txtDate
      If .Value = "" Or Not (IsDate(.Value)) Then
         MsgBox "No valid date entered, ending!"
         Exit Sub
      End If
   End With
   
   
   'use the ListIndex of the ComboBox to determine row number.
   'NB add extra for header row and ListIndex base=0
   rw = Me.cboNames.ListIndex + 2
      
   'determine the output row and column
   With Sheets("Sheet1")


      'subtract the date entered from the start
      'NB add extra to account for dates starting on E1
      dteStart = CDate(.Range("E1").Value)
      dteEnd = CDate(Me.txtDate.Value)
      col = dteEnd - dteStart + 5
      
      'output
      .Cells(rw, col).Value = 1
   End With
   
End Sub

Hope this helps,
Bertie

 

[rkol297]

Works like a charm Thanks!