VBA User Form : Reference Value should come based on Button Click

[sachinns]

Hi Friends,

I have a VBA User Form consisting of 1 button and 2 textbox. When i press this button , the value of button is coming in the textbox1. Till this part i have completed.

Private Sub button1_Click()


If button1.Value = False Then
TextBox1.SelText = ComboBox1.Text + " "
Else
TextBox1.SelText = ComboBox1.Text + " " + TextBox1.Text + " "
End If
End Sub

I have one sheet where some values (value) are present. Corresponding to this values there are some other values (value2) .

So, when i press on the button , the value will goto textbox1 and this value (value) should be searched in the sheet and its corresponding value (value2) should come in the textbox2.

Can someone please help me on this.

Thanks

 

[My Aswer Is This]

If button1.Value

Your button1 is a Option Button named button1 ??
​

And search where on the sheet?

We need more specific details.

 

[sachinns]

@My Aswer Is This

Screenshot 1 : Sheet
https://cdn3.imggmi.com/uploads/2019/7/31/af2cd4947869bee022f064e50d1e12d8-full.jpg

Screenshot 2 : Form
https://cdn3.imggmi.com/uploads/2019/7/31/e3408fbdbdf1af0f0545b150ad28bfe4-full.jpg

When i press on the button the value assigned to the button will be generated in textbox1. This value has to be searched in the sheet and its corresponding value (Column B) should be generated in textbox2.

Example (refer screenshots) :
Note : value assigned to the button is Relationship.

When we click on the button then value in textbox1 will be "Relationship" and the value in textbox2 will be "A8" . (refer the screenshot)

 

[My Aswer Is This]

I have been using Excel for a long time.
Tell me:
How do you assign a value to a button?
And please explain what type button are you referring to.
Do you mean the Button Caption?
Or do you mean a Option Button value of true or false.
I never click on links like you posted.

When i press on the button the value assigned to the button will be generated in textbox1.​

 

[sachinns]

@My Aswer Is This

There is a combobox with dropdown in the form . After selecting the value in dropdown , we can press on the button. So that value is moved to the textbox.

It is a normal command button .

Please check on the links for screenshot. The links are safe. Please help me on this.

 

[My Aswer Is This]

We may be getting there.

So you say you select a value from a UserForm ComboBox

Then when you click a command button you want that value entered into TextBox1

Then you want to search for that value and when found enter that value into Textbox2

So I assume your want to search column A for the value in TextBox1

and if found enter that value in Textbox2


So if you select "Alpha" in Combobox1

You want to search column A of active sheet and if "Alpha" is found in Range("A27") then you want value in Range("B27") entered into TextBox2

Is this correct.

I'm going to assume the ComboBox is named ComboBox1

I'm not sure what this is all about

If button1.Value = False Then

See you said when you click on ComboBox


Maybe someone else here will understand.

If Button1.value=False

???​

 

[sachinns]

Yeah This is exactly what i want.

alpha is the value selected in comboBox1.
When i press the button , this "alpha" is moved to textbox1 .
Now "alpha" will be searched in column A of active sheet and if found in Range("A27") then i want value in Range("B27") entered into TextBox2 .


Now , Regarding If button1.Value = False Then .

Previously i put = True in the code But value was not coming in textbox1. When i put False it was coming. I dont know why . You can ignore it.

And One more thing.
The dropdown value is "Alpha" and in active sheet the value is "AlphaXYZ" and is found in Range("A27") then also i want value of Range("B27") entered into TextBox2


Please please help on this.
Thanks

 

[My Aswer Is This]

I suggest you put this script in your ComboBox

Will search Column A for value selected in ComboBox

Private Sub ComboBox1_Change()
Dim SearchString As String
Dim SearchRange As Range
SearchString = ComboBox1.Value
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set SearchRange = Range("A2:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlPart)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
TextBox1.Value = ComboBox1.Value
TextBox2.Value = SearchRange.Offset(, 1).Value
End Sub

 

[sachinns]

Thanks a lot for the code.

I was trying to do code myself and somehow i did it myself. I will show the code which i did. The button name is import1.

Private Sub import1_Click()

If import1.Value = False Then
TextBox2.SelText = ComboBox1.Text + " "
Else
TextBox2.SelText = ComboBox1.Text + " " + TextBox2.Text + " "
End If


ThisWorkbook.Activate
Sheets("Formula Reference").Activate

Dim oFound As Range
Dim oLookin As Range
Dim sLookFor As String


sLookFor = ComboBox1.Text 'Change to suit
Set oLookin = Worksheets("Formula Reference").UsedRange 'Change sheet name to suit
Set oFound = oLookin.Find(what:=sLookFor, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not oFound Is Nothing Then
    'MsgBox oLookin.Range("A" & oFound.Row).Value
    ActiveCell.Value = oLookin.Range("A" & oFound.Row).Value
    Set oAdjacent = oFound.Offset(0, 1) ' One cell to right


TextBox3.SelText = oAdjacent.Value + " "
Else
TextBox3.SelText = oAdjacent.Value + " " + TextBox3.Text + " "
    
End If


End Sub

My Code is working fine. But one issue.

I can import how much ever values i want to add to textbox1 and textbox2 based on the selected position by mouse click ( I am using Seltext instead of text).

Example :

Sheet values :
Column A : Alpha , beta , gamma.
Column B : A , B , C .

User selected alpha in dropdown and clicked the button.
So textbox1 will have alpha and textbox2 will have A.

now the user clicked on the beginning of the textboxt1.
Now user selected gamma in dropdown and clicked button.
So textbox1 will have "gamma alpha" and textbox2 will have " A C " .

But I wanted the values in textbox2 as per textbox1 values. ie., Textbox2 values should be " C A "

Please help me to modify this code to perform the way i want. Please sir.

 

[My Aswer Is This]

The way you were wanting to do things were if I understand it.
Click on A Command Button
Have command Button look and see if a item in ComboBox1 was selected
Have the value in ComboBox1 entered into Textbox1
And then search Column A for value in TextBox1
And if found then enter that value into TextBox2

Not really sure why you wanted to go through all those steps

My script eliminates the command button and could also eliminate the TextBox1 but I kept the Textbox1 even if not needed.