Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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

Sponsored Links:

Related Forum Messages:
Change Shape Text Without Selecting Shape
when i run the below code i get an error 438 'object doesnt support this property or method'

View Replies!   View Related
Change The Shape Of Cell
Is it possilbe to change the shape of a cell to a circle?Or is there a way you can make a shape act the same as one of your cells?


View Replies!   View Related
Change Color Of A Shape With Click
what i am trying to do is toggle the color of a shape between red and green. what i have currently (courtesy of a search on this site) is this macro:

View Replies!   View Related
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:

Sheet1.Shapes("button 15").Select
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.

View Replies!   View Related
Change Line Border Color Of Shape
I have this simple code which sets the objects format as default.

Private Sub FormatTextBox()
ActiveSheet.Shapes("FormatBox").Select
Selection.ShapeRange.SetShapesDefaultProperties
End Sub

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?

View Replies!   View Related
Add Shape On Cell Formula Calculation Value Change
I am trying to get different shapes (previously created) to appear in a certain cell, dependant on the resultof a formula in an adjacent cell.

View Replies!   View Related
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 ()
Worksheets("sheet2").Select
Worksheets("sheet1").Shapes("Button 2").Select
Selection.Characters.Text = "Done"
End Sub

Problem with this is it gives cell A1 on sheet2 the "done" text. I also tried this...but it gives the error.

Sub macro1 ()
Worksheets("sheet2").Select
Worksheets("sheet1").Shapes("Button 1").Select
With Worksheets("sheet1").Shapes("Button 1")
.Characters.Text = "Done"
End With
End Sub

View Replies!   View Related
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

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Aircraft1")) Is Nothing Then Exit Sub
ActiveSheet.Shapes("Arrow1").Select

With Range("Aircraft1")

If .Value = 1 Then
ActiveSheet.Shapes("Arrow1").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17

View Replies!   View Related
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)

Option Explicit

Private Sub Worksheet_Calculate()

If Range("N10").Value = "1.0" Then
ActiveSheet.Shapes("AutoShape 578").Visible = True
Else
ActiveSheet.Shapes("AutoShape 578").Visible = False
End If

View Replies!   View Related
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.

View Replies!   View Related
IF On Shape
code doesn't work

Sub test()

If ActiveSheet.Shapes.Name = ("five") Then
Range("B4").Select
Else
Exit Sub

End If

End Sub

View Replies!   View Related
Shape A Textbox
Is it possible to find out if the selected item is a textbox through VB?


View Replies!   View Related
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.

View Replies!   View Related
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 ...

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
Which Hyperlink Was Clicked And Associated Shape Name
How do I capture which hyperlink has been clicked (selected in any fashion)?

Then get associated shape name.

View Replies!   View Related
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"

View Replies!   View Related
How To Get The Cells In Which The Shape Object Is There
I have a shape object which is created dynamically.

Now onAction event of the shape object, I need to get the cells row and column number where the shape object is sitting. How can i get it?

View Replies!   View Related
Check If Shape Exists
how do i check if a shape exists?

i have a shape created by a macro.
sShape

so when i get rid of this shape i use

View Replies!   View Related
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
End With

View Replies!   View Related
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( _
Type:=msoShapeRectangle, _
Top:=.Top, Left:=.Left, _
Width:=.Width, _
Height:=.Height)
.Placement = xlFreeFloating '
.Fill.ForeColor _
.RGB = RGB(Red:=192, Green:=192, Blue:=192)
End With

View Replies!   View Related
Accessing The Properties Of A Shape
How does one access the properties of a shape? For instance getting the text on a button ( from the forms toolbar ) on a worksheet. This works

View Replies!   View Related
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.

View Replies!   View Related
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
Next

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

View Replies!   View Related
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?

View Replies!   View Related
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?

View Replies!   View Related
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
...
Also:

If ActiveSheet.Shapes(Shapes. Name).OnAction = "Freeform51" Then

