Need VB formula to retrieve data

D

dave

Hello,

I need help here. I need the VB formula to retrieve data from sheet to
text box in form. Data in sheet 1 A1:E1. When type the part number in
text box and press search, I need bring all the relevant information
the into the text that located in form.

Please help

Thanks

Dave
 
I

isabelle

hi Dave,

if the value to search is to be found in column A and the relevant information to be find
in columns B:E you can use the combination of INDEX and MATCH function,
but you did not say where to put the result.
in which control ?


--
isabelle



Le 2011-12-22 17:25, dave a écrit :
 
D

dave

hi Dave,

if the value to search is to be found in column A and  the relevant information to be find
in columns B:E you can use the combination of INDEX and MATCH function,
but you did not say where to put the result.
in which control ?

--
isabelle

Le 2011-12-22 17:25, dave a écrit :


Hi Isabelle thank you for the time and sorry as not provide full explanation. Let me explain. I have created form in excel which contain add, edit, remove and search. All buttons work except search as i dont know the VB code. What I done was I have placed search box and search button next to it. Ialso have placed large textbox below it as when we enter required information in search box and press search button. All information related to should display at the bottom.
Click to expand...

example - when I type ATH1267(part number) in the textbox1, the system
should display information from A1:E1 for ATH1267 in textbox2 as well
as if there's more than one ATH1267, then it should display all. So
this is what Im working on. I placed the textbox2 for display
informatin but not sure if it is the correct tool to use.

Please help and thanks alot.

dave
 
D

dave

hi Dave,

i made a model using "Scripting.Dictionary"

here it is:  http://cjoint.com/?ALxrDDZiBzK

is that what you expected ?
Click to expand...

Hi Isabelle

That is absolutely marvellous. I will amend the logic and and
information according to my project. That is wonderful. Thanks alot. I
do need to your help in one more thing but I will work out first and
come back to you if I need as this help is more than I expect. Thanks
again Isabelle.
 
D

dave

hi Dave,

i made a model using "Scripting.Dictionary"

here it is:  http://cjoint.com/?ALxrDDZiBzK

is that what you expected ?
Click to expand...
Hi Isabelle,

I have a bit of problem. I try to change the information but getting
error. Instead of date i want the combo box to display the part number
such as ATHK12392-12-1123 or RWNH1990-12-2201.

Private Sub Cbobox1_Change()
Dim c As Range
Me.ListBox1.Clear
For Each c In Range([B2], [B65536].End(xlUp))
If c.Value = CDate(Me.Cbobox1.Value) Then

I tried the to change to CDate to CInt but getting error. I know im
doing mistake. Please advice where it should be changed so that it can
display part number in column B

Thank you

dave
 
I

isabelle

hi Dave,

you can change CDate by CStr
or
If c.Value = Me.Cbobox1.Value Then


--
isabelle



Le 2011-12-23 19:38, dave a écrit :
 
D

dave

hi Dave,

you can change CDate by CStr
or
If c.Value = Me.Cbobox1.Value Then

--
isabelle

Le 2011-12-23 19:38, dave a crit :






Hi Isabelle,
Click to expand...
I have a bit of problem. I try to change the information but getting
error. Instead of date i want the combo box to display the part number
such as ATHK12392-12-1123 or RWNH1990-12-2201.
Click to expand...
Private Sub Cbobox1_Change()
     Dim c As Range
     Me.ListBox1.Clear
     For Each c In Range([B2], [B65536].End(xlUp))
         If c.Value = CDate(Me.Cbobox1.Value) Then
Click to expand...
I tried the to change to CDate to CInt but getting error. I know im
doing mistake. Please advice where it should be changed so that it can
display part number in column B
Click to expand...
Thank you
Click to expand...
dave
Click to expand...
Click to expand...


Isabelle,

Its work. You a star.. it work as i want it. Now im working on delete
selected item. I might come back to you. I appreciate your help and
time. Thanks alot Isabelle

regards

dev
 
D

dave

hi Dave,
Click to expand...
you can change CDate by CStr
or
If c.Value = Me.Cbobox1.Value Then
Le 2011-12-23 19:38, dave a crit :
Hi Isabelle,
I have a bit of problem. I try to change the information but getting
error. Instead of date i want the combo box to display the part number
such as ATHK12392-12-1123 or RWNH1990-12-2201.
Private Sub Cbobox1_Change()
     Dim c As Range
     Me.ListBox1.Clear
     For Each c In Range([B2], [B65536].End(xlUp))
         If c.Value = CDate(Me.Cbobox1.Value) Then
