Free Microsoft Excel 2013 Quick Reference

draw on top of embedded object in Excel 2007

draw on top of embedded object in Excel 2007

Is there a way to place a drawing object on top of an embedded object in Excel 2007?


Post your answer or comment

comments powered by Disqus
Draw on top of embedded objects in Excel 2007

Is it possible to place a drawing object on top of an embedded object in Excel 2007?

I'm attempting to put a word on top of another word in a cell. How do I do
this? I have Windows 2000. Thanks!

How do you add dates to the top of each column in Excel? I want each column
to represent a day. I could write the date in manually, but so far, I have
been starting from the top of each column (in Line 1), so I would have to
move the data in each column down by one cell to make room to write in the
date. Is there any way to do that automatically?

How to Optimize the use of Query Table in Excel 2007 VBA?

Hi,

I have an exisiting excel vba that uses QueryTable. The Macro runs fast before in excel 2003, but when we migrated to Excel2007 the macro's performance became very slow. Especilly when calling QueryTable.Add that uses an external url to get its connection. This line is being use repeatedly as it pulls information in the url with different inputs.

Is there anything I can use of other than QueryTable to pull records from a website?

Animating Objects in Excel 2007 Using the 3-D Animation Engine

... the 3-D Animation Engine tool, which demonstrates the new OfficeArt rendering platform in Microsoft Office Excel 2007 ... You must explicitly call the functions in VBA. Each column in ...

I'm having trouble displaying all of a linked or embeded workbook in excel
2003 on my school's EP computers. Every time I try to create one I only get
10 lines of the workbook. Any suggestions??

I am having trouble with my gridlines printing on top of the text in my
worksheet. I have tried to align both vertically and horizontally. It looks
fine when I preview before printing, but everytime I print, the cell contents
move up above the lines. Any ideas?

Whenever I draw arrows and lines in Excel 2007 and then save and exit a
particualr document, upon reopening often the drawn objects have moved
around, and sometimes arrows and lines change lengths?

Any ideas how to stop this happening?

Hello,

While further refining my waterfall VBA code, I could not find the way to position the data labels on top of the stacks. And of course, the labels are often positioned in the stacks themselves, making them difficult to read and very ugly to watch.

Is there a way to request the labels to be on top of the stacks?

Here comes my code, in which data labels are displayed only if they are different from zero. I also attach a file ready to be tested.

Help on this would, as usual, be MUCH appreciated

Thank you in advance

Public Sub
DrawWaterfallChart()

Dim SheetName As String
Dim Data1Col As Integer, Data2Col As Integer, LabelsCol As Integer
Dim FirstRow As Long, LastRow As Long

LabelsCol = 1
Data1Col = 2
Data2Col = 3
FirstRow = 3
LastRow = 23

SheetName = "Global"


Dim rng1 As Range, rng2 As Range, rnglabels As Range, rngGlobal As Range

Dim myChtObj As ChartObject
Dim iColumn As Long

Dim plus() As Double
Dim minus() As Double
Dim basement() As Double
Dim labels() As String
Dim Height As Long
Height = LastRow - FirstRow + 1
Dim Initial As Double

'Let's put the data in arrays because in the final application the data will be coming from arrays

ReDim plus(1 To Height)
ReDim minus(1 To Height)
ReDim basement(1 To Height)
ReDim labels(1 To Height)

Dim Row As Long, Col As Integer

For Row = 1 To Height
    If Sheets(SheetName).Cells(FirstRow + Row - 1, Data2Col) > Sheets(SheetName).Cells(FirstRow + Row - 1, Data1Col) Then
        plus(Row) = Sheets(SheetName).Cells(FirstRow + Row - 1, Data2Col) - Sheets(SheetName).Cells(FirstRow + Row - 1,
Data1Col)
        minus(Row) = 0
    Else
        plus(Row) = 0
        minus(Row) = -Sheets(SheetName).Cells(FirstRow + Row - 1, Data2Col) + Sheets(SheetName).Cells(FirstRow + Row - 1,
Data1Col)
    End If
Next Row

For Row = 1 To UBound(plus)
    If plus(Row) > 0 Then
        basement(Row) = Sheets(SheetName).Cells(FirstRow + Row - 1, Data2Col)
    Else
        basement(Row) = Sheets(SheetName).Cells(FirstRow + Row - 1, Data1Col)
    End If
    labels(Row) = Sheets(SheetName).Cells(FirstRow + Row - 1, LabelsCol)
Next Row

Set myChtObj = Sheets(SheetName).ChartObjects.Add(Left:=250, Width:=375, Top:=75, Height:=225)

Dim Invisible As Series
    
Dim Positive As Series
    
Dim Negative As Series

