Need help in small command button Project!!

[tv9_rohith]

I want to do a small project with 2 sheets in the same excel sheet, it can be done using Vlookup in excel but I want to make a document very people can just input the value in the textbox1 and click on command button and related information should display in the 2 textbox which will be pulled from the Sheet2( which will be hidden ).

I have an example below but I have more data than this :

In the First sheet I am planning to place a 2 text boxes and one command button.

1 text box is to input the data

2 Text box is for output

and the command button has to be placed in sheet 1 to rerive data from sheet2 cells.

--

In sheet 2

in 1st column I will give site name's like

Yahoo
Google
Wikipedia
youtube

and in the second column I will the site URL's like

Yahoo.com
Google.com
Wikipedia.com
youtube.com


and when i input data - "Yahoo" in 1 Text Box ( sheet 1 ) and then click on Command button it should retrive me value from sheet2 Column 2.( i.e., Yahoo.com ) and the output should display in 2 Textbox which is there in sheet 1.

Please help me I dont know not getting any idea how to do this..

and kindly note that I will not be able to place combo boxes in this sheet since the lookup data is big.

 

[bensonsearch]

Hi, in a macro that you assign the button to put.

dim vlook as variant
dim result as variant
dim count as long
 
count = Range("A2").CurrentRegion.Rows.count
vlook = textbox1.text
result = Application.VLookup(vlook, Worksheets("Sheet2").Range("A2:H"&count), 2, False)
if iserror(result)then
exit sub
msgbox("No data found")
end if
textbox2.text= result

make sure the lookup value (the name) is in the first row as per normal vlookup function

 

[tv9_rohith]

I am only gettng the yahoo's value and if I try to enter in google in Text box 1 I am not getting the resul.

 

[bensonsearch]

Are you ensuring the entered value is exact?

is the data in coloum 2 continuos? no breaks?

 

[bensonsearch]

sorry just noticed a problem

count = Range("A2").CurrentRegion.Rows.count

should be

count = sheets("Sheet2").Range("A2").CurrentRegion.Rows.count

 

[tv9_rohith]

I am getting the values now but If I give any other values then its not showing up me msg box "no data found " as u specified.

and one small thing I want to add onemore thing here when I close the sheet and reopen it Im seeing the old values what I even I have given before but I want that field to be blank when I open the excel sheet. and also I want to place one more button for clear the fields.

Thanks a Lot for your help!!!!!!!!!!!!!!!!

 

[bensonsearch]

ok, the msgbox is only showing if there is an error with something. try changing:

if iserror(result)then
exit sub
msgbox("No data found")
end if

to

if iserror(result) OR result = "" then
exit sub
msgbox("No data found")
end if

the clear button would be a separte macro assigned to another button of course simply

textbox1.text= ""
textbox2.text = ""

you can also place this code in the workbook close event (in the editor click this workbook)
that would clear any values if there (shouldnt be, when you say old values you mean the textbox yes?)

welcome, glad to help

 

[tv9_rohith]

Sorry to disturb you again.

need very very small favour.

just to fine tune the sheet.

No one should enter anything in textbox2 ( is that possible )?

and every time I close the sheet its asking for save n do not save.

If I click on save the values will remain there and re-open it the values are coming back again ( the previous value ).

Im asking this becoz many people will keep using this and someone saves the sheet and the next person who opens it then it will good if its clear rite.

 

[bensonsearch]

not a worry about distrubing

instead of under workbook close place it in workbook open, that way whenever someone opens it the values get reset.

with the textbox yes. click the textbox, go to the developer tab, select properties. under locked change this to true

 

[tv9_rohith]

If im giving the below mentioned code in Workbook open and Im opening the workbook for the First time then Im getting the below mentioned error.

textbox1.text= ""
textbox2.text = ""

then Im getting Error

Run time Erro 424