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.
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...
Function Macro()
'
DELETEALLSHAPES
'
'
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 220.5, 105.75, 92.25, 51#). _
Select
End Function
Function CIRCLES()
'
DELETEALLSHAPES
'
'
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 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.
Where do I find a tutorial or examples to show me how to create bars for gantt chart in excel, based on date values?
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
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.
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.
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 RelatedI 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 RelatedI'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?
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 RelatedI 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.
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 RelatedI 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?
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
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.
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.
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 RelatedI 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.
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
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.
How do i draw shapes on the fly on forms?
View 4 Replies View RelatedI 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.
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 RelatedI 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.
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?
I have tried to make cartesian coordinates (X)and (Y) using excel 2007. now I want to represent some shapes (circles, squares, triangles, and so one). My problem now is with formating those shapes. I use this path (Insert > Shapes),, but the shape I add cover the grid,
I want (for example) a circle but I need only its circumference, and without covering the grid by its area. the same for other shapes.
I am developing a multiple sheet workbook with lots of shapes - with another person. The shapes are moving if he opens the file when I save it and they also move when I open the file after he saves it. So, I coded in the beforesave function, I save .top and .left position of every shape to a hidden sheet. On workbook open, I re-position all Shapes based on their saved locations. Both of our screens are set to the same resolution... What I think it is - display scale. His is set to 100%, and mine is set to 125%! Anyway to adjust for this? I cannot find out how to find / change the display scale with vba.
View 3 Replies View RelatedI 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.