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?
Since I cannot record the code when I create an shape (eg a rectangle), I am trying to write an small sub that would return the attributes of the object.
I select a red Rectangle I put on a tab.
I started as follow:
HTML Code:
Sub GetMeSelectedShapeAttributes()
Dim Attributes(1 To 10, 1 To 2) As String
With Selection
Attributes(1, 1) = "name"
Attributes(1, 2) = .Name
Attributes(2, 1) = "Back color shapes"
Attributes(2, 2) = .Fill.BackColor.RGB
End With
End Sub
My goal is to learn about the different attributes of shapes so I can manipulate them with code,
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 4 Replies View RelatedI have Excel and Outlook 2010 64-bit. I'm trying to develop HTML e-mails that are a mix of text drawn from cells on one sheet and picture objects on another. I have no problem with the HTML body text, but I cannot figure out how to reference and place the Shape objects. I am using:
Code:
...
.HTMLBody = "" & Range("F1").Offset(i) & Range("G1").Offset(i) & ""
...
and I want to embed, between the text brought in with cells F and G, a picture object like
Code:
Sheets("Images").Shapes("Picture 1")
I've found code that brings in a picture from a file but, for portability's sake, I really want to have the pictures maintained on the Images sheet and not in a separate file. However, I cannot figure out how to connect the HTML code with the spreadsheet object.
when i run the below code i get an error 438 'object doesnt support this property or method'
View 2 Replies View Relatedfollowing code is supposed to check whether cell contains a value and based on that either display or hide a shape. The last couple of lines are moving it. What I would need is to check all cells in a row (not only the first one) and if lets say first 1,2,3...10... cells contain a value move the shape all the way to the end of that cell and so on.
VB:
Sub Worksheet()
If Sheets("Text").Range("z6") <> 0 Then
With ActiveSheet.Shapes("Rectangle 8").Fill
[Code].....
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.
if I wanted to slowly move a shape (small circle) in a straight line between cells R20 & W7.
The straightest line possible is by drawing an arrow line between the 2 cells.
Is there a way to then use this arrow as a guide for moving the shape?
In other words, would it make sense for the porgram to draw a transparent line between the cells, and use that line as a "path" along which to move the shape?
I just don't know if this is worth the trouble.
On the attached spreadsheet there is two irregular shapes. "Area1" & "Area2". I need to be able to determine which area the "ball" shape is located in. If the ball is in Area1 then "Multiply 8" gets the ball and "Multiply 9" moves to the blue cell "AH39". If the ball is in "AreaB" Multiply 9 gets the ball and "Multiply 8" runs to cell "S37"
I should be able to do the moving of the shapes using all the samples, its determining which area the ball is in is the problem.
In the real spreadsheet i will probably have around 10 different Areas.
Move Two Objects - Select Case.xlsm
I am using Excel to fill in information for W-9 tax documents. I have formulas on one sheet that pull from other sheets where the numbers for the form are entered. Currently we have to put the actual form in the printer. I'd like to embed the form into excel so that the numbers pulled by the formulas and the form itself all print. The problem I'm having is I can not get the form to sit in the background. The cells are hidden behind the form and do not show up when printing.
View 7 Replies View RelatedI'm getting the following error:
"Object library invalid or contains references to object definitions that could not be found"
I wasn't getting that error last night and I'm not sure what I may have done to cause this error.
It seems to be cause by code running on one sheet of my workbook, but I'm not really sure about that. I'm still a bit of a novice at VBA.
I'm using Excel 2002 SP3 and I'm running MS XP Home as my OS.
Do you have any ideas what can cause this error and/or how to trace down the offending objects/code?
I have a compilation of data on all employees ( named agents in here). On one sheet I will show graphs on diffrent figures for a specific agent. They choose which agent from a validation box in the cell 1,2 (range A2). When they have done that, I make a new list for the daterange and Cell 1,4 and Cell 1,6 (Range A4, Range A6) are validationboxes showing that list. The cells are empty to begin with.
When all validationboxes are filled, I do the graphs, and here comes the problem. After doing the graphs I get an errormessage on saying that "Method 'Cells' of object '_Global' failed" when it comes to the if-statement that has the Daterange-call in it. Ie: I dont get the errormessage when it DOESNT do the ShowCharts-sub, but after it has done that, I do get the errormessage.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Application. ScreenUpdating = False
'Cell 1,2 = Validation-box based on a list of agents
'Cell 1,4 = Startdate for the data to be picked for the graphs.
'Cell 1,6 = Enddate for the data to be picked for the graphs.
If Not (Cells(1, 2).Value = Empty And Cells(1, 4).Value = Empty And Cells(1, 6).Value = Empty) Then......................
I am trying to find particular text in a cell then format adjacent cells in the same row. In my code below I am trying to search for "*[Tx]*" using Like, however this is formatting all text that contain "T".
View 7 Replies View RelatedI am trying to use the Cells Property in VBA but it is not working and I cannot figure out why for the life of me. Below is my ....
View 9 Replies View RelatedThe problem that I am having today is defining a range object that cannot include null cells. If it does include Null cells then the filterwill fail. the cells that I need to define are all in a cohesive unit. the other thing to know is that the cells that are not null will never be mixed in with cells that are null. so for instance you might have a range of cells from one to 100, the first 50 might be full. the last 50 would all be null. in that situation I would need to loop through those cells to define a range object that would just see the first 50 cells ....
View 6 Replies View RelatedIm trying to add hyperlinks to cells using an array but im getting "run time error 424 - object required" error. THe code im using is:
Code:
Sub hyper3()
' not WORKING - ARRAY to add hyperlink to cells
Dim rngIn As Range
[Code]....
I'm having a problem in a single line of code in which I'm using a Range Object to essentially select an area of cells whose contents I want cleared.
Here is the
Sheets(Left(c.Value, Len(c.Value) - 1)).Range(Cells(4, 2), " &:& ", Cells(Rows.Count, "b").End(xlUp).Offset(0, 9).Select).ClearContents
I want the range part to evaluate as (b4:whatever the last cell is before the first blank row).
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 4 Replies View Related1) i have office 2003 on a laptop. within powerpoint, i can create a 'microsoft excel chart 11' object. to create a link to the excel data source, do i have to go through the odbc sql setup? it works, but i don't want my powerpoint to be dependent on some excel file somewhere. what are the other options to insert/make a functional pivot chart in powerpoint with the data also within powerpoint? the data as sheet option does not result in the chart being a pivot, it's just a plain chart. it has to be a proper object, not an image paste or a chart that updates links with the excel file open.
2) i have office 2007 on my other laptop. i can not find any suitable object to choose from to make a pivot chart in powerpoint. what's the best way to go about in 2007 version?
3) am i going about this the wrong way with the objects? should i be after vba code?
code doesn't work
Sub test()
If ActiveSheet.Shapes.Name = ("five") Then
Range("B4").Select
Else
Exit Sub
End If
End Sub
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.
Is it possible to find out if the selected item is a textbox through VB?
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.
How do i Keep shape where it is no matter if i scroll up or down.
Sub KeepshapeStatic()
Dim ws As Worksheet
Dim shp As Shape
Set ws = Sheets("Sheet1")
Set shp = ws.Shapes("MyShape")
'How do i Keep shape where it is no matter if i scroll up or down
End Sub
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 ...
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.
I have put a rectangle shape in a cell in excel. The shape is within the border of a particular cell.
Is there a way to find the reference of the cell on which that particular shape resides.
For example, I have kept a rectangle shape in cell F5. I was looking for a macro which would return the cell reference "F5" in which the shape resides.
I have a few dates and one Total value. I need to distribute this Total Value in such a way so I can get the same shape S curve (Date vs Value).
View 4 Replies View RelatedI really dislike the look of the command buttons and would like to use a nice flat-looking Shape into a functional button.
View 3 Replies View RelatedIs there any way to place a Control Tip for a shape on a excel worksheet?
View 3 Replies View Related
