Change Shape Name
I have a shape in Excel called Canada. I grouped it with another shape and want this new grouping to be called Canada. But when I enter Canada in the shape name entry box, it doesn't actually change it (as it thinks I am referring to the existing Canada).
I need something like Names manager, but for shapes, not ranges.
View Complete Thread with Replies
Related Forum Messages:
Change Button Shape Caption
How do you references the properties of a shape? I am having trouble with 2 different types. Firstly, I have a worksheet with a group of commandbuttons (all added using the Control toolbox) and I want to loop through them changing, for example, the enabled property of each to TRUE.
Secondly, I have some commandbuttons on a worksheet (added using the Forms toolbox). How would I loop through changing, say, the text on the buttons. I used the macro recorder to try to get an idea and got the following:
Selection.Characters.Text = "New Text"
Although this works, it is not very elegant, how can it be achieved without selecting the button each time, but rather by directly accessing the property.
Change Line Border Color Of Shape
I have this simple code which sets the objects format as default.
Private Sub FormatTextBox()
Is there a way to set an objects shape to the default format...not as default?
ie: if the default line color is blue. what code could i use to set an object with a black line color, to the default blue?
Change/Modify/Add Text In Shape On Non-Active Worksheet
I have a button on say, sheet1 with text that I want to change after a certain action takes place; however, I was trying to change this text without switching sheets. (switching sheets isn't a big deal, I'm mainly trying to do it my way for the practice). I'm able to select the button on the other sheet without actually switching sheets, but when it comes to changing it's text I get an error. Here is the code I tried:
Sub macro1 ()
Selection.Characters.Text = "Done"
Problem with this is it gives cell A1 on sheet2 the "done" text. I also tried this...but it gives the error.
Sub macro1 ()
With Worksheets("sheet1").Shapes("Button 1")
.Characters.Text = "Done"
Change Shape Color Based On Combo Box Selection
I am trying to use a userform ( Combo Box) to update my named cell "Aircraft1" then based on the value selected change the color of an object.
The code works great if I manually enter the value in the cell "Aircraft1", however if the dropdown list selects the value the object does not update its color.
Is there a way to update the cell via a combo box, and then have the VB code change the color of the object?
This is my
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Aircraft1")) Is Nothing Then Exit Sub
If .Value = 1 Then
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
Add Shape To Cell With Cell Value Change
The Following works great in it's own workbook, however it will not run in mine. Is it because I have other macros running?
This is the error that I'm getting.
Automation error (Error 440)
Private Sub Worksheet_Calculate()
If Range("N10").Value = "1.0" Then
ActiveSheet.Shapes("AutoShape 578").Visible = True
ActiveSheet.Shapes("AutoShape 578").Visible = False
If Formula: =IF(D4<0.38,"Thin",IF(0.38<D4<=0.48,"Good Shape","Bad Shape"))
I have D4=0.42. I thought this formula =IF(D4<0.38,"Thin",IF(0.38<D4<=0.48,"Good shape","Bad shape")) was supposed to give me "Thin" for D4<0.38, "Good shape" for 0.38<D4<=0.48 and "Bad shape" for the contrary, but it only gives me "Bad shape" when D4 is clearly between 0.38 and 0.48.
Get The Name Of The Shape Clicked
Is there any way that I can get the name of the shape i just clicked to call a procedure?
I have 35 shapes in my Worksheet.
Each shape has a different name. Each shape has a macro procedure assigned, wich do almost the same procedure (with just small differences, depending on from wich shape the macro was called).
I want to make just one macro procedure and catch from wich shape the macro was called.
Add Shape & Rotate
i am trying to draw a shape (square or rectangle) using vba textbox's i have managed to draw 2 of the 4 lines.
i am stuck on the ShapeRange.Flip msoFlipHorizontal codeing.
i have attached a example of what iam trying to do.
the coorinates from the last line enterd are saved to sheet1 for use with the next line entered ...
Shape On A UserForm
I want to create a shape connecting them and place that shape on a userform. I can create the area without any problems on a worksheet using ActiveSheet.Shapes.BuildFreeform but I don't know how to get it onto my userform.
I have looked through previous posts on similar topics and seen responses that it's as easy as copying the shape from the worksheet and pasting it into an image or frame on the userform. I can copy the shape to the clipboard without any problem. However, when I try to paste it onto an image or frame in a userform I get a message saying that the object doesn't support the use of paste. I've tried using both Selection.Copy and Selection.CopyPicture xlScreen, xlBitmap thinking it might treat a bitmap differently. When I paste I'm trying both UserForm1.Frame1.Picture.Paste and UserForm1.Image1.Picture.Paste but neither work, even though I have blank images and frames with those names on my form.
Find Out If Shape Has Text
Have an .xls file with multiple sheets and many objects (Shapes) on of those sheets.
The objects are all rectangles, some of which contain text frames, where I had entered manually some text.
Also, each shape has a name.
Now, I want my VBA Macro to take a specific shape (using its name), and find out if it has text frame in it.
Automatic Shape Placing
I am trying to have 8 various shapes placed on running pages alphabetically, ie 8 modules to a page being 2wide by 4 high. There would be say 200 entries of various combinations and names equating to 80 pages If these can be sorted alphabeitically by name then placed on subsequent pages. Is this possible in excel or in "VB"
Dynamically Add A Hyperlink To A Shape In VBA
I am trying to create a table of contents for a very long list of items on a sheet. I am using shapes on a seperate sheet to act as buttons, that go to the location on the correct sheet using a hyperlink. I found how to add a hyperlink using a macro, but I can't find how to create a hyperlink for the shape using a macro.
What is the code I would need to set a hyperlink to "Sheet1!A2:Sheet1!C2", with the a tooltip of "Some text"?
Here is how I create the shapes:
With myDocument.Shapes.AddShape(msoShapeRoundedRectangle, 10, IntVerticalOffset, 630, IntHeight)
.Fill.ForeColor.RGB = RGB(204, 255, 204)
.Line.ForeColor.RGB = RGB(0, 0, 0)
.TextFrame.Characters.Text = StrRowName
.TextFrame.HorizontalAlignment = xlHAlignLeft
.TextFrame.Characters.Font.ColorIndex = xlAutomatic
.TextFrame.Characters.Font.FontStyle = "Bold"
.Locked = True
Create Shape & Cover To The Last Used Row
Is there any way that this bit of code can be combined to create a shape using dates that fall on weekends (that are listed in row2) and at the same time cover all rows up to the last Row used in a worksheet?
For instance, in the columns of Row2 we have many dates and we would have "16-Jan-10" in one column and "17-Jan-10" in another column (usually the next column) which are weekend dates. the shape should be created in the next row under the weekend date and cover the column all the way down to the last used Row (last Row contaning data).
With ActiveSheet.Shapes.AddShape( _
Top:=.Top, Left:=.Left, _
.Placement = xlFreeFloating '
.RGB = RGB(Red:=192, Green:=192, Blue:=192)
Toggle Shape Color
I am trying to write a macro so that when you click a circle shape it will fill it black and then when you click again it will go back to no fill. Like the option button but is not linked to any other one. So it should just work like a check box but I need it to be a circle.
Move Shape Across Screen
I'm trying to write some VB to draw a line shape and then move it across the screen. I have managed to draw the line and have used the following code to move it:
Set linetest = ActiveSheet.Shapes(1)
For iCount=1 To 100
linetest.Left = linetest.Left + 1
linetest.Top = linetest.Top + 1
i) I can only actually see the line move if I run the code using the debugger and step through the loop. If I just run the code then I only see the end position of the line (note, this is not an issue with the calculation being too fast for Excel to display the line. I tried the code with a delay loop in it and it made no difference). I can get it to work by calculating the active sheet for each iteration of the loop, but this means I have no control over the speed of the movement; incidentally it moves very slowly).
ii) This seems a poor way to move the line. Is there anyway to move the start and end node of the line rather than using the left, top, width and height properties (i.e. can I get the old coordinates of the points and set them to different numbers)?
Positioning Shape Objects In VBA
I am trying to work out how to pisition a new object in some VBA code, I can do this if I copy an object then click on a cell, e.g. H74 then paste, but the properties for an object want this in points.
ActiveSheet.Shapes.AddShape(msoShapeOval, 50, 50, 26.25, 26.25).Select
What I want to do is select the cell H74, then find the coordinates for that cell then use these when creating the shape, I appreciate these coordinates will need to be assigned to variables, but how do you get then from a given cell?
Shape With Hollow Centre
if it was possible to create a shape, such as a rectangle, with a hollow centre so you can see the cell positioned behind it? E.G. the shape could cover cells B3:D5 so you could see cell C4 through the hollow centre/gap?
Checking For Shape Name On Click
I am trying to create a clickable image map. I have one macro that is activated when any shape is clicked, but it executes different conditions depending on which shape is clicked.
How do I check whether a specific shape is clicked?
Some of the code I tried:
If ActiveSheet.Shapes("Freeform51") <> null Then
If ActiveSheet.Shapes(Shapes. Name).OnAction = "Freeform51" Then
Also, is there an easy way to pull the shape names and manually change them?
Cut And Paste Shape Into Same Position
I am trying to cut and paste an object (an excel shape to be more precise) into its same position. It could, for example, be relative to the top left corner, but how to modify the paste method will be welcome. Surely this is a piece of cake for most of you, but I haven't been able to find the answer for objects floating on the screen.
Adjust The Shape Of The Chart...
How to adjust the shape of the chart in the worksheet? I try to write the code, but can not work.
ActiveChart.Shapes.ScaleWidth 0.87, msoFalse, msoScaleFromBottomRight
ActiveChart.Shapes.ScaleHeight 1.5, msoFalse, msoScaleFromBottomRight
Measure Shape Lines
is it possible in VBA to measure each line of a freeform shape and measure the angles that are created between two joining lines. Is it also possible to set the length of each individual line in VBA? See attachment for example.
Add Shape (Arrow) To Worksheet
This one has me baffled: I recorded a macro to add a shape to a worksheet, very simple. When I try and run it it comes up with a "The specified value is out of range" error. I've searched the net and it seems to come up where the file has been converted from an earlier version of excel (which this file has) but I haven't been able to find a work around for it. The relevant code is
.Shapes.AddShape(msoShapeLeftArrow, 205.5, 312#, 144#, 23.25).Select
VBA Macro To Move Shape Around Spreadsheet
I am trying to write a set of code, part of which involves moving a shape around an excel spreadsheet in a square shape within a certain range. It is based on a random number generator. The shape moves a number of cells to the left/right/up/down depending on the random number (d) multiplied by 16. If the shape reaches a row or column border point, it will change direction but continue moving in the new direction until it has gone as far as was determined by the random number (*16). I have some code which works fine... to a point! The shape starts out, reaches a right hand border, changes direction to go up, reaches another border, changes again and goes left, but then when it comes to change dorection the 3rd time and go down, it fails. The direction of the shape is determined by a word in cell A1 - left/right/up/down:
Application. ScreenUpdating = True
Dim d As Integer
Dim i As Integer
d = Range("AZ1").Value
For i = 1 To 16 * d
Select Case Range("A1").Value
Assigning Macro To Shape In 2007
I posted similar yesterday. No one helped...sniff sniff (where's the crying smily)
Now verified to be occur on other machines running various versions of Office 2007...
In Excel 2007, write this simple macro and assign it to a shape:
Event Triggered When A Shape Is Moved
Is there a way to trigger a macro when a shape is moved by the user? If I set the OnAction property for a shape, then the cursor changes to a hand when it is over the shape, and I can detect a mouse click on the shape, but I cannot drag and drop the shape. If I reset the OnAction property to "" (empty quotes), then I can drag and drop again within the worksheet, but I can't trigger the macro.
Worksheet_Change or Worksheet_SelectionChange are not triggered by this event.
People have suggested using timers to continuously poll the shapes and determine their locations, but is there an easier way?
What I am trying to do is create a sheet where the user can visually move around objects (in this case representing employees) and deposit them in various zones. The spreadsheet would then apply certain attributes to the shape i.e. change colour according to where the shape is and if it is in an unsuitable zone. I can do all of this, but I want it to work the instant the shape is moved, not rely on the user to hit a button.
Pass Values From Cells To Shape
I am trying to convert some text from a number of cells to shape in another workbook. The problem is, if the text in one of the cells is too long (from testing it by too long i pretty much mean roughly 100 characters) then it doesn't pass anything at all to the shape. Is there a way around this so that all text will be converted to the shape regardless of its size?
By the way, the code is:
ActiveSheet.Shapes("Text Box 1").TextFrame.Characters.Text = "1. " & Priority1Range.Value & Chr(10) & "2. " & Priority2Range.Value _
& Chr(10) & "3. " & Priority3Range.Value & Chr(10) & "4. " & Priority4Range.Value & Chr(10) & "5. " & _
The variables 'Priority1Range' represent the cell the text is in that I am trying to pass and it's variable type is Range. I have tried declaring the Priority1Range.Value as a string variable and using this instead but this doesn't work.
Shape Size Dependent On Cell Value
I'm trying to make a circles size (diameter) change depending on a value inputted in a cell, preferable I would like to have a limit to the sizes too so if that the circle will not have a diameter larger than 20 or smaller than 2 regardless of the values put in. the sheet will have several circles.
I know this must be documented somewhere but I donít think Iím using the right terminology in my searches,
Lock Shape Position On Screen
I created a shape (rectangle). I assigned a macro to it, to activate another worksheet.
Can I fix the position of the shape on the screen, near the top. I have 700 rows in the worksheet and I want this rectangle always visible, near the top.
Prevent Shape & Range Selection
I have put a 3d shape on the excel sheet, and would like it unclickable. Is there way to accomplish that with the sheet still clickable. I know that you can use protection from Tools manual to protect the sheet. Is there way to protect just specific object or range of cell unclickable?
Border Around Range To Form Shape
For my university coursework I've created a comprehensive building cost model but I'm struggling with one aspect, copying the plotted ground floor footprint to enable floor finishes to be selected. A user can plot shapes in a 50x50 grid (B2:AY51). The shapes are displayed using conditional formatting. All shapes are conditionally formatted red, i.e. colour index 3. I wish to place a border around the plotted shapes, so that I can then copy the outline to a new tab. e.g. Range B2:AY51, If cell conditonally formatted red then place borders to the sides of the that cell that share a boundary with a non-conditonally formatted red cell.
Hide & Show Shape On Worksheet
I've been trying to make use of 'Run "doit", but the macro stops when it gets to my sheets called 'RST' and 'RST Pivot'. What I'm I doing wrong?
Application. ScreenUpdating = True
.Visible = msoTrue = (Not Sheet1.Shapes("Rectangle1").Visible)
'Toggling sheets Forces Rectangle 1to show while code is running
Also, do I need to change 'With Sheet1.Shapes' to reflect the actual sheet name?
Fill Shape With Picture 2007
The following works perfect in 2003.
Private Sub CommandButton1_Click()
Dim shpTemp As Shape
Set shpTemp = Worksheets("Sheet3").Shapes(ListBox1.Value)
Set shpTemp = .Shapes(.Shapes.Count)
However, when run in excel 2007 the shape is not filled. If you right click the shape, format shape and click on the "Picture or texture fill" it will fill the shape with the previously selected picture in the userform. I have tried by adding
shpTemp.UserPicture.Fill.Visible = True....................
Set ConnectorFormat Start & End To Selected Shape
I have two Textboxes created from the drawing toolbar, named "FlowChart1", "FlowChart2". Below is a simple macro I recorded and modified which connects these two textboxes.
ActiveSheet.Shapes.Range( Array("FlowChart1", "FlowChart2")).Select
ActiveSheet.Shapes.AddConnector(msoConnectorCurve, 149.25, 146.25, 159.75, _
Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes( _
Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes("FlowChart2" _
Although it works fine... A problem I am having is that I am working with multiple (duplicated) flowcharts. Currently, the connection is based on the name of the objects. If I duplicate both textboxes and run the macro, the connection will connect to the first set of ( already connected) textboxes.
Q. Is it possible to form a connection based on active selection rather than the name of the objects? ie: if any two textboxes are selected.. connect these two objects.
Add Position Shape To Cell Location
i need to loop through a column of values and get each value
Dim LastRow2 As Long
ActiveSheet. Range("A65536").End(xlUp).Offset(1, 0).Select
LastRow2 = ActiveCell.Row - 1
For Each c In Worksheets("Q2SCNeg").Range("A2:A" & LastRow2).Cells
i did this but it selects the first blank row (row 15) and gives me an empty message box 14 times (which is right, but i need 14 values) the reason i need the loop is because the number ov values will always change
Changing Auto-shape Text Vba
This it possible to change an auto-shape text say Rectangle 200 based on what cell is selected. For Example Peps: -If i select cells(3,3) then the text in Rectangle 200 will change to Hello!!!, but if i select cells (3,4) then the text will change to Bye Bye!!! Do you understand me babes
Reference Shape Made With BuildFreeForm
How do I select a shape I have just created with BuildFreeform function? It is not convenient for me to reference the resultant shape as I have done below:
Public Sub DrawDoor(TileType As String, Xpos As Integer, Ypos As Integer)
With ActiveSheet.Shapes.BuildFreeform(msoEditingCorner, Xpos * 9 + 1, Ypos * 9 + 5)
.AddNodes msoSegmentCurve, msoEditingAuto, Xpos * 9 + 5, Ypos * 9 + 1
.AddNodes msoSegmentCurve, msoEditingAuto, Xpos * 9 + 9, Ypos * 9 + 5
.AddNodes msoSegmentLine, msoEditingAuto, Xpos * 9 + 9, Ypos * 9 + 10
.AddNodes msoSegmentLine, msoEditingAuto, Xpos * 9 + 1, Ypos * 9 + 10
.AddNodes msoSegmentLine, msoEditingAuto, Xpos * 9 + 1, Ypos * 9 + 5
Shapes(8).Fill.ForeColor.RGB = RGB(200, 90, 20)
I want to give the shape a name, without having to use Shapes(8) at any stage. Is this possible when making freeform shapes?