Okay thank you
Posts by smn617
-
-
I don't think so
I'll try to be more clear based on the workbook i just sent
there are only 2 options for Project/Contract#:
PS006X/C000015030
PS182X/C000015765
On the userform the first dropdown box is a choice between these two projects
after one of these projects is selected I want the user to select an NOD# from the next combobox
If you look at the data that is currently there, the unique values in the NOD# column are:
12
2
7
3
9
4
Looking across the rows for each of these values you can see that the only NOD#s that are in the same row as PS006X/C000015030 are:
12
2
3
4
Similarly the only NOD#s that are in the same row as PS182X/C000015765 are:
7
9
Therefore, When I select a Project/Contract#, for example PS006X/C000015030, I only want the NOD# dropdown to display values that are in the same rows as that Project/Contract#, so 12, 2, 3, 4 in this case
And if I selected PS182X/C000015765 instead then the NOD# dropdown would only display 7, 9
Im not sure if this is what you meant or not, but this is what im trying to do
-
Here is the workbook ive been working on:
UserForm1 is the form im trying to make this work on
After selecting a Project/Contract# it should filter the NOD#s to be only the ones associated with that project. Right now all NOD#s become available to select
-
They are userform comboboxes
-
Hello,
I am trying to write a code for populating a combobox with values that are based on criteria selected in another combobox.
For example I have two columns, "Project/Contract #" and "NOD #", I want to select a Project/Contract # in ComboBox1 and have only the NOD #s that correspond to that project be available to select, ie:
Project/Contract # NOD#
PS6..............................1
PS6..............................2
PS182..........................7
PS48...........................12
PS6..............................8
So in this example if I select "PS6" from the drop down I want the next combobox to only have the options 1, 2, and 8 from the drop down.
Right now I just have this code from the AfterUpdate of the Project/Contract # combobox, which doesn't filter the options from the NOD# combobox:
Code
Display MorePrivate Sub cmbPC_AfterUpdate() Dim cUnique As Collection Dim Cell As Range Dim rng As Range Dim sh As Worksheet Dim vNum As Variant Set sh = ThisWorkbook.Sheets("data") With sh Set rng = .Range("C2:C" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With Set cUnique = New Collection On Error Resume Next For Each Cell In rng.Cells cUnique.Add Cell.Value, CStr(Cell.Value) Next Cell On Error GoTo 0 For Each vNum In cUnique Me.cmbNOD.AddItem vNum Next vNum
Any ideas on how I could make this combobox populate based on another combobox's criteria?
-
The combobox is supposed to be used to select an Index No.
Then the text boxes should populate with the information from rows that correspond to the selected Index No., so it can be edited and then updated with the userform.
-
Hello,
I am trying to create a excel sheet that I can submit half completed entries into with one userform and then later go back and edit/add info on those same entries with another userform
Currently I have this userform:
Edit Entry:
Code
Display MorePrivate Sub cmbslno_AfterUpdate() SLNo = Me.cmbslno.Value On Error Resume Next Me.txtSub.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range("A2:L8"), 9, 0) Me.txtPC.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range("A2:L8"), 1, 0) Me.txtNOD.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range("A2:L8"), 3, 0) Me.txtRFI.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range("A2:L8"), 4, 0) Me.txtDT.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range("A2:L8"), 5, 0) Me.txtNS.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range("A2:L8"), 6, 0) Me.txtTPA.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range("A2:L8"), 7, 0) Me.txtPA.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range("A2:L8"), 10, 0) Me.txtNTA.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range("A2:L8"), 11, 0) Me.txtNSA.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range("A2:L8"), 12, 0) End Sub Private Sub CommandButton1_Click() SLNo = Me.cmbslno.Value Sheets("data").Select Dim rowselect As Double rowselect = Me.cmbslno.Value rowselect = rowselect + 1 Rows(rowselect).Select Cells(rowselect, 9) = Me.txtSub.Value Cells(rowselect, 1) = Me.txtPC.Value Cells(rowselect, 3) = Me.txtNOD.Value Cells(rowselect, 4) = Me.txtRFI.Value Cells(rowselect, 5) = Me.txtDT.Value Cells(rowselect, 6) = Me.txtNS.Value Cells(rowselect, 7) = Me.txtTPA.Value Cells(rowselect, 10) = Me.txtPA.Value Cells(rowselect, 11) = Me.txtNTA.Value Cells(rowselect, 12) = Me.txtNSA.Value End Sub Private Sub UserForm_Initialize() Dim ws As Worksheet Set ws = Worksheets("data") Dim myCollection As Collection, cell As Range On Error Resume Next Set myCollection = New Collection With cmbslno .Clear For Each cell In ws.Range("B2:B5000" & Cells(Rows.Count, 1).End(xlUp).Row) If Len(cell) <> 0 Then Err.Clear myCollection.Add cell.Value, cell.Value If Err.Number = 0 Then .AddItem cell.Value End If Next cell End With End Sub
I can't get the Edit entry form to populate the userform text boxes after i enter an SLNo value to the combobox. The SLNo refers to the Index No on the sheet. I am trying to get all the rows that correspond to a given index No to populate into the edit form
The excel sheet is attached
Any help is appreciated
Thank you