I tried the to change to CDate to CInt but getting error. I know im
doing mistake. Please advice where it should be changed so that it can
display part number in column B
Thank you
dave
Click to expand...
Click to expand...

Isabelle,

Its work. You a star.. it work as i want it. Now im working on delete
selected item. I might come back to you. I appreciate your help and
time. Thanks alot Isabelle

regardso

dev
Click to expand...

Isabelle,

I have a bit of problem here. In the work book I have created 2
sheets. When user open the workbook, they only able to view the
welcome sheet which ask them to click the the button to open the form.
so when they clicked to button, then only the form open. The problem
is when two sheet are available, the information on display on the
combo box. when I hide the welcome sheet, it works. How to make it
work when both sheet available.Also I want the system automatically
pick the database sheet as Im planning to hide the sheet as this only
for management view and not for user. User should only work the form.

Second thing is there any formula the change information when trigger
the button. for example, I have E1 which show available and what i
want is when user click the sell button i want this E1 to change to
sold. can u help me?

Thank you

regards

dave
 
I

isabelle

hi dave,


the sheet must be specified in the code
i add a second sheet in the file to show you

http://cjoint.com/?ALydQZIRyzA

also if you want to delete a record in the ListBox
add a CommandButton, and this code


Private Sub CommandButton1_Click()
'Ensure ListBox contains list items
If ListBox1.ListCount >= 1 Then
'Ensure there are a selection
If ListBox1.ListIndex = -1 Then
MsgBox "Select an item"
Exit Sub
Else
ListBox1.RemoveItem (ListBox1.ListIndex)
End If
End If
End Sub



--
isabelle



Le 2011-12-23 21:00, dave a écrit :
 
D

dave

hi dave,

the sheet must be specified in the code
i add a second sheet in the file to show you

http://cjoint.com/?ALydQZIRyzA

also if you want to delete a record in the ListBox
add a CommandButton, and this code

Private Sub CommandButton1_Click()
     'Ensure ListBox contains list items
     If ListBox1.ListCount >= 1 Then
         'Ensure there are a selection
         If ListBox1.ListIndex = -1 Then
            MsgBox "Select an item"
            Exit Sub
         Else
            ListBox1.RemoveItem (ListBox1.ListIndex)
         End If
     End If
End Sub
Click to expand...

Hi Isabelle,

Thanks for your help but Im getting an error message when open the
form. It could not load the PartsData sheet straight away. Any
solution?

Thanks again

Dave
 
I

isabelle

have you opened the file from Excel, or from internet explorer ?

--
isabelle



Le 2011-12-24 10:09, dave a écrit :
 
D

dave

have you opened the file from Excel, or from internet explorer ?
Click to expand...

Hi

I opened the file from excel. Do you want me to send the file, so you
can check it? If you want, give me your email. I'll forward to you.

Thank you

dave
 
I

isabelle

ok, sabyzzzz_gmail.com changed underscore by @

i'll look on Monday, now i should leave to be with my family for Christmas. i wish you happy holidays
 
D

dave

ok,  sabyzzzz_gmail.com changed underscore by @

i'll look on Monday, now i should leave to be with my family for Christmas. i wish you happy holidays
Click to expand...

Thats absolutely fine. Merry Christmas. Have a good time.

Thanks

Dave
 
I

isabelle

glad to help you
also wish you a happy new year


--
isabelle



Le 2011-12-27 16:15, dave a écrit :
 
D

dave

glad to help you
also wish you a happy new year

--
isabelle

Le 2011-12-27 16:15, dave a écrit :



Hi Isabelle,
Click to expand...

I need one more help from you if you still follow up this forum. I
have created an edit form placed and edit button in in parts form.
what im trying to do is when user entered incorrect information and
required to amend this information. Obviously not delete but still
allow to amend it. I want this to similar to sold but a bit change. I
want user user to the part in parts form, click the relevant part and
then click the edit button. So all the information will appear in edit
form which should allow them to change. Once done they should just
click it. Any solution?

Thank you

Dave
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need VB formula to exit workbook 1
Need VB formula to execute form 2
Retrieve data based on 3 criteria 3
Need VB formula to edit and save data 2
VBA - retrieve data on last create named range from previous inserted named range 0
Display relevant information from excel sheet into form 3
Copy system 4
Excel Need Countifs Formula Help 0

Top