populate values in textbox based on optionbutton doesn't work

[Maklil]

Hello
I try to populate values in textbox based on select optionbuttonS after select combobox.
so should brings the values from cloumn F when select combobox1 and optionbutton1 into textbox1,and if I select combobox1 and optionbutton2 should brings the values from cloumn G and populate into textbox1.

Private Sub ComboBox1_Change()
Dim c As Range
With Sheets("BS")
  
    Set c = .Range("B:B").Find(What:=ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If OptionButton1.Value = True Then
    If Not c Is Nothing Then
        ComboBox2.Value = c.Offset(, 1).Value
        ComboBox3.Value = c.Offset(, 2).Value
        ComboBox4.Value = c.Offset(, 3).Value
        TextBox1.Value = c.Offset(, 5).Value
        ElseIf OptionButton2.Value = True Then
        If Not c Is Nothing Then
        ComboBox2.Value = c.Offset(, 1).Value
        ComboBox3.Value = c.Offset(, 2).Value
        ComboBox4.Value = c.Offset(, 3).Value
        TextBox1.Value = c.Offset(, 6).Value
        End If
    End If
    End If
End With

End Sub

How can I do that ,please

 

[vw412]

You are having some difficulty with the If-Then-Else constructs. The logic as shown here is not going to work as you want it to.

The only time to code in the outer If then is executed is when OptionButton1 is True. But OptionButton2 is set it will never be tested since that If Then is contained inside the OptionButton1 test.

 

[Maklil]

so what's the right procedure?

 

[dmt32]

so what's the right procedure?

See if this update to your code does what you want

Private Sub ComboBox1_Change()
    Dim c       As Range, rng As Range
    Dim search  As String
    
    Set rng = ThisWorkbook.Worksheets("BS").Range("B:B")
    
    search = Me.ComboBox1.Value
    
    Set c = rng.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, _
                     SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                    SearchFormat:=False)
                    
    If Not c Is Nothing Then
        'search value found
        Me.ComboBox2.Value = c.Offset(, 1).Value
        Me.ComboBox3.Value = c.Offset(, 2).Value
        Me.ComboBox4.Value = c.Offset(, 3).Value
        
        If Me.OptionButton1.Value = True Then
            Me.TextBox1.Value = c.Offset(, 5).Value
        ElseIf Me.OptionButton2.Value = True Then
            Me.TextBox1.Value = c.Offset(, 6).Value
        End If
        
        'if you only have two option buttons then above section in RED probably could be replaced with this line
        'Me.TextBox1.Value = c.Offset(, IIf(Me.OptionButton1.Value, 5, 6)).Value
        
    Else
        'optional search value not found message
        'MsgBox search & Chr(10) & "Record Not Found", 48, "Not Found"
        
    End If
    
End Sub

Dave

 

[Maklil]

thanks Dave,
unfortunately the code doesn't work well .
when I press optionbutton1 and combobox1 will brings the values from column G
press optionbutton1 should brings values from column F and optionbutton2 should brings values from column G with considering when move from optionbutton to another when select one of them , then should change value in textbox1 automatically .

 

[dmt32]

thanks Dave,
unfortunately the code doesn't work well .
when I press optionbutton1 and combobox1 will brings the values from column G
press optionbutton1 should brings values from column F and optionbutton2 should brings values from column G with considering when move from optionbutton to another when select one of them , then should change value in textbox1 automatically .

code only updated your ComboBox1_Change event code - you have not shared any other codes for your OptionButtons

Can suggest you publish ALL the codes in your userform

Dave

 

[Maklil]

I don't have others codes except this

Private Sub OptionButton1_Click()
    If Me.OptionButton1 Then
        If Me.ComboBox1.ListIndex > -1 Then ComboBox1_Change
    End If
End Sub

Private Sub OptionButton2_Click()
    If Me.OptionButton2 Then
        If Me.ComboBox1.ListIndex > -1 Then ComboBox1_Change
    End If
End Sub

Private Sub UserForm_Activate()
ComboBox1.RowSource = "BS!" & Sheets("BS").Range("B2", Sheets("BS").Range("B65536").End(xlUp)).Address

End Sub

 

[dmt32]

Then the offset values published in your original code, niether are not correct

change to the values shown in bold below & see if this now does what you want

If Me.OptionButton1.Value = True Then
            Me.TextBox1.Value = c.Offset(, 4).Value
        ElseIf Me.OptionButton2.Value = True Then
            Me.TextBox1.Value = c.Offset(, 5).Value
        End If

Dave

 

[Maklil]

very strange ! and now I'm so confused how this works now . the matching based on column B and the values into column F,G

is the column B should be first column then the columns F,G should be 5,6 ?

last thing it doesn't change the values automatiaclly when move from optionbutton to another . any simple procedure to overcome this problem?

 

[dmt32]

is the column B should be first column then the columns F,G should be 5,6 ?

you count from the search column so column C = 1, D = 2 on so on

last thing it doesn't change the values automatiaclly when move from optionbutton to another . any simple procedure to overcome this problem?

Using your code, Textbox value is returned from Column F for optionbutton1 & Column G for optionbutton2
but this assumes that combobox1 has a value that can be found in the search range.

Dave