Need VB formula to retrieve data

[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

 

[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 :

 

[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.

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

 

[isabelle]

hi Dave,

i made a model using "Scripting.Dictionary"

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

is that what you expected ?

--
isabelle




Le 2011-12-23 07:43, dave a écrit :

 

[dave]

hi Dave,

i made a model using "Scripting.Dictionary"

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

is that what you expected ?

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.

 

[dave]

hi Dave,

i made a model using "Scripting.Dictionary"

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

is that what you expected ?

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

 

[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 :

 

[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,

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

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

 

[dave]

hi Dave,

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

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

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

 

[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 :

 

[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

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

 

[isabelle]

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

--
isabelle



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

 

[dave]

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

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

 

[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

 

[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

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

Thanks

Dave

 

[isabelle]

hi Dave,

here's your file : http://cjoint.com/?ALBthXuTb6x
i make several changes, i hope that's what you wanted

 

[dave]

hi Dave,

here's your file :http://cjoint.com/?ALBthXuTb6x
i make several changes, i hope that's what you wanted

Hi Isabelle,

Hope had a good Christmas. Yes.. you had done what exactly in my mind.
Now Im working on next stage. Thanks alot for the help and your time.
Wish you happy new year.

Thanks again

dave

 

[isabelle]

glad to help you
also wish you a happy new year


--
isabelle



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

 

[dave]

glad to help you
also wish you a happy new year

--
isabelle

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



Hi Isabelle,

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

 

[isabelle]

hi Dave,

i make a new model that you can use to modify your's or used this new one

http://cjoint.com/?ALDtsRUxN75

--
isabelle



Le 2011-12-28 16:50, dave a écrit :