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.
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?
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.
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.
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.
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.
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
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?
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.
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.
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)
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)
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.
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.
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.
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?
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.
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................................
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?
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?
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.
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.
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.
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.