' Add the chart
With myChtObj.Chart
    .ChartArea.Fill.Visible = False
    .PlotArea.Format.Fill.Solid
    .PlotArea.Format.Fill.Transparency = 1
    .HasTitle = True
    .HasLegend = False
    
    .ChartTitle.Text = "My chart"
        
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Units"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Quantity"
    
    
    .ChartGroups(1).GapWidth = 0

    ' Make Column Stacked chart
    .ChartType = xlColumnStacked
    
    ' Add series from selected range, column by column
    Set Invisible = .SeriesCollection.NewSeries
    With Invisible
        .Values = basement
        .XValues = labels
        .Name = "Labels"
        With .Border
            .ColorIndex = 13
            .Weight = xlMedium
            .LineStyle = xlNone
        End With
        .Format.Fill.Visible = False
        .Format.Line.Transparency = 0
        .MarkerStyle = xlNone
    End With
    
    Set Positive = .SeriesCollection.NewSeries
    With Positive
        .Values = plus
        .XValues = labels
        .Name = "Plus"
        .Interior.ColorIndex = 14
    
        .HasDataLabels = False
    End With
    
    nPts = Positive.Points.Count 'save the number of points
    aVals = Positive.Values 'save all the values in array

    For Col = 1 To nPts ' loop through all points
        If aVals(Col) > 0 Then
            t = "+" & CStr(Round(aVals(Col)))
            Positive.Points(Col).HasDataLabel = True
            With Positive.Points(Col).DataLabel
                .Text = t
                ' Here I'd like to be able to request the data label to be above the stack (xlLabelPositionAbove)
                .Position = 4 'Only accepts 3 (xlLabelPositionInsideEnd) and 4 (xlLabelPositionInsideBase)
                With .Font
                    .ColorIndex = 14
                    .Size = 6
                End With
            End With
        End If
    Next Col

            
    Set Negative = .SeriesCollection.NewSeries
    With Negative
        .Values = minus
        .XValues = labels
        .Name = "Minus"
        .Interior.ColorIndex = 18
    End With
    
    nPts = Negative.Points.Count 'save the number of points
    aVals = Negative.Values 'save all the values in array

    For Col = 1 To nPts ' loop through all points
        If aVals(Col) > 0 Then
            t = "-" & CStr(Round(aVals(Col)))
            Negative.Points(Col).HasDataLabel = True
            With Negative.Points(Col).DataLabel
                .Text = t
                ' Here I'd like to be able to request the data label to be above the stack (xlLabelPositionAbove)
                .Position = 4 'Only accepts 3 (xlLabelPositionInsideEnd) and 4 (xlLabelPositionInsideBase)
                With .Font
                    .ColorIndex = 18
                    .Size = 6
                End With
            End With
        End If
    Next Col
        
End With

End Sub


The one feature in Excel 2007 that's still giving me a lot of trouble is the behavior of the Find and Replace dialog box.

In Excel 2000 (I never had 2003), when I clicked on "replace", it brought up a dialog box with the cursor in the "find" position. I'd type the thing I wanted to look for, then tab, then the thing I wanted to replace it with. Then, I could select from "find next", "replace all", etc.

Excel 2007 behaves differently. I could live with different, but it behaves inconsistently, so I have to actually look hard at the dialog box to see what it wants each time. I can't just rely on my fingers to "know" what to type.

If it's the 1st time I've used find and replace in this "incidence" of Excel (i.e. today), then the dialog box behaves just like in Excel 2000. If it's a subsequent time, then the cursor defaults to the "replace" position rather than the "find" position. I'm all the time putting my "find" criteria in the "replace" box, and then having to backup and fix it.

Is this a "feature" that I can change? I can't find any mention of it on anyone's website, but have trouble believing that I'm the only one getting tripped up by this change.

I have table set up in Excel 2007 (see below) where the columns on the left are available for user input (denoted by x) and the columns on the right (denoted by f) are columns with formulas in them.

A B C D E F G
x x x f f f f f
x x x f f f f f

A feature of Excel 2007 tables is that once you start to enter data on a new row (in this case A3 for example), the table automatically expands to cover the new row and the formulas in the above row are copied over (the ones in D-G).

Everythings works ok until I want to lock the cells other than those intended for user input. That is, I want to lock columns C-G to avoid the user messing up the formulas and leave columns A-C open for input. If use format cells -> protect -> lock cells for columns C-G and protect the sheet, the table no longer resizes when I input data in the row below and if I try to copy & insert the last row of the table, I get an error message saying "The cell or chart that you are trying to change is protected and therefore read-only".

Any thoughts on how I could get this setup to work? Many thanks.

I am trying to print a spreadsheet that contains a photo and a telephone
symbol beside my contact information. I can not get either to print in Excel
2007 Beta. When I print to Adobe PDF the photo and object show up however the
header and footer information are missing.
Any suggestions on how to correct both problems?

Hi,

I have the intermittant problem of files created in Excel 2007 not
appearing inside the Excel window when I open them with Excel 2007.

After opening the file the Windows bar at the bottom shows the file as
open and If I try to open the again file Excel tells me it is already
open.
I do not think the file is minimised so small it cannot be seen as I
have tried clicking everywhere iin the Excel window and cannot
activate it to be viewable.

What does work is to go to "View" tab and then to the "arrange all"
icon where I tell the open files to be tiled on the screen. The file
window then pops up and behaves as normal.

I can work on the file, save, close and open it normally and this will
not happen again until I reast expect it.

This problem is not restricted to just one particular file, it seems
it can happen to any .xlsx, .xlsm and .xls files.

