Populate VBA userform Drop Down based on an existing value of another textbox

[egrospeRP]

Hello Excel Gurus,
For backround, I have an excel table that lists Mitigations related to a risk Item and it is as follows:

Risk No.	Mitigation No.
1001	1
1001	2
1001	3
1002	1
1002	2
1003	1
1004	1
1004	2
1004	3
1004	4

I designed a userform that allows users to update Risk Mitigations Records. This userform has a textbox for "Risk No." that is inherited from a different userform. The problem I am having is that I would like my combo box to display mitigation number based on what the risk number is.

For example, if the userform textbox for risk no is "1001", my mitigation no. combobox should give a list option of 1, 2, and 3. If the risk no. is "1002", it should give a list option of 1 and 2. I'm stuck and I can't seem to find a way to make this work.

 

[DanteAmor]

This userform has a textbox for "Risk No."

In that userform put this code:

Private Sub UserForm_Activate()
  Dim i As Long
  Dim sh2 As Worksheet
  
  Set sh2 = Sheets("Sheet2")    'fit the sheet name.
  
  For i = 2 To sh2.Range("A" & Rows.Count).End(3).Row   'fit the column "A" to Risk no.
    If sh2.Range("A" & i).Text = TextBox1.Value Then    'fit the column "A" to Risk no.
      ComboBox1.AddItem sh2.Range("B" & i).Value        'fit the column "B" to Mitigation no.
    End If
  Next
End Sub

 

[egrospeRP]

In that userform put this code:

Private Sub UserForm_Activate()
  Dim i As Long
  Dim sh2 As Worksheet
 
  Set sh2 = Sheets("Sheet2")    'fit the sheet name.
 
  For i = 2 To sh2.Range("A" & Rows.Count).End(3).Row   'fit the column "A" to Risk no.
    If sh2.Range("A" & i).Text = TextBox1.Value Then    'fit the column "A" to Risk no.
      ComboBox1.AddItem sh2.Range("B" & i).Value        'fit the column "B" to Mitigation no.
    End If
  Next
End Sub

This worked! Thanks so much! Can I ask you for a follow up question though? I wanted to check first if the risk no. exist before executing the loading of combo box, If it doesn't, i want it say that the risk no wasn't found and there weren't any existing mitigation to update. I tried the following code but it wouldn't let me exit out of the 'for' loop. What I want it to do is to exit the loop and not load the userform if the Risk item wasn't found.

Dim i As Long
Dim wsMT As Worksheet
Set wsMT = Sheets("Mitigations")

For i = 2 To wsMT.Range("A" & Rows.Count).End(3).Row
    If txtUpdateMitRiskNo.Value <> wsMT.Range("A" & i).Text Then
           MsgBox "There aren't any existing Mitigations associated with this Risk Item"
    Exit For
    ElseIf wsMT.Range("A" & i).Text = txtUpdateCtrlRiskNo.Value Then
            cboUpdateCtrlNo.AddItem wsMT.Range("B" & i).Value
    End If

Next

End Sub

Thanks for your help. Not sure if I need to post a separate thread for this. If so, please let me know.

 

[DanteAmor]

It would be easier if you put the names of your forms, your sheets and all your controls.

But let's do the following.

You have an initial form (I'm going to call it "userform2").
In this userform2 put this code.

Private Sub CommandButton1_Click()
  Dim wsMT As Worksheet
  Dim f As Range
 
  Set wsMT = Sheets("Mitigations")
 
  Set f = wsMT.Range("A:A").Find(txtUpdateMitRiskNo.Value, , xlValues, xlWhole, , , False)
  If f Is Nothing Then
    MsgBox "There aren't any existing Mitigations associated with this Risk Item"
  Else
    With UserForm3      'fit with the name of the userform: "This userform has a textbox for "Risk No.""
      .TextBox1.Value = Me.txtUpdateMitRiskNo.Value
      .Show
    End With
  End If
End Sub

I am putting a button that will open the "other" form, which I have called "userform3".

---------------------------​

If userform3 isn't going to open, then there's no need to load userform3's combobox.
The combobox will be loaded when userform3 is activated. Then put the following code in the userform3

Private Sub UserForm_Activate()
  Dim i As Long
  Dim wsMT As Worksheet
 
  Set wsMT = Sheets("Mitigations")    'fit the sheet name.
 
  For i = 2 To wsMT.Range("A" & Rows.Count).End(3).Row   'fit the column "A" to Risk no.
    If wsMT.Range("A" & i).Text = TextBox1.Value Then    'fit the column "A" to Risk no.
      cboUpdateCtrlNo.AddItem wsMT.Range("B" & i).Value        'fit the column "B" to Mitigation no.
    End If
  Next
End Sub

You already had the code, I just adjusted the name of the sheet. I hope I was clear enough.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[egrospeRP]

It would be easier if you put the names of your forms, your sheets and all your controls.

But let's do the following.

You have an initial form (I'm going to call it "userform2").
In this userform2 put this code.

Private Sub CommandButton1_Click()
  Dim wsMT As Worksheet
  Dim f As Range
 
  Set wsMT = Sheets("Mitigations")
 
  Set f = wsMT.Range("A:A").Find(txtUpdateMitRiskNo.Value, , xlValues, xlWhole, , , False)
  If f Is Nothing Then
    MsgBox "There aren't any existing Mitigations associated with this Risk Item"
  Else
    With UserForm3      'fit with the name of the userform: "This userform has a textbox for "Risk No.""
      .TextBox1.Value = Me.txtUpdateMitRiskNo.Value
      .Show
    End With
  End If
End Sub

I am putting a button that will open the "other" form, which I have called "userform3".

---------------------------​

If userform3 isn't going to open, then there's no need to load userform3's combobox.
The combobox will be loaded when userform3 is activated. Then put the following code in the userform3

Private Sub UserForm_Activate()
  Dim i As Long
  Dim wsMT As Worksheet
 
  Set wsMT = Sheets("Mitigations")    'fit the sheet name.
 
  For i = 2 To wsMT.Range("A" & Rows.Count).End(3).Row   'fit the column "A" to Risk no.
    If wsMT.Range("A" & i).Text = TextBox1.Value Then    'fit the column "A" to Risk no.
      cboUpdateCtrlNo.AddItem wsMT.Range("B" & i).Value        'fit the column "B" to Mitigation no.
    End If
  Next
End Sub

You already had the code, I just adjusted the name of the sheet. I hope I was clear enough.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

This worked perfectly, thank you so much!!