PDA

View Full Version : Combo/List Box populated by user form entries



nkasper
09-12-2011, 07:46 AM
I am just getting started in VBA and I have a pretty good start to what I am trying to do. I am basically building a program that will use Excel as the database and report garnering. I have 2 different user forms in the VBA, the first NewWorkOrder and the second WorkOrderDesc. I want to automatically populate the combo box (or list box) in WorkOrderDesc based upon the facility WO Type that was selected in NewWorkOrder. I have the WOType separated on 4 different worksheets, for example, if the user selected "Vehicle" as the WO Type and Facility 10, then I want the combo box (or list box) to populate all instances of facility 10 under the "Vehicle" Worksheet. I have a variable named strFacility and WOType to hold the string values of the selection and NewWorkOrder is hidden so the memory of the strings is still available.

NewWorkOrder form:

Private Sub CommandButton1_Click()

'Pass values from NewWorkOrder form to frmVehicle and WorkOrderDesc
strFacility = FacListBox.Value
WOType = WOTypeListBox.Value

'Determine if the Work Order Type is Vehicle and needs the Vehicle Form
'No Vehicle Type selection is needed at this time, so I am commenting this portion out. 9/9/11
'If WOType = "Vehicles" Then
'frmVehicle.Show

'If it is not Vehicles, then proceed to Work Order Desc
'Else:

WorkOrderDesc.Show

'End If

Me.Hide

End Sub

Private Sub CommandButton2_Click()

'Ask for confirmation to confirm user wants to close workbook
Select Case MsgBox( _
"Are you sure that you want to close the Work Order System?", _
vbYesNo + vbQuestion, _
"Confirm Exit.")

Case vbYes
'Close Work Order Workbook if user Cancels work order
'Don't save changes since no WO was entered.
ThisWorkbook.Close SaveChanges:=False


Case vbNo
'Don't Close Work Order System, return to previous screen
NewWorkOrder.UndoAction

End Select
End Sub

Private Sub UserForm_Initialize()

'Populate the Facility List based upon the Database
Dim LastRow As Long, facility As Range
With Worksheets("FacilitiesDB")

'Find the Last Row in the Worksheet
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row

For Each facility In .Range("A2:A" & LastRow)
If Len(facility) > 0 Then FacListBox.AddItem facility.Value
Next facility
End With

'Populate the Work Order Type List - hard coded!
With WOTypeListBox
.AddItem "Vehicles"
.AddItem "Building"
.AddItem "Grounds"
.AddItem "Equipment"
.AddItem "Other"
End With

End Sub

Bob Phillips
09-12-2011, 08:01 AM
Post the workbook.

nkasper
09-12-2011, 08:15 AM
Here's the workbook. I have put some data in the worksheets but eventually I want to get to the point that the VBA forms and macros will automatically populate this data.

Bob Phillips
09-12-2011, 08:33 AM
WorkOrderDesc form initialize errors for me.

nkasper
09-12-2011, 08:44 AM
Yes, the WorkOrderDesc is not functional right now. I have no clue how to populate this list and the code inside this form is just me trying various tactics. Again, I am new to VBA and programming, so I just figure to take the logical steps through the program that I am trying to create, so when I get to the WorkOrderDesc, my first focus is to get the list box to populate, as well as the combo box below it, but I know if I can understand how to do the conditional populating on one, I could handle the other too.