Bar Graphs: Can I draw arrows in a graph like the sample screen shot below?

try this code:
 ActiveSheet.Shapes.AddShape(msoShapeDownArrow(Left:=259.5, Width:=41.25, Top:=120, Height:=37.5))
       

Hi pdb,

Thx for writing:
a) I guess your code applies to Excel bec. it says "Active sheet"
b) I have several courses (members of x axis series)  running across x axis
     Each course can have a max. of 3 bars for grades (HP, H, P)
     So a "series set" can have max of 3 bars.

    Would your code work for each 'set of bars"?
c) I learned graphing last week via EE for Access, can your technique be used for Access...?
tx, s

The code I gave you is vba code, so it should work for  Access, provided the syntax is exactly correct.....and that is what I am not sure of.  The code came from my code library, but has not yet been tested.  The only way to find out is to test it out on your chart, and modify the syntax as needed using intellisense. ..... unless you prefer to wait for tested code.  As to your question about sets of bars, I believe all you have to do is create a code loop to create a "set" of arrows.

hi pbd,

ok, will experiment... will be in touch... tx, s

Sorry,   pdb   - typo on initials.... tx, s

Hi pdb,

I googled different words in the vba code that you supplied, but i am not understanding...
It seems to me a feature of an Excel spreadsheet....

To be honest, I'm also trying another approach, which is unfinished, at this post:
https://www.experts-exchange.com/questions/24584887/Access-rectangle-object-programmatically-resizing-rectangle.html

tx, s

As far as I know, the Graphing engine in Microsoft Office does not support anything like this.

The best option might be to contact the makers of that particular graph and ask how they did it, or what software they used.

Besides, what do the arrosw signify?
What does the source data look like?

(Perhaps you can create the the Bars and arrows manually)

JeffCoachman

Jeff,
<<<As far as I know, the Graphing engine in Microsoft Office does not support anything like this.>>>
Wha are you referring to?  Excel supports shapes, and you can call excel from Access via VBA as an automation object.
pdb

Good morning,

Hi Pdb,
    Thx for providing alot of useful info.

Regarding your idea of drawing an arrow.... I did not realize that you can draw on a slant, wow!
A few days ago, I found this post, which suggested using a wingding character:

https://www.experts-exchange.com/questions/21541168/how-can-you-draw-an-arrow-in-forms-designer.html?sfQueryTermInfo=1+access+arrow+draw

I don't have complete control using wingding, sometimes my line pointing to the arrow is slightly
off center.... so maybe will have better control by drawing as you suggested...

Hi Jeff,
        I had a feeling that I would not be able to draw arrows on top on MS/graph.....  
        I was told that this graph was created 10 years ago, so no-one on that website to contact.
        I was able to create a report and resize horitzontal bars via code.
        I have a related post asking how to resize vertically.
               
So for now, pdb gave alot of info, so will close this post.
I do have related quests, so will create new posts, tx, Sandra

puppydogbuddy,

<Excel supports shapes, and you can call excel from Access via VBA as an automation object.>
Yes, I was just making it clear to the asker that you indeed would have to use some form of "real" automation to do this.

Based on the askers previous questions, I was just making sure that their use of the word "Automation" was consistent with ours.

Because of the rather odd way that Access makes you edit charts, some people think of that as "Automation".
;-)
My point was that Office charts do not have an "Associate Image with Data Point" option, which can be automated through VBA.
This functionality must be "created totally" in VBA.

For example Graphs have a "SeriesCollection" that can be manipulated with VBA.
There is no "DataPointImage" object in the Charting object model (again, AFAIK)
Again, you have to create this functionality totally from scratch in VBA.

As we all know, you can use automation to do just about anything you want, as your post illustrates.

Sorry for any confusion.
;-)

Jeff

Gentlemen,

I have used automation from Access to communicate "under the hood" via VBA with Excel.
1st example - massage Excel columns before importing into Access
2nd example - write from Access recordset to Excel spreadsheet

I have no experience with graphs.
So first wanted to learn Access graphs.
Then I wanted to try an Excel graph. (Jeff, after all your lessons, I found it easier to control graph
in Access (as a non-pgmer) than when tried with Excel).

Pdb offered yesterday info that via VBA, a pmger has more possibilities of being in control
Jeff offered this morning that there ARE BOOKS just on Excel charts.

Yesterday, I was also exploring self authored graphs such as the horizontal screens shot below.
I'm kind of pleased with it.

