Positioning Shape Objects In VBA
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
ADVERTISEMENT
Dec 27, 2013
So I'm creating a grid worksheet for engineering calculations and I have a couple questions about the best way to do it. I've been messing with excel for my calcs for about a decade now, and I every once in a while I try and improve them.
First: I will have several input areas that will either be colored text or shaded background (either works for me). I don't want these 'input required' objects to print as color, just black. But I want my logo at the sheet top to print as color. I've only found ways to not print any color. Can I print the logo as color and the 'input required' stuff as black?
Second: When I do calcs by hand, I write them out on 10x10 grid paper. Each 10x10 grid is one inch. In the past I've created this grid out of the cells, which works. I frequently need to change formulas around though, and each time I do this, I end up needing to mess with the grid cells also. Is there a way to create the grid and have it in the background so it doesn't need to be adjusted each time I change formulas? I wan't the grids to print, and also want to see them on the screen, as I sometimes draw simple objects along with the formulas.
View 9 Replies
View Related
Mar 4, 2009
when i run the below code i get an error 438 'object doesnt support this property or method'
View 2 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
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
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
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
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
Jan 24, 2014
On the attached spreadsheet there is two irregular shapes. "Area1" & "Area2". I need to be able to determine which area the "ball" shape is located in. If the ball is in Area1 then "Multiply 8" gets the ball and "Multiply 9" moves to the blue cell "AH39". If the ball is in "AreaB" Multiply 9 gets the ball and "Multiply 8" runs to cell "S37"
I should be able to do the moving of the shapes using all the samples, its determining which area the ball is in is the problem.
In the real spreadsheet i will probably have around 10 different Areas.
Move Two Objects - Select Case.xlsm
View 5 Replies
View Related
Jun 23, 2009
I have D4=0.42. I thought this formula =IF(D4<0.38,"Thin",IF(0.38<D4<=0.48,"Good shape","Bad shape")) was supposed to give me "Thin" for D4<0.38, "Good shape" for 0.38<D4<=0.48 and "Bad shape" for the contrary, but it only gives me "Bad shape" when D4 is clearly between 0.38 and 0.48.
View 4 Replies
View Related
Jul 15, 2008
I want to use VBA to update a drawing object's color, such as a circle.
For example, pretend I am counting sheep and I need to know when I have reached the maximum count by the visual aide of a stoplight. The script in my head reads: If the max. count of the sheep is less than 5, then the stoplight is green. If the max. count of the sheep is equal to 5, then the stoplight is yellow. If the max. count of the sheep is greater than 5, then the stoplight is red.
Is there a way to do such a thing on excel and with VBA?
View 9 Replies
View Related
Jun 29, 2006
I created four Ranges called "Range1", "Range2", "Range3" and "Range4", and wrote short program, that fills them:
Dim i As Integer
For i = 1 To 4
Range("Range" & i).value = 1
Next i
Is it possible to do similiar thing with for example ComboBox?
Let's say that i have comboBoxes named "cb1", "cb2", "cb3" and "cb4" and want to disable them.
View 9 Replies
View Related
Jan 26, 2007
I ran into some difficulties by calling an object's function from a collection.
'BondIndex Class Module
Option Explicit
Private m_BondReturn As Collection
Public Sub Initialize()
Set m_BondReturn = New Collection
End Sub
Public Sub Load(BondPoints As Integer)
Dim i As Integer
Initialize
'Load Parameters of Bond
End Sub
Property Get BondReturn() As Collection
Set BondReturn = m_BondReturn
End Property................................
View 2 Replies
View Related
Mar 22, 2007
If I hadn't read it on here, how would I have know that they existed? They are not mentioned in the standard Excel help files (not that I could see anyway).
What other "objects" are available? I had a look through the references in the VBE and there are literally hundreds on items which all look pretty "juicy". Does that mean there are a whole host of other objects that I could use in my Excel spreadsheets to either make them simplier or more powerful?
If so, where does one find any reference info on them?
View 3 Replies
View Related
Dec 21, 2007
Why should I set objects to nothing (ie clear them) just before ending a sub routine? Doesn't excel dump the variables after the sub is finished running?
Example:
Dim ws As worksheet
Then at the end...
Set ws = Nothing
View 3 Replies
View Related
Dec 31, 2009
In the attached sample workbook, (Excel 2007 format), there are hundreds, maybe thousands, of drawing objects and rectangles. They are mostly located near the top of column AC. You can't see any of them, because they are empty, or formatted to have no line and no fill, ubt if you move the cursor around in that area, it will eventually give you the option to "select" one of the objects. They appear to be stacked on top of each other.
This bloats the worksheet terribly. I have managed to shave the size of the attached sample down to 100K, bu deleting about 200 of the objects, but if I save this balnk file as an Excel 2003 file, it is 1.3 Mb.
I have discovered how to show what objects are on the worksheet, by selecting "Find and Select" and choosing to show the "Selection Pane". This pane shows hundreds of blank objects in the sheet. When this pane is open, however, if I try to select and delete an object, Excel locks up on me.
View 5 Replies
View Related
Feb 5, 2013
I'm not sure why they decided to export data this way but...I have a report that has pictures/objects that need to be in a cell so I can manipulate the data. I'm not really sure how to do this- and the only thread that related to this was over my head. This is not the original export- I've already done some sorting so the objects are all over the place- but basically if the object is C (picture 23) then I want that picture to say C in a cell somewhere. or if the picture is the dollar sign (picture 27) then I want a cell to say $. I don't know if I'm even explaining this right. Here is my workbook.
View 6 Replies
View Related
Jan 23, 2014
Is it possible to change colour of an object (Fill) say fill circle Red when A1="1" and Green when A1="0"
View 12 Replies
View Related
Jan 24, 2008
I manually Copied and Pasted contains in TXT File.
The TXT file is as below.
It has only 1 Column
7AB/9AB
8AB/10AB
7CA/10CA
7BB/10BB
7CB/10CB
7AA/9AA
What I want is to run some VB Script so that anything after / is ignored
and the Output shold be reformatted as below
7A1
8A1
7C0
7B1
7C1
7A0
The Above O/p is anything aftyer / is ignore and the last character of the 1st field is A is replace with 0 and B is replace with 1.
View 13 Replies
View Related
Dec 29, 2008
Hi all, starting this as a new problem because it's so far different from what I was originally talking about; but this does relate in part to my previous thread.
Anyway. I'm trying to set an array to set Range objects so that I can define each one as a seperate With block. Here's what I "know" when starting out.
I developed the following. I keep thinking it should be multidimensional but my sleep deprived brain came up with this instead. The problem is, it's throwing a 1004 Method "Range of object '_Worksheet'" failed at the With statement.
View 6 Replies
View Related
Jan 10, 2012
I have a Master sheet linked with another worksheets with formula.
Now I want to insert 1 column in between , and when trying to insert the column in it a message sowing that " Cannot Shift Objects of Sheet"
Why this error shwoing and how can i insert any column in it.
View 7 Replies
View Related
Apr 23, 2007
Here is the Sheet so far: ...
View 9 Replies
View Related
Jul 24, 2007
I have a range of cells I want to copy which has some textboxes and check boxes in it. When I do a copy/insert cells in vba it only copies the cells and their values but not any of the boxes.
.Range(.Cells(Origin.Row + 1, Origin.Column), .Cells(Origin.Row + 12, Origin.Column + 10)).EntireRow.Copy
.Range(.Cells(Ind.Row, 3), .Cells(Ind.Row, 3)).EntireRow.Insert Shift:=xlDown
View 9 Replies
View Related
Aug 9, 2007
If cell.value = {userArray} and cell.offset(0, 2).value = {taskArray}
Then cell.offset(0, 2).interior.colorindex = 3
I have three different {userArray}s and {taskArray}s and there will essentially be different code for each of the arrays and that's fine....
View 9 Replies
View Related
