Private Sub MultiPage1_Change()
Dim i As Integer
Dim num_alternatives As Integer
Dim found As Boolean
Dim SearchSheet As Worksheet
Dim PreviousSORs As String
Dim ArmySORs(1 To 8) As String
Dim AirForceSORs(1 To 3) As String
Dim NavySORs(1 To 26) As String
Dim MarineSORs(1 To 2) As String
Dim OtherSOROptions(1 To 6) As String
Dim Army As Boolean
Dim Navy As Boolean
Dim AirForce As Boolean
Dim Marines As Boolean
Dim Other As Boolean
'this handler gets called every time there is a change to a new page
'we'll use this to handle a lot of the functions associated with editing an alternative
'every time there is a page change, we check to see if we're editing, and if so, we do anything that needs to be done on that page
If EnableEvents = False Then
Exit Sub
End If
If (MultiPage1.Value = 1) And (CurrentFunction = "Edit") Then
'we're editing, and we're on the Existing Capabilities page, so we need to check for MILCON,
If ((comboFacilities.ListIndex = 1) Or (comboFacilities.ListIndex = 2)) Then
'there's a choice that requires MILCON - make sure they're happy with the current amount
num_alternatives = Sheets("Tactical").Range("AG1").Value
found = False
For i = 1 To num_alternatives
If Sheets("Tactical Ancillary").Range("A1").Offset(i).Value = edit_alternative_name Then
found = True
Exit For
End If
Next i
If Not found Then
MsgBox "Error in Page Change Event Handler - Facilities. Alternative Not Found.", vbCritical, "Error"
Exit Sub
End If
milcon_required = InputBox("Estimated facility MILCON requirement. Enter 0 if not available.", "Estimated MILCON Requirement", Sheets("Tactical Ancillary").Range("P1").Offset(i).Value)
End If
ElseIf (MultiPage1.Value = 2) And (CurrentFunction = "Edit") And (CurrentModelType = "Tactical") Then
'we're editing, and we're on the Strategic Posturing page, so we need to see if they said Tech Data couldn't be purchased - only for tactical alternatives
num_alternatives = Sheets("Tactical").Range("AG1").Value
found = False
For i = 1 To num_alternatives
If Sheets("Tactical Ancillary").Range("A1").Offset(i).Value = edit_alternative_name Then
found = True
Exit For
End If
Next i
If Not found Then
MsgBox "Error in Tech Orders Change Event Handler. Tactical alternative Not Found.", vbCritical, "Error"
Exit Sub
End If
'get the values from the existing entry
legal_office = Sheets("Tactical Ancillary").Range("Q1").Offset(i).Value
If legal_office = "Not Contacted" Then
'blank it out
legal_office = ""
End If
legal_date = Sheets("Tactical Ancillary").Range("R1").Offset(i).Value
If legal_date = "N/A" Then
'blank it out
legal_date = ""
End If
legal_date = InputBox("Please enter the date legal was contacted. Leave blank if unknown:", "Legal Contact Date", legal_date)
legal_office = InputBox("Please enter the office symbol of the legal office contacted. Leave blank if unknown:", "Legal Office", legal_office)
ElseIf (MultiPage1.Value = 3) And CurrentFunction = "Edit" Then
'we're editing, and we're on the ROI page, so we need to do the common repairable check
If (Not DoneEditingCommon) And optCommonRepairable_Yes.Value = True Then
'the DoneEditingCommon flag keeps us from getting caught in a loop every time we change back to this page
EnableEvents = False
MultiPage1.Pages(9).Visible = True
MultiPage1.Value = 9
For i = 0 To 8
MultiPage1.Pages(i).Visible = False
Next i
MultiPage1.Pages(10).Visible = False
MultiPage1.Pages(11).Visible = False
EnableEvents = True
setup_common_form
End If
ElseIf (MultiPage1.Value = 5) And CurrentFunction = "Edit" Then
'we're editing, and we're on the first Additional Data page, so we need to check the Organic SOR for "other"
'this is pain - need to prepopulate their prior selections
If CurrentModelType = "Tactical" Then
num_alternatives = Sheets("Tactical").Range("AG1").Value
Set SearchSheet = Sheets("Tactical Ancillary")
Else
num_alternatives = Sheets("Strategic").Range("AG1").Value
Set SearchSheet = Sheets("Strategic Ancillary")
End If
found = False
For i = 1 To num_alternatives
If SearchSheet.Range("A1").Offset(i).Value = edit_alternative_name Then
found = True
Exit For
End If
Next i
If Not found Then
MsgBox "Error in Organic SOR Change Event Handler. Alternative Not Found.", vbCritical, "Error"
Exit Sub
End If
If CurrentModelType = "Tactical" Then
PreviousSORs = SearchSheet.Range("H1").Offset(i).Value
Else
PreviousSORs = SearchSheet.Range("F1").Offset(i).Value
End If
'now the fun part - need to determine which options to pre-populate
'build some arrays of all the depot source of repairs
ArmySORs(1) = "Anniston Army Depot (ANAD), AL"
ArmySORs(2) = "Corpus Christi Army Depot (CCAD), TX"
ArmySORs(3) = "Pine Bluff Arsenal, AR"
ArmySORs(4) = "Letterkenny Army Depot (LEAD), Chambersburg, PA"
ArmySORs(5) = "Pirmasens Army Depot, Pirmasens, Germany"
ArmySORs(6) = "Red River Army Depot (RRAD), Texarkana, TX"
ArmySORs(7) = "Sierra Army Depot, Herlong, CA"
ArmySORs(8) = "Tobyhanna Army Depot (TYAD), PA"
NavySORs(1) = "Naval Surface Warfare Center, Carderock Division (NSWCCD), West Bethesda, MD"
NavySORs(2) = "Combat Directions Systems Activity (CDSA) Dam Deck, NSWC Dahlgren Division, Virginia Beach, VA"
NavySORs(3) = "Space and Naval Warfare Systems Center Atlantic (SSC LANT), Charleston, SC"
NavySORs(4) = "Naval Air Warfare Center, Weapons Division, China Lake CA"""
NavySORs(5) = "Fleet Readiness Center East (FRC-E), Cherry Point NC"
NavySORs(6) = "Naval Surface Warfare Center (NSWC), Crane Division, Crane IN"
NavySORs(7) = "Weapons Support Facility Yorktown, Detachment Earle, Colts Neck NJ"
NavySORs(8) = "Space and Naval Warfare Systems Center Atlantic (SSC LANT), Portsmouth, VA"
NavySORs(9) = "Space and Naval Warfare Systems Center Pacific (SSC PAC), San Diego, CA"
NavySORs(10) = "Naval Surface Warfare Center (NSWC), Indian Head Division, Indian Head MD"
NavySORs(11) = "Fleet Readiness Center South East (FRC-SE), Jacksonville FL"
NavySORs(12) = "Naval Air Warfare Center, Aircraft Division, Lakehurst, Lakehurst NJ"
NavySORs(13) = "Fleet Readiness Center, South West (FRC-SW), North Island, San Diego CA"
NavySORs(14) = "Naval Shipyard (NSY) Norfolk, Portsmouth VA"
NavySORs(15) = "Naval Undersea Warfare Center (NUWC), Newport Division, Newport, RI"
NavySORs(16) = "Naval Explosive Ordnance Disposal Technology Center (NEOTC), Indian Head MD"
NavySORs(17) = "Naval Surface Warfare Center (NSWC), Corona Division, Corona, CA"
NavySORs(18) = "Naval Air Warfare Center, Weapons Division, Point Mugu, Point Mugu, CA"
NavySORs(19) = "Puget Sound Naval Shipyard and Intermediate Maintenance Facility (PSNSY & IMF), Bremerton WA"
NavySORs(20) = "Naval Shipyard (NSY) Portsmouth NH"
NavySORs(21) = "Naval Air Warfare Center, Aircraft Division, Patuxent River MD (St. Inigoes)"
NavySORs(22) = "Naval Undersea Warfare Center (NUWC), Keyport Division, Keyport, WA"
NavySORs(23) = "Weapons Support Facility Yorktown, Detachment Charleston, Charleston SC"
NavySORs(24) = "Naval Munitions Command CONUS East Division Detachment Yorktown, Yorktown VA"
NavySORs(25) = "Pearl Harbor Naval Shipyard and Intermediate Maintenance Facility (PHNSY & IMF),Pearl Harbor, HI"
NavySORs(26) = "Naval Surface Warfare Center (NSWC), Panama City Division, Panama City FL"
AirForceSORs(1) = "Aerospace Maintenance and Regeneration Center (AMARC), Davis Monthan AFB, AZ"
AirForceSORs(2) = "Support Center Pacific (SCP), Kadena, Japan"
AirForceSORs(3) = "Cryptologic Systems Group (CPSG), Lackland AFB, TX"
MarineSORs(1) = "Maintenance Center Albany (MCA), Marine Corps Logistics Command (MCLC), Albany, GA"
MarineSORs(2) = "Maintenance Center Barstow (MCB), Marine Corps Logistics Base (MCLB), Barstow, CA"
OtherSOROptions(1) = "Contract, Contiguous United States (CONUS)"
OtherSOROptions(2) = "Contract, Overseas and Noncontiguous United States (OCONUS)"
OtherSOROptions(3) = "Industrial Plant Equipment Repair Facility, Mechanicsburg, PA"
OtherSOROptions(4) = "Federal Aviation Agency, Mike Monroney Aeronautical Center, Oklahoma City, OK"
OtherSOROptions(5) = "North Atlantic Treaty Organization (NATO) Maintenance and Supply Agency (NAMSA), Luxembourg City, Luxembourg"
OtherSOROptions(6) = "National Oceanographic and Atmospheric Administration, National Weather Service (NWS), National Reconditioning Center, Kansas City, MO"
'now look to see which of these is present in the string
Army = False
For i = 1 To UBound(ArmySORs)
If InStr(1, PreviousSORs, ArmySORs(i)) Then
Army = True
Exit For
End If
Next i
Navy = False
For i = 1 To UBound(NavySORs)
If InStr(1, PreviousSORs, NavySORs(i)) Then
Navy = True
Exit For
End If
Next i
Marines = False
For i = 1 To UBound(MarineSORs)
If InStr(1, PreviousSORs, MarineSORs(i)) Then
Marines = True
Exit For
End If
Next i
AirForce = False
For i = 1 To UBound(AirForceSORs)
If InStr(1, PreviousSORs, AirForceSORs(i)) Then
AirForce = True
Exit For
End If
Next i
Other = False
For i = 1 To UBound(OtherSOROptions)
If InStr(1, PreviousSORs, OtherSOROptions(i)) Then
Other = True
Exit For
End If
Next i
'now we can pre-populate the check boxes
If Army Then
chkArmy.Value = True
End If
If Navy Then
chkNavy.Value = True
End If
If AirForce Then
chkAF.Value = True
End If
If Marines Then
chkMarines.Value = True
End If
If Other Then
chkOther.Value = True
End If
'now call the Populate button click handler to populate the list box
btnPopulate_Click
'now go through whatever has wound up in the list box, and if it is also in the string, select it
For i = 0 To lstSORs.ListCount - 1
If InStr(1, PreviousSORs, lstSORs.List(i)) Then
'this item in the listbox is in the list of previous SORs, so select it
lstSORs.Selected(i) = True
End If
Next i
'switch to the additional SOR entry page
EnableEvents = False
For i = 0 To 9
MultiPage1.Pages(i).Visible = False
Next i
MultiPage1.Pages(11).Visible = False
lblSOR_AlternativeName.Caption = edit_alternative_name
lstSORs.Visible = False
btnSORSave.Visible = False
MultiPage1.Pages(10).Visible = True
MultiPage1.Value = 10
EnableEvents = True
End If
End Sub
The important block of code is
LinkBack URL
About LinkBacks
jerH 
Register To Reply
Bookmarks