Display Data at Userform Based on Conditions

Hi Experts,

I would like to request Experts help to modify the attached script to display data from “Daily Tracking List” sheet at Userform 2 based on populated data at column B43 onwards at Chart 2 sheet. The data was copied from “Daily Tracking List” if  2 conditions “Module 1 - Sub InsertIssue()”:

 data with “Yes” at column I “Daily Tracking List” and referring to week of the month (week of the month based on cell at B41 – Chart sheet which is looking at column A “Daily Tracking List”).

The userform is activated when we double click cell at column B 43 and onward but not extracting the right data as per condition. Hope Experts will help me to  modify this script and able to display the right data  at Userform.



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rng As Range
Dim celle As Range

With Sheets("Chart2")
    Set rng = Range(.Cells(2, "B"), .Cells(.Rows.Count, "C"))
End With

If Not Intersect(rng, Target) Is Nothing Then
    On Error GoTo ende
    UserForm2.Show
Else
    On Error GoTo ende
    UserForm2.Hide
    Unload UserForm2
End If
ende:
End Sub

Open in new window

Chart-Userform.xls
CartilloAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eric ZwiekhorstSAP Business ConsultantCommented:
Hi Cartillo,

as I can see where you went wrong is this:

You take the row number of the issue you doubleclick to look at the same row in Daily tracking.
These don't match.

What you should do is remember the row where is the first occurence that is in your issue list.
then you can find that row and all others by just taking the doubleclick row minus 43 and adding to the remembered row.
then you will find the correct data...


Kind regards

Eric
0
CartilloAuthor Commented:
Hi Eric,

Please assist me, hope you can share with me the amended code.
0
Eric ZwiekhorstSAP Business ConsultantCommented:
Dear Cartillo,

please find attached your file with the remember code...
Chart-Userform.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

CartilloAuthor Commented:
Hi Eric,

Thank for the file. Only first row showing the right matched, the other following rows showing a wrong match. E.g. selection for TH9 (Line chart will display different chart plot based on spinner selection), first row matched with issue 3, but the 2nd to 4th rows are showing  different data in Userform.  
0
Eric ZwiekhorstSAP Business ConsultantCommented:
Oh, I was thinking the rows followed but apparently they don't
Then we have to make the variable StrtRow a array and remember for each row in the generated issue list the corresponding row in the details list...

Can you cope with that or do you need me to write code for you?


Kind regards

Eric
0
CartilloAuthor Commented:
Hi Eric,

Hope you will consider create a variable StrtRow, please.
0
Eric ZwiekhorstSAP Business ConsultantCommented:
Code altered to array...
Chart-Userform.xls
0
CartilloAuthor Commented:
Hi Eric,

Thanks a lot for the code. The userform always show the last data that was captured even though the row with status "No Issue". Is that possible the userform only active if the rows with content? if no issue activate boxMsgBox ("No incidents relative to this channel or time period")
Hope you'll consider this request.
0
Eric ZwiekhorstSAP Business ConsultantCommented:
Dear Cartillo,
attached the changes...

Kind Regards

Eric
Chart-Userform.xls
0
CartilloAuthor Commented:
Hi Eric,

Thanks a lot for giving me a superb solution
0
Eric ZwiekhorstSAP Business ConsultantCommented:
No problem,

Thanks for the points...

Eric
0
CartilloAuthor Commented:
Hi Eric,

Hope you will consider this request, based on same workbook:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26949568.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.