PDA

View Full Version : Integrating Calendar with WorkSheet and UserForm



HussNoobExce
11-29-2017, 01:36 AM
Hello,

I would like to Integrate Calendar with a Worksheet and a Userform.
My file is attached.

I Have a userform where I enter a new entry for my data sheet (Transportation_Tracking).
My wish is after I enter the data, the name depending on THE DAY and THE TIME will be insert automatically in my calendar to the accurate Cell. Like a Planning Table.

In my File, I have put in green the cell (in transportation_tracking sheet) the criteria that need to be in consideration.
In my file, I have put the Name Robert in the cell corresponding at my data sheet in the Calendar.

The goal is then to automatically Insert the Name ROBERT in the calendar depending on the Transportation_tracking Sheet when I finish to enter the entry in my userform.

Userform = Enter information in the data sheet (transportation_tracking) and put the name in the accurate cell (depending on Day and Hour)

Is it feasible ?

Thanks in advance guys

SamT
11-30-2017, 07:14 AM
Moderator Bump

offthelip
11-30-2017, 09:41 AM
try this, I think it does what you want:


Private Sub cmdAdd_Click()Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("TransportationTracking")


'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row




'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
' .Unprotect Password:="password"
.Cells(iRow, 1).Value = Me.txtOrderNumber.Value
.Cells(iRow, 2).Value = Me.txtRequestor.Value
.Cells(iRow, 3).Value = Me.txtPassanger.Value
.Cells(iRow, 4).Value = Me.txtPassanger2.Value
.Cells(iRow, 5).Value = Me.txtPassanger3.Value
.Cells(iRow, 6).Value = Me.txtOrderDate.Value
.Cells(iRow, 7).Value = Me.txtDateOfTravel.Value
.Cells(iRow, 8).Value = Me.txtTimeRequested.Value
' this is the new code
End with
datev = DateValue(Me.txtDateOfTravel)
timev = Me.txtTimeRequested
With Worksheets("Calendar")
inarr = Range(.Cells(1, 1), .Cells(18, 9))
For i = 3 To 9
If (inarr(4, i) = datev) Then
For j = 6 To 18
If inarr(j, 2) = timev Then
.Cells(j, i) = Me.txtPassanger.Value
Exit For
End If
Next j
End If
Next i
End With
With ws
' this is the end of the new code
.Cells(iRow, 9).Value = Me.txtFrom.Value
.Cells(iRow, 10).Value = Me.txtStop1.Value
.Cells(iRow, 11).Value = Me.txtStop2.Value
.Cells(iRow, 12).Value = Me.txtTo.Value
.Cells(iRow, 13).Value = Me.txtRoundtrip.Value
.Cells(iRow, 14).Value = Me.txtDescription.Value
.Cells(iRow, 15).Value = Me.txtType.Value
.Cells(iRow, 16).Value = Me.txtName.Value
.Cells(iRow, 17).Value = Me.txtEstimatedCost.Value

' .Protect Password:="password"
End With


Call UserForm_Initialize
End Sub