Any-one have an idea of what is going on or where I am inadvertantly
changing some setting?

Thanks in advance,

Dave

In previous versions I used the selection arrow to drag a rectangle around a
number of objects to select them for moving or grouping or applying a common
attribute. How is this done in Excel 2007?

I just noticed that Excel 2007 60-day trial version totally corrupted my
Excel 2003 chart that combines a stock chart with line charts and X-Y charts.
Then when I try to create such combined chart from scratch, I get a message
saying "Some charts cannot be combined with other charts"!

My department does a lot of statistical analysis, and we've been overlaying
other chart types on top of stock charts since Excel 2000. Is there a way to
do this in Excel 2007 or will MS add this feature as a service pack?

For an example of the combined chart, see this website:

http://peltiertech.com/Excel/Charts/StockChartPlus.html

Hi everyone,

I'm new to the forum. I'm seeking assistance in a situation I found to be challenging. I'm working on a single table using MS Excel 2007. The table consists a list of different activities that need to be done and the people each is assigned in two or five of them and the completion progress for each assigned activity. The issue doesn't point toward the outer table and data, but the underlying Macro. I'm very new in handling Macro in Excel.

This is just to provide a better image and does not accurately depict my situation. Basically, the table is broken into say, 10 columns total. The 1st column shows unique ID for each activity record. The 2nd column shows brief description for each activity. Now, there are 8 columns left. These columns represent the people related to some (but not all) of the listed activities and completion progress for each of his or her assigned activities in percentage. These columns are headed by their own names. A person's progress value can be either 80%, 30% or even 0%.

When I run my own Macro, it did not work. I should be able to see only those people who have progress value other than 0% in at least one of their assigned activities (not necessarily all). A person with 0% progress for all activities he or she is assigned to, should not be displayed.

Does anyone know the proper Macro framework to help me get started? Does anyone know any direction in this situation?

Many thanks for any response.

Hi,

I have migrated the Excel 2003 templates to Excel 2007 with Ribbon Customization. For that I used Office 2007 Custom UI Editor tool. Also used VBA.
The basic functionality of Paste is not working in New Excel 2007 templates.
I am getting Argument Not Optional error. So i wanted to disable Ctrl+v which is also not happening.

Please help.

Pri

Hi,

I created multiple tables, which is suppose to chart the same types of results for each person:

• Total Pending Tasks
• # of Tasks Due This Week
• Day(s) Early or On-Time
• Day(s) Late
• TAT % (Turn Around Time)
• Pending Tasks

The problem is that my 'X' axis label of individuals are on-top of each other in the chart. How to spread these out?

Thanks

Hi,

I want to use VBA to insert an Organisation Chart in Excel 2007 (ie if I did this manually it would be Insert, SmartArt, Hierarchy), but I cannot find anything on line that shows how to do this in Excel 2007. I know the code for Excel 2003, but that does not work in 2007.

Many thanks in advance for any help anyone can provide.

Shaz

Hello all

I have a large list of names and i have to get other people to fill in certain columns on the list.
Id like to create a drop down list for an entire column in the list.
Whats the best way of doing this in Excel 2007?

I have names in column 1 address in columns 2-5 in column 6 i need to put the area this co works in. I need to fix the entries that can be made to the listwith a list thats been created.

Thanks

D

I am trying create a chart 'on the fly' based on the user selected data range
in Excel 2007. I was trying to use Jon Peltier's 'interactive chart
creation' VB scripts. Somehow it does not work on Excel 2007. After Jon's
interactiveChartCreation.xls opens, both input boxes does not work, meaning
one can not input any data by mouse selection. I tried to click 'ok', but it
only selected the input box. I hope Jon or some one else can see this and
offer some help. Thank you all very much for your tips.

What has happened to custom charts in 2007?

I used to create a chart with one data series displayed as columns
(result data) with another series on the same chart displayed as a
line (budget data). I cannot find any way of doing this in Excel 2007,
anyone offer some help on this?

When I selected a range of cells in Excel 2007, they turn light blue, how can
I change this color. I want it darker....

Hi

I have never used pivot tables in 2003, so nothing of that sort... but whenever I use pivot tables in excel 2007, it irritates me, and people around me who generally claim that they could perhaps do it in excel 2003.

So here goes my questions.

1. How to make it remember what I have done right now? (Or may be how to change default settings?)
eg- In first column I have some classification, and then I have 12 months data, in 12 columns.
So I put first column in Row Label. Then I put 2nd column (first month data) in "Values" as "sum". Then I want to add all the months into the "values" column as sum, just by ticking in the checkboxes present in Field Section. But somehow excel behaves badly, it sometimes add the field into row label, or sometimes it will put the field in "values" but not as "sum" but as "count".

2. Is 24 month data too big to handle?
If I have 24 month data, and I want to add all the 24 fields in "values", it seems pretty irritating to me to drag each field into the right area and then set up the position of field section and area sections so that you can put next field in right place. (Or if you are dragging to pivot table itself instead of area sections, you will have to set up the position of worksheet so that you can put the next field in right place.

Please help with these two problems, it seems that I might ask more questions in future.


No luck finding an answer? You could always try Google.