Populate values in textbox based on combobox ,optionbutton

Solved
Mussa_8911 Posts 7 Registration date Friday October 1, 2021 Status Member Last seen September 9, 2022 - Updated on Aug 19, 2022 at 03:50 PM
Mussa_8911 Posts 7 Registration date Friday October 1, 2021 Status Member Last seen September 9, 2022 - Sep 9, 2022 at 12:17 PM

Hello,

I  have  the  items  in column B  in sheet1,2,3   and  the  prices  in columns F,G  for sheet1,2      and  the  prices  for columns F  for  sheet3   and  the  userform  contains  combobox1  is  linked  with column  B  across  sheets , also  I   have  optionbutton1, textbox1  are linked  with value for  column  F   for  sheet1,2   and  the  optionbutton2  is  linked  with  the  values  for  column G,F  for  sheet1,3 . the  items  are  repeated  across  sheets    should  brings  the  last  value  for  the  duplicates items across sheets  lets   take  some  example   if  I  select  item CD-01  from  combobox1  and  select  the  optionbutton 1   then  will search  the  item  in  column B  and  to  brings  last  value . I suppose the  item contains  values  in columns F for sheet1,2    (10,11,12,14)  theses  values  mean  the  item is  repeated  four  times  then  when populate  the  value in textbox1   after  select  combobox1, optionbutton1   should  be 14 .and  if  I  have  values  for  column G,F   for   sheet1,3   and  I  have  many  values (22,23,111,66)  then  when populate  the  value in textbox1   after  select  combobox1, optionbutton2   should  be 66. in textbox1  not  necessary should  be  duplicated  for  all  of  the  sheets , but  should  search  for  all  of  the  sheets  if   there  is  duplicates items. 

here  is  my  file  contains some  some codes  but  doesn't  work  as  what  I  want

https://www.dropbox.com/scl/fi/5g5pwhhtmdm5jk35w19ww/a.xlsm?dl=0&rlkey=juwikeo84vmp3ierv8fcl4mkv

I  ccept  any  suggestion or alternative .

thanks 


Windows / Chrome 101.0.0.0

2 replies

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Aug 29, 2022 at 11:56 AM

Hi Mussa,

Click on column F on sheet 3 and insert a column so that the PCR values from sheet 1 and 3 are both in column G.

Now go to your code and look for (code line 6):

ws = Array("sheet1", "sheet2", "sheet3")

And place the following lines below it:
 

If Me.OptionButton1 Then ws = Array("sheet1", "sheet2")
If Me.OptionButton2 Then ws = Array("sheet1", "sheet3")

That's it.

Best regards,

Trowa


0
Mussa_8911 Posts 7 Registration date Friday October 1, 2021 Status Member Last seen September 9, 2022
Updated on Aug 30, 2022 at 10:20 AM

Hi TrowaD 

I appreciate  for  your  help ,   I  know   my  way  is  not  to  easy  to  deal  with  different  columns  location .  I undersood why   you  want  to  making  the  same  loctaion  .  I  thought   to  be  possible  to  match  with different  columns in location withoit  need insert  columns  I  don't  need it , and  if  you  see  in  sheet3   the  column F  will be empty  if   I  insert column G  to  match  it .

best regards,

Mussa

0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Aug 30, 2022 at 11:11 AM

Hi Mussa,

Well you can, just thought this would be the easiest way. You know you can hide the empty column.

Anyway, without inserting an empty column, look for code line 32:

Me.TextBox2.Value = c.Offset(, 5).Value

And replace it with:

If n = 0 Then Me.TextBox2.Value = c.Offset(, 5).Value
If n = 1 Then Me.TextBox2.Value = c.Offset(, 4).Value

Do this along with the previous mentioned adjustment to your code.

Best regards,

Trowa


0
Mussa_8911 Posts 7 Registration date Friday October 1, 2021 Status Member Last seen September 9, 2022
Updated on Aug 31, 2022 at 09:19 AM

Hi Trowa ,

based on  your  suggestion , is  that  right in  this  part?

 Me.Label15.Caption = "PRC"
                If n = 0 Then Me.TextBox2.Value = c.Offset(, 5).Value
                If n = 1 Then Me.TextBox2.Value = c.Offset(, 4).Value

actually  there  is  something  wrong  when  select optionbutton1  PRICE will  give  last  value   for  PCR , the  same  thing  when select  optionbutton2  PRC  will  give  last  value   for  PRICE . brings  value  is  reverse process.

is it my  mistake  or  what?

best regards, 

Mussa 

0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523 > Mussa_8911 Posts 7 Registration date Friday October 1, 2021 Status Member Last seen September 9, 2022
Sep 6, 2022 at 12:01 PM

Hi Mussa,

Looks fine to me. let me post the entire code:

 

Private Sub ComboBox1_Change()
Dim c As Range, rng As Range
Dim search  As String
Dim ws As Variant, n As Long

ws = Array("sheet1", "sheet2", "sheet3")
If Me.OptionButton1 Then ws = Array("sheet1", "sheet2")
If Me.OptionButton2 Then ws = Array("sheet1", "sheet3")

'loop through worksheets
For n = LBound(ws) To UBound(ws)
    ' look in worksheet
    Set rng = Worksheets(ws(n)).Range("B2:B12")
    
    search = Me.ComboBox1.Value
    Set c = rng.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False, _
                    SearchFormat:=False)
    For Each Cll In rng
        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
                'rewrite label
                Me.Label15.Caption = "PRICE"
                Me.TextBox2.Value = c.Offset(, 4).Value
            ElseIf Me.OptionButton2.Value = True Then
                'rewrite label
                Me.Label15.Caption = "PRC"
                If n = 0 Then Me.TextBox2.Value = c.Offset(, 5).Value
                If n = 1 Then Me.TextBox2.Value = c.Offset(, 4).Value
            End If
        End If
    Next Cll
 Next n

End Sub

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

End Sub

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

As you can see from code lines 25 to 28: Option button 1 changes the caption to "Price" and pulls the data 4 cells to the right of column B.
From code lines 29 to 33: Option button 2 changes the caption to "PRC" and pulls the data 5 cells to the right of column B for sheet1 and 4 cells to the right for sheet3.

Maybe you forgot or used a different save so that code lines 7 and 8 are missing? Replace your code with the one above and see if the issue persists.

Best regards,
Trowa

1
Mussa_8911 Posts 7 Registration date Friday October 1, 2021 Status Member Last seen September 9, 2022 > TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022
Sep 9, 2022 at 12:17 PM

Hi Trowa 

this  works  greatly ! many  thanks  for  your  help .

best regards,

Mussa

0