Smart Generation Of Coordinates For Excel Shapes VBA
Sep 5, 2013
I'm using Excel to create a flowchart from a configuration sheet. I'm not manually entering the coordinates. Instead, I draw the start step in a set location. The next step(s) that's connected to the first step is placed directly below the start step. if there's more than one step connected to the start step, it's placed at the same y-coordinate but shifted along the x-axis.
The problem I'm running into here is this: say I have three steps connected to the start step and three further steps connected to the first of the three connected to the start step, how can I keep track of this and shift the other two steps connected to the start step along the x-axis so they're not positioned above the four steps below the first step (and as such, in the same location as other steps)? I'm not sure if my description is particularly clear or not so I've attached a drawing that will hopefully clear things up.
Is there any way of giving Excel shapes a "weight" so that other shapes won't line up on top of them?
I want to prepare a graph / chart for the column A and column B. Column A has headers and B has the data that I want to represent in the graphs. The real problem in this is that the two columns length is not fixed. For eg. for January Column A & B could have 5 rows of data but for February they could have 10 rows of data. I dont want to prepare the chart manually every month. I want that if we could apply any VBA coomands that could prepare the chart automatically irrespective of the rows count.
i have a built in gps receiver into my laptop. what i am looking for is to simply click a button which will give me the x and y, with current speed if possible. and input to cell a1, b1, c1
I have XYZ Coordinates for a continuous 3-D line that has numerous segments. I want to input a distance along that line, and have it create the XYZ coordinates at that point. See Image for reference.
Row 2 is my start point - I input the initial coordinates here - this point is the origin of the 3-D line Column B is where I want the calculated Y value to go for each point Column C is where I want the calculated X value to go Column D is where I want the calculated Z Value to go Column E is the how far along the 3-D line that the (to be calculated) point should be at. (MD1) Columns G, H, & I are given to me, and I use this data to generate the coordinates in Columns J, K, & L Column O is the cumulative length of the line at that coordinate. (MD2)
Basically, I had planned on writing a formula to:
Find the coordinates of the point who's MD2 (column O) is before the desired point's MD1 (column E)Find the coordinates of the point who's MD2 (column O) is after the desired point's MD1 (column E)Subtract MD's (column O) to get the length of the segmentFind the distance along that segment that MD1 (column E) fallsUse that distance to traverse along that line to the desired point.
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.
I have an Excel (.xlsm) that opens an existing PPT and uses VBA to modify several slides.
There are several shapes on each slide and my .xlsm has VBA that modifies those shapes.
(The slides have designs on them for different size business cards which get filled in with spreadsheet names,...)
Now I want to "select all" the shapes on a slide as a "group"(not sure that term is correct). Then I want to export that group as a .PNG image file. Save location can be the same folder location of the .xlsm file (doesn't really matter).
This is different than exporting each slide as a .PNG.(which I can already do). In this case the business card designs on each slide are different sizes. So I need the images to be just the size of the "group" on that slide.
The VBA code is within the .xlsm file (not the PPT)
Several posts show VBA code for within the .pptm file that works fine if using just a PPT
However that code doesn't seem to work when I put it in the .xlsm file to drive the image creation from Excel.
I would like to use Shapes in excel, instead using userform as the code below.In this userform, it has command buttons for the numbers and the a text box to show the numbers.How can I do it, using square shapes for numbers and another rectangle shape, to show the written numbers.I would like to create a number keyboard using "shapes".
I am struggling to get a macro code for zooming in and out the shapes. I have a macro which fulfill half of my requirement. By assigning the below macro to the shape I am able to increase the current size to double by clicking on the shape however I need the same macro should retain the original size upon second click.
Here is my code to increase the size.
[Code] .....
"The requirement: Once after assigning the macro, on first click it should double the shape size and on second click it should retain the original size."
I did use the solution in a thread by Andy to make a picture or shape rotate. It works perfectly as a standalone. With this I mean all the information on the same sheet as per example in the file 901259.
I'am using it to rotate a dial. The problem is instead of using the value from the sheet itself I link the scrollbar to a cell on a different sheet in the same workbook. There some calculations are done and the value which makes the dial rotate is linked to that sheet. The dial however is on another sheet.
The calculations are almost displayed realtime but the dial is lagging. How would I go about to "solve" this lagging.
1.) Create shapes (customize oval shape), I was able to create one but I think the code needs improvement to have the shape a fixed name. - see attached excel file.
2.) Move shapes into corresponding cell. Example: (Oval shape1, should be in cell g6). - see attached excel file.
I have shapes in 3 different rnages of cells on a cell worksheet: R1 = $D$5:$D & lngRows (row=32) R2 = $G$5:$G & lngRows (row = 51) R3 = $M$5:$M & lngRows (row = 50)
I need to set the properties of the shapes in each range differently The shapes in R1 are Left + 46 But the shapes in the other 2 ranges need to be just left My problem is in this bit
Code:
'Set properties for each shape in ShapeRange For Each sh In ws.Shapes
I need to be able to set properties for each range separately instead of the entire sheet. Full code below (only Range $D at the moment - works)
Code:
Option Explicit Sub AddShape3() 'Purpose: Add small rectangles to database table/fields for ' brainstorming and documenting relationships and queries ' 'Resources:
Ii have a UserForm with a ComboBox in it. the selections in the box are
0 5 10 15 20 25 30
I have 7 shapes in Worksheets("Interface") and renamed each shape caution1 through caution7
What i need is when i click on continue in my userform each shape will change according to the selection.
if 5 is selected all caution shapes will read, Caution flag is out 5 minute break if 10 is selected all caution shapes will read, Caution flag is out 10 minute break if 15 is selected all caution shapes will read, Caution flag is out 15 minute break and so on
So I have two worksheets. One sheet has all of the names of attendees to a current event (column a, about 109 entries), and the other sheet has all of the names that have ever registered for my events along with their emails (column a and column b, 3000 entries). I need to merge data from column B of the second sheet to the corresponding name in the first worksheet, and disregard the extras (or just put them at the end so i can clear the contents). So I am just trying to get the email addresses of the people in the first sheet, from the data in the second sheet.
I've tried to look at the consolidate data function, but that does not match up the names to the email addresses correctly. What about a filter?
I have a drop down list in excel. What do I need to do so that it will automatically take me to the closest/best label on the list when I type just a few first letters?
Im having some problems with range selection in macros. Basically, what the macro does is Copy / Paste as Values in differet sectors of an active worksheet, so this is the code for each range of cells that I need to copy paste:
The problem with this code, is that because it specifies EXACTLY which range to select, if I add a row before that range the macro will be now selecting an incorrect range.
For example, say I have a value on cell A5, and the macro is set up to copy/paste that cell. Later on, I decide to add a row in A3, so the value I would need to copy/paste will now be in A6, but the macro will still execute on A5 (incorrect, as it should now execute in A6, and that is what I have to manually correct each time).
In my userform I have a lot of textboxes. When the user clicks "OK" the values of these textboxes are transfered to row 3 of sheet1.
For this I use some of the code below:
Code: If NaamTextBox.Value = "" Then ActiveCell.FormulaR1C1 = " " Else: ActiveCell.FormulaR1C1 = NaamTextBox.Value ActiveCell.Offset(0, 2).Range("A1").Select If GroepComboBox.Value = "" Then ActiveCell.FormulaR1C1 = "" Else: ActiveCell.FormulaR1C1 = GroepComboBox.Value ActiveCell.Offset(0, 1).Range("A1").Select If TypeTextBox.Value = "" Then ActiveCell.FormulaR1C1 = " " Else: ActiveCell.FormulaR1C1 = TypeTextBox.Value ActiveCell.Offset(0, 3).Range("A1").Select If TypecodeTextBox.Value = "" Then ActiveCell.FormulaR1C1 = " " Else: ActiveCell.FormulaR1C1 = TypecodeTextBox.Value
This goes on for about 55 columns. Also, not every column is used as you can see in de code above.
It seems to me that this is not smart coding, so my question is: can you make this more efficient?
I did find some nice code with uses EmptyRow (find first empty row) but in my case it's always row 3 where de input is placed. After this row follows much more data.
It is Gross Profit in Dollars and % for a particular project. Each row is a contract Item. The Overall Gross Profit for the month in dollars is 34,114.86 In preparing a short commentary to discuss this 34,114.86 I need to discuss atleast some contract items. If there were no negative results I have often in the past simply written. The top 5contributors to this Gross Profit were contract items 1 -6, out of 42, delivering 80% of the result. I might also go into what actually those contract items were and why they presented such a decent Gross Margin, ie. Costs came in well under budget(estimate). But with negative results, the bottom 4 contract items represent a large dollar loss of-34k which is 100% basically of the Gross Profit itself. If these had even just broken even we would have a GP double the current amount. So they obviously deserve as much comment.
Q1 In excel orsimilarly in say Crystal Reports what is a good way of further sorting these 42records either in a table or chart, whereby say the top 80% contract items that had a major impact on the gross profit are shown and the rest hidden. Ie. All thelittle sub 1000 or sub -1000 amounts.
Q2 How wouldother people here write a commentary on this data set? Can you script something to illustrate?
I have multiple users that use a spreadsheet to enter data. I am trying to figure a way to assign a case number to each row of data that is entered. I cannot really do sequential numbers, because often people are entering data at the same time, and often these people are working offline.
So I was thinking if I could somehow have the case number be a few letters (to identify a specific person) and then a series of numbers (to signify which case number it is). That would allow multiple users to enter data offline simultaneously without having to worry that the case numbers are going to be the same. Maybe I could use the Application.UserName and somehow parse it to give me the letters (perhaps Intials or something), and then somehow apply sequential numbers to that...
I have created a sheet that shows distance between coordinates. Also working on something to put them in order of the closest point. I used a fixed point to find the nearest coordinate, but since i'm using a fixed point it doesnt go the order I want it to. I have attached the excel file I am working on.
how to get a single cell (C2) and (D2) to make the numbering format go from (## ## ##) to (######).
The Excel spread sheet is a coordinate converter, designed to take Degree's minuets seconds and convert it to Decimal Degrees, the formula is set up and work Great, but every time I copy and paste the coordinate to the excel spread sheet, i have to manuelly erase the spaces between the numbers so the formula can work properly. How can i get the cell to automatically delete the space between the numbers to save me time.(I.e 29 35 42.34325 -to-> 293542.34325)
I m trying to achieve is generating ref numbers automatically.
I have attached an example.
In Cell A if the user choose yes it gives a ref number. The trouble im having is that say in cell A5 a Yes is choosen at a later date, it will throw the numbers out of order and the ref numbers get mixed up, there any way of stopping this from happening or any function that prevents this from happening
I have multiple files that I need to get data from. My dream is to push a button and gather the data from variable files. (I know how to make the button and get data from single file).
The file name is below:
Cell A1 contains this formula ='C:ReportsYear 2009[CityWaterReports Jan 2009.xls]PHFlows'!$B$44
Cell A2 contains this formula ='C:ReportsYear 2009[CityWaterReports Feb 2009.xls]PHFlows'!$B$44 and so on.
I would like the customer to be able to push the button and be asked to enter the year(eg: 2008) and have the formula update to the new year in each cell.
I have only two sheets..first sheet is the user sheet where the user gives the data...second is the sheet where in we get the result according to user given data..
there is a cell in User sheet where User gives a Number. for example " 8 "
so sheet2 : here we have only 9 rows. as user gave "8" the fisrt 8 cells in the first column should see like this
B 01/08 , B02/08 , B03/08 as so on till B08/08
when user gives 9 in user sheet
then the first sheet is finished with all the 9 rows ending with B09/08
when user gives 10...then sheet 3 should automatically appear with the first row and first colum saying B10/08.
when user gives 20 in user sheet...then sheet4 should appear with last number as B20/08
so each sheet is having only 9 rows..
first sheet ends with B09/08 second sheet ends with B18/08 third sheet ends with B27/08
I get the following error whenever I try running a Monte Carlo simulation with iterations in excess of 64000 (The MC simulation calls the 'NormSInv' worksheet function at least once per iteration, and the argument to the 'NormSInv' function is always a standard normal RANDOM number also generated by Excel VBA): Run-time error '1004': Unable to get the NormSInv property of the WorksheetFunction class Why do I inevitably encounter this error ONLY when the number of iterations exceeds 64000, for instance? Simulations with iterations < 64000 run smoothly without a glitch.
