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


ADVERTISEMENT

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

Select All Shapes In Selected Cells?

May 29, 2014

I have a spreadsheet that has many shapes throughout and are connected together in a specific order. It is basically a drawing of steps and transitions in sequential order. I want to be able to select a group of cells that contain all the shapes that I want to select, run the macro and it select all the shapes within the selected cells. With all the shapes selected that I want, I can move and manipulate them as desired. The other option is to select each shape individually holding shift to make the group selection. The code that I am working with is as follows:

Sub SELECT_SHAPES_too()
Dim SH As Shape
Dim Rg As Range
Set Rg = Selection
For Each SH In Rg.Shapes
SH.Select Replace:=False
Next SH
End Sub

This is the idea that I want to do but I am having problems with the "for each shape in selection" portion.

View 2 Replies View Related

Hide/Unhide Shapes Along With Rows/cells

Aug 18, 2009

I have an workbook with many sheets. each sheet contains loads of checkboxes, dropdowns, option buttons, groupboxes ... and they are created using a macro when the workbook is opened. all this works fine. Now I've grouped different rows based on their level of importance. Here's the glitch. whenever I select a group level, the rows get hidden, but not the shapes & objects present on them. Instead, they just jump to the row above or below and overlap the other shapes.

View 9 Replies View Related

VLOOKUP On Cells Which Contain Auto Shapes Or Drawing Objects?

Jun 11, 2014

how VLOOKUP can be used to look up cells with auto shapes or drawing objects with a number next to it. I keep getting the value in return but the auto shape/drawing object is omitted from the answer. I use Office 2013.

View 8 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

Compare Values From Cells To Shapes To Cells

Feb 18, 2010

In the attached excel file I have two macros that create hyperlinks:

1. BalloonToNumber - Creates hyperlinks from "Oval" shapes on sheet "Op60_1" to sheet1 (column D).

2. NumberToBalloon - Creates hyperlink scheme from sheet1 (column D) to sheet "Op60_1" "Oval" shapes.

In Q #1 my macro seems to skip some shapes on sheet "OP60_1" and I simply can not figure out why.

In Q #2 my macro creates hyperlinks to shapes that do not exist and hyperlinks to numbers that are not a "100%" match, IE: 182 and 82 would share the same hyperlink?

If you open the workbook and use the hyperlinks on sheet1 they will take you to sheet "Op60_1" and shape hyperlinks from sheet "Op60_1" take you to sheet1. On sheet "Op60_1" there are some red arrows indicating the shapes that get skipped when running the "BalloonToNumber" macro.
Shape color changes as the hyperlinks are selected from sheet1.....

View 8 Replies View Related

VBA With Shapes

Dec 3, 2008

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.

Here's the
Item("Direction1").Range.InsertAfter Worksheets("BSID").Range("Line3").Value

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.

View 9 Replies View Related

Working With Shapes

Mar 24, 2009

Where do I find a tutorial or examples to show me how to create bars for gantt chart in excel, based on date values?

View 14 Replies View Related

How To Remove All Shapes

Nov 17, 2009

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

View 10 Replies View Related

Deleting Shapes In VBA

Sep 18, 2012

I have some shapes on a worksheet that need deleting on change of a combo box. Unfortunately I'm unable to use the below code as not all the shapes need deleting, just the ones that are named in a table on another worksheet.

For Each s In ActiveSheet.Shapes
s.Delete
Next s

Also not all the shapes named in the table will be on the worksheet as this depends on what is selected from the combo box.

View 2 Replies View Related

Replacing Shapes With VBA

Aug 30, 2009

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.

View 9 Replies View Related

Shapes With No Macro

Jul 10, 2006

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.

View 9 Replies View Related

Delete Shapes On Specified Row

Jan 5, 2008

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?

View 3 Replies View Related

Drawing Circles And Other Shapes?

Jun 16, 2013

I want to draw circles from 3 points and other geometrical shapes. The points will be given from clicks on the background picture.

View 1 Replies View Related

Auto Shapes : Areas

Apr 23, 2008

I'm trying to calculate the area of a Freeform shape.

Is this already stored somewhere e.g a shape property?

If not is there a macro available to do this?

View 12 Replies View Related

Create Hyperlinks With Shapes

Feb 9, 2010

This code finds and matches the Text value of a Shape to the value of a cell on seperate worksheets within the same workbook.

View 9 Replies View Related

VBA To Ungroup All Shapes On A Page

Feb 23, 2007

I need code to do this: When I select a range of cells, say E20:H24 then hit a button the code behind the button would ungroup all the shapes in that area. So if I have grouped shapes elsewhere, they would be uneffected.

View 9 Replies View Related

Selecting Group Name For Shapes

Mar 5, 2007

is there a way to select the group of the shape instead of a single shape i've tried .onAction and Application.Caller to do this ..but this is giving me the single shape that i clicked on instead of the whole group.'

View 9 Replies View Related

Can You Disable Right-click On Shapes

Jul 17, 2007

I would like to completely disable the drop down list on the right-click.
I've got

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

View 9 Replies View Related

Change The Colour Of Shapes

Jul 23, 2007

the VBA to change to colour of a shape based upon the value in a cell?

e.g. if cell "a1" = 10 then colour circle red

View 9 Replies View Related

Using Shapes To Hide Zeros

Oct 17, 2007

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.

View 9 Replies View Related

Define Name For Lines/shapes

Feb 10, 2008

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.

View 9 Replies View Related

Delete All Shapes In A Range

May 29, 2008

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.

View 9 Replies View Related

Arrays Of Shapes Specs

Aug 17, 2008

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.

View 9 Replies View Related

Inserting Pictures Into Shapes

Apr 30, 2009

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.

Sub ConditionPics()

Dim ConditionListCount As Integer
Dim ShapesCount As Integer
Dim ConditionList As Range

On Error Resume Next
Application.EnableEvents = False

View 9 Replies View Related

VBA To Group Worksheet Shapes

Apr 1, 2009

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
Selection.ShapeRange.Group.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.

View 9 Replies View Related

Add Shapes To UserForm Via Code

Nov 25, 2006

How do i draw shapes on the fly on forms?

View 4 Replies View Related

Select Shapes In Macro

Dec 2, 2006

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:

ActiveSheet.Shapes("Line 2").select
Selection.ShapeRange.Top = 159

ActiveSheet.Shapes("Line 10").select
Selection.ShapeRange.Top = 300

For some reason it just moves Line 2 no matter what I do.

View 6 Replies View Related

Stop Shapes From Printing

Dec 6, 2006

I have created some block arrows from "Autoshapes" in a worksheet. Is there a way not to show these block arrows if I print this worksheet?

View 3 Replies View Related







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