Pop up Userform

Hi Experts,

I would like to request Experts help. Need to activate Userform1 "Sub summarylist()" module when bring the cursor at Line Chart series 2 plot area (Chart 2 sheet). The Userform only active if the targeted point has been highlighted (Data with "Yes" at "Daily Tracking List - Column I) as red at Detail sheet. Userform need to hide automatically after moving out from the "Red" data area.

Hope Experts will help me to add this function in the attached workbook.


Chart-V7.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.

rspahitzCommented:
See if the attached does what you want.

I overlayed a label on top of the chart so that when you click it launches the form you had in your project.
 Chart-V7.xls
CartilloAuthor Commented:
Hi rspahitz,

Thanks for the file. Is that possible to show related data in the Userform for the targeted marker at Line Chart?. Similar way if we activate this Userform at Detail sheet. Target the "red" highlighted cell at Detail sheet and hit the "Show Detail" button to display that data for that week. Hope this is possible.  
rspahitzCommented:
Let me look into it and get back to you tomorrow...
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

CartilloAuthor Commented:
Hi rspahitz,

OK, I will wait then.
rspahitzCommented:
I change this a bit, using an ActiveX label rather than an Excel forms label; that allows you to intercept the location of the mouse so I can capture which diamond was clicked.

So I'm not quite sure what you're looking for next.
So try the attached to see if it's closer.  Maybe that's enough to get you going.
 Chart-V7.xls

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
CartilloAuthor Commented:
Hi rspahitz,

Thanks for the help. Intent to modify the data display at Userform but end up with error "invalid property value" especially when setting for ListBox1 with Column C data (Time). Hope you can help me to fix this error.


       '.DetailsBox.Text = "clicked on #" & iWeekNumber
        .ListBox1.Text = objSheet.Range("C" & iWeekNumberRow).Value
        .DetailsBox.Text = objSheet.Range("J" & iWeekNumberRow).Value
        .Occurrence.Text = objSheet.Range("E" & iWeekNumberRow).Value
        .Outage.Text = objSheet.Range("G" & iWeekNumberRow).Value
        .TotalOutage.Text = objSheet.Range("H" & iWeekNumberRow).Value
        .KPI.Text = objSheet.Range("I" & iWeekNumberRow).Value
        .Status.Text = objSheet.Range("M" & iWeekNumberRow).Value
        .Updatesbox.Text = objSheet.Range("K" & iWeekNumberRow).Value
rspahitzCommented:
For listboxes, you should use the AddItem method rather than the Text property.


    With UserForm1
        .ListBox1.AddItem objSheet.Range("C" & iWeekNumberRow).Value
...
    End With

You may also need:
     .ListBox1.Clear
Without this, the items may accumulate.
CartilloAuthor Commented:
Hi,

Thanks for the fix. I've tried to include " ListBox1.Clear after End With but it shows error as "invalid or unqualified reference". How to fix this?

Also, at Listbox1, I have multiple data display ( actually the Listbox1 looking for column B (Date) sorry for the wrong info). When I click the date, the data at the Userform disappear. If you noticed the userform is also active at Detail sheet. When we click the date(especially highlighted with red)  and hit the "Detail View" button that will display similar data from "Daily Tracking List', here we can select other data from date selection. Hope this is possible at Chart view as well.
rspahitzCommented:
Put it near the beginning:

...
    With UserForm1
        .ListBox1.Clear '<===============clear current list box contents before adding new stuff
        .ListBox1.AddItem objSheet.Range("B" & iWeekNumberRow).Value
...

--
>"When I click the date, the data at the Userform disappear."
Not sure what you mean here.  Which date are you clicking?  And is this what's happening or what you WANT to happen?

--
Sounds like you want the form to do several things, depending on context.  If so, change this block of code so the "WITH" block appears above the SHOW, as I noted below.

Sub summarylist()
If ActiveCell.Value = "100.0000; 100.0000" Or ActiveCell.Value = "" Then
  MsgBox ("No incidents relative to this channel or time period")
  Exit Sub
Else
    Load UserForm1
    UserForm1.Show  '<= insert the "WITH" block from the other code above this line
    Unload UserForm1
End If
End Sub

---
However, it will also need some changes to determine where to get the data:


    Dim iWeekNumberRow As Integer
   
     iWeekNumberRow = 1 'TBD
CartilloAuthor Commented:
Hi,

Have atttached the code, hope I've followed you.

>"When I click the date, the data at the Userform disappear."

If multiple data available on the week, few date will display at ListBox1. Therefore, when we click the date, the data of the each column from Daily Tracking List" will displayed accordingly. Now the userform not allow this type of selection as what happen when I use "Private Sub UserForm_Initialize()" macro.
Sub summarylist()
Dim iWeekNumberRow As Integer
iWeekNumberRow = 1 'TBD
If ActiveCell.Value = "100.0000; 100.0000" Or ActiveCell.Value = "" Then
  MsgBox ("No incidents relative to this channel or time period")
  Exit Sub
Else
    Load UserForm1
    UserForm1.Show
    With UserForm1
        '.DetailsBox.Text = "clicked on #" & iWeekNumber
        .ListBox1.AddItem objSheet.Range("B" & iWeekNumberRow).Value
        .DetailsBox.Text = objSheet.Range("J" & iWeekNumberRow).Value
        .Occurrence.Text = objSheet.Range("E" & iWeekNumberRow).Value
        .Outage.Text = objSheet.Range("G" & iWeekNumberRow).Value
        .TotalOutage.Text = objSheet.Range("H" & iWeekNumberRow).Value
        .KPI.Text = objSheet.Range("I" & iWeekNumberRow).Value
        .Status.Text = objSheet.Range("M" & iWeekNumberRow).Value
        .Updatesbox.Text = objSheet.Range("K" & iWeekNumberRow).Value
    Unload UserForm1
End If

End Sub

Open in new window

rspahitzCommented:
It appears that you like to continue this here: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26941952.html?cid=1752

If so, did you find the initial parts of this useful?

I'll continue it in the above question.
CartilloAuthor Commented:
Hi rspahitz,

Your solution at this  question enabling me to activate the userform. But the problem is its not populating multiple data and displayed the data with date selection at Listbox1.

I'll close this question then.
CartilloAuthor Commented:
Hi rspahitz,

Thanks a lot for the solution.
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.