Previous Topic Next Topic

Populate values in textbox based on combobox ,optionbutton

Solved/Closed
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 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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
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

Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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
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 
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555 > 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
Mussa_8911 Posts 7 Registration date Friday October 1, 2021 Status Member Last seen September 9, 2022 > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022
Sep 9, 2022 at 12:17 PM

Hi Trowa 

this  works  greatly ! many  thanks  for  your  help .

best regards,

Mussa

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Fill adjacent cell with number from text answer
MitchP -
vcoolio -

20 responses
Excel function question
1234ah -
ac3mark -

3 responses
Clear cell contents based on value of another cell range using VBA
Tbonekiller -
Tbonekiller -

6 responses
Return Seq Values Until Blank value then return different set of seq values
lorieb63 -
TrowaD -

1 response