Can Draw Shapes Or Angles Based On Data
Can Excel Draw Shapes or Angles based on Data?
I have a radiation Excel file that solves for Radiation intensity using Distance and shielding as reducing factors.
Similar to a circle of light being less intense with distance so is radiation.
I need excel to draw a circle from a point specified based on a value I give.
For example at 1 ft I might have 500 R in 1 hour (life threatening!)
At 16 feet I have about 2 R in 1 hour (not safe for too long)
These circles would be overlayed on an map.
I would like to draw a new circle at various distances, simialar to a bullseye.
I would also like a triangle drawn based on angles and SIN / COS / TAN formulas
Could this be done in VB or preferably without.
View Complete Thread with Replies
Related Forum Messages:
Shapes Or Angles Based On Cell Data
Can Excel Draw Shapes or Angles based on Data? I have a radiation Excel file that solves for Radiation intensity using Distance and shielding as reducing factors. Similar to a circle of light being less intense with distance so is radiation. I need excel to draw a circle from a point specified based on a value I give. For example at 1 ft I might have 500 R in 1 hour (life threatening!) At 16 feet I have about 2 R in 1 hour (not safe for too long). These circles would be overlayed on an map. I would like to draw a new circle at various distances, simialar to a bullseye.
I would also like a triangle drawn based on angles and SIN / COS / TAN formulas
Cosine Of Angles Based On Corresponding Cell Value
Iv got Column A with angles between 0 and 1.56 radians. These angles are generated randomly, so it can be 0.22, 0.56,1.55 etc etc...Now in column B Iv got to carry out a function. The function is =43.22/COS(AX) X depends on the corresponding value in column A.
Every thing is fine up to here and I can do it...BUT I only need to carry out the function if the angle in Column A is smaller than 1...And if the value is bigger than 1 it only needs to output a value of 80. Thats it so...depending on the value in column A it will either carry out =43.22/COS(A) OR just make it show =80.
Average Of Angles (headings)
I have data sent to me describing angles as integers from -180 to 180. I need to average these angles together. For example, the average of 10 and -2 should be 4. Great. However, the average of 175 and -175 should be 180.
Has anyone ever worked with this type of issue?
(I've thought about converting -175 to 185, but then I have trouble with 5 and -5, which becomes 355).
I should probably note that I have a list of angles, not just 2. The analysis I need to do is 1) average of all angles and 2) difference from the average for each angle.
Deleting All Shapes Deletes Data Validation Drop Downs
I am trying to do here is select a value from a drop down which is linked to a formula which triggers the macro shape..i have all that down already... but the problem is that I will have multiple values in the same drop down and I needed to figure a way to delete the previous macro shape in that range. And so i have created the delete all shapes code below but it seems whenever I use the code it deletes the data validation drop downs
here is what I have so far...
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 220.5, 105.75, 92.25, 51#). _
I was looking for some code to randomly generate a number for a prize draw I am doing. I found a great piece on this site, which I have slightly changed to give me what I have below.
It works great except for one thing I cannot work out, I have the code below working on cell "C1", I would like it to work in cell "C5", but I can't seem to work this out.
Dim w As Long, x As String, y As Long
x = Format(Int((Range("B2") - Range("B1") + 1) * Rnd + Range("B1")), "0000")
For y = 1 To 1
For w = 1 To 250
Range("C" & y) = Int(8000 * Rnd)
Range("C" & y) = CInt(Mid(x, y, 4))
Also, whilst here. i will have one other problem, with it being a prize draw I do not want to draw the same number twice. At the moment I have a small macro that copies the winning name that appears on the main screen (via a vlookup) onto a winners spreadsheet. I am thinking of then creating another macro that replaces the winning name on the master list with an "Already Won" entry, so we can just select again.
Combinations - Win Or Draw
I'm creating a spreadsheet regarding football teams and want to lis thte combinations of results for each team.
Specifically, if the team will win or draw.
So, I have 6 teams, each team can either win or draw.
I want to display the combinations of team 1 winning AND team 2 winning AND team 3 winning etc.
How To Draw A Line In Userform
I have a 5-column listbox in a userform. I do not know how to make the column borders visible. If somebody can tell me, that would be great.
If not, then the next alternative would be to manually draw a line. I tried to so one, by creating a Label with a width of 1, and backcolor ot black. This works but not in front of a list box. For some reasons it cannot be brought to front/forward (order) of a listbox, I already right-clicked several times and sent tthe order to front.
Is it possible to bring it in front of a listbox?
League Cup Draw By Using VBA
i want to make a cup draw, but i don't want to use RAND, I want to use VBA.
What i want is to make a English Football League Cup, so all 92 leagues teams are in it, but as follows
First Round 72 teams
Second Round 50 Teams
Third Round 32 Teams
Fourth Round 16 Teams
Quarter-Finals 8 Teams
Semi-Final 4 Teams
Final 2 Teams
for a team list see attached excel spreadsheet
If you are not sure what i mean google search English League Cup or try soccerway
I want a command box or similar for each round, i dont want it to change unless i click on the command button again.
i will be inputing the scores myself.
Sorry i forget one team in the First round "Darlington"
Macro To Draw Colored Circles
I've recently come across this wonderful site and have found it to be a pool for great information. I looking for some assistance with finding a macro that will draw circles/dots on a worksheet when the left mouse button is clicked at the mouse cursor location. There may be something out there already??? Here's exactly what I need; I want a macro that will draw 7 different coloured circles (blue, pink, cyan, lime green, red, yellow, and orange) on a worksheet. The macro would be activated from a button (7 different buttons- one for each colour) on the worksheet and when the left mouse button is clicked the coloured circle is drawn at the mouse cursor location.
Randomize List For Competition Draw
I'm running a Secret Santa DJ competition this year and today now wish to make the draw.
Basically all entrants send in their mixes (in this case - upload to a webserver) and I then wish to try and use Excel to make the draw.
What I'd like to do is have a list of all the entrants in column a, use RAND() in column b to assign them each a number, and in column c use the list of entrants in column a to assign each person a mix to receive.
Through my browsing and searching on here, I came across the following (entered as an array); it's brilliant but unfortunately gives me duplications.
Calculate Draw Down. Futures Trading
way to calculate the drawdown for a futures trading account’s equity. The following is the record of taking a sequence of 20 trades. Drawdown is defined as the lowest point between two consecutive equity highs. Therefore a formula should detect pairs of equity highs and then find the maximum difference between these highs and the lowest point in between each of these pairs. Do you think this can be done using standard excel functions?
You might wish to plot a linear graph to visualize the equity. Looking forward to hear what you think! I include more examples of equity records.
formal definition of a drawdown:
Drawdown: The magnitude of a decline in account value, either in percentage or dollar terms, as measured from peak to subsequent trough. For example, if a trader's account increased in value from $10,000 to $20,000, then dropped to $15,000, then increased again to $25,000, that trader would have had a maximum drawdown of $5,000 (incurred when the account declined from $20,000 to $15,000) even though that trader's account was never in a loss position from inception
Rankings Draw Sheet For A Tournament
I have a worksheet set up as a draw sheet for a tournament. I have a set of rankings showing all points collated from last tournament. What i want to do is set up a formula/macro so that a cell containing a player eg. the winner will be found on the ranking list and the points won will be added to the next cell on the ranking list.
Draw Parallel Line Through Scatter Plot
Currently we have data that is plotted to a scatter plot along with an already defined line. Next, with a straight edge we attempt to draw a line parallel to the already defined line through the scatter points of data. We basically find two points that give us the best fit to the original line. What I attempted to do was calcualte the slope of the original line and then the slope of each combination of two points in the data set. I then subtracted the slopes, found the minimum difference and thought that would give me the line most parallel to the original. This doesn't seem to work. I'm at a loss, any advice?
App To Draw Curved Shape To Scale With Drag & Drop Capabilities
I am researching the possibility of using Excel 2003 WITHOUT a Cad or Drawing package to simulate a drawing. (Consultants inquiries are WELCOME). The idea is to prompt the user (Sales Rep or Customer) for a few key dimensions and then plot out a closed curve based on the coordinantes entered. This shape, which would need to be accurately scaled, would then be sectioned and used as a canvas for dragging and dropping various icons.
Why use Excel instead of CAD?
Other than "everyone" having it, the placement of the "icons" relative to the perimeter of the curve will be used to estimate material costs and generate a quotation like a configurator application.
Key hurdles seem to be the drawing of a curve with cells as pixels and using drag & drop with custom icons - possible as an Add-In?
Shapes With No Macro
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.
VBA With Shapes
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.
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.
How To Remove All Shapes
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
Delete All Shapes In A Range
I have been researching how to create a macro (that will attach to a button), that will delete ALL Shapes and Pictures in a range of cells. So far I have only met with limited results. The Range can be hard coded into the script as it will not change. Does anyone have a script written already they would care to post? I am using Excel 2003.
Replacing Shapes With VBA
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.
Inserting Pictures Into Shapes
I have written some code to insert pictures into shapes as part of a facility management problem I have.
Code takes the names from a list of pictures (ConditionList) and places each one into a corresponding shape - therefore populating a report on the facility.
Works to the point of inserting the pictures OK but if I refresh the ConditionList by adding or deleting pictures, my shapes dont refresh.
Dim ConditionListCount As Integer
Dim ShapesCount As Integer
Dim ConditionList As Range
On Error Resume Next
Application.EnableEvents = False
Arrays Of Shapes Specs
I have a collection of shapes (Rectangles) that are being grouped into a single diagram with the For each shape, index on name, group method. I am having difficulty approaching 2 further issues:
1. before I group them I want to assign the Name, Height and Width to an array so that I can write the array to a worksheet once all the shapes have been identified.
2.Also, the Length needs to be assigned (in some cases it is the Height and in others it is the Width where Length is the larger of Height or Width)to the array for each item. Where is the best place to add the definition of length as it is not relevant to drawing the rectangle but to recording the components.
Define Name For Lines/shapes
Is there a way to define a name (Insert-->Name-->Define) for lines and/or shapes drawn using the Drawing toolbar?
I'd like to assign a specific name to a rectangle I've drawn outside of the sheet print limits. By using an If-Then formula, I'd like to bring that shape into a specified cell based on the conditions of the If-Then statement.
Can You Disable Right-click On Shapes
I would like to completely disable the drop down list on the right-click.
.CommandBars("Cell").Enabled = False
set up and this works on cells but it still leaves it working on shapes.
.CommandBars("Toolbar List").Enabled = False
is similar. Anyone know how to fix this?
Using Shapes To Hide Zeros
I want to write a macro that uses shapes to hide zeros; I have seen this done before but don't really understand how to set it up for myself.
On my sheet, there is a list of funds (Cells A4 to A79), a mixture of which will make up any given portfolio. Cell A1 houses a drop down list of portfolios. When I change cell A1, the numbers are automatically updated, as they are pulled from other worksheets in the spreadsheet. I want to be able to have the funds with zeroes next to them be automatically hidden. I believe there is a way to do this using shapes.
Delete All Shapes On Worksheet
I've written a macro that plots a graph directly in a white painted sheet, using lines and arrows figures.
The problem is that everytime I plot something, the xls-file gets larger, and the size doesn't change if I delete the figures. Since there's a lot of lines and arrows in my plot, I use 'Select All' and Edit-> Remove and this removes all figures, or at least for the eye. I suspect they are still left there somewhere, since the size grows with every new plot and since that sheet seems to have gotten a bit slower, when moving between cells.
Am I correct, or is there another reason for the size/ speed issue? If I my theory is correct, how do you permanently delete these "invisible" figures?
Sizing And Positioning Of Shapes
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.
Delete Shapes On Specified Row
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?
Select Shapes In Macro
I have two lines (in a sheet) that I am trying to move based on a formula. The wierd thing is I can get the first one to work fine, but when I select the next line it will not work. I have Line 2, and Line 10. Basically the code is:
Selection.ShapeRange.Top = 159
Selection.ShapeRange.Top = 300
For some reason it just moves Line 2 no matter what I do.
Object Shapes Move When Printed
I make a lot of charts that have text boxes, or autoshapes in them. Often, the text boxes will be shifted in print preview. Even if they look right in print preview, they some times shift when being printed.
WYSIWYG, doesn't seem to apply
I've noticated that when viewing the spread sheet normally if I change the amount of zoom the text boxes/autoshapes will appear to move around, but what ever the position is for a given zoom amount, it will be same everytime.
Is there a "magic" zoom level that will display what will be printed, or are there some other tips to What-You-See-Is-What-You-Get in excel?
Programmatically Selecting Multiple Shapes
Trying to select multiple shapes based on shape properties. The missing piece is the code to select the shapes. The examples in the help use the 'Array' function with a list of shape names.
Is there a way to select a random set of shapes without using the Array function?
Or how does one build a list for use with the Array function?
Align Shapes No Overlap Or Gaps
I am trying to get two shapes to butt up to each other. Unfortunately the shapes either leave a small gap or a slight overlay. I have tried using Ctrl + arrow key to move in small increments, but that didn't work. I have also tried adjusting the width of the rows, but the rows jump backwards or forwards to a number instead of staying with the number I entered. I want to create a seamless shape out of many different shapes.
Group Shapes In A Certain Selected Area
I don't think this is possible, but I thought I would ask.
I have buttons on a sheet.
Then I have drawings, that I make, using different shapes.
Instead of selecting all shapes on the sheet, I want to just select the shapes in a range say B17:F28 so I can group them.
VBA To Group Worksheet Shapes
I want to "GROUP" a selection of objects that are put onto a worksheet. But I won't know until the objects are created what the names of each object will be. When I create the objects I will know which objects that I want grouped together.
When I record a macro when I select then "GROUP" said objects I get the following line of code
ActiveSheet.Shapes.Range( Array("FlowCtrl_S2_Decision000011", "FlowCtrl_S2_Decision00001", "FlowCtrl_S2_Decision000012", "FlowCtrl_S2_Decision00001a", "FlowCtrl_S2_Decision00001b")).Select
What I need is how to "dynmically" create the Array(....) portion from say a delimited string. Like
"FlowCtrl_S1_Decision00002", "FlowCtrl_S1_Decision000021", "FlowCtrl_S1_Decision000021_1FlowCtrl_S1_Decision00002
In the attached workbook. When you click the button 7 objects will be put onto the worksheet. I want when the routine is complete that the the 7 objects are "GROUP"ed and given a particular name. So that I can point and select at one item and move all 7 at the same time.
Delete Shapes On Workbook Deactivation
I have a workbook in which certain shape objects (2 command buttons and a combobox) can be present on a worksheet if another macro has been run. When a worksheet is closed and another opened, or the workbook closed, I need the shapes to be cut if they are present. There are other shapes on the sheet which must not be deleted.
Each shape has a defined name which is the worksheet name followed by DD, GetStats and Rfrsh
I have tried the code below but get 'The item with the specified name was not found' error message if the one or more of the shapes are not present. The code works for any of the specified shapes that are present.
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Not Sh.Shapes(Sh.Name & "DD") Is Nothing Then Sh.Shapes(Sh.Name & "DD").Cut
If Not Sh.Shapes(Sh.Name & "GetStats") Is Nothing Then Sh.Shapes(Sh.Name & "GetStats").Cut
If Not Sh.Shapes(Sh.Name & "Rfrsh") Is Nothing Then Sh.Shapes(Sh.Name & "Rfrsh").Cut
I am sure I am not using the 'If Not' and 'Is Nothing' properly but can't figure out how it should be.