However, ( my user may prefer vertical graph (she is returning from vac. time)
and have been exploring self vertical.  Learning Excel graphs via VBA may need to be option

I have been having trouble with self vertical  (have posted a sep quest on that - and may need to
abandon that option)

The Excel/vba option may require alot of time to learn.... and still I'm not sure if positioning the arrow
on vertical bar will be possible.

Once option to explore is to post - asking if anyone has generated an Excel spreadsheet via
automation from Access using VBA and would they be kind enough to post it - bec I do not know
where to begin.

That merits a sep post.... so that's how things are with this graph project....

Option Compare Database
 
Option Explicit
 
Dim objExcel                           As Excel.Application
Dim objExcelActiveWkbs                 As Excel.Workbooks
Dim objExcelActiveWkb                  As Excel.Workbook
Dim objExcelActiveWs                   As Excel.Worksheet
 
Dim mfExcelAlreadyRunning              As Boolean
Dim mfCancel                           As Boolean
 
 
Public Sub e002_CreateExcel(strExcelPath As String)
 
Call e090_LaunchExcel
' Set objExcel = CreateObject("Excel.Application")
  
Set objExcelActiveWkbs = objExcel.Workbooks
 
Set objExcelActiveWkb = objExcelActiveWkbs.Open(FileName:=strExcelPath)
Set objExcelActiveWs = objExcel.ActiveSheet
 
objExcel.Visible = True
 
 
Dim intColCount As Long
Dim intRowCount As Long
Dim intMaxRowCount As Long
 
 
intRowCount = 1
intColCount = 1
intMaxRowCount = 1
 
 
 
'change header before import
 
 
With objExcelActiveWs
 
        Debug.Print objExcelActiveWs.UsedRange.Rows.Count
 
        For intRowCount = 1 To intMaxRowCount
            For intColCount = 1 To .UsedRange.Columns.Count
                If .Cells(intRowCount, intColCount).Value = "Advisor Name Lfmi" Then
                   .Cells(intRowCount, intColCount).Value = "Clinical Assistant Dean"
                End If
            Next
        Next
End With
 
 
With objExcelActiveWs
     With .Cells
          .Select
          .EntireColumn.AutoFit
     End With
End With
 
 
' With objExcelActiveWs
'     For intRowCount = 1 To intMaxRowCount
'          For intColCount = 1 To Rows.Columns.Count
'
'             If .Cells(intRowCount, intColCount).Value = "Advisor Name Lfmi" Then
'                .Cells(intRowCount, intColCount).Value = "Clinical Assistant Dean"
'
'             End If
'          Next intColCount
'     Next intRowCount
'
' End With
 
 objExcelActiveWkb.Save
 
Call e110_CloseExcel(True)
Exit Sub
 
' objExcelActiveWkb.Close savechanges:=False
' rev objExcel.ActiveWorkbook.Close savechanges:=False
'objExcel.Application.Quit
 
Set objExcel = Nothing
Set objExcelActiveWkb = Nothing
Set objExcelActiveWkbs = Nothing
Set objExcelActiveWs = Nothing
 
 
    
End Sub
 
' Call e105_SaveExcelSpreadsheet(strExcelPath)
   
' Call e110_CloseExcel(blnHowToCloseExcel)
 
 
Public Sub e090_LaunchExcel()
On Error Resume Next
 
If e095_WasExcelRunningBeforeThisExecution Then
    mfExcelAlreadyRunning = True
    Set objExcel = GetObject(, "Excel.Application")
Else
    mfExcelAlreadyRunning = False
    Set objExcel = CreateObject("Excel.Application")
End If
 
objExcel.Visible = True
 
End Sub
Function e095_WasExcelRunningBeforeThisExecution() As Boolean
 
On Error Resume Next
 
Set objExcel = GetObject(, "Excel.Application")
 
e095_WasExcelRunningBeforeThisExecution = (Err.Number = 0) ' if err.number = 0, true else false
Debug.Print Err.Number
Debug.Print Err.Description
 
 
Err.Clear
 
End Function
 
 
 
Public Sub e110_CloseExcel(blnHowToCloseExcel As Boolean)
                       
 
On Error GoTo CloseExcel_Err
 
DoCmd.Hourglass False
  
If blnHowToCloseExcel = False Then
   Exit Sub
Else
           
    objExcelActiveWkb.Close savechanges:=False     ' saving in e105     'blnHowToCloseExcel
'    objExcel.ActiveWorkbook.Close savechanges:=False
 
    If Not mfExcelAlreadyRunning Then
       objExcel.Application.Quit
    End If
End If
 
CloseExcel_Exit:
    Set objExcel = Nothing
    Set objExcelActiveWkb = Nothing
    Set objExcelActiveWkbs = Nothing
    Set objExcelActiveWs = Nothing
    
    Exit Sub
    
CloseExcel_Err:
    MsgBox "Error # " & Err.Number & ": " & Err.Description
    Resume CloseExcel_Exit
 
    
End Sub
 
 
'Public objExcel                           As Excel.Application
'Public objExcelActiveWkbs                 As Excel.Workbooks
'Public objExcelActiveWkb                  As Excel.Workbook
'Public objExcelActiveWs                   As Excel.Worksheet
 
 
Public Sub e005_CopyFromRecordSet(strSql As String, _
                                  strExcelPath As String, _
                                  strExcelMode As String)
 
Call e090_LaunchExcel
' Set objExcel = CreateObject("Excel.Application")
  
Set objExcelActiveWkbs = objExcel.Workbooks
 
Set objExcelActiveWkb = objExcelActiveWkbs.Open(FileName:=strExcelPath)
Set objExcelActiveWs = objExcel.ActiveSheet
 
objExcel.Visible = True
 
Dim intColCount As Long
Dim intRowCount As Long
Dim intMaxRowCount As Long
 
 
Dim db                    As DAO.Database
Dim rst                   As DAO.Recordset
Dim fld                   As DAO.Field
 
 
Set db = CurrentDb
Set rst = db.OpenRecordset(strSql)
 
Debug.Print strSql
 
 
Dim frm            As Form
Set frm = Forms!FRM_020_Reg_ViewStudents_CAD
 
objExcelActiveWs.Cells(2, 2).Value = "Class of " & frm.hdrGradYear
 
 
intRowCount = 4
intColCount = 1
intMaxRowCount = 1
 
 
'For Each fld In rst.fields
'    If fld.Type <> dbLongBinary Then
'       objExcelActiveWs.Cells(intRowCount, intColCount).Value = fld.Name
'       intColCount = intColCount + 1
'    End If
'Next fld
 
 
objExcelActiveWs.Cells(4, 1).Value = "First Name"
objExcelActiveWs.Cells(4, 2).Value = "Last Name"
objExcelActiveWs.Cells(4, 3).Value = "Clinical Assistant Dean"
 
objExcelActiveWs.Range("A5").CopyFromRecordset rst
 
 
With objExcelActiveWs
     With .Cells
          .Select
          .EntireColumn.AutoFit
     End With
End With
 
If strExcelMode = "U" Then
' * user manually disconnects from excel
 
Else
   ' save Excel file
     objExcel.DisplayAlerts = False
   
     objExcelActiveWkb.SaveAs FileName:= _
                  pgc_strPathName & "x_CAD_Listing.xls", _
                  FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
                  ReadOnlyRecommended:=False, CreateBackup:=False
                  
     objExcel.DisplayAlerts = True
     
     'objExcel.ActiveWorkbook.Save
     'objExcelActiveWkb.Save
 
End If
 
 
Set objExcel = Nothing
Set objExcelActiveWkb = Nothing
Set objExcelActiveWkbs = Nothing
Set objExcelActiveWs = Nothing
 
rst.Close
db.Close
 
Set rst = Nothing
Set db = Nothing
Set fld = Nothing
 
End Sub

Select allOpen in new window

HorizSelfGraph.GIF

Sandra,
Thanks for the points and grade.  I think the following link is a good "getting started" reference source on Excel vba.   Hope it heilps.
                 http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html
Jeff,
Thanks for the clarification.

Hi pdb,

Thanks sooo much for your help and newest link.... very kind of you....
May be opening related posts...

right now, I'm organizing my mdb to rename and discard non working graphs etc.
so that i have something cohesive to present my user when she returns from vac tomm.

I'm thinking that before i invest more time in learning excel charts/vba i should show her what
i have and take it from there....

tx, s

I try to do as much as I can in "edit mode" as I can.

Because of Access' odd way of accsessing these options, many people turn to VBA when they really don't need to.

Obviously, in this case VBA is needed.

For me, learning the Graphing VBA Object Model is like re-learn a totally new Object Model.
(When I still have trouble sometimes with Access' object model!)
;-)

BTW, I don't think you ever mentioned directly *What* the arrow signifies?
FWIW, the chart/report in your screenshot can be quite easily created without a chart at all, just Colored Rectangles.
Screensht attached

JeffCoachman
untitled.JPG

Hi Jeff,

Thx for writing...
a) I did a self-horizontal graph - the screen shot appear in one of my replys on this post, a little on
top of yours

