Posts by smn617

Okay thank you

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:

Change Orger Log.xlsm

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:

Private 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:

Private 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