Excel Forms - userform to view and edit data

[MJD1]

hi, I'm looking for some help and direction to create a user form that allows me a search criteria and then retreives the information from a sheet named Data. I've tried the built in Form function but didn't like because of the formatting it provides and the fact that I can't have more then 32 fields.

Not sure how to code and reference to the Data Sheet. I created a userform to add new records without any issues.

thanks for your help in advance!

martin

 

[SkipVought]

hi,

Suppose you explain what your workbook is used for and how it is structured rather than telling us how you think a particular task ought to be performed.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MJD1]

sure. the workbook is to keep track of investigations. I have a sheet named "Data" which has about 40 fields, some auto-calculate information such as case number, age, tenure, vlookup to specific lists in a seperate sheet named "parameters", ect. I created a userform to enter the data to make it easier which work just fine. Now what I would like is a form to search, say a name and case, which then populates the fields in the form with the data found. not sure how to reference the data sheet and populate the fields. I can't use the built in form function becasue too many fields, plus, really don't like the lay out.

I created an access db for this, however, due to IT security it's been made very hard to access. wanted to create something easy to use local on my computer.

martin

 

[SkipVought]

You could build a "form" on a blank sheet.

Use MS Query to build dynamic lists of the field data you want to use to select on. Use the Data > validation --LIST feature to create an in-cell drop down control for picking a criteria value.

Then you could either use MS Query again, using your selections for criteria values in a query.

Alternatively, use the Data >Validation --LIST selections in several cells in a column to select different values for each row and then use VLOOKUP or INDEX & MATCH (my personal preference) to return selected values from your Data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MJD1]

hi, so I've got a lisbox populating with my Sheet"Data". What I'd like is for when I click on a record, it looks up the values in the Data sheet and populates the text boxes. this is what I have but doesn't work, I'm pretty sure it's a typo but can't see it. Thanks for helping in advance.


Private Sub ListBox_Click()
'Me.casenumber = ListBox.Value

Dim result As String
Dim sheet As Worksheet
Set sheet = ActiveWorkbook.Sheets("Data")

Me.casenumber = Application.WorksheetFunction.VLookup(Me.ListBox, sheet.Range("A9:Ap8000"), 0, False)

 

[SkipVought]

So are you only returning case number? No other fields?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MJD1]

hi, no I plan on having a all the fields from the worksheet Data. I just wanted to start one field to understand how to do it.

thanks
martin

 

[SkipVought]

You can have ONE listbox control to do everything.

Design a context sensitive process using the Workbook_SelectionChange event. When you select in a predefined range (and that range can be dynamic) the control will either be assigned FALSE to the Visible property if it is outside the area or TRUE is it is within the area, along with assigning the Left, Top, Width & Height properties to position and size the control as needed. The context also determines what LIST might be loaded or referenced in the control.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MJD1]

hi, well...I've been researching your lead, but this newby to excel vba can't figure out

can you provide an example?

thanks
martin

 

[MJD1]

hi, this is what I have on the Lisbox_click. It fetches the data and shows it in the text boxes. Now i need to figure out how to edit the data in the text boxes. for exemple, if I need to modify a person's last name, I'd like to be able to simply click the box and write the correct name and have the cell updated. As mentioned above, it has to be on the Change event..I just don't know how.

Private Sub ListBox_Click()

Dim result As String
Dim sheet As Worksheet
Set sheet = Worksheets("Data")

mylookup = Me.ListBox.Value

Me.casenumber = Application.WorksheetFunction.VLookup(mylookup, sheet.Range("A4:as8000"), 1, False)
Me.store_num = Application.WorksheetFunction.VLookup(mylookup, sheet.Range("A4:As8000"), 4, False)

Me.store_name = Application.WorksheetFunction.VLookup(mylookup, sheet.Range("A4:As8000"), 5, False)
Me.case_type = Application.WorksheetFunction.VLookup(mylookup, sheet.Range("A4:As8000"), 7, False)

Me.case_value = Application.WorksheetFunction.VLookup(mylookup, sheet.Range("A4:As8000"), 8, False)
case_value.Value = Format(case_value.Value, "Currency")

Me.name_last = Application.WorksheetFunction.VLookup(mylookup, sheet.Range("A4:As8000"), 9, False)
Me.name_first = Application.WorksheetFunction.VLookup(mylookup, sheet.Range("A4:As8000"), 11, False)



thanks

 

[MJD1]

ok, got most of it all figured, thanks for helping with the direction. I just need to figure out how to filter my listbox with a couple of pull down list.

 

[MJD1]

alright, one last bit of help. it seems I made a mistake with the below code, it Returns at the Query line "Unable to get the Vlookup property of the Worksheet Function class". not sure where I'm going wrong, I've used very similar vlookup syntax elsewhere and it worked.

Please help!

Private Sub empl_num_AfterUpdate()

Dim sheet As Worksheet
Set sheet = Worksheets("data2")

emplnum = Me.empl_num.Value

Query = Application.WorksheetFunction.VLookup(emplnum, sheet.Range("A1:l8000"), 1, False)

If emplnum = Query Then

Me.last_name = Application.WorksheetFunction.VLookup(emplnum, sheet.Range("A1:l8000"), 2, False)

Else
MsgBox "The Subject was not found, please fill in the fields below to add", vbInformation

End If


End Sub

 

[MJD1]

well, it seems that I was able to figure this one out as well. turns out the column in the data2 table didn't match the formatting. just changed it to Text and it worked.

martin