Sizing And Positioning Of Shapes
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
ADVERTISEMENT
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
Aug 30, 2013
Im trying to create shapes Rectangles and Squares with different colours within an excel sheet, where the length and height of the shapes is generated by input values.
Also is it possible to create 3D shapes, again where the size of the shap is generated by input cell values.
View 1 Replies
View Related
Feb 9, 2010
I have the this code in ThisWorkbook to open the workbook with two windows, each with a different tab showing, and each taking up half the screen:
View 3 Replies
View Related
Jul 10, 2009
I've written a macro to add buttons to a sheet of mine. The buttons call up various other macros. The problem I'm having seems to be a screen update. I've set the button size to be greater than the number of characters it contains. However when I run the macro, it first adds the button at its default size (width which is too small), and bombs out.
Below is the code I'm using:
Dim Width As Integer, Height As Integer, HzSpace As Integer, VtPos as integer
'Dimensions
Width = 144
Height = 20
VtPos = 400
HzPos = 275
View 9 Replies
View Related
May 27, 2006
Once again this is a general best practice question.
What does the forum believe is the best practice sizing for command buttons and which font and size is best.
I am trying to give a solution I am working on astandard Microsoft application feel to it.
View 3 Replies
View Related
Jan 28, 2014
macro to automatically size an XY grid based on user input (see picture - [URL]......)
I want to enter a value in B6 that will automatically create a new grid or edit the existing grid to increase or decrease it depending on the desired grid size.Once the grid has been sized I want to enter a data table into it, therefore the grid would need to be cleared of contents before it could be resized.I think these are the steps that are needed:
- Enter grid size.
- Area (named range based on existing grid values?) is cleared of the data table.
- new rows / columns are inserted with correct XY coordinates shown based on newly entered grid size
- data table written into the new grid
View 5 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
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
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
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
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
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
May 27, 2014
I have the following code:
[Code] ....
When I run the macro, some columns are already hidden. The macro doesn't seem to autosize cells correctly. For instance, one cell in a row appears to have some contents hidden (or below the reading area of the cell). In other instances, the rows are auto-size to huge heights and widths.
View 7 Replies
View Related
Dec 3, 2008
I have a macro that opens Word pastes data in from Excel,
I want to be able to paste an arrowed line that I have drawn, also if it changes, I want it to reflect that change.
Here's the
Item("Direction1").Range.InsertAfter Worksheets("BSID").Range("Line3").Value
I have my bookmark in Word setup, I'm guessing that instead of a cell ref I need the name of the Line, which is "Line 3" in this case.
I ran a recorded macro and copied and pasted the line and instead of "Range" I got "Shapes" so I assumed that I just needed to change these words but no joy.
View 9 Replies
View Related
Mar 24, 2009
Where do I find a tutorial or examples to show me how to create bars for gantt chart in excel, based on date values?
View 14 Replies
View Related
Nov 17, 2009
i have an excel file for using my manufacture planning. i dont know why but there was created too many TextBoxes(but its shapes)
I'm giving my excel file , you can see what i'm talking about at G column and 193. field
if you can solve this problem, i will be pleasure to you
View 10 Replies
View Related
Sep 18, 2012
I have some shapes on a worksheet that need deleting on change of a combo box. Unfortunately I'm unable to use the below code as not all the shapes need deleting, just the ones that are named in a table on another worksheet.
For Each s In ActiveSheet.Shapes
s.Delete
Next s
Also not all the shapes named in the table will be on the worksheet as this depends on what is selected from the combo box.
View 2 Replies
View Related
Aug 30, 2009
I am a not too good programmer, but I construct chemotherapy regimens for my medical department. They are used to minimise the risk of wrong dosing to patients, etc. Every regimen is an .xlt file, and about a third of them (there are about 100) generate, in addition to pharmaceutical prescription, automatic letters to patients (for those regimens where patients administer oral dosing at home).
These patient-letters have the hospital's logo on them (the logo is a "shape").
However, the problem is that another hospital also wants to use my system. So I will have to replace all these logos with the other hospital's. I would like to do this in an automated way. I have received the other hospital's logo also as a shape.
I have already constructed a data-inserting macro in a separate file. This macro systematically opens each xlt file and replaces the text in key locations according to the file the macro is in (geographical names, etc.).
Is it possible to automatically replace the logo-shapes in a similar way, with these additional facts/wishes in mind:
1. There is always only one type of shape (a logo) in the xlt files.
2. Even though the shapes are exactly the same, it is possible the names are different.
3. The exact location for the shape is different in each xlt file, so I'd like the replacement to get the same top left coordinate as the one it replaces.
I have constructed 2 different files with the same data-inserting macro, each containing one of the logos (so I can change back to my own hospital) and geographic data. When the data-inserting macro is used, I'd like it to identify the shape in the data-inserting file (there's only one shape in this file), copy it, then identify the shape(s) in the present xlt file, and then replace these, in the exact same location.
View 9 Replies
View Related
Jul 10, 2006
I have a shape that will have no macro. How do I prevent this shape
from being selected and prevent the message that no macro can be found,
when it is clicked?
With shapes that do have macros how do I disable them ?
How do I keep them visible but not have the mouse cursor change on mouse
over and not have the macro run on a mouse click.
I tried to use Enabled = false but apparently that property cannot be used
for a shape.
I need to toggle between enabled and disabled.
View 9 Replies
View Related
Jan 5, 2008
I have some drop down control boxes in rows that I want to be able to delete using a macro. If highlight the rown and use Edit, Delete, the data is gone but the controls are still there. How can I delete this using a macro?
View 3 Replies
View Related
Jun 16, 2013
I want to draw circles from 3 points and other geometrical shapes. The points will be given from clicks on the background picture.
View 1 Replies
View Related
Apr 23, 2008
I'm trying to calculate the area of a Freeform shape.
Is this already stored somewhere e.g a shape property?
If not is there a macro available to do this?
View 12 Replies
View Related
Feb 9, 2010
This code finds and matches the Text value of a Shape to the value of a cell on seperate worksheets within the same workbook.
View 9 Replies
View Related
Feb 23, 2007
I need code to do this: When I select a range of cells, say E20:H24 then hit a button the code behind the button would ungroup all the shapes in that area. So if I have grouped shapes elsewhere, they would be uneffected.
View 9 Replies
View Related
Mar 5, 2007
is there a way to select the group of the shape instead of a single shape i've tried .onAction and Application.Caller to do this ..but this is giving me the single shape that i clicked on instead of the whole group.'
View 9 Replies
View Related
