Retrieving data from a multiple worksheets to userform

[Kmahraz]

Hello Ninjas!
Since joining Chandoo my knowledge with VBA is getting better and I am growing more confident by the day with VBA, UserForm...this leads me pushing my boundaries more and more!

I created this sample userform with the help and suggestion of some of the members to create several entries/records, now I would like to pull the data from both sheets and be able to review, edit, update and delete if I can.

The idea is to be able to search by Company name using a combobox that shows the list of companies existing from previous records and pull the list of CASE ID's that are attributed to that company as shown below, then when pressing "Retrieve" it should load the record data for the specific company selected back to the userform so that it can be updated and overwrite back to the sheet in the same rows/Sheets.


The file attached has the userform that already has the code to be able to create records
Any tips, help links to similar problems would be very welcome !
Regards,
Karim

 

[Nebu]

Hi:

This need a different set of codes , the form should be designed to allow the user to enter the customer Id and search. You can use the find method in VBA to get the desired results. This question have been asked in this forum before. Give a search in the forum you can get ready made codes for this.

Thanks

 

[Kmahraz]

Hi Nebu,
I requested help with this question before in one of my previous threads but it was never answered, I am open to any suggestions/ solutions.
I made a mistake what you referring to as the customer ID is the CASE ID and not customer ID
They may be several cases for the same customer with different set of data.
my intentions are to pull all the CASE ID's records for the same customer.
This is one of the thread where I got the idea vs using an ID I thought its better to use a dropdown
http://chandoo.org/forum/threads/tr...a-from-workbook-to-userform.16426/#post-99700
Thoughts?

Thanks,
Karim

 

[Nebu]

Hi:

What I understand from your post is that, if you give an ID you want the corresponding customer name and his job title to be displayed. If you have a data base that captures this info it is simply a matter of searching for the Id entered on the user form and retrieve the corresponding info from the excel sheet to be displayed on the user form. I assume that the ID will be unique. I have attached a sample form to display what I meant , copy any customer ID from your Customer info tab paste it in ID text box and hit search it will display the corresponding name and job title .

Thanks

 

[Kmahraz]

Hi Nebu,
That's partially correct, please forgive me I will try to elaborate.
I want to be able to have a combobox selection with current customer names
Selecting customer name from combobox will display list of Case ID's records tied to that specific customer, when selecting one of those records it will display the name and job title as you provided in your sample file. If the user press Retrieve it will pull record that's tied to that specific ID.
All ID's are unique.
Thanks again Nebu for all your help!
Best,
Karim

 

[Kmahraz]

Any tips, help with my thread please!
I never had to setup a pull data from different sheets and can really use any help I can get.

Here's the thread where I got the idea
http://chandoo.org/forum/threads/tr...a-from-workbook-to-userform.16426/#post-99700

Regards,

 

[Nebu]

Hi:

If you already have customer name in the combobox why you want to pull it again based on an ID. I am confused now. The post you are referring is solution given by Deepak if you can inbox him he will be able to help you out with the codes.

Thanks

 

[Kmahraz]

Hi:
I want to pull CASE ID's in the Listbox1 not customer name.
Please see picture below, BERLN2-0001-16 is one of the case id's that's assigned to Cavaliers, there are 3 case ID's attributed to Cavaliers in my sample
Here are the steps for my search process

1. User will select a company name exp (Cavaliers)
2. Result will display in ListBox1
   1. BERLN2-0001-16
      
   2. BERLN6-0001-16
      
   3. BERLN3-0002-16
3. When user select one of the CASE ID's from the ListBox1 it will display Customer Name & JobTitle

Note: We receive several request from the same company but from different contact so we want to make sure we pull the correct CASE ID
Please let me know if this make sens.
attached is the sample file where i was able to code the combobox using a dynamic list
Best,
Karim

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[Kmahraz]

Hi Nebu,
Thank so much, that's exactly what I had in mind and you delivered,
Greatly appreciated.

For the next step, when I select a CASE ID from the ListBox and press Retrieve button it will retrieve the record attributed to that CASE ID selected and enable me to review, edit and update.

If you can please just give me an idea/ Hint on how I can achieve this or how the code will look like and I will give it a try.

I know this is too much asking; but any help will be much appreciated.

Regards,

Karim

 

[Kmahraz]

Hi:
I was able to create solution to retrieve my data from sheet1, now i would like to see what changes I need to make to my code so that it also retrieve the data from sheet 2 and sheet3 (All sheets ) that's tie to the same CASE ID selected in the listbox?
Best,
Mahraz

Private Sub Retrieve_Click()
Dim c, m, l As Long, i As Variant, rng, fnd As Range
l = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row
m = Val(Me.ComboBox1.Value)

id = Me.ListBox1.Value

If Me.ListBox1.ListIndex < 0 Then
    MsgBox "Pls select Cade ID"
    Exit Sub
End If

Set fnd = Sheet1.Range("M3:M" & l)
Set rng = fnd.Find(What:=Me.ListBox1.List(i), LookIn:=xlFormulas)
c = rng.Row
With Me
    .txtFname.Value = Sheet1.Cells(c, 2).Value
    .txtCompany.Value = Sheet1.Cells(c, 3).Value


End With
L0:
Set rng = Nothing
Set fnd = Nothing
End Sub

 

[Kmahraz]

Any tips, help with my request please!
Here's the updated file where I added my code to retrieve data from the first sheet

 

[Kmahraz]

Hi
I am looking for any suggestions, Ideas or solution to my issue.
I need to retrieve data from both sheets, running out of time with my little project I will really appreciate to get some assistance with this project.
I found out that my solution has some flow so I am back to square 0

 

[p45cal]

see if changing

If Me.ListBox1.ListIndex < 0 Then
  MsgBox "Pls select Cade ID"
  Exit Sub
End If

to:

If Me.ListBox1.ListIndex < 0 Then
  MsgBox "Pls select Cade ID"
  Exit Sub
Else
  i = Me.ListBox1.ListIndex
End If

helps.