Interactive Chart Shows Start point and End Point Using MouseDown and MouseUp Event in Vba

Hi Friends ! 
Today I will discussed how to select specified location inside a chart using Vba I am facing this problem from 2-3 days but now I got exact solution to Operate such type of Problem. To explain this scenario I took some data that by which I could create a chart and so I generate a  Sheet which contains some data 

Now, I select data containing columns and press F11 and then it automatically generate a chart sheet like this


Now I will go to developer tab and click on chart as circled portion shown below  inside image and then a window will be opened 

Inside that window paste the given code like this


Code
Dim startX As String
Dim startY As Double
Dim endX As String
Dim endY As Double
Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, _
        ByVal x As Long, ByVal y As Long)

    Dim ElementID As Long, Arg1 As Long, Arg2 As Long
    Dim myX As Variant, myY As Double
 
    With ActiveChart
        ' Pass x & y, return ElementID and Args
        .GetChartElement x, y, ElementID, Arg1, Arg2
        ' Did we click over a point or data label?
        If ElementID = xlSeries Or ElementID = xlDataLabel Then
            If Arg2 > 0 Then
                ' Extract x value from array of x values
                myX = WorksheetFunction.Index _
                    (.SeriesCollection(Arg1).XValues, Arg2)
                ' Extract y value from array of y values
                myY = WorksheetFunction.Index _
                    (.SeriesCollection(Arg1).Values, Arg2)
                    startX = CDate(myX)
                    startY = myY
            End If
        End If
    End With
End Sub

Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
        ByVal x As Long, ByVal y As Long)
    Dim ElementID As Long, Arg1 As Long, Arg2 As Long
    Dim myX As Variant, myY As Double
  With ActiveChart
        ' Pass x & y, return ElementID and Args
        .GetChartElement x, y, ElementID, Arg1, Arg2
        ' Did we click over a point or data label?
        If ElementID = xlSeries Or ElementID = xlDataLabel Then
            If Arg2 > 0 Then
                ' Extract x value from array of x values
                myX = WorksheetFunction.Index _
                    (.SeriesCollection(Arg1).XValues, Arg2)
                ' Extract y value from array of y values
                myY = WorksheetFunction.Index _
                    (.SeriesCollection(Arg1).Values, Arg2)
                    endX = CDate(myX)
                    endY = myY
                ' Display message box with point information
                     MsgBox "X = " & startX & vbCrLf _
                    & "Y = " & startY & vbCrLf _
                    & "X = " & endX & vbCrLf _
                    & "Y = " & endY
                   
            End If
        End If
    End With
End Sub 

Explanation

Inside this scenario I am using two events called MouseDown event and MouseUp event MouseDown event means left click on mouse and hold the mouse i.e Don't release it and MouseUp event shows to release mouse and after performing this Operation we will select start and end point of our selected area.

Comments

Post a Comment

Popular posts from this blog

Export data from mysql db to csv file using java

Image
Hi friends ! Today I am discussing about simple but very important scenario. When we create any application then during creation of application we have face many scenarios like 1.Taking information from user and storing these information inside database : Generally such type of Operation is performed by user using form application 2.Taking Data from Database and Displaying on Page : We can perform such type of Operation in various ways like using JSP,JSTL tags or you can perform such operations using JQUERY also. 3.Taking Data from Database and download it in Excel or CSV format : In this we want our data from database and store it inside Excel or text files for further use. And we have chosen third scenario from discussion for performing this Operation we have to need three files exportCsv.html  :- this contains a button for clicking and performing exporting data from database Operation ExportData.java  :- It is a servlet class which defines connectivity fro...
Keep reading

Secure Database Connectivity in node.js with mysql

Image
Hi Everyone! Yesterday my friend Andrea from Italy asked me a question about secure Database connectivity in node.js with mysql i.e. he wants to a secure database connection in node.js for some secure keys like username,password,database name that we use during database connectivity.His problem is obviously right from security point of view as we know that JavaScript is client side scripting knowledge so it is compiled on client side so after inspecting page some one can be able to view these secure keys which I discussed previously. I surf internet and found there are various ways to solve this problem I am not discussing all of them here but also I discussing here two of them and after this discussion I think Andrea and other reader who are facing with same problem can be able to resolve their problem. I choose two ways to solve this problem. Connection code in app.js file Create a Json file Connection code in app.js file : Javascript is client side language and code i...
Keep reading

Java Web Scraping

Image
Today I introduced you from a new word i.e. Web scraping this word is used for extracting data from a website for some purpose. In this technique we extract large amount data from website and store inside our local file or we can also store these data inside our database for personal use .Now this technique is widely used all around the world because in the age of Information data is every thing.As much amount of you have as much power you have.Most of programming languages contains package for Web Scraping such in python we used BeautifulSoup.  BeautifulSoup is a fantastic tool of python for web Scraping. I don't know much about python and I am good in java so, here I am discussing here  how we can scarp data from website using concepts of Java? Now, we are presenting a little package for Java web Scarping. Jsoup is an open source Java HTML parser. Jsoup is an open source HTML parser which is used for scraping in Java.It uses DOM,CSS and Jquery-like methods for ...
Keep reading