Resizing Charts And Positioning Legend
May 4, 2007
I have made charts in VBA charts that needs to be placed on specific position and have specific sizes on a chartsheet. I use plotarea (top, left, width, height) for that. Unfortunately when I set these values they keep chaning, there is some scaling going on in Excel and they won't get the specified size. I have been looking on the internet and finally I found a piece of code which works, but still not good enough, because the legend is also not good positioned
De data for resizing the new charts (ChtNew), the legends and the charttitle I get from other charts (ChtOrig). The legend.legendposition of ChtOrig does not have a value but it still have a top,left,height en width which I use.
I show you my code, the part with the for-next is taken from the internet.
I am trying to find a solution now for 3 days and now I only dream of huge charts (which is not good). So please can someone help me with this? This is the last forum, that I can try, I didn't get reactions from others.
with ChtNew.legend
.Top = ChtOrig.Legend.Top
.Height = ChtOrig.Legend.Height
.Left = ChtOrig.Legend.Left
.Width = ChtOrig.Legend.Width '* 1.1
.Top = ChtOrig.Legend.Top + ChtOrig.ChartTitle.Top
end with
With ChtNew.Plotarea
.Top = ChtOrig.PlotArea.Top
.Height = ChtOrig.PlotArea.Height
.Width = ChtOrig.PlotArea.Width
.Left = ChtOrig.PlotArea.Left
For i = 1 To ChtOrig.PlotArea.Top........
View 9 Replies
ADVERTISEMENT
Jun 1, 2007
I am applying for a job as an executive assistant and before the interview I have to complete a "test." I am having trouble with one direction. Here is the question:
"10.Apply series name and legend key in data labels to the Star data series. "
I have included what I think is the correct answer, but I am not sure. It is under the column chart sheet.
View 3 Replies
View Related
May 14, 2014
I have a sheet with about 50-60 charts and I am hoping to have a master list of chart titles on the side, how can I link these titles with the charts themselves?
View 1 Replies
View Related
Jun 2, 2014
I've got the following code going :
[Code] .....
I can't get my With statement to work.
View 1 Replies
View Related
Apr 10, 2009
I've got an Excel spreadsheet with quite a bit of code behind it (2007).
User can select a cell in Col A and push 'F2" and a ListBox pops up (becomes visible).
But if the User is on line 72 say, then the box is in the wrong position (and often becomes visible with diagonal lines across it!); that is I need to determine where the User is and set the ListBox position further down.
I tried to do this by calculating the offset based upon the row number they were on. This works really well, unless, the lines/records are varying heights.
Is there an easy way to do this ????? I have this feeling I must be missing something really simple about how this should work - I'm sure there must be a way that Excel has to handle this.
View 9 Replies
View Related
Aug 22, 2006
I am trying to work out how to pisition a new object in some VBA code, I can do this if I copy an object then click on a cell, e.g. H74 then paste, but the properties for an object want this in points.
ActiveSheet.Shapes.AddShape(msoShapeOval, 50, 50, 26.25, 26.25).Select
What I want to do is select the cell H74, then find the coordinates for that cell then use these when creating the shape, I appreciate these coordinates will need to be assigned to variables, but how do you get then from a given cell?
View 3 Replies
View Related
Feb 14, 2007
I have a workbook where each name in a list has a picture of a flag of the country for that name. The flags are initially assigned to a cell with the country name (see sample attached). Later in the project the flag is copied to a cell in another sheet and assigned to the correct name in the list. The row heights in this list of names is larger than the row height of the sheet with list of countries.
The flags in the sample have been manually sized and positioned as accurately as possible. I am struggling with the VBA code to size and position each flag exactly so that there is a very small gap between the top, bottom and left of the flag and the cell border. Each is set to move and resize with cell.
View 2 Replies
View Related
Apr 2, 2009
is it posible to control the positioning of the pop-up data validation input messsage box via vba? As often it obscures the view of critical cells below.
You can manually click and drag it to a desired position manually. Mana users are not aware of that. It would be desirable to reposition it elsewhere rogramatically.
View 2 Replies
View Related
Jan 9, 2008
I am using data validation input messages for data entry tips/definitions. The data entry cells are grouped in small tables (about 5 rows X 6 columns) so when the user clicks on a cell, the data validation input message box pops up next to the active cell. This covers the adjacent cells which need to be completed as well.
If I drag the message box over to right of the input table, the messages for the other cells stay in the new location as well. However, Excel doesn't remember this location the next time the workbook is opened.
Is there a way to set the location of the message box or some other way to achieve the same objective of having text boxes pop up in a defined location when a cell is activated?
View 9 Replies
View Related
Aug 8, 2006
i try to change the chart position (chart properties) to make it auto- move to empty cells, but anyway i fail.
say example:-
i have a excel sheet,
Worksheet 1
------------
raw data, all records.
worksheet 2
-------------
1 pivot table + a graph/chart at the same sheet.
top area of worksheet is pivot table, below is chart.
The pivot table size can be change from time to time (by increasing the records at the worksheet 1)
settings or using vba to control it? by auto move the chart position down, and not overlap with the pivot table.
View 6 Replies
View Related
Jul 12, 2007
Using a macro, how do I tell Excel to:
1. Copy the xth row and paste it before the yth row
2. Copy the mth column and paste it before the nth column
3. Copy the cell a,b and paste it in he position c,d
where x,y,m,n,a,b,c,d are variables, the value of which the user inputs, say in cells A1, A2, A3, A4, A5, A6, A7 and A8 respectively.
In other words, I need to read numbers m and n which are the values of cells A1 and A2, then I need to go to column number m (for instance, if m=4, I go to column D), copy the whole row, go to column number n (for instance, if m=6, I go to column F), and insert the copied column.
Also wondering if VBA provides any way to quickly horizontally and vertially flip copied tables while pasting. While I am aware of the transpose function which swaps rows with columns, I am looking for a way to reverse the order of either the rows or the columns as per my need, without having to copy-paste row-by-row or column-by-column.
View 9 Replies
View Related
Jun 30, 2008
Sub CreateRectangles()
X = 1
Do Until ActiveCell.Offset(X, X).Value = 0
X = X + 1
Loop
variable = Round(-0.026 * (X * X * X) + 0.56 * (X * X) + 24.7 * X + 5, 0)
Set Box = ActiveSheet.Rectangles.Add(ActiveCell.Offset(0, 0).Left, ActiveCell.Offset(0, 0).Top, variable, variable)
With Box
.ShapeRange.Fill.Transparency = 0.3
.Font.Size = 18
.Font.Name = "Trebuchet MS"
.Text = ActiveCell.NoteText
End With
End Sub
View 3 Replies
View Related
Mar 13, 2012
My set up office 2007 win xp pro ie 8 I am using the code below to retrieve web pages etc it works fine, however it display in differ areas of the screen, so I was wondering if there is a way to control the positioning and the size of the window?
Code:
Set EXP = CreateObject("InternetExplorer.application")
EXP.Visible = True
EXP.Navigate (aurl)
[Code]....
View 2 Replies
View Related
Jan 19, 2007
I can't find a way to re-size the name box (the one in the top left hand corner of a spreadsheet, to the left of the formula bar), or change the type size that this box uses.
I've looked through Excel help, but I can only find help on re-sizing toolbars and not the name box.
View 9 Replies
View Related
Jul 31, 2006
Is there a way (in VBA) to position a specific cell of a worksheet in the upper left-hand corner (i.e. cell H40)?? I have some code that I'd like to have do this.
View 3 Replies
View Related
Jun 27, 2013
My DB is in table format . I use this table as source data for 2 barcharts and 1 pie chart.Following are my table headers
Costs|exp heads|Month1|Month2|...|Month n|Spark lines|Average
When i add a month coloumn,Sparklines and Average coloumn should get updated automaticaly.Now this is not happening even if the data is in table format.I also want the graphs to be automaticaly updated.
View 2 Replies
View Related
Oct 13, 2011
I now have 5 textboxes "on top" of one other (i.e. vertically arranged). I would like to - depending on which checkbox is checked - extend the top one all the way down and make the bottom four invisible. I'm pretty sure I can handle the second part:
Code:
Textbox2.visible=False
For example. How to resize a textbox?
View 7 Replies
View Related
Sep 30, 2012
I have table1 that has its original range as A1:B3. Depending on the amount of n in another column, I want table1 to be resized to A1:Bn.
I have tried with
ActiveSheet.ListObjects("Table1").Resize range("A1", "witdh")
with "width" being a variant.
View 9 Replies
View Related
Oct 26, 2013
I am trying to copy down a range of formulae using the resize function. Here's the code I have :-
Code:
Dim no_of_transactions As Long
no_of_transactions = Last_Row_of_data(main_workbook.Sheets("working").Range("g2"))
main_workbook.Sheets("working").Range("w2:ak2").resize(no_of_transactions,1)
However I am told by the vba editor that I need an = sign ? The function Last_Row_of_data tells me the number of rows that I have.
View 4 Replies
View Related
Apr 18, 2006
I have a worksheet named Advisers with a range also named Advisers, the range is a list of names which is added or deleted from by use of a UserForm.
I have the following lines of -
The first populates the worksheet
and
The second is supposed to resize the range Advisers accordingly but it gives a Run-time error I've tried the 2 examples below
c.Offset(0, 0).Value = Me.Adviser_txt.Value
Worksheets("Advisers").Resize(Range("Advisers").Rows.Count, 1).Name = "Advisers"
c.Offset(0, 0).Value = Me.Adviser_txt.Value
Worksheets(Sheet2).Resize(Range("Advisers").Rows.Count, 1).Name = "Advisers"
View 9 Replies
View Related
Aug 2, 2007
= SUMIFS(E9:E1494,C9:C1494,1) This formula works ok. If I rename range E9:E1494 as "Col_1" and change the formula to =SUMIFS(Col_1,C9:C1494,1). I get #VALUE error.
The range is formatted as number.
Also ...
If I delete some cells in a range, referring formulae are modified accordingly, but if I add cells in the range the formulae do not recognize the change
View 4 Replies
View Related
Apr 8, 2008
Is it possible to re-size a non-contiguous range? I'm guessing "no", and a Google trawl hasn't provided me with anything.
Here's what I'm trying to do:
dim mult_rng as range
dim rng2 as range
set mult_rng = range("A1:B1,D1:E1")
set rng2 = mult_rng.resize(rowsize:=10)
That is, my original range is comprised of non-contiguous cells in the same row. When re-sizing, I'm trying to increase the number of rows, but leave the columns the same.
Like I said, my gut instinct says that this isn't possible using the 'resize' property. Can anyone think of another method to use? My constraints are that the original mult_rng isn't always the same (and isn't always non-contiguous).
View 9 Replies
View Related
Jun 3, 2006
i have a command button on a userform that resizes/inlarges a userform.
i need some code to place the new sized form in the centre of the screen once the resizing code is executed.
View 3 Replies
View Related
Jun 7, 2013
I have created this Excel schedule that is attached. The point of the schedule is to organize Jobs and Crew members. I have VBA code in, so when I calculate how long the job should take, the arrows next to the job will automatically extend. (This should all make sense if you take a look at the attachment.) The only problem is that my arrows do not extend the correct distance. In the attached file I have scheduled "James Lorenz" to do a job starting on tuesday, towards the bottom of the page I did my calculation that figures he should be on that job for 9.5 days. I would like the Arrow next to James' job to extend 9.5 days on the calender instead of 9.5 centimeters or whatever it is doing now.
View 9 Replies
View Related
Feb 13, 2014
Lets say i type for now...
A1 = USA
B1 = Philippines
and then later
A1 = dog
B1 = cat
Is there a way that the width would automatically adjust even as I change the values of the given cells; meaning, if a word is long, the width will automatically adjust "longer", if a word is short, then it will automatically adjust "shorter".
I know how the "AutoFit Column Width" works but I don't want to press that every time my values change (words becoming shorter or longer).
View 4 Replies
View Related
May 17, 2014
I finally have the merged cells resizing the way I need, however it seems to lock the cell after the process not allowing it to be edited again until I unlock the sheet. below is the current code I am using
[Code] .....
View 5 Replies
View Related
Jun 3, 2006
I have a two-parter...
1) Is it possible to write a macro that will resize fonts in a chart (axes, text boxes) to specific sizes?
2) I usually copy chart sheets and paste them into Publisher but I have to resize them because they are huge when pasted. I would like to create a toolbar command that will take the selected chart sheet (not just a chart in a worksheet) and resize it and its components (part 1) to specific dimensions, then copy it as a picture so that I can simply go to Publisher and paste the chart after pressing said button in Excel.
View 9 Replies
View Related
Jun 7, 2006
create a form that will look at the spreadsheet and figure out which of the 32 possible checkboxes will be used and resize accordingly. I can make it so that only the checkboxes I want are visible using the .visible property, but for the sake of aesthetics I'm wondering if there is a way to disable the checkboxes I don't want and to resize the form to fit the checkboxes I do want. I just don't want a form that looks like it has checkboxes put on it at random spots.
View 3 Replies
View Related
Sep 5, 2012
I am trying to put some charts into a report that is pivot table based. I have some code that will work if the pivot tables stay stagnic but the users may change the tables around so that could be an issue of new data. I have the following code where I changed the source to the pivot table name (general name because of numberous report possibilites). I have taken out the other charts because they are just a variation of the chart 1. I am crashing on the line with the * on it.
Sub UWTierChart()
Dim oCell As Range
Dim oChart As Chart
Set ws_data = ActiveSheet
'Chart 1
Sheets(wsPT).Select
Range("B21").Select
[code]....
View 2 Replies
View Related
Jul 30, 2007
how can I add an legend entry that has been removed by error?
View 3 Replies
View Related