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?

View 3 Replies


ADVERTISEMENT

Event OnClick - Show Smart Art Shapes Below (child)

Oct 10, 2012

I'm trying to make a menu in my Workbook to access all the others sheets.

To make things easier for my boss, I'm trying to make the menu with SmartArt Shapes (Hierarchic) because it's easier to add/remove itens.

The thing is: he wants it to show the boxes below (hierarchic child) when i click the "mother" box.

It will work pretty like web menus except the mouseover. Image below

menu.jpg

View 1 Replies View Related

Automatic Report Generation From Excel Worksheet

Apr 24, 2014

want to generate automatic report from excel work sheet

View 1 Replies View Related

Graphs / Charts Generation Automatically In Excel?

Sep 5, 2012

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.

View 9 Replies View Related

Excel 2007 :: Realtime GPS Coordinates From Built In Receiver

Sep 19, 2011

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

im using excel 2007, and window xp pro.

View 9 Replies View Related

Excel 2010 :: Calculating Coordinates For A Point On Multi-Segment 3D Line

Sep 19, 2013

Windows 8, Excel 2010

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.

View 1 Replies View Related

Creating Shapes (Rectangles And Shapes) From Input Cells?

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

Excel VBA To Save PPT Shapes As PNG Images

May 29, 2014

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.

PC with latest of all software.

View 1 Replies View Related

Number Keyboard In Excel Shapes?

Jun 7, 2014

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".

[Code]....

View 2 Replies View Related

Excel Shapes And Mouse Rollover

Jul 25, 2014

Is it possible to create a piece of VBA that displays a comment box when a user rolls over a specific shape in Excel?

View 1 Replies View Related

Excel Macros To Zoom In And Out Shapes

Aug 10, 2014

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."

View 5 Replies View Related

Rotating Group Of Shapes In Excel VBA

Feb 4, 2014

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.

View 6 Replies View Related

VBA Code - Creating And Moving Shapes In Excel

Mar 9, 2014

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.

Customize Heatmap creation.xlsm

View 1 Replies View Related

Excel 2007 :: Add Shapes In Range Of Cells To ShapeRange

Feb 3, 2012

Using Excel 2007.

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:

[Code]....

View 2 Replies View Related

Excel 2007 :: Change Text In A Group Of Shapes?

Jun 22, 2012

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

excel 2007 btw

View 3 Replies View Related

Excel 2007 :: Default Settings For Drawing Shapes?

Jun 14, 2013

how to change the default settings for shapes in Excel 2007? I select a line tool and it is a blue color and I would like to have its default as black

View 6 Replies View Related

Merge Data (smart) From One Sheet To Another?

Mar 18, 2014

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?

View 2 Replies View Related

Creating Smart Dropdown List

Apr 6, 2011

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?

View 14 Replies View Related

Smart Range Selection In Macro

May 22, 2013

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:

Code:

ActiveSheet.Range("C14:E15").Select
Selection.Copy
ActiveSheet.Range("C14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

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

View 3 Replies View Related

Finding Smart Coding Userform Textbox Values To Sheet

Mar 21, 2012

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.

View 5 Replies View Related

Smart Filter To Only Show Contract Items That Had Major Impact On Gross Profit

Mar 10, 2014

seeattached data set.

Contract Item
Gross Profit
GP %

[Code]....

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?

View 3 Replies View Related

Unique No Generation

Apr 5, 2007

my requirement is attached here with.
in generating the unique no?

View 14 Replies View Related

Number Generation

Apr 26, 2007

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

for instance, JJH00001, JJH00002, etc

View 9 Replies View Related

Distance Between Coordinates?

Dec 8, 2013

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.

View 11 Replies View Related

Numbering For Coordinates

Oct 24, 2007

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)

View 10 Replies View Related

XYZ Coordinates Graph 3D

Aug 22, 2007

I have the following data:

X Axis - Years (2004, 2005 ,...)
Y Axis - Quantity (imported, exported, produaced)
Z- Axiz - Name of the products

I want to make a graph with teh X, y,z axis in excel. When i use the 3D graphs in excel, it is not taking the z vales connected to teh cell.

View 9 Replies View Related

Automated Ref Number Generation

Oct 7, 2009

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

View 14 Replies View Related

Automate Report Generation

Jun 17, 2009

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.

View 4 Replies View Related

Automatic Sheet Generation

Nov 3, 2008

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

View 13 Replies View Related

MC W/Random Number Generation

May 4, 2006

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.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved