• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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
0
Cartillo
Asked:
Cartillo
  • 6
  • 6
2 Solutions
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now