If Not Application.Intersect problem

P

plcoomer

New Member
Joined
Jul 5, 2017
Messages
4
I've been using the following code in a worksheet1 to open Userform1 loaded with the data in which ever row in the range is double clicked.
Worked great until I added a second worksheet & Userform2 to perform the same task with a different data set.

Problem is When I open userform2 on worksheet2 and then close it. Moved to the original worksheet1 and double click - it retains the row number from Worksheet2 and loads data from the wrong row in Worksheet1.
Hope that makes sense.

So how would I clear out range data stored so that each double click opens that rows data each time? Thank you in advance for your thoughts

Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)

If Not Application.Intersect(target, Range("B2:CM180")) Is Nothing Then
Cancel = True
Application.ScreenUpdating = False
UserForm1.Show
End If
End Sub
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
If Not Application.Intersect(target, Range("B2:CM180")) Is Nothing Then
Cancel = True
Application.ScreenUpdating = False
UserForm1.Show
End If
Application.ScreenUpdating = True
End Sub
 
Upvote 0
bebo021999 said:
If Not Application.Intersect(target, Range("B2:CM180")) Is Nothing Then
Cancel = True
Application.ScreenUpdating = False
UserForm1.Show
End If
Application.ScreenUpdating = True
End Sub
Click to expand...
Thank you bebbo. I inserted the line below in the code after End If and the problem persists. No change. Still opens the row number from the previous sheet.
Application.ScreenUpdating = True
 
Upvote 0
Yes Below is Userform1
VBA Code: 
Public wsmp As Worksheet


Private Sub CommandButton1_Click()
' Save the changes and then the spreadsheet button
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = Worksheets("MP Program")
On Error Resume Next


wsmp.Range("BA" & ActiveCell.Row).Value = Now() ' save last updated
UserForm1.Label61.Caption = Now() ' update the screen
UserForm1.Label61.ForeColor = &HC000&     ' screen color for last updated goes to Green

ActiveWorkbook.Save
CommandButton1.BackColor = &H8000000F  ' turn the save button back to normal

End Sub

Private Sub CommandButton10_Click()
StationDesignNotesFrm.Show

End Sub

Private Sub CommandButton11_Click()
LineDesignNotesFrm.Show
End Sub

Private Sub CommandButton12_Click()
RiskNoteForm.Show
End Sub

Private Sub CommandButton13_Click()
 Dim doc As Object, rng As Range
 Application.SendKeys "(%{1068})"
 DoEvents
 'ActiveSheet.Paste
 With CreateObject("Outlook.Application").CreateItem(0)
 .display
 Set doc = .GetInspector.WordEditor
 doc.Range(0, 0).Paste
 .To = "[EMAIL]phil.coomer@oncor.com[/EMAIL]"
 .Subject = TextBox1.Text
 End With
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub CommandButton3_Click()
If IsDate(TextBox11.Value) = True Then
   
    TextBox12.Text = Format(DateValue(TextBox11.Text) + Val(90), "mm/dd/yyyy")
    wsmp.Range("J" & ActiveCell.Row).Value = TextBox12.Value ' steady state due date
    TextBox12.ForeColor = &HFF&
    wsmp.Range("J" & ActiveCell.Row).Font.Color = vbRed
    wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
   
    TextBox13.Text = Format(DateValue(TextBox12.Text) + Val(30), "mm/dd/yyyy")
    wsmp.Range("K" & ActiveCell.Row).Value = TextBox13.Value ' Short Circuit due
    TextBox13.ForeColor = &HFF&
    wsmp.Range("K" & ActiveCell.Row).Font.Color = vbRed
    wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
   
    TextBox14.Text = Format(DateValue(TextBox12.Text) + Val(90), "mm/dd/yyyy")
    wsmp.Range("L" & ActiveCell.Row).Value = TextBox14.Value ' Stability study due
    TextBox14.ForeColor = &HFF&
    wsmp.Range("L" & ActiveCell.Row).Font.Color = vbRed
    wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
   
    TextBox15.Text = Format(DateValue(TextBox14.Text) + Val(90), "mm/dd/yyyy")
    wsmp.Range("M" & ActiveCell.Row).Value = TextBox15.Value ' FIS Due
    TextBox15.ForeColor = &HFF&
    wsmp.Range("M" & ActiveCell.Row).Font.Color = vbRed
    wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
    CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub



Private Sub CommandButton4_Click()
If IsDate(TextBox16.Value) = True Then
    TextBox17.Text = Format(DateValue(TextBox16.Text) + Val(180), "mm/dd/yyyy")
    wsmp.Range("O" & ActiveCell.Row).Value = TextBox17.Value ' SGIA deadline
    TextBox17.ForeColor = &HFF&
    wsmp.Range("O" & ActiveCell.Row).Font.Color = vbRed
    wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
    CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If

End Sub

Private Sub CommandButton5_Click()
If IsDate(TextBox60.Value) = False Then
    TextBox60.Text = Format(DateValue(TextBox54.Text) - Val(45), "mm/dd/yyyy")
    wsmp.Range("AN" & ActiveCell.Row).Value = TextBox60.Value ' MCPR deadline
    TextBox60.ForeColor = &HFF&
    wsmp.Range("AN" & ActiveCell.Row).Font.Color = vbRed
    wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
    CommandButton5.Visible = False
    CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub CommandButton6_Click()
ResourceForm.Show
End Sub

Private Sub CommandButton7_Click()
ResourceFormLine.Show
End Sub

Private Sub CommandButton8_Click()
StudiesNotesForm.Show
End Sub

Private Sub CommandButton9_Click()
ROWNotes.Show
End Sub

Private Sub Frame1_Click()

End Sub

Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then
wsmp.Range("CL" & ActiveCell.Row).Value = True
wsmp.Range("CM" & ActiveCell.Row).Value = False
wsmp.Range("CL" & ActiveCell.Row).Font.Color = vbRed
OptionButton1.ForeColor = &HFF&
OptionButton2.ForeColor = &H80000012
TextBox75.Text = "------------------- Engineering Only ------------------"
TextBox75.BackColor = &H80000008
wsmp.Range("A" & ActiveCell.Row).Value = 1
CommandButton1.BackColor = &H80FFFF
End If
End Sub
Private Sub OptionButton2_Click()
If OptionButton2.Value = True Then
wsmp.Range("CL" & ActiveCell.Row).Value = False
wsmp.Range("CM" & ActiveCell.Row).Value = True
wsmp.Range("CM" & ActiveCell.Row).Font.Color = vbRed
OptionButton2.ForeColor = &HFF&
OptionButton1.ForeColor = &H80000012
TextBox75.Text = "------------------- Engineering & Procurement ------------------"
TextBox75.BackColor = &H80FF&

wsmp.Range("A" & ActiveCell.Row).Value = 1
CommandButton1.BackColor = &H80FFFF
End If
End Sub
Private Sub SpinButton1_Change()
'Set wb = ThisWorkbook
'Dim ws As Worksheet
'Set ws = Worksheets("MP Program")
'On Error Resume Next
'UserForm1.Label61.Caption = SpinButton1.Value

'Call UserForm_Initialize
'ActiveCell.Value = SpinButton1.Value
'Range("B" & SpinButton1.Value).Activate
'Call UserForm_Initialize