Also, is there an easy way to pull the shape names and manually change them?

View Replies!   View Related
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.

View Replies!   View Related
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

View Replies!   View Related
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.

View Replies!   View Related
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

With ActiveSheet
.Shapes.AddShape(msoShapeLeftArrow, 205.5, 312#, 144#, 23.25).Select
End With

View Replies!   View Related
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:

Sub MoveRed()
Application. ScreenUpdating = True
Dim d As Integer
Dim i As Integer

d = Range("AZ1").Value

With ActiveSheet.Shapes("RedDot")
For i = 1 To 16 * d

Select Case Range("A1").Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

View Replies!   View Related
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:

View Replies!   View Related
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.


View Replies!   View Related
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:

ActiveWorkbook. Sheets("Sheet1").Activate
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. " & _
Priority5Range.Value

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.


View Replies!   View Related
Typecasting Selection Object To Shape
I am unable to cast the Application.Selection object to Type Shape. I have created a Rectangle, and tried running code after selecting it. And for some reasons I am unable to cast the selected object to shape.

View Replies!   View Related
Count How Many Shape In The Activeworkbook Are Ovals
i'm tring to count how many shape in the activeworkbook are ovals like so :

View Replies!   View Related
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,

View Replies!   View Related
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.

View Replies!   View Related
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?

View Replies!   View Related
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.

View Replies!   View Related
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?

Sub DoIt()
Application. ScreenUpdating = True
With Sheet1.Shapes("Rectangle1")
.Visible = msoTrue = (Not Sheet1.Shapes("Rectangle1").Visible)
End With
'Toggling sheets Forces Rectangle 1to show while code is running
Sheets("RST").Select
Sheets("RST Pivot").Select
End Sub

Also, do I need to change 'With Sheet1.Shapes' to reflect the actual sheet name?

View Replies!   View Related
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)
shpTemp.Copy

With Worksheets("Sheet1")
.Activate
. Range("A1").Select
.Paste
Set shpTemp = .Shapes(.Shapes.Count)
shpTemp.Fill.UserPicture ListBox2.Value
End With
End Sub

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

View Replies!   View Related
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.

Sub FlowChartConnection()
ActiveSheet.Shapes.Range( Array("FlowChart1", "FlowChart2")).Select
ActiveSheet.Shapes.AddConnector(msoConnectorCurve, 149.25, 146.25, 159.75, _
257.25).Select
Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes( _
"FlowChart1"), 3
Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes("FlowChart2" _
), 1
End Sub

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.

View Replies!   View Related
Add Shape & Textbox Values For Coordinates
i am trying to enter the EndX coordinates (The third number: 500) by entering a number in a forms textbox

ActiveSheet.Shapes.AddLine(0, 100, 500, 100).Select

how can i break the code up to enter the coordinates via textbox's

View Replies!   View Related
Add Position Shape To Cell Location
i need to loop through a column of values and get each value

Dim LastRow2 As Long
Windows("SCFOutput.xlsm").Activate
Sheets("Q2SCNeg").Select
Columns("A:B").Select

ActiveSheet. Range("A65536").End(xlUp).Offset(1, 0).Select
LastRow2 = ActiveCell.Row - 1
For Each c In Worksheets("Q2SCNeg").Range("A2:A" & LastRow2).Cells
MsgBox ActiveCell.Value
Next c

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

View Replies!   View Related
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

View Replies!   View Related
Link Cell To Picture Or Shape
It's difficult to explain, the attached shows the situation better. Basically there's a picture that is somehow linked to a cell. But, you can update the picture or the cell and the other one gets updated.

View Replies!   View Related
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
.ConvertToShape
End With
Shapes(8).Fill.ForeColor.RGB = RGB(200, 90, 20)
End Sub

I want to give the shape a name, without having to use Shapes(8) at any stage. Is this possible when making freeform shapes?

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved