VBA project

[nanobuns]

Hi i need help with my project.


I am using excel vba(userform), I want to search data from an excel sheet.


Example.


I will type a serial number on a textbox and if matches data from the excelsheet it will display all the information on a specific text box.


is there anyway to do that?


Thanks in advance :D

 

[Marc L]

Hi !


Different ways to do that but need detailed information …

 

[nanobuns]

Hi Marc,


This is what i need.


I will type a unique number (e.i. Item code) when it find the same item code from my worksheet all other details under the item code must display on a text box in a different userform.


thanks,

 

[SirJB7]

Hi, nanobuns!


As Marc L told you there're many different ways to do it and you didn't help providing any useful new information in your 2nd post.


You should specify in detail:

a) Where is the data placed in the workbook? As a range in a worksheet, as a table range, as a table, as a named range...

b) Which is the key column, that which holds the serial number?

c) Which are the "all other details" you want to display on a text box? Columns, row offsets (if in different from SN)

d) Why in a text box and not in a label, since a label doesn't allow the user to update it while the text box does? And if you're planning to update those "all other details" then you should use an individual text box for each one, otherwise it will be hardly messy to separate the text box contents into many cell values.


Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!

 

[nanobuns]

Hi Sir JB7,


I am sorry for that, i'll keep that in mind


Anyways, here is a link screenshot of my project.


https://rapidshare.com/files/3562577163/sample.JPG


I have this code for the find button, but it doesnt works like what i wanted.

[pre]

Private Sub cmdfind_Click()
Dim strFindWhat As String
strFindWhat = txtfind.Text
On Error GoTo ErrorMessage

Cells.Find(What:=strFindWhat, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Select
Exit Sub
txtfind.SetFocus

ErrorMessage:
MsgBox ("The data you are searching for does not exist")
txtfind.Value = ""
End Sub

[/pre]

Thanks in advance,

 

[Lymm]

Might be easier if uploaded a sanitised file rather than a jpg, then we can see what you have done and where you want all the details to go. And save someone having to recreate your user form. Cheers.

 

[SirJB7]

Hi, nanobuns!


First, you must change "xlFormulas" by "xlValues" and check if MatchCase should be False or True, but then what are you going to do with the selected (ergo active) cell and its value? In your code you're not doing anything.


As you neither answered any of my questions a) thru d) explicitly nor uploaded a sample file (just an user form image and no clue to the source of data), I'm not able to further help you except for these general considerations:


a) Source data: worksheet Sheet1, columns A:Z, rows 2 in advance, dynamic named range DataTable

b) One control (text box or option button) for each column: txt01, txt02, ...

c) Code for Find command button:

-----

[pre]

Sub x()
Dim I As Long, c As Range
With Worksheets("Sheet1").Range("DataTable")
With .Columns(1)
Set c = .Find(What:=txtFind.Text, After:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
End With
If c Is Nothing Then
txt01.Text = ""
txt02.Text = ""
...
MsgBox "Not found"
Else
I = c.Row - .Row + 1
txt01.Text = .Cells(I, 2).Value
txt02.Text = .Cells(I, 3).Value
...
End If
End With
End Sub

[/pre]
-----


Regards!

 

[nanobuns]

Sorry for that.

thank you so much your code works.

 

[amit16cool]

Hi...


I have a excel workbook with different tabs in it...

In each tab cell A1:An can be filled, can you help me with a macro to give an error msg when A1:An is filled however any of the cells B1:Bn,C1:Cn, etc, till cell F1:F500 is blank.


Also, the macro should work when I try to save the worksheet and it should automatically work for all tabs (sheet1, sheet2, sheet3, etc.). Means I do not have to specify the sheet name in macro.

 

[SirJB7]

@amit16cool

Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

Perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).

Regards!

PS: Please don't answer here at this thread.