Vlookup in a Form 1

[SebbeKine]

Hello,

I want to make a form with 2 textboxes and 2 commandbuttons.
The first textbox has to refer to te cellname Naam on the first sheet of the workbook. In the second textbox there wil be the result (with vlookup in the second row of the sheet) of the first textbox. The firts commandbutton is to close the form with no changes. The second commandbutton has to apply the change made in the second textbox. It has to search for the correct cell in the sheet.
In the sheet I have 2 coloms. In the firts there are names and in the second there are numbers.
I've made the form, but I don't know how to make the code.

I'm new in VBA.

Thanks

 

[SkipVought]

Hi,

If you dou ble-click a control in your form, you will see the code sheet. Each control has MANY events. Click the DropDown in the upper-left to select any control and the dropdown in the upper-right to see the available events.

You might chave code like this...

sub Userform1.activate
   textbox1.text = Sheets("Sheet1").Cells(1,1).value
end sub

put Sheet1!A1 value in textbox1 on the userform when the form activates (F5)

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SebbeKine]

Thanks,

After several hours of looking around, I've now this code.

Code:

Private Sub ComboBox1_Click()
TextBox1.Text = Application.VLookup(ComboBox1.Text, _
Worksheets("Uurlonen").Range("A2:B75"), 2, False)
End Sub

With this code I get the result in Textbox1 after I make a selection in the list of ComboBox1.
But what I want to do now is that, if I change the value in Textbox1 and I click on Commandbutton2, the change is made in the correct cell.

Thanks for your help.

 

[PHV]

Have a look at the Match function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SebbeKine]

Thanks, but I really don't know how compile the code to let it work.
Can you help me?
Thanks

 

[SkipVought]

VB can be compiled or not.

To run, there must be an event that runs whatever entry code you have: could be...

Tools > Macro > Macros - Select & RUN

Or some event you have coded.


Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SebbeKine]

Euh...

I ment that I don't know how to write the code.

 

[PHV]

No F1 key on your keyboard ?

 

[SkipVought]

You'll need to by a VBA reference book, take a course, experiment in VBA etc.

This is not a training forum. Its Tek-Tips.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SebbeKine]

Yes, I know. But I'm a fysiotherapist and not a programmer. That's why I ask my question here.
I'm trying but no luck. So if you can help me, it's appreciated.

 

[Bong]

Use the Macro Recorder (Tools/Macros...). Do your thing manually. Come back with any questions about changing the code/behavior.

_________________
Bob Rashkin

 

[SkipVought]

Please post any code that you have that is not working as expected.

If the issue is the starting point, please list the procedures that you have and a brief description of what each does.

Also describe what your application is supposed to do, functionally.

Something like this...

When the workbook opens, UserForm1 opens.

UserForm1 is a means of the user doing....
UserForm1 has x Textboxes and y CommandButtons.
Textbox1 is for...
Textbox2 is for...
When the user hits CommandButton1, then ....

Get the idea?



Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SebbeKine]

OK, thanks verry much!!
I'll come back

 

[SebbeKine]

I've a sheet ('Uurlonen')
Colum A are the names of the clients
Colum B are the pay rates of that client
I've a commandbutton on the sheet, when I click on it, it opens form frmUurlonen
In this form I've 1 Combobox, 1 textbox and 2 commandbuttons
Combobox1 is a list of the clients
Textbox1 gives the pay rates of the selected client
Commandbutton1 close the form without changes

This is all OK, but now comes the part I don't find:
Commandbutton2 apply the changes made in Textbox1,
but the value in Textbox1 is given with the vlookup function.
So I don't know wich code to put here

This is all the code that I have for the form:
Private Sub Annuleren_Click()
Unload frmUurlonen
End Sub

Private Sub Aanpassen_click()
TextBox1.Text = Application.VLookup(ComboBox1.Text, _
Worksheets("Uurlonen").Range("$A:$B"), 2, False).Select
Unload frmFUurlonen
End Sub

Private Sub ComboBox1_Click()
TextBox1.Text = Application.VLookup(ComboBox1.Text, _
Worksheets("Uurlonen").Range("$A:$B"), 2, False)
End Sub


Thanks again for helping me!!

 

[SkipVought]

What is the purpose of TextBox1? A Textbox is for user entry. What is the user entering in Textbox1?

Don't you want the value in a sheet somewhere?

Private Sub ComboBox1_Click()
    Sheets("SomeName").Cells(1,1).value = Application.VLookup(ComboBox1.Text, _
    Worksheets("Uurlonen").Range("$A:$B"), 2, False)
End Sub

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SebbeKine]

Textbox1 gives the pay rate (in colum B) of the selected client (in colum A) in combobox1.
This code works. But what I want to do: is that if I change the pay rate manualy in textbox1 and I click on commandbutton2, the pay rate in colum B an row?? is changed to the value I put in textbox1.

 

[SebbeKine]

Textbox1, Combobox1, Commandbutton1 and 2 are on a form (frmUurlonen).

 

[SkipVought]

"...click on commandbutton2, the pay rate in colum B an row?? is changed to the value I put in textbox1."

Private Sub commandbutton2_Click()
    lRow = ??
    Sheets("TheSheetName").Cells(lRow, "B").Value = TextBox1.Text
End Sub

what do you want the row to be based on?


Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SebbeKine]

I know in which colum the change will be, but not in wich row. That's why ??. Is there no function like vlookup to search the exact row?

 

[SebbeKine]

so lrow could be:
lrow = Application.VLookup(ComboBox1.Text, _
Worksheets("Uurlonen").Range("$A:$B"), 2, False)

?