b) BTW, did you use wingdings to create your arrow?
c) Now I'm attempting to make this a self-vertical graph....
    Erez help alot on this one
    https://www.experts-exchange.com/questions/24587827/How-can-I-make-this-logic-more-variable-dependent.html?anchorAnswerId=24909346#a24909346

d) yet I have  issues:
     a) I want the arrow to stretch the diff. bet the size of bar and max of bar

btw, arrow shows which bar the student's grade falls into

Below is a screen shot of what I have so far....
HorizSelfGraph.GIF

What I presented works best with the "Horizontal Style" I presented.

But being that this question is closed, this should be a new question.

Is there a reason why you are refusing to tell us what the arrows signify?

Hi Jeff,

No prob, about making new posts

anyway i answered your prev. quest. above:
<a) I want the arrow to stretch the diff. bet the size of bar and max of bar>

tx, s

"Stretch the diff"???

HI Jeff,

ok, thx for asking
I made a mistake, I thought I had to make the arrow very long in the white space,
but in the sample graph that I am trying to copy,
   the arrow does not grow, it just moves:

Erez, showed me how to manipulate the top property of a rectangle to simulate NORTHWARD
growth of a bar here:

https://www.experts-exchange.com/questions/24587827/How-can-I-make-this-logic-more-variable-dependent.html

tx for questioning my "arrow" assumption.... I have more quests. on this topic, so may post again, tx, s

GraphWithArrows.GIF

OK

Jeff,
fyi... new related post...

https://www.experts-exchange.com/questions/24592586/Reports-Landscape-question.html

tx, s