End Sub
Private Sub Combobox11_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox11.Value = wsmp.Range("F" & ActiveCell.Row).Value Then
Else
wsmp.Range("F" & ActiveCell.Row).Value = ComboBox11.Value 'FIRM
ComboBox11.ForeColor = &HFF&
wsmp.Range("F" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub Combobox12_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox12.Value = wsmp.Range("AX" & ActiveCell.Row).Value Then
Else
wsmp.Range("AX" & ActiveCell.Row).Value = ComboBox12.Value 'ROW PM
ComboBox12.ForeColor = &HFF&
wsmp.Range("AX" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub Combobox13_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox13.Value = wsmp.Range("CN" & ActiveCell.Row).Value Then
Else
wsmp.Range("CN" & ActiveCell.Row).Value = ComboBox13.Value 'RTC category
ComboBox13.ForeColor = &HFF&
wsmp.Range("CN" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub Combobox14_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox14.Value = wsmp.Range("CO" & ActiveCell.Row).Value Then
Else
wsmp.Range("CO" & ActiveCell.Row).Value = ComboBox14.Value 'ROW Status
ComboBox14.ForeColor = &HFF&
wsmp.Range("CO" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub Checkbox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If CheckBox5.Value = wsmp.Range("CE" & ActiveCell.Row).Value Then
Else
wsmp.Range("CE" & ActiveCell.Row).Value = CheckBox5.Value 'TXDOT permit required
CheckBox5.ForeColor = &HFF&
wsmp.Range("CE" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub Checkbox7_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If CheckBox7.Value = wsmp.Range("CK" & ActiveCell.Row).Value Then
Else
wsmp.Range("CK" & ActiveCell.Row).Value = CheckBox7.Value 'CCN Required
CheckBox7.ForeColor = &HFF&
wsmp.Range("CK" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub Checkbox6_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If CheckBox6.Value = wsmp.Range("CF" & ActiveCell.Row).Value Then
Else
wsmp.Range("CF" & ActiveCell.Row).Value = CheckBox6.Value 'TXDOT permit required
CheckBox6.ForeColor = &HFF&
wsmp.Range("CF" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox69_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox69.Value = wsmp.Range("Z" & ActiveCell.Row).Value Then
Else
wsmp.Range("Z" & ActiveCell.Row).Value = TextBox69.Value 'GIR Number
TextBox69.ForeColor = &HFF&
wsmp.Range("Z" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox70_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox70.Value = wsmp.Range("AY" & ActiveCell.Row).Value Then
Else
wsmp.Range("AY" & ActiveCell.Row).Value = TextBox70.Value 'GIR Number
TextBox70.ForeColor = &HFF&
wsmp.Range("AY" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox65_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox65.Value = wsmp.Range("BX" & ActiveCell.Row).Value Then
Else
wsmp.Range("BX" & ActiveCell.Row).Value = TextBox65.Value 'Station Engineer
TextBox65.ForeColor = &HFF&
wsmp.Range("BX" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub Combobox8_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox8.Value = wsmp.Range("BY" & ActiveCell.Row).Value Then
Else
wsmp.Range("BY" & ActiveCell.Row).Value = ComboBox8.Value 'Station Resource Engineer
ComboBox8.ForeColor = &HFF&
wsmp.Range("BY" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox68_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox68.Value = wsmp.Range("BZ" & ActiveCell.Row).Value Then
Else
wsmp.Range("BZ" & ActiveCell.Row).Value = TextBox68.Value 'Line Engineer
TextBox68.ForeColor = &HFF&
wsmp.Range("BZ" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub Combobox9_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox9.Value = wsmp.Range("CA" & ActiveCell.Row).Value Then
Else
wsmp.Range("CA" & ActiveCell.Row).Value = ComboBox9.Value 'Line Engineer
ComboBox9.ForeColor = &HFF&
wsmp.Range("CA" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox67_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox67.Value = wsmp.Range("CB" & ActiveCell.Row).Value Then
Else
wsmp.Range("CB" & ActiveCell.Row).Value = TextBox67.Value 'Facility Engineer
TextBox67.ForeColor = &HFF&
wsmp.Range("CB" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox63_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox63.Value = wsmp.Range("AZ" & ActiveCell.Row).Value Then
Else
wsmp.Range("AZ" & ActiveCell.Row).Value = TextBox63.Value 'Project Summary
TextBox63.ForeColor = &HFF&
wsmp.Range("AZ" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox61_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox61.Value = wsmp.Range("BU" & ActiveCell.Row).Value Then
Else
wsmp.Range("BU" & ActiveCell.Row).Value = TextBox61.Value 'Program Notes
TextBox61.ForeColor = &HFF&
wsmp.Range("BU" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox60_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox60.Value = wsmp.Range("AN" & ActiveCell.Row).Value Then
Else
wsmp.Range("AN" & ActiveCell.Row).Value = TextBox60.Value 'MCPR Target
TextBox60.ForeColor = &HFF&
wsmp.Range("AN" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox57_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox57.Value = wsmp.Range("BR" & ActiveCell.Row).Value Then
Else
wsmp.Range("BR" & ActiveCell.Row).Value = TextBox57.Value 'line outages
TextBox57.ForeColor = &HFF&
wsmp.Range("BR" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox56_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox56.Value = wsmp.Range("BP" & ActiveCell.Row).Value Then
Else
wsmp.Range("BP" & ActiveCell.Row).Value = TextBox56.Value 'line outages
TextBox56.ForeColor = &HFF&
wsmp.Range("BP" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox55_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox55.Value = wsmp.Range("AR" & ActiveCell.Row).Value Then
Else
wsmp.Range("AR" & ActiveCell.Row).Value = TextBox55.Value '
TextBox55.ForeColor = &HFF&
wsmp.Range("AR" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox54_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox54.Value = wsmp.Range("AQ" & ActiveCell.Row).Value Then
Else
wsmp.Range("AQ" & ActiveCell.Row).Value = TextBox54.Value 'Line Const Start
TextBox54.ForeColor = &HFF&
wsmp.Range("AQ" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox53_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox53.Value = wsmp.Range("BT" & ActiveCell.Row).Value Then
Else
wsmp.Range("BT" & ActiveCell.Row).Value = TextBox53.Value 'Line precon
TextBox53.ForeColor = &HFF&
wsmp.Range("BT" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub Combobox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox1.Value = wsmp.Range("BS" & ActiveCell.Row).Value Then
Else
wsmp.Range("BS" & ActiveCell.Row).Value = ComboBox1.Value 'Line Const. Contractor
ComboBox1.ForeColor = &HFF&
wsmp.Range("BS" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox58_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox58.Value = wsmp.Range("BN" & ActiveCell.Row).Value Then
Else
wsmp.Range("BN" & ActiveCell.Row).Value = TextBox58.Value 'Line Const. Progress
TextBox58.ForeColor = &HFF&
wsmp.Range("BN" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox50_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox50.Value = wsmp.Range("BQ" & ActiveCell.Row).Value Then
Else
wsmp.Range("BQ" & ActiveCell.Row).Value = TextBox50.Value 'Station Const. Notes
TextBox50.ForeColor = &HFF&
wsmp.Range("BQ" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox49_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox49.Value = wsmp.Range("BO" & ActiveCell.Row).Value Then
Else
wsmp.Range("BO" & ActiveCell.Row).Value = TextBox49.Value 'Station Const. Outages
TextBox49.ForeColor = &HFF&
wsmp.Range("BO" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
'Private Sub TextBox52_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'If TextBox52.Value = wsmp.Range("AU" & ActiveCell.Row).Value Then
'Else
'wsmp.Range("AU" & ActiveCell.Row).Value = TextBox52.Value 'Statin const contractor
'TextBox52.ForeColor = &HFF&
'wsmp.Range("AU" & ActiveCell.Row).Font.Color = vbRed wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
'End If
'End Sub
Private Sub Combobox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox2.Value = wsmp.Range("AU" & ActiveCell.Row).Value Then
Else
wsmp.Range("AU" & ActiveCell.Row).Value = ComboBox2.Value 'Statin const contractor
ComboBox2.ForeColor = &HFF&
wsmp.Range("AU" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox51_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox51.Value = wsmp.Range("BM" & ActiveCell.Row).Value Then
Else
wsmp.Range("BM" & ActiveCell.Row).Value = TextBox51.Value 'Statin const progress
TextBox51.ForeColor = &HFF&
wsmp.Range("BM" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox48_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox48.Value = wsmp.Range("AT" & ActiveCell.Row).Value Then
Else
wsmp.Range("AT" & ActiveCell.Row).Value = TextBox48.Value 'Station End
TextBox48.ForeColor = &HFF&
wsmp.Range("AT" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If

If IsDate(TextBox47) = True Then
    If IsDate(TextBox48) = True Then
Label64.Caption = "<- " & DateDiff("d", TextBox47.Value, TextBox48.Value) & " days ->"
Else
Label64.Caption = "---"

    End If
End If

End Sub
Private Sub TextBox47_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox47.Value = wsmp.Range("AS" & ActiveCell.Row).Value Then
Else
wsmp.Range("AS" & ActiveCell.Row).Value = TextBox47.Value 'Station Start
TextBox47.ForeColor = &HFF&
wsmp.Range("AS" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If

If IsDate(TextBox47) = True Then
    If IsDate(TextBox48) = True Then
Label64.Caption = "<- " & DateDiff("d", TextBox47.Value, TextBox48.Value) & " days ->"
Else
Label64.Caption = "---"

    End If
End If

End Sub
Private Sub TextBox40_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox40.Value = wsmp.Range("AP" & ActiveCell.Row).Value Then
Else
wsmp.Range("AP" & ActiveCell.Row).Value = TextBox40.Value 'Station Precon
TextBox40.ForeColor = &HFF&
wsmp.Range("AP" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox43_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox43.Value = wsmp.Range("AL" & ActiveCell.Row).Value Then
Else
wsmp.Range("AL" & ActiveCell.Row).Value = TextBox43.Value 'Line TR
TextBox43.ForeColor = &HFF&
wsmp.Range("AL" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox45_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox45.Value = wsmp.Range("AK" & ActiveCell.Row).Value Then
Else
wsmp.Range("AK" & ActiveCell.Row).Value = TextBox45.Value 'line designs
TextBox45.ForeColor = &HFF&
wsmp.Range("AK" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox44_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox44.Value = wsmp.Range("AI" & ActiveCell.Row).Value Then
Else
wsmp.Range("AI" & ActiveCell.Row).Value = TextBox44.Value 'Line Materials
TextBox44.ForeColor = &HFF&
wsmp.Range("AI" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox41_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox41.Value = wsmp.Range("BL" & ActiveCell.Row).Value Then
Else
wsmp.Range("BL" & ActiveCell.Row).Value = TextBox41.Value 'line wA
TextBox41.ForeColor = &HFF&
wsmp.Range("BL" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox39_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox39.Value = wsmp.Range("BK" & ActiveCell.Row).Value Then
Else
wsmp.Range("BK" & ActiveCell.Row).Value = TextBox39.Value 'Line PETE
TextBox39.ForeColor = &HFF&
wsmp.Range("BK" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox42_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox42.Value = wsmp.Range("BH" & ActiveCell.Row).Value Then
Else
wsmp.Range("BH" & ActiveCell.Row).Value = TextBox42.Value 'Line Design Progress
TextBox42.ForeColor = &HFF&
wsmp.Range("Bh" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
'Private Sub TextBox46_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'If TextBox46.Value = wsmp.Range("BV" & ActiveCell.Row).Value Then
'Else
'wsmp.Range("BV" & ActiveCell.Row).Value = TextBox46.Value 'Line Design Notes
'TextBox46.ForeColor = &HFF&
'wsmp.Range("BV" & ActiveCell.Row).Font.Color = vbRed wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
'End If
'End Sub

'Private Sub TextBox38_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'If TextBox38.Value = wsmp.Range("BW" & ActiveCell.Row).Value Then
'Else
'wsmp.Range("BW" & ActiveCell.Row).Value = TextBox38.Value 'Station Design notes
'TextBox38.ForeColor = &HFF&
'wsmp.Range("BW" & ActiveCell.Row).Font.Color = vbRed wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
'End If
'End Sub

Private Sub TextBox36_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox36.Value = wsmp.Range("BI" & ActiveCell.Row).Value Then
Else
wsmp.Range("BI" & ActiveCell.Row).Value = TextBox36.Value 'Constructio Pkg
TextBox36.ForeColor = &HFF&
wsmp.Range("BI" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox71_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox71.Value = wsmp.Range("CH" & ActiveCell.Row).Value Then
Else
wsmp.Range("CH" & ActiveCell.Row).Value = TextBox71.Value ' station WF Finish
TextBox71.ForeColor = &HFF&
wsmp.Range("CH" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox72_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox72.Value = wsmp.Range("CG" & ActiveCell.Row).Value Then
Else
wsmp.Range("CG" & ActiveCell.Row).Value = TextBox72.Value ' station WF Start
TextBox72.ForeColor = &HFF&
wsmp.Range("CG" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox73_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox73.Value = wsmp.Range("CJ" & ActiveCell.Row).Value Then
Else
wsmp.Range("CJ" & ActiveCell.Row).Value = TextBox73.Value ' Line WF Finish
TextBox73.ForeColor = &HFF&
wsmp.Range("CJ" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox74_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox74.Value = wsmp.Range("CI" & ActiveCell.Row).Value Then
Else
wsmp.Range("CI" & ActiveCell.Row).Value = TextBox74.Value ' Line WF Start
TextBox74.ForeColor = &HFF&
wsmp.Range("CI" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox35_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox35.Value = wsmp.Range("AJ" & ActiveCell.Row).Value Then
Else
wsmp.Range("AJ" & ActiveCell.Row).Value = TextBox35.Value ' station materials staged
TextBox35.ForeColor = &HFF&
wsmp.Range("AJ" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox34_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox34.Value = wsmp.Range("AM" & ActiveCell.Row).Value Then
Else
wsmp.Range("AM" & ActiveCell.Row).Value = TextBox34.Value ' Station TR
TextBox34.ForeColor = &HFF&
wsmp.Range("AM" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox33_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox33.Value = wsmp.Range("BG" & ActiveCell.Row).Value Then
Else
wsmp.Range("BG" & ActiveCell.Row).Value = TextBox33.Value ' Station design progress
TextBox33.ForeColor = &HFF&
wsmp.Range("BG" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox32_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox32.Value = wsmp.Range("AH" & ActiveCell.Row).Value Then
Else
wsmp.Range("AH" & ActiveCell.Row).Value = TextBox32.Value 'proposed RTC
TextBox32.ForeColor = &HFF&
wsmp.Range("AH" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox31_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox31.Value = wsmp.Range("AG" & ActiveCell.Row).Value Then
Else
wsmp.Range("AG" & ActiveCell.Row).Value = TextBox31.Value 'proposed RTC
TextBox31.ForeColor = &HFF&
wsmp.Range("AG" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox30_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox30.Value = wsmp.Range("AD" & ActiveCell.Row).Value Then
Else
wsmp.Range("AD" & ActiveCell.Row).Value = TextBox30.Value 'survey perm
TextBox30.ForeColor = &HFF&
wsmp.Range("AD" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox29_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox29.Value = wsmp.Range("BE" & ActiveCell.Row).Value Then
Else
wsmp.Range("BE" & ActiveCell.Row).Value = TextBox29.Value 'ROW PEte2
TextBox29.ForeColor = &HFF&
wsmp.Range("BE" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

'Private Sub TextBox26_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'If TextBox26.Value = wsmp.Range("BF" & ActiveCell.Row).Value Then
'Else
'wsmp.Range("BF" & ActiveCell.Row).Value = TextBox26.Value 'ROW PEte2
'TextBox26.ForeColor = &HFF&
'wsmp.Range("BF" & ActiveCell.Row).Font.Color = vbRed
'End If
'End Sub

Private Sub TextBox25_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox25.Value = wsmp.Range("BD" & ActiveCell.Row).Value Then
Else
wsmp.Range("BD" & ActiveCell.Row).Value = TextBox25.Value 'ROW PEte
TextBox25.ForeColor = &HFF&
wsmp.Range("BD" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox28_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox28.Value = wsmp.Range("W" & ActiveCell.Row).Value Then
Else
wsmp.Range("W" & ActiveCell.Row).Value = TextBox28.Value ' station WA
TextBox28.ForeColor = &HFF&
wsmp.Range("W" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Value = wsmp.Range("B" & ActiveCell.Row).Value Then
Else
wsmp.Range("B" & ActiveCell.Row).Value = TextBox1.Value ' Project Name
TextBox1.ForeColor = &HFF&
wsmp.Range("B" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If

End Sub
Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If IsDate(TextBox2.Text) = True Then
    wsmp.Range("C" & ActiveCell.Row).Value = TextBox2.Value ' EISD
    TextBox2.ForeColor = &HFF&
    wsmp.Range("C" & ActiveCell.Row).Font.Color = vbRed
    wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
    CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
 Else
    MsgBox "This does not appear to be a date"
 Cancel = True
End If
End Sub
Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox3.Value = wsmp.Range("D" & ActiveCell.Row).Value Then
Else
wsmp.Range("D" & ActiveCell.Row).Value = TextBox3.Value ' Commercial Operation Date
TextBox3.ForeColor = &HFF&
wsmp.Range("D" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub Combobox6_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox6.Value = wsmp.Range("E" & ActiveCell.Row).Value Then
Else
wsmp.Range("E" & ActiveCell.Row).Value = ComboBox6.Value ' Project Stage
ComboBox6.ForeColor = &HFF&
wsmp.Range("E" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox5.Value = wsmp.Range("G" & ActiveCell.Row).Value Then
Else
wsmp.Range("G" & ActiveCell.Row).Value = TextBox5.Value ' TSR ID
TextBox5.ForeColor = &HFF&
wsmp.Range("G" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox6_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox6.Value = wsmp.Range("S" & ActiveCell.Row).Value Then
Else
wsmp.Range("S" & ActiveCell.Row).Value = TextBox6.Value ' PETE ID
TextBox6.ForeColor = &HFF&
wsmp.Range("S" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub Combobox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox5.Value = wsmp.Range("R" & ActiveCell.Row).Value Then
Else
wsmp.Range("R" & ActiveCell.Row).Value = ComboBox5.Value ' PETE Status
ComboBox5.ForeColor = &HFF&
wsmp.Range("R" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub


Private Sub TextBox8_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox8.Value = wsmp.Range("U" & ActiveCell.Row).Value Then
Else
wsmp.Range("U" & ActiveCell.Row).Value = TextBox8.Value ' Pat Spread
TextBox8.ForeColor = &HFF&
wsmp.Range("U" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub Combobox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox3.Value = wsmp.Range("AV" & ActiveCell.Row).Value Then
Else
wsmp.Range("AV" & ActiveCell.Row).Value = ComboBox3.Value ' Pat Spread
ComboBox3.ForeColor = &HFF&
wsmp.Range("AV" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub Combobox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox4.Value = wsmp.Range("BJ" & ActiveCell.Row).Value Then
Else
wsmp.Range("BJ" & ActiveCell.Row).Value = ComboBox4.Value ' Construction Season
ComboBox4.ForeColor = &HFF&
wsmp.Range("BJ" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox20_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox20.Value = wsmp.Range("T" & ActiveCell.Row).Value Then
Else
wsmp.Range("T" & ActiveCell.Row).Value = TextBox20.Value ' Planning Story
TextBox20.ForeColor = &HFF&
wsmp.Range("T" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox21_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox21.Value = wsmp.Range("Y" & ActiveCell.Row).Value Then
Else
wsmp.Range("Y" & ActiveCell.Row).Value = TextBox21.Value ' Risk
TextBox21.ForeColor = &HFF&
wsmp.Range("Y" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox10_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox10.Value = wsmp.Range("H" & ActiveCell.Row).Value Then
Else
wsmp.Range("H" & ActiveCell.Row).Value = TextBox10.Value ' FIS DSA
TextBox10.ForeColor = &HFF&
wsmp.Range("H" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox11_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox11.Value = wsmp.Range("I" & ActiveCell.Row).Value Then
Else
wsmp.Range("I" & ActiveCell.Row).Value = TextBox11.Value ' sign date
TextBox11.ForeColor = &HFF&
wsmp.Range("I" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub

Private Sub TextBox12_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox12.Value = wsmp.Range("J" & ActiveCell.Row).Value Then
Else
wsmp.Range("J" & ActiveCell.Row).Value = TextBox12.Value ' steady state due date
TextBox12.ForeColor = &HFF&
wsmp.Range("J" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox13_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox13.Value = wsmp.Range("K" & ActiveCell.Row).Value Then
Else
wsmp.Range("K" & ActiveCell.Row).Value = TextBox13.Value ' Short Circuit Due date
TextBox13.ForeColor = &HFF&
wsmp.Range("K" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox14_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox14.Value = wsmp.Range("L" & ActiveCell.Row).Value Then
Else
wsmp.Range("L" & ActiveCell.Row).Value = TextBox14.Value 'Stability Study Due date
TextBox14.ForeColor = &HFF&
wsmp.Range("L" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox16_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox16.Value = wsmp.Range("N" & ActiveCell.Row).Value Then
Else
wsmp.Range("N" & ActiveCell.Row).Value = TextBox16.Value 'FIS Due date
TextBox16.ForeColor = &HFF&
wsmp.Range("N" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
CommandButton4.Visible = True
End If
If IsDate(TextBox16.Text) = True Then
CommandButton4.Visible = True ' if there is an FIS Complete date then turn on the estimate button else its off
Else
CommandButton4.Visible = False
End If
End Sub
Private Sub TextBox15_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox15.Value = wsmp.Range("M" & ActiveCell.Row).Value Then
Else
wsmp.Range("M" & ActiveCell.Row).Value = TextBox15.Value 'FIS Complete
TextBox15.ForeColor = &HFF&
wsmp.Range("M" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox17_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox17.Value = wsmp.Range("O" & ActiveCell.Row).Value Then
Else
wsmp.Range("O" & ActiveCell.Row).Value = TextBox17.Value
TextBox17.ForeColor = &HFF&
wsmp.Range("O" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub Combobox10_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox10.Value = wsmp.Range("P" & ActiveCell.Row).Value Then
Else
wsmp.Range("P" & ActiveCell.Row).Value = ComboBox10.Value
ComboBox10.ForeColor = &HFF&
wsmp.Range("P" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub TextBox19_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox19.Value = wsmp.Range("Q" & ActiveCell.Row).Value Then
Else
wsmp.Range("Q" & ActiveCell.Row).Value = TextBox19.Value
TextBox19.ForeColor = &HFF&
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
wsmp.Range("Q" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
End If
End Sub
'Private Sub TextBox23_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'If TextBox23.Value = wsmp.Range("BB" & ActiveCell.Row).Value Then
'Else
'wsmp.Range("BB" & ActiveCell.Row).Value = TextBox23.Value 'Studies Notes
'TextBox23.ForeColor = &HFF&
'wsmp.Range("BB" & ActiveCell.Row).Font.Color = vbRed wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
'End If
'End Sub
Private Sub Combobox7_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox7.Value = wsmp.Range("BC" & ActiveCell.Row).Value Then
Else
wsmp.Range("BC" & ActiveCell.Row).Value = ComboBox7.Value 'TS Agent
ComboBox7.ForeColor = &HFF&
wsmp.Range("BC" & ActiveCell.Row).Font.Color = vbRed
wsmp.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub


Private Sub UserForm_Initialize()
Set wb = ThisWorkbook
Set wsmp = Worksheets("MP Program")
On Error Resume Next
Label24.Caption = Format(Now(), "mm/dd/yyyy")

SpinButton1.Value = ActiveCell.Row

TextBox1.Value = wsmp.Range("B" & ActiveCell.Row).Value ' Project Name
TextBox2.Value = wsmp.Range("C" & ActiveCell.Row).Value ' EISD
TextBox3.Value = wsmp.Range("D" & ActiveCell.Row).Value ' Commercial Operation Date
ComboBox6.Value = wsmp.Range("E" & ActiveCell.Row).Value ' Stage
ComboBox6.List = [Lists!E2:E12].Value
If IsDate(wsmp.Range("BA" & ActiveCell.Row).Value) = True Then
    If Now() - wsmp.Range("BA" & ActiveCell.Row).Value > 21 Then
        UserForm1.Label61.ForeColor = &HFF&
    End If
UserForm1.Label61.Caption = wsmp.Range("BA" & ActiveCell.Row).Value ' Last Updated
Else
UserForm1.Label61.Caption = " - "

End If
TextBox5.Value = wsmp.Range("G" & ActiveCell.Row).Value ' TSR ID
TextBox6.Value = wsmp.Range("S" & ActiveCell.Row).Value ' PETE ID

TextBox65.Value = wsmp.Range("BX" & ActiveCell.Row).Value ' Station Engineer
ComboBox8.Value = wsmp.Range("BY" & ActiveCell.Row).Value ' Station Resource
ComboBox8.List = [Lists!H2:H12].Value
TextBox68.Value = wsmp.Range("BZ" & ActiveCell.Row).Value ' Line Engineer
ComboBox9.Value = wsmp.Range("CA" & ActiveCell.Row).Value ' Line Resource
ComboBox9.List = [Lists!H2:H12].Value
ComboBox5.Value = wsmp.Range("R" & ActiveCell.Row).Value ' PETE Status
ComboBox5.List = [Lists!F2:F10].Value
TextBox8.Value = Format(wsmp.Range("U" & ActiveCell.Row).Value, "Currency") ' PAT Spread
TextBox20.Value = wsmp.Range("T" & ActiveCell.Row).Value ' Planning Story
TextBox21.Value = wsmp.Range("Y" & ActiveCell.Row).Value 'Risk
ComboBox3.List = [Lists!B2:B30].Value
ComboBox3.Value = wsmp.Range("AV" & ActiveCell.Row).Value 'Location

TextBox70.Value = wsmp.Range("AY" & ActiveCell.Row).Value ' GIR Number
ComboBox11.Value = wsmp.Range("F" & ActiveCell.Row).Value ' Firm
ComboBox11.List = [Lists!J2:J5].Value
'If TextBox9.Value = "D" Then TextBox9.Value = "Dashiell"
'If TextBox9.Value = "P" Then TextBox9.Value = "POWER Engineers"
TextBox69.Value = wsmp.Range("Z" & ActiveCell.Row).Value
ComboBox12.Value = wsmp.Range("AX" & ActiveCell.Row).Value ' ROW PM
ComboBox12.List = [Lists!k2:k5].Value

ComboBox13.Value = wsmp.Range("CN" & ActiveCell.Row).Value ' RTC Category
ComboBox13.List = [Lists!L2:L7].Value

ComboBox14.Value = wsmp.Range("CO" & ActiveCell.Row).Value ' ROW status
ComboBox14.List = [Lists!M2:M8].Value

TextBox67.Value = wsmp.Range("CB" & ActiveCell.Row).Value 'facilities engineer
TextBox10.Value = wsmp.Range("H" & ActiveCell.Row).Value ' FIS DSA
TextBox11.Value = Format(wsmp.Range("I" & ActiveCell.Row).Value, "mm/dd/yyyy") ' Sign Date

TextBox12.Value = Format(wsmp.Range("J" & ActiveCell.Row).Value, "mm/dd/yyyy") ' Steady State Due date
TextBox13.Value = Format(wsmp.Range("K" & ActiveCell.Row).Value, "mm/dd/yyyy") '  Short Circuit Due date
TextBox14.Value = Format(wsmp.Range("L" & ActiveCell.Row).Value, "mm/dd/yyyy") '  Stability Study Due date
TextBox15.Value = Format(wsmp.Range("M" & ActiveCell.Row).Value, "mm/dd/yyyy") '  FIS Due date
TextBox16.Value = wsmp.Range("N" & ActiveCell.Row).Value '  FIS Complete
If IsDate(TextBox16.Text) = True Then
CommandButton4.Visible = True ' if there is an FIS Complete date then turn on the estimate button else its off
Else
CommandButton4.Visible = False
End If
If IsDate(TextBox60.Text) = False Then
CommandButton5.Visible = True ' if there is no date then turn on the estimate button
Else
CommandButton5.Visible = False
End If

If wsmp.Range("CE" & ActiveCell.Row).Value = "True" Then
CheckBox5.Value = True ' TXDOT Permit Required
CheckBox5.ForeColor = &HFF&
Else
CheckBox5.Value = False
End If

If wsmp.Range("CK" & ActiveCell.Row).Value = "True" Then
CheckBox7.Value = True ' CCN Required
CheckBox7.ForeColor = &HFF&
Else
CheckBox7.Value = False
End If

If wsmp.Range("CF" & ActiveCell.Row).Value = "True" Then
CheckBox6.Value = True ' Railway Permit Required
CheckBox6.ForeColor = &HFF&
Else
CheckBox6.Value = False
End If

If wsmp.Range("AO" & ActiveCell.Row).Value = "" Then ' Risk Notes make button colored if notes
Else
CommandButton12.BackColor = &H8080FF
End If

If wsmp.Range("BB" & ActiveCell.Row).Value = "" Then ' Studies Notes make button colored if notes
Else
CommandButton8.BackColor = &HC0FFC0
End If

If wsmp.Range("BF" & ActiveCell.Row).Value = "" Then ' ROW Notes make button colored if notes
Else
CommandButton9.BackColor = &HC0FFC0
End If

If wsmp.Range("BW" & ActiveCell.Row).Value = "" Then ' Station Notes make button coloredif notes
Else
CommandButton10.BackColor = &HC0FFC0
End If

If wsmp.Range("BV" & ActiveCell.Row).Value = "" Then  ' Line design Notes make button colored if notes
Else
CommandButton11.BackColor = &HC0FFC0
End If

If wsmp.Range("CC" & ActiveCell.Row).Value = "" Then  ' make button colored if Station Resources exist
Else
CommandButton6.BackColor = &HC0FFC0
End If

If wsmp.Range("CD" & ActiveCell.Row).Value = "" Then  ' make button colored if Line Resources exist
Else
CommandButton7.BackColor = &HC0FFC0
End If

TextBox17.Value = Format(wsmp.Range("O" & ActiveCell.Row).Value, "mm/dd/yyyy") '
ComboBox10.Value = wsmp.Range("P" & ActiveCell.Row).Value ' SGIA
ComboBox10.List = [Lists!I2:I6].Value
TextBox19.Value = wsmp.Range("Q" & ActiveCell.Row).Value '

'TextBox23.Value = wsmp.Range("BB" & ActiveCell.Row).Value 'Studies Notes
ComboBox7.Value = wsmp.Range("BC" & ActiveCell.Row).Value ' TS Agent
ComboBox7.List = [Lists!C2:C16].Value
TextBox28.Value = wsmp.Range("W" & ActiveCell.Row).Value ' Station WA
TextBox25.Value = wsmp.Range("BD" & ActiveCell.Row).Value ' ROW Pete
'TextBox26.Value = wsmp.Range("BF" & ActiveCell.Row).Value ' ROW
TextBox29.Value = wsmp.Range("BE" & ActiveCell.Row).Value ' ROW Pete2
TextBox30.Value = wsmp.Range("AD" & ActiveCell.Row).Value ' Survey Perm
TextBox31.Value = wsmp.Range("AG" & ActiveCell.Row).Value ' Proposed RTC
If IsDate(TextBox31.Value) = False Then
TextBox31.BackColor = &H8080FF
Else
End If

TextBox32.Value = wsmp.Range("AH" & ActiveCell.Row).Value ' RTC
TextBox33.Value = wsmp.Range("BG" & ActiveCell.Row).Value ' Station design progress
TextBox34.Value = wsmp.Range("AM" & ActiveCell.Row).Value ' Station TR
TextBox35.Value = wsmp.Range("AJ" & ActiveCell.Row).Value ' Materials staged
TextBox36.Value = wsmp.Range("BI" & ActiveCell.Row).Value ' Constructio Pkg
ComboBox4.Value = wsmp.Range("BJ" & ActiveCell.Row).Value ' Construction Season
ComboBox4.List = [Lists!D2:D11].Value
' Waterfall Fields
TextBox72.Value = wsmp.Range("CG" & ActiveCell.Row).Value ' WF Station Start
TextBox71.Value = wsmp.Range("CH" & ActiveCell.Row).Value ' WF Station Finish
TextBox74.Value = wsmp.Range("CI" & ActiveCell.Row).Value ' WF Line Start
TextBox73.Value = wsmp.Range("CJ" & ActiveCell.Row).Value ' WF Line Finish
If TextBox72.Value = "" Then
TextBox72.BackColor = &HC0FFFF
End If
If TextBox71.Value = "" Then
TextBox71.BackColor = &HC0FFFF
End If
If TextBox73.Value = "" Then
TextBox73.BackColor = &HC0FFFF
End If
If TextBox74.Value = "" Then
TextBox74.BackColor = &HC0FFFF
End If




TextBox42.Value = wsmp.Range("BH" & ActiveCell.Row).Value ' Line design Progress
TextBox39.Value = wsmp.Range("BK" & ActiveCell.Row).Value ' Line Pete
TextBox41.Value = wsmp.Range("BL" & ActiveCell.Row).Value ' Line WA
TextBox44.Value = wsmp.Range("AI" & ActiveCell.Row).Value ' Line Materials
TextBox45.Value = wsmp.Range("AK" & ActiveCell.Row).Value ' Line Designs
TextBox43.Value = wsmp.Range("AL" & ActiveCell.Row).Value ' Line TR
TextBox40.Value = wsmp.Range("AP" & ActiveCell.Row).Value ' Station Precon
TextBox47.Value = wsmp.Range("AS" & ActiveCell.Row).Value ' Station Start
TextBox48.Value = wsmp.Range("AT" & ActiveCell.Row).Value ' Station End

If IsDate(TextBox47) = True Then
    If IsDate(TextBox48) = True Then
        Label64.Caption = "<- " & DateDiff("d", TextBox47.Value, TextBox48.Value) & " days ->"
        If DateDiff("d", TextBox47.Value, TextBox48.Value) < 6 Then
        TextBox48.BackColor = &H80FFFF
        TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Station Construction period may be too short"
       
        End If
Else
Label64.Caption = "---"

    End If
End If

TextBox51.Value = wsmp.Range("BM" & ActiveCell.Row).Value ' Station Const. Progress
'TextBox52.Value = wsmp.Range("AU" & ActiveCell.Row).Value ' Station Const. Contractor
ComboBox2.List = [Lists!A2:A7].Value
ComboBox2.Value = wsmp.Range("AU" & ActiveCell.Row).Value ' Station Const. Contractor
TextBox49.Value = wsmp.Range("BO" & ActiveCell.Row).Value ' Station Const. Outages
TextBox50.Value = wsmp.Range("BQ" & ActiveCell.Row).Value ' Station Const. Notes
TextBox58.Value = wsmp.Range("BN" & ActiveCell.Row).Value ' Line Const. Progress
'TextBox59.Value = wsmp.Range("BS" & ActiveCell.Row).Value ' Line Const. Contractor
ComboBox1.List = [Lists!A2:A7].Value
ComboBox1.Value = wsmp.Range("BS" & ActiveCell.Row).Value ' Line Const. Contractor

TextBox53.Value = wsmp.Range("BT" & ActiveCell.Row).Value ' Line Precon
TextBox54.Value = wsmp.Range("AQ" & ActiveCell.Row).Value ' Line Const. Start
TextBox55.Value = wsmp.Range("AR" & ActiveCell.Row).Value ' Line Const. End

If IsDate(TextBox54) = True Then
    If IsDate(TextBox55) = True Then
Label65.Caption = "<- " & DateDiff("d", TextBox54.Value, TextBox55.Value) & " days ->"
Else
Label65.Caption = "---"

    End If
End If

TextBox56.Value = wsmp.Range("BP" & ActiveCell.Row).Value ' Line Outages
TextBox57.Value = wsmp.Range("BR" & ActiveCell.Row).Value ' Line Notes
TextBox60.Value = wsmp.Range("AN" & ActiveCell.Row).Value ' MCPR Target
TextBox61.Value = wsmp.Range("BU" & ActiveCell.Row).Value ' Program Notes
TextBox63.Value = wsmp.Range("AZ" & ActiveCell.Row).Value ' Project Summary

If TextBox11 <> "" Then
If ComboBox10.Value = "" Then
    If TextBox19.Text = "" Then
    TextBox75.Visible = True
  End If
  End If
End If

If TextBox71.Text = "" Then
    TextBox71.BackColor = &H80FFFF
End If

If TextBox72.Text = "" Then
    TextBox72.BackColor = &H80FFFF
End If
If TextBox73.Text = "" Then
    TextBox73.BackColor = &H80FFFF
End If
If TextBox74.Text = "" Then
    TextBox74.BackColor = &H80FFFF
End If
'Engineering Only toggle
If wsmp.Range("CL" & ActiveCell.Row).Value = True Then
OptionButton1.Value = True
OptionButton1.ForeColor = &HFF&
TextBox75.BackColor = &H80000008
Else
 If wsmp.Range("CL" & ActiveCell.Row).Value = False Then
 OptionButton1.Value = False
 End If
End If
'Engineering and Procurement option
If wsmp.Range("CM" & ActiveCell.Row).Value = True Then
OptionButton2.Value = True
OptionButton2.ForeColor = &HFF&
TextBox75.Text = "------------------- Engineering & Procurement ------------------"
TextBox75.BackColor = &H80FF&
Else
 If wsmp.Range("CM" & ActiveCell.Row).Value = False Then
 OptionButton2.Value = False
 End If
End If
'----------------------------------------------------------Below this line are some Logical Checks
'Check there is a valid RTC date
If IsDate(TextBox31.Value) = False Then
TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Proposed RTC is not a valid Date"
Else

End If

' Check that RTC occurs before Const. Starts
  If DateDiff("d", TextBox47.Value, TextBox31.Value) > 0 Then
    TextBox47.BackColor = &H80FFFF
    TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Station Construction Starts before Proposed RTC"
  End If
  If DateDiff("d", TextBox54.Value, TextBox31.Value) > 0 Then
    TextBox54.BackColor = &H80FFFF
    TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Line Construction Starts before Proposed RTC"
  End If
' Check that station construction does not start before design deliverables are complete
  If DateDiff("d", TextBox47.Value, TextBox35.Value) > 0 Then
    TextBox35.BackColor = &H80FFFF
    TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Station Materials deliverd after Station Construction Starts"
  End If
  If DateDiff("d", TextBox36.Value, TextBox47.Value) < 0 Then
     TextBox36.BackColor = &H80FFFF
     TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Station Construction Package deliverd after Station Construction Start"
  End If
If DateDiff("d", TextBox34.Value, TextBox47.Value) < 0 Then
     TextBox34.BackColor = &H80FFFF
     TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Station Construction Task Request Target occurs after Station Construction Starts"
End If
 
' Check Station Construction End is not before Station start.
If DateDiff("d", TextBox47.Value, TextBox48.Value) < 0 Then
     TextBox48.BackColor = &H80FFFF
     TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Station Construction End date preceeds the Start date"
End If
' Check that the Station PreCon is in alignment
If DateDiff("d", TextBox40.Value, TextBox47.Value) < 0 Then
     TextBox40.BackColor = &H80FFFF
     TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Station PreCon not aligned"
End If
' Check that Line construction does not start before design deliverables are complete
  If DateDiff("d", TextBox54.Value, TextBox44.Value) > 0 Then
      TextBox44.BackColor = &H80FFFF
    TextBox64.Value = TextBox64.Value & Chr(13) & "* Line Materials deliverd after Construction Starts"
  End If
  If DateDiff("d", TextBox54.Value, TextBox45.Value) > 0 Then
       TextBox45.BackColor = &H80FFFF
     TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Line Construction Package deliverd after Line Construction Start"
  End If
If DateDiff("d", TextBox54.Value, TextBox43.Value) > 0 Then
     TextBox43.BackColor = &H80FFFF
     TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Line Construction Task Request Target after Line Construction Starts"
End If

' Check Line Construction End is not before Line start.
If DateDiff("d", TextBox54.Value, TextBox55.Value) < 0 Then
     TextBox55.BackColor = &H80FFFF
     TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Line Construction End date preceeds the Start date"
End If
' Missing MCPR - Make field yellow after Construction Start Date
If IsDate(TextBox60) = False Then
TextBox60.BackColor = &H80FFFF
Else
    If DateDiff("d", TextBox60.Value, TextBox54.Value) < 45 Then
    TextBox60.BackColor = &H80FFFF
    TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* MCPR Issue?"
    End If
End If

' Check Station Construction Start is not before WF start.
If DateDiff("d", TextBox47.Value, TextBox72.Value) > 0 Then
     TextBox47.BackColor = &H80FFFF
     TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Station construction start before WF start"
End If
' Check Station Construction End is not after WF end.
If DateDiff("d", TextBox48.Value, TextBox71.Value) < 0 Then
     TextBox48.BackColor = &H80FFFF
     TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Station construction End after WF End"
End If

' Check Line Construction Start is not before WF start.
If DateDiff("d", TextBox54.Value, TextBox74.Value) > 0 Then
     TextBox54.BackColor = &H80FFFF
     TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Line construction start before WF start"
End If

' Check LineConstruction End is not after WF end.
If DateDiff("d", TextBox55.Value, TextBox73.Value) < 0 Then
     TextBox55.BackColor = &H80FFFF
     TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Line construction End after WF End"
End If
' Missing NOMCR - Make field yellow after construction start

' Check Station Construction time too short

'Chedk Line construction time too short

End Sub


' Below is Userform2
VBA Code: 
Public ws As Worksheet


Private Sub CommandButton1_Click()
' Save the changes and then the spreadsheet button
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = Worksheets("POI-POD")
On Error Resume Next


ws.Range("BR" & ActiveCell.Row).Value = Now() ' save last updated
UserForm2.Label61.Caption = Now() ' update the screen
UserForm2.Label61.ForeColor = &HC000&     ' screen color for last updated goes to Green

ActiveWorkbook.Save
CommandButton1.BackColor = &H8000000F     ' turn the save button back to normal

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub


Private Sub CommandButton3_Click()
If IsDate(TextBox60.Value) = False Then
    If IsDate(TextBox54.Text) = True Then
    TextBox60.Text = Format(DateValue(TextBox54.Text) - Val(45), "mm/dd/yyyy")
    ws.Range("AC" & ActiveCell.Row).Value = TextBox60.Value ' MCPR deadline
    ws.Range("A" & ActiveCell.Row).Value = 1
    TextBox60.ForeColor = &HFF&
    ws.Range("AC" & ActiveCell.Row).Font.Color = vbRed
    CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
   
    CommandButton3.Visible = False
    Else
    MsgBox "Need a Construction Start Date"
    End If
End If
End Sub




Private Sub CommandButton4_Click()
 Dim doc As Object, rng As Range
 Application.SendKeys "(%{1068})"
 DoEvents
 'ActiveSheet.Paste
 With CreateObject("Outlook.Application").CreateItem(0)
 .display
 Set doc = .GetInspector.WordEditor
 doc.Range(0, 0).Paste
 .To = "[EMAIL]phil.coomer@oncor.com[/EMAIL]"
 .Subject = TextBox1.Text
 End With
End Sub

Private Sub SpinButton1_Change()
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = Worksheets("MP Program")
On Error Resume Next
UserForm1.Label61.Caption = SpinButton1.Value

'Call UserForm_Initialize
'ActiveCell.Value = SpinButton1.Value
Range("B" & SpinButton1.Value).Activate
Call UserForm_Initialize

End Sub

Private Sub Combobox10_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox10.Value = ws.Range("BW" & ActiveCell.Row).Value Then
Else
ws.Range("BW" & ActiveCell.Row).Value = ComboBox10.Value 'ROW PM
ComboBox10.ForeColor = &HFF&
ws.Range("BW" & ActiveCell.Row).Font.Color = vbRed
ws.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub




Private Sub Combobox9_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox9.Value = ws.Range("BV" & ActiveCell.Row).Value Then
Else
ws.Range("BV" & ActiveCell.Row).Value = ComboBox9.Value 'Statin const contractor
ComboBox9.ForeColor = &HFF&
ws.Range("BV" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub Combobox8_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox8.Value = ws.Range("BT" & ActiveCell.Row).Value Then
Else
ws.Range("BT" & ActiveCell.Row).Value = ComboBox8.Value 'Statin const contractor
ComboBox8.ForeColor = &HFF&
ws.Range("BT" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub Checkbox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If CheckBox1.Value = ws.Range("BX" & ActiveCell.Row).Value Then
Else
ws.Range("BX" & ActiveCell.Row).Value = CheckBox1.Value 'CCN Required
CheckBox1.ForeColor = &HFF&
ws.Range("BX" & ActiveCell.Row).Font.Color = vbRed
ws.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub Checkbox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If CheckBox2.Value = ws.Range("BY" & ActiveCell.Row).Value Then
Else
ws.Range("BY" & ActiveCell.Row).Value = CheckBox2.Value 'CCN Required
CheckBox2.ForeColor = &HFF&
ws.Range("BY" & ActiveCell.Row).Font.Color = vbRed
ws.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub Checkbox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If CheckBox3.Value = ws.Range("BZ" & ActiveCell.Row).Value Then
Else
ws.Range("BZ" & ActiveCell.Row).Value = CheckBox3.Value 'CCN Required
CheckBox3.ForeColor = &HFF&
ws.Range("BZ" & ActiveCell.Row).Font.Color = vbRed
ws.Range("A" & ActiveCell.Row).Value = 1   'mark record as changed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
End If
End Sub
Private Sub Combobox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox1.Value = ws.Range("AX" & ActiveCell.Row).Value Then
Else
ws.Range("AX" & ActiveCell.Row).Value = ComboBox1.Value 'Statin const contractor
ComboBox1.ForeColor = &HFF&
ws.Range("AX" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox66_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox66.Value = ws.Range("R" & ActiveCell.Row).Value Then
Else
ws.Range("R" & ActiveCell.Row).Value = TextBox66.Value 'Station Engineer
TextBox66.ForeColor = &HFF&
ws.Range("R" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox9_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox9.Value = ws.Range("F" & ActiveCell.Row).Value Then
Else
ws.Range("F" & ActiveCell.Row).Value = TextBox9.Value 'LQ
TextBox9.ForeColor = &HFF&
ws.Range("F" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub


Private Sub TextBox65_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox65.Value = ws.Range("BU" & ActiveCell.Row).Value Then
Else
ws.Range("BU" & ActiveCell.Row).Value = TextBox65.Value 'Station Engineer
TextBox65.ForeColor = &HFF&
ws.Range("BU" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub

Private Sub TextBox64_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox64.Value = ws.Range("BS" & ActiveCell.Row).Value Then
Else
ws.Range("BS" & ActiveCell.Row).Value = TextBox64.Value 'Station Engineer
TextBox64.ForeColor = &HFF&
ws.Range("BS" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub


Private Sub TextBox61_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox61.Value = ws.Range("BP" & ActiveCell.Row).Value Then
Else
ws.Range("BP" & ActiveCell.Row).Value = TextBox61.Value 'Program Notes
TextBox61.ForeColor = &HFF&
ws.Range("BP" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub

Private Sub TextBox60_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox60.Value = ws.Range("AC" & ActiveCell.Row).Value Then
Else
ws.Range("AC" & ActiveCell.Row).Value = TextBox60.Value 'MCPR Target
TextBox60.ForeColor = &HFF&
ws.Range("AC" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox57_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox57.Value = ws.Range("BO" & ActiveCell.Row).Value Then
Else
ws.Range("BO" & ActiveCell.Row).Value = TextBox57.Value 'Line Const.notes
TextBox57.ForeColor = &HFF&
ws.Range("BO" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox56_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox56.Value = ws.Range("BN" & ActiveCell.Row).Value Then
Else
ws.Range("BN" & ActiveCell.Row).Value = TextBox56.Value 'Line Const.outages
TextBox56.ForeColor = &HFF&
ws.Range("BN" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox55_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox55.Value = ws.Range("AL" & ActiveCell.Row).Value Then
Else
ws.Range("AL" & ActiveCell.Row).Value = TextBox55.Value 'Line Const. End
TextBox55.ForeColor = &HFF&
ws.Range("AL" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
If IsDate(TextBox54) = True Then
    If IsDate(TextBox55) = True Then
Label65.Caption = "<- " & DateDiff("d", TextBox54.Value, TextBox55.Value) & " days ->"
Else
Label65.Caption = "---"

    End If
End If

End Sub
Private Sub TextBox54_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox54.Value = ws.Range("AK" & ActiveCell.Row).Value Then
Else
ws.Range("AK" & ActiveCell.Row).Value = TextBox54.Value 'Line Const. Start
TextBox54.ForeColor = &HFF&
ws.Range("AK" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If

If IsDate(TextBox54) = True Then
    If IsDate(TextBox55) = True Then
Label65.Caption = "<- " & DateDiff("d", TextBox54.Value, TextBox55.Value) & " days ->"
Else
Label65.Caption = "---"

    End If
End If

End Sub
Private Sub TextBox53_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox53.Value = ws.Range("AJ" & ActiveCell.Row).Value Then
Else
ws.Range("AJ" & ActiveCell.Row).Value = TextBox53.Value 'Line Const. PRECON
TextBox53.ForeColor = &HFF&
ws.Range("AJ" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub

Private Sub Combobox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Application.ScreenUpdating = True

If ComboBox2.Value = ws.Range("AY" & ActiveCell.Row).Value Then
Else
ws.Range("AY" & ActiveCell.Row).Value = ComboBox2.Value 'Line const contractor
UserForm2.ComboBox2.ForeColor = &HFF&
ws.Range("AY" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub


Private Sub TextBox58_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox58.Value = ws.Range("BM" & ActiveCell.Row).Value Then
Else
ws.Range("BM" & ActiveCell.Row).Value = TextBox58.Value 'Line Const. Progress
TextBox58.ForeColor = &HFF&
ws.Range("BM" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox50_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox50.Value = ws.Range("BL" & ActiveCell.Row).Value Then
Else
ws.Range("BL" & ActiveCell.Row).Value = TextBox50.Value 'Station Const.Notes
TextBox50.ForeColor = &HFF&
ws.Range("BL" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox49_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox49.Value = ws.Range("BK" & ActiveCell.Row).Value Then
Else
ws.Range("BK" & ActiveCell.Row).Value = TextBox49.Value 'Station Const. outages
TextBox49.ForeColor = &HFF&
ws.Range("BK" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox51_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox51.Value = ws.Range("BJ" & ActiveCell.Row).Value Then
Else
ws.Range("BJ" & ActiveCell.Row).Value = TextBox51.Value 'Station Const. Progress
TextBox51.ForeColor = &HFF&
ws.Range("BJ" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox48_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox48.Value = ws.Range("AO" & ActiveCell.Row).Value Then
Else
ws.Range("AO" & ActiveCell.Row).Value = TextBox48.Value 'Station end
TextBox48.ForeColor = &HFF&
ws.Range("AO" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
If IsDate(TextBox47) = True Then
    If IsDate(TextBox48) = True Then
Label64.Caption = "<- " & DateDiff("d", TextBox47.Value, TextBox48.Value) & " days ->"
Else
Label64.Caption = "---"

    End If
End If
End Sub
Private Sub TextBox47_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox47.Value = ws.Range("AN" & ActiveCell.Row).Value Then
Else
ws.Range("AN" & ActiveCell.Row).Value = TextBox47.Value 'Station Start
TextBox47.ForeColor = &HFF&
ws.Range("AN" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
If IsDate(TextBox47) = True Then
    If IsDate(TextBox48) = True Then
Label64.Caption = "<- " & DateDiff("d", TextBox47.Value, TextBox48.Value) & " days ->"
Else
Label64.Caption = "---"

    End If
End If
End Sub

Private Sub TextBox40_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox40.Value = ws.Range("AM" & ActiveCell.Row).Value Then
Else
ws.Range("AM" & ActiveCell.Row).Value = TextBox40.Value 'Station Precon
TextBox40.ForeColor = &HFF&
ws.Range("AM" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox43_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox43.Value = ws.Range("AB" & ActiveCell.Row).Value Then
Else
ws.Range("AB" & ActiveCell.Row).Value = TextBox43.Value 'Line TR
TextBox43.ForeColor = &HFF&
ws.Range("AB" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox45_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox45.Value = ws.Range("AA" & ActiveCell.Row).Value Then
Else
ws.Range("AA" & ActiveCell.Row).Value = TextBox45.Value 'Line designs
TextBox45.ForeColor = &HFF&
ws.Range("AA" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub

Private Sub TextBox44_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox44.Value = ws.Range("Z" & ActiveCell.Row).Value Then
Else
ws.Range("Z" & ActiveCell.Row).Value = TextBox44.Value 'Line materials
TextBox44.ForeColor = &HFF&
ws.Range("Z" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox41_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox41.Value = ws.Range("N" & ActiveCell.Row).Value Then
Else
ws.Range("N" & ActiveCell.Row).Value = TextBox41.Value 'Line WA
TextBox41.ForeColor = &HFF&
ws.Range("N" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox39_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox39.Value = ws.Range("BH" & ActiveCell.Row).Value Then
Else
ws.Range("BH" & ActiveCell.Row).Value = TextBox39.Value 'Line PETE
TextBox39.ForeColor = &HFF&
ws.Range("BH" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox42_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox42.Value = ws.Range("BG" & ActiveCell.Row).Value Then
Else
ws.Range("BG" & ActiveCell.Row).Value = TextBox42.Value 'Line design Progress
TextBox42.ForeColor = &HFF&
ws.Range("BG" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox46_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox46.Value = ws.Range("BI" & ActiveCell.Row).Value Then
Else
ws.Range("BI" & ActiveCell.Row).Value = TextBox46.Value 'Line design notes
TextBox46.ForeColor = &HFF&
ws.Range("BI" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox38_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox38.Value = ws.Range("BF" & ActiveCell.Row).Value Then
Else
ws.Range("BF" & ActiveCell.Row).Value = TextBox38.Value 'Station Design notes
TextBox38.ForeColor = &HFF&
ws.Range("BF" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub Combobox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox4.Value = ws.Range("AZ" & ActiveCell.Row).Value Then
Else
ws.Range("AZ" & ActiveCell.Row).Value = ComboBox4.Value 'Construction Season
ComboBox4.ForeColor = &HFF&
ws.Range("AZ" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox36_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox36.Value = ws.Range("AG" & ActiveCell.Row).Value Then
Else
ws.Range("AG" & ActiveCell.Row).Value = TextBox36.Value 'Construction Pkg
TextBox36.ForeColor = &HFF&
ws.Range("AG" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox35_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox35.Value = ws.Range("AE" & ActiveCell.Row).Value Then
Else
ws.Range("AE" & ActiveCell.Row).Value = TextBox35.Value 'materials staged
TextBox35.ForeColor = &HFF&
ws.Range("AE" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox34_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox34.Value = ws.Range("AH" & ActiveCell.Row).Value Then
Else
ws.Range("AH" & ActiveCell.Row).Value = TextBox34.Value 'Station TR
TextBox34.ForeColor = &HFF&
ws.Range("AH" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox33_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox33.Value = ws.Range("BE" & ActiveCell.Row).Value Then
Else
ws.Range("BE" & ActiveCell.Row).Value = TextBox33.Value 'Station Design Progress
TextBox33.ForeColor = &HFF&
ws.Range("BE" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox32_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox32.Value = ws.Range("Y" & ActiveCell.Row).Value Then
Else
ws.Range("Y" & ActiveCell.Row).Value = TextBox32.Value 'RTC
TextBox32.ForeColor = &HFF&
ws.Range("Y" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox31_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox31.Value = ws.Range("X" & ActiveCell.Row).Value Then
Else
ws.Range("X" & ActiveCell.Row).Value = TextBox31.Value 'Proposed RTC
TextBox31.ForeColor = &HFF&
ws.Range("X" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub

Private Sub TextBox63_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox63.Value = ws.Range("W" & ActiveCell.Row).Value Then
Else
ws.Range("W" & ActiveCell.Row).Value = TextBox63.Value 'Exhibits finalized
TextBox63.ForeColor = &HFF&
ws.Range("W" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub

Private Sub TextBox20_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox20.Value = ws.Range("V" & ActiveCell.Row).Value Then
Else
ws.Range("V" & ActiveCell.Row).Value = TextBox20.Value 'Proposed Exhibits date
TextBox20.ForeColor = &HFF&
ws.Range("V" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub

Private Sub TextBox30_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox30.Value = ws.Range("U" & ActiveCell.Row).Value Then
Else
ws.Range("U" & ActiveCell.Row).Value = TextBox30.Value 'Survey Permission
TextBox30.ForeColor = &HFF&
ws.Range("U" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox29_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox29.Value = ws.Range("BC" & ActiveCell.Row).Value Then
Else
ws.Range("BC" & ActiveCell.Row).Value = TextBox29.Value 'ROW PETE2
TextBox29.ForeColor = &HFF&
ws.Range("BC" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub

Private Sub TextBox26_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox26.Value = ws.Range("BD" & ActiveCell.Row).Value Then
Else
ws.Range("BD" & ActiveCell.Row).Value = TextBox26.Value 'ROW notes
TextBox26.ForeColor = &HFF&
ws.Range("BD" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox25_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox25.Value = ws.Range("BB" & ActiveCell.Row).Value Then
Else
ws.Range("BB" & ActiveCell.Row).Value = TextBox25.Value 'ROW PETE
TextBox25.ForeColor = &HFF&
ws.Range("BB" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1

End If
End Sub
Private Sub TextBox28_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox28.Value = ws.Range("O" & ActiveCell.Row).Value Then
Else
ws.Range("O" & ActiveCell.Row).Value = TextBox28.Value 'Station WA
TextBox28.ForeColor = &HFF&
ws.Range("O" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub

Private Sub Combobox7_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox7.Value = ws.Range("BA" & ActiveCell.Row).Value Then
Else
ws.Range("BA" & ActiveCell.Row).Value = ComboBox7.Value 'TS Agent
ComboBox7.ForeColor = &HFF&
ws.Range("BA" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox23_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox23.Value = ws.Range("BQ" & ActiveCell.Row).Value Then
Else
ws.Range("BQ" & ActiveCell.Row).Value = TextBox23.Value 'Studies notes
TextBox23.ForeColor = &HFF&
ws.Range("BQ" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox18_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox18.Value = ws.Range("Q" & ActiveCell.Row).Value Then
Else
ws.Range("Q" & ActiveCell.Row).Value = TextBox18.Value 'CIAC PAID
TextBox18.ForeColor = &HFF&
ws.Range("Q" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox17_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox17.Value = ws.Range("P" & ActiveCell.Row).Value Then
Else
ws.Range("P" & ActiveCell.Row).Value = TextBox17.Value 'CIAC Invoiced
TextBox17.ForeColor = &HFF&
ws.Range("P" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox14_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox14.Value = ws.Range("I" & ActiveCell.Row).Value Then
Else
ws.Range("I" & ActiveCell.Row).Value = TextBox14.Value 'Customer 1 line & Functional dwgs
TextBox14.ForeColor = &HFF&
ws.Range("I" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox13_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox13.Value = ws.Range("H" & ActiveCell.Row).Value Then
Else
ws.Range("H" & ActiveCell.Row).Value = TextBox13.Value 'TE & ROW Reviewed
TextBox13.ForeColor = &HFF&
ws.Range("H" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox12_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox12.Value = ws.Range("G" & ActiveCell.Row).Value Then
Else
ws.Range("G" & ActiveCell.Row).Value = TextBox12.Value 'RIA received
TextBox12.ForeColor = &HFF&
ws.Range("G" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox11_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox11.Value = ws.Range("K" & ActiveCell.Row).Value Then
Else
ws.Range("K" & ActiveCell.Row).Value = TextBox11.Value 'Sign date
TextBox11.ForeColor = &HFF&
ws.Range("K" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox10_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox10.Value = ws.Range("J" & ActiveCell.Row).Value Then
Else
ws.Range("J" & ActiveCell.Row).Value = TextBox10.Value 'FEA DSA
TextBox10.ForeColor = &HFF&
ws.Range("J" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub Combobox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox3.Value = ws.Range("As" & ActiveCell.Row).Value Then
Else
ws.Range("AS" & ActiveCell.Row).Value = ComboBox3.Value 'Location Location Location
ComboBox3.ForeColor = &HFF&
ws.Range("AS" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub

Private Sub Combobox11_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox11.Value = ws.Range("CA" & ActiveCell.Row).Value Then
Else
ws.Range("CA" & ActiveCell.Row).Value = ComboBox11.Value 'RTC Category
ComboBox11.ForeColor = &HFF&
ws.Range("CA" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub Combobox12_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox12.Value = ws.Range("CB" & ActiveCell.Row).Value Then
Else
ws.Range("CB" & ActiveCell.Row).Value = ComboBox12.Value 'ROW status
ComboBox12.ForeColor = &HFF&
ws.Range("CO" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox21_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox21.Value = ws.Range("AU" & ActiveCell.Row).Value Then
Else
ws.Range("AU" & ActiveCell.Row).Value = TextBox21.Value 'Risk
TextBox21.ForeColor = &HFF&
ws.Range("AU" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox15_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox15.Value = ws.Range("L" & ActiveCell.Row).Value Then
Else
ws.Range("L" & ActiveCell.Row).Value = TextBox15.Value 'Planning Story
TextBox15.ForeColor = &HFF&
ws.Range("L" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub

Private Sub TextBox8_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox8.Value = ws.Range("AV" & ActiveCell.Row).Value Then
Else
ws.Range("AV" & ActiveCell.Row).Value = TextBox8.Value 'PETE Status
TextBox8.ForeColor = &HFF&
ws.Range("AV" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub Combobox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox5.Value = ws.Range("T" & ActiveCell.Row).Value Then
Else
ws.Range("T" & ActiveCell.Row).Value = ComboBox5.Value 'PETE Status
ComboBox5.ForeColor = &HFF&
ws.Range("T" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox6_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox6.Value = ws.Range("S" & ActiveCell.Row).Value Then
Else
ws.Range("S" & ActiveCell.Row).Value = TextBox6.Value 'PETE ID
TextBox6.ForeColor = &HFF&
ws.Range("S" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub TextBox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox5.Value = ws.Range("E" & ActiveCell.Row).Value Then
Else
ws.Range("E" & ActiveCell.Row).Value = TextBox5.Value 'TSR ID
TextBox5.ForeColor = &HFF&
ws.Range("E" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub
Private Sub Combobox6_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ComboBox6.Value = ws.Range("D" & ActiveCell.Row).Value Then
Else
ws.Range("D" & ActiveCell.Row).Value = ComboBox6.Value 'Stage
ComboBox6.ForeColor = &HFF&
ws.Range("D" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox2.Value = ws.Range("C" & ActiveCell.Row).Value Then
Else
ws.Range("C" & ActiveCell.Row).Value = TextBox2.Value 'EISD
TextBox2.ForeColor = &HFF&
ws.Range("C" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Value = ws.Range("B" & ActiveCell.Row).Value Then
Else
ws.Range("B" & ActiveCell.Row).Value = TextBox1.Value 'Project Name
TextBox1.ForeColor = &HFF&
ws.Range("B" & ActiveCell.Row).Font.Color = vbRed
CommandButton1.BackColor = &H80FFFF     ' turn the save button yellow
ws.Range("A" & ActiveCell.Row).Value = 1
End If
End Sub



Private Sub UserForm_Initialize()
Set wb = ThisWorkbook
'Dim ws As Worksheet
Set ws = Worksheets("POI-POD")
On Error Resume Next
Label24.Caption = Format(Now(), "mm/dd/yyyy")
SpinButton1.Value = ActiveCell.Row

If IsDate(ws.Range("BR" & ActiveCell.Row).Value) = True Then
    If Now() - wsmp.Range("BR" & ActiveCell.Row).Value > 21 Then
        UserForm2.Label61.ForeColor = &HFF&
    End If
UserForm2.Label61.Caption = ws.Range("BR" & ActiveCell.Row).Value ' Last Updated
Else
UserForm2.Label61.Caption = " - "

End If



TextBox1.Value = ws.Range("B" & ActiveCell.Row).Value ' Project Name
TextBox2.Value = ws.Range("C" & ActiveCell.Row).Value ' EISD

ComboBox6.Value = ws.Range("D" & ActiveCell.Row).Value ' Stage
ComboBox6.List = [Lists!G2:G13].Value

ComboBox10.Value = ws.Range("BW" & ActiveCell.Row).Value ' ROW PM
ComboBox10.List = [Lists!k2:k5].Value

If ws.Range("BX" & ActiveCell.Row).Value = "True" Then
CheckBox1.Value = True ' CCN Required
CheckBox1.ForeColor = &HFF&
Else
CheckBox1.Value = False
End If

If ws.Range("BY" & ActiveCell.Row).Value = "True" Then
CheckBox2.Value = True ' TXDOT Permit Required
CheckBox2.ForeColor = &HFF&
Else
CheckBox2.Value = False
End If

If ws.Range("BZ" & ActiveCell.Row).Value = "True" Then
CheckBox3.Value = True ' Railway Permit Required
CheckBox3.ForeColor = &HFF&
Else
CheckBox3.Value = False
End If



TextBox5.Value = ws.Range("E" & ActiveCell.Row).Value ' TSR ID
TextBox6.Value = ws.Range("S" & ActiveCell.Row).Value ' PETE ID
ComboBox5.Value = ws.Range("T" & ActiveCell.Row).Value ' PETE Status
ComboBox5.List = [Lists!F2:F10].Value
TextBox8.Value = Format(ws.Range("AV" & ActiveCell.Row).Value, "Currency") ' PAT Spread

TextBox21.Value = ws.Range("AU" & ActiveCell.Row).Value 'Risk
ComboBox3.Value = ws.Range("AS" & ActiveCell.Row).Value 'Location
ComboBox3.List = [Lists!B2:B20].Value

ComboBox11.Value = ws.Range("CA" & ActiveCell.Row).Value 'RTC Category
ComboBox11.List = [Lists!L2:L7].Value
ComboBox12.Value = ws.Range("CB" & ActiveCell.Row).Value 'ROW Status
ComboBox12.List = [Lists!M2:M8].Value

TextBox9.Value = ws.Range("F" & ActiveCell.Row).Value ' Load Questionaire

TextBox10.Value = ws.Range("J" & ActiveCell.Row).Value ' DSA or FEA
TextBox11.Value = Format(ws.Range("K" & ActiveCell.Row).Value, "mm/dd/yyyy") ' Sign Date

TextBox12.Value = Format(ws.Range("G" & ActiveCell.Row).Value, "mm/dd/yyyy") ' RIA Received
TextBox13.Value = Format(ws.Range("H" & ActiveCell.Row).Value, "mm/dd/yyyy") '  TE & ROW REview
TextBox14.Value = Format(ws.Range("I" & ActiveCell.Row).Value, "mm/dd/yyyy") '  Customer 1 Line & Functionaldwg
TextBox15.Value = Format(ws.Range("L" & ActiveCell.Row).Value, "mm/dd/yyyy") '  Planning story uploaded
TextBox16.Value = ws.Range("N" & ActiveCell.Row).Value '  FIS Complete

TextBox17.Value = ws.Range("P" & ActiveCell.Row).Value ' CIAC Invoiced
TextBox18.Value = ws.Range("Q" & ActiveCell.Row).Value ' CIAC PAID
TextBox19.Value = ws.Range("Q" & ActiveCell.Row).Value '

TextBox23.Value = ws.Range("BQ" & ActiveCell.Row).Value 'Studies Notes
ComboBox7.Value = ws.Range("BA" & ActiveCell.Row).Value ' TS Agent
ComboBox7.List = [Lists!c2:c15].Value
TextBox28.Value = ws.Range("O" & ActiveCell.Row).Value ' Station WA
TextBox25.Value = ws.Range("BB" & ActiveCell.Row).Value ' ROW Pete
TextBox26.Value = ws.Range("BD" & ActiveCell.Row).Value ' ROW Notes
TextBox29.Value = ws.Range("BC" & ActiveCell.Row).Value ' ROW Pete2
TextBox20.Value = ws.Range("V" & ActiveCell.Row).Value ' Proposed Exhibits Date
TextBox30.Value = ws.Range("U" & ActiveCell.Row).Value ' Survey Perm
TextBox63.Value = ws.Range("W" & ActiveCell.Row).Value ' Exhibits Finalized
TextBox31.Value = ws.Range("X" & ActiveCell.Row).Value ' Proposed RTC
TextBox32.Value = ws.Range("Y" & ActiveCell.Row).Value ' RTC
TextBox33.Value = ws.Range("BE" & ActiveCell.Row).Value ' Station design progress
TextBox64.Value = ws.Range("BS" & ActiveCell.Row).Value ' Station Engineer
ComboBox8.Value = ws.Range("BT" & ActiveCell.Row).Value ' Station Resource
ComboBox8.List = [Lists!H2:H12].Value
TextBox65.Value = ws.Range("BU" & ActiveCell.Row).Value ' Line Engineer
ComboBox9.Value = ws.Range("BV" & ActiveCell.Row).Value ' Line Resource
ComboBox9.List = [Lists!H2:H12].Value
TextBox34.Value = ws.Range("AH" & ActiveCell.Row).Value ' Station TR
TextBox66.Value = ws.Range("R" & ActiveCell.Row).Value ' NOMCR
TextBox35.Value = ws.Range("AE" & ActiveCell.Row).Value ' Station Materials staged
TextBox36.Value = ws.Range("AG" & ActiveCell.Row).Value ' Constructio Pkg
ComboBox4.Value = ws.Range("AZ" & ActiveCell.Row).Value ' Construction Season
ComboBox4.List = [Lists!D2:D12].Value
TextBox38.Value = ws.Range("BF" & ActiveCell.Row).Value ' Station Design notes
TextBox46.Value = ws.Range("BI" & ActiveCell.Row).Value ' Line design notesSt
TextBox42.Value = ws.Range("BG" & ActiveCell.Row).Value ' Line design Progress
TextBox39.Value = ws.Range("BH" & ActiveCell.Row).Value ' Line Pete
TextBox41.Value = ws.Range("N" & ActiveCell.Row).Value ' Line WA
TextBox44.Value = ws.Range("Z" & ActiveCell.Row).Value ' Line Materials
TextBox45.Value = ws.Range("AA" & ActiveCell.Row).Value ' Line Designs
TextBox43.Value = ws.Range("AB" & ActiveCell.Row).Value ' Line TR
TextBox40.Value = ws.Range("AM" & ActiveCell.Row).Value ' Station Precon
TextBox47.Value = ws.Range("AN" & ActiveCell.Row).Value ' Station Start
TextBox48.Value = ws.Range("AO" & ActiveCell.Row).Value ' Station End
TextBox51.Value = ws.Range("BJ" & ActiveCell.Row).Value ' Station Const. Progress
'TextBox52.Value = ws.Range("AX" & ActiveCell.Row).Value ' Station Const. Contractor
ComboBox1.List = [Lists!A2:A7].Value
ComboBox1.Value = ws.Range("AX" & ActiveCell.Row).Value ' Station Const. Contractor

If IsDate(TextBox47) = True Then
    If IsDate(TextBox48) = True Then
Label64.Caption = "<- " & DateDiff("d", TextBox47.Value, TextBox48.Value) & " days ->"
Else
Label64.Caption = "---"

    End If
End If

TextBox49.Value = ws.Range("BK" & ActiveCell.Row).Value ' Station Const. Outages
TextBox50.Value = ws.Range("BL" & ActiveCell.Row).Value ' Station Const. Notes
TextBox58.Value = ws.Range("BM" & ActiveCell.Row).Value ' Line Const. Progress
'TextBox59.Value = ws.Range("AY" & ActiveCell.Row).Value ' Line Const. Contractor
ComboBox2.List = [Lists!A2:A7].Value
ComboBox2.Value = ws.Range("AY" & ActiveCell.Row).Value 'Line Const. Contractor

TextBox53.Value = ws.Range("AJ" & ActiveCell.Row).Value ' Line Precon
TextBox54.Value = ws.Range("AK" & ActiveCell.Row).Value ' Line Const. Start
TextBox55.Value = ws.Range("AL" & ActiveCell.Row).Value ' Line Const. End

If IsDate(TextBox54) = True Then
    If IsDate(TextBox55) = True Then
Label65.Caption = "<- " & DateDiff("d", TextBox54.Value, TextBox55.Value) & " days ->"
Else
Label65.Caption = "---"

    End If
End If

TextBox56.Value = ws.Range("BN" & ActiveCell.Row).Value ' Line Outages
TextBox57.Value = ws.Range("BO" & ActiveCell.Row).Value ' Line Notes
TextBox60.Value = ws.Range("AC" & ActiveCell.Row).Value ' MCPR Target
If IsDate(TextBox60.Value) = True Then ' if we need a MCPR date show the Estimate button.
CommandButton3.Visible = False
Else
CommandButton3.Visible = True
End If

TextBox61.Value = ws.Range("BP" & ActiveCell.Row).Value ' Program Notes

If TextBox10.Value = "DSA" Then
    TextBox75.Visible = True
    Else
    TextBox75.Visible = False
End If

'----------------------------------------------------------Below this line are some Logical Checks
TextBox67 = ""
' Check that station construction does not start before design deliverables are complete
  If DateDiff("d", TextBox47.Value, TextBox35.Value) > 0 Then
    TextBox35.BackColor = &H80FFFF
    TextBox67.Value = TextBox67.Value & Chr(13) & Chr(13) & "* Materials deliverd after Station Construction Starts"
  End If
  If DateDiff("d", TextBox36.Value, TextBox47.Value) < 0 Then
     TextBox36.BackColor = &H80FFFF
     TextBox67.Value = TextBox67.Value & Chr(13) & Chr(13) & "* Construction Package deliverd after Station Construction Start"
  End If
If DateDiff("d", TextBox34.Value, TextBox47.Value) < 0 Then
     TextBox34.BackColor = &H80FFFF
     TextBox67.Value = TextBox67.Value & Chr(13) & Chr(13) & "* Construction Task Request Target occurs after Station Construction Starts"
End If
 
' Check Station Construction End is not before Station start.
If DateDiff("d", TextBox47.Value, TextBox48.Value) < 0 Then
     TextBox48.BackColor = &H80FFFF
     TextBox67.Value = TextBox67.Value & Chr(13) & Chr(13) & "* Station Construction End date preceeds the Start date"
End If
' Check that the Station PreCon is in alignment with construction & Designs
If DateDiff("d", TextBox40.Value, TextBox47.Value) < 0 Then
     TextBox40.BackColor = &H80FFFF
     TextBox67.Value = TextBox67.Value & Chr(13) & Chr(13) & "* Station PreCon not aligned"
End If
If DateDiff("d", TextBox36.Value, TextBox47.Value) < 0 Then
     TextBox36.BackColor = &H80FFFF
     TextBox67.Value = TextBox67.Value & Chr(13) & Chr(13) & "* Station PreCon set before designs ready"
End If



' Check that Line construction does not start before design deliverables are complete
'  If DateDiff("d", TextBox54.Value, TextBox44.Value) > 0 Then
'      TextBox44.BackColor = &H80FFFF
'    TextBox64.Value = "* Line Materials deliverd after Construction Starts"
'  End If
'  If TextBox54.Value < TextBox45.Value Then
'     TextBox45.BackColor = &H80FFFF
'     TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Line Construction Package deliverd after Line Construction Start"
'  End If
'If DateDiff("d", TextBox54.Value, TextBox43.Value) > 0 Then
'     TextBox43.BackColor = &H80FFFF
'     TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Line Construction Task Request Target after Line Construction Starts"
'End If

' Check Line Construction End is not before Line start.
'If DateDiff("d", TextBox54.Value, TextBox55.Value) < 0 Then
'     TextBox55.BackColor = &H80FFFF
'     TextBox64.Value = TextBox64.Value & Chr(13) & Chr(13) & "* Line Construction End date preceeds the Start date"
'End If
' Missing MCPR - Make field yellow after Construction Start Date


' Missing NOMCR - Make field yellow after construction start

' Check Station Construction time too short

'Chedk Line construction time too short



End Sub
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Using VBA Doubleclick on a cell containing formula
2
Replies
14
Views
415
Peter_SSs
Peter_SSs
nemmi69
  • Question
Stop hyperlinks running when cells are locked
Replies
1
Views
201
nemmi69
nemmi69
D
  • Question
Double click set file path to a cell and include as hyperlink
Replies
1
Views
401
Micron
M
R
  • Question
Hide Unhide entire row
Replies
1
Views
283
rollis13
rollis13
W
  • Solved
Moving/transferring data between tables with matching column names, not by column order - vba
Replies
5
Views
235
Alex Blakenburg
A

Forum statistics

Threads
1,215,013
Messages
6,122,694
Members
449,092
Latest member
snoom82

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top