Resize Shape Based On Cell Position?

Oct 13, 2013

I want to resize a shape and set the height to a specific cell. How can I achieve that?

View 7 Replies


Inserting Pictures And Resize To Active Cell Shape

Jun 23, 2003

I now have this code that inserts a picture and then resizes it the active cell shape.

Sub InsertPicture()

Dim myPicture As String

myPicture = Application.GetOpenFilename _
("Pictures (*.gif; *.jpg; *.bmp; *.tif),*.gif; *.jpg; *.bmp; *.tif", , "Select Picture to Import")

If myPicture "" Then

ActiveSheet.Pictures.Insert (myPicture)
With Selection

.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = ActiveCell.RowHeight
.ShapeRange.Width = ActiveCell.Width
.Placement = xlMoveAndSize
End With
End If

End Sub

View 9 Replies View Related

Add Position Shape To Cell Location

Jan 15, 2008

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

VBA To Move Shape From Current Cell Position To Cell 1 Row Up

Nov 11, 2008

I require a macro to enable a selected shape to be moved from current cell location to relative position but 1 row up. eg topleft address = A4 and shift shape to topleft address = A3

View 5 Replies View Related

Cut And Paste Shape Into Same Position

Aug 6, 2008

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

Change Shape Size / Keep Its Position?

Jul 10, 2014

I have an autoshape on an sheet, and use this code (it's a circle), to rezise it....

[Code] ....

However, it does rezise it, but the shape doesn't keep it's central position. Is there a bit of code I can add to stop the shape from moving? In other words, have the shape rezise around it's center of location?

View 4 Replies View Related

Find Position Of Rotated Shape?

May 7, 2012

I'm messing around with two shapes, a Cannon and Cannon Ball. With code the Cannon rotates for the angle of fire. What I can't do is position the Cannon Ball at the end of the Cannon when Cannon is at an angle. Long time since I was in school so forget most of my maths.

Sub IamUseless()
Rem this is work in progress and in NOT WORKING!!!!!!!
Dim xDistance As Double ' is inital x position of object


View 1 Replies View Related

Lock Shape Position On Screen

Feb 15, 2007

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

Determine Position Of Shape/Button On Worksheet

Jun 2, 2008

I have written a small piece of code that handles clicks on buttons on an excel spread sheet. When a button is pressed, i need to know the row number where the button resides in.

I have searched and read about this issue on several forums, and they all indicate that i need to use something like


to get the row number and


to get the column number.

However, when i use the row variant, it ALWAYS returns row number 1. When i use the column variant, it returns the column number it is actually in.

i have added the full code that is creating the buttons below, and also the part that displayes the rownumber that is incorrect.

Private Sub Workbook_Open()
Dim name
Dim time As Integer
time = InputBox("Typ the hour you are checking" & vbCrLf & "eg: 7, 10, 13, 14, 15, 16, 17, 18, 19", "Which Check")

View 9 Replies View Related

Shape Name Change Based On Cell Value Using VBA

Jul 22, 2014

I have a shape on one sheet. Need to change the name of a shapes depending on a value in cell from an other sheet.

View 6 Replies View Related

Change Shape Based On Cell Value

Jan 6, 2014

I'm tying to change a shape based on a cell value. I have this but it will not change fron green to red.

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A100").Value >= 0 And Range("A100").Value Range("A100").Value Then
ActiveSheet.Shapes("Oval 5").Fill.ForeColor.RGB = vbRed
End If

End Sub

I would like the shape to go red, yellow and green but I can't find anything that works.

View 1 Replies View Related

Formula For Cell Value Based On Reference Or Position

Jun 23, 2009

HTML Imagine the following table (Ax, etc is the cell position):

A1=John C1=Name D1=Date E1=Version F1=IP
A2=10/2/2008 C2=A1 D2=A2 .... ....
A3=Client version 7.01 C3=A1+5pos D3=A2+5pos
A4= C4=A1+10pos D4=A2+10pos

A8=Client version 7.03

A13=Client version 7.02

I have a table of data on the left side and I want to re-organize just like the right side. Let's take C2 as an example, the value for that cell = A1;
Now, C3 in the other hand, the value for that cell = A1 + 5 positions down;
C4 = A1 + 10 positions down;. The same type of formula applies for the remaining columns on the right. This seems like an easy solution, but it's not easy when you don't know it... :

View 4 Replies View Related

Excel 2010 :: VBA Code To Automatically Change Colour Shape Based On Cell Value

Mar 2, 2012

I have a peice of code that i know is inefficient and it is in danger of becoming too large. I have a spreadsheet that has circles aligned to each cell. There are around 100 in total. The code changes the colour of the shape based on the cell value in which it sits. However, the code needs changing and also it does not automatically update the colour shape even though the cell value changes. I have to manually select a cell and then the formula bar and then press return for it to update.

I am using excel 2010.

This is the code i am using for each shape.

If Range("n12").Value = text Then
ActiveSheet.Shapes("Oval 250").Fill.ForeColor.RGB = RGB(255, 255, 255)
End If


View 6 Replies View Related

Referencing A Cell Based On Position Of Another Cell

Jan 23, 2009

Right now cell B7 is referencing cell D3. However I want this to be dynamic in the sense that suppose I change the Phase 2 (which starts from ww3 (cell D3)) to ww5 (cell F3) then I would like that B7 should also get updated to F3 instead of D3.

Basically the Development phase of "Support" Project starts only when Phase 2 of "Project 1" starts.

ww = Work Week.

In short cell B7 should always be in sync with the ww of Phase 2 of Project 1.

View 3 Replies View Related

Position Cell In Top Left Position

Jan 7, 2009

Is it possible to position a predetermined cell (e.g. A42) in the top left corner of the screen. (Not every screen users use has the same size)

View 3 Replies View Related

Moving Shape Based On Cells Value?

Jun 27, 2014

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

Sub Worksheet()
If Sheets("Text").Range("z6") <> 0 Then
With ActiveSheet.Shapes("Rectangle 8").Fill


View 4 Replies View Related

Change Shape Text Without Selecting Shape

Mar 4, 2009

when i run the below code i get an error 438 'object doesnt support this property or method'

View 2 Replies View Related

Text In Circle Shape Based On VLOOKUP

Aug 9, 2013

I have this code and want to place text in shapes (circles).Text changes based on position in data table.


POINT_1 = Range("E" & j) * 7
NORTH = Range("E" & j) * 7

For j = 23 To 33

CIRCLE_TEXT = Range("A" & j)

[Code] ............

View 7 Replies View Related

Resize From Active Cell To Range?

Sep 27, 2009

how can I resize a from my active cell to a specific cell (eg. L1) and select all the cells in between? I am using this code to select the last cell after my data (active cell is in column A).

View 2 Replies View Related

Automatically Resize Row With Merged Cell?

Dec 21, 2011

Is there a way to automate the resizing of a row to accomodate the text that is pulled into a cell as the result of a formula?

I have a merged cell which receives the result of a DGET formula. The result can be as few as 6 lines of text or as many as 24 lines.

I suspect I need to count the number of lines in the result and resize based on that count multiplied by 15 points.

I don't know how to write the VBA but it would be kind of Resize Row.Countlines x 15 pts. and be executed by the event of calculating the formula from its default setting.

Practically speaking, the default setting is sized per the result of the desired code: there is a blank formatted merged cell that can be manually overwritten that is sized by the typical number of bullet points.

View 3 Replies View Related

Change Shape Color Based On Combo Box Selection

Aug 7, 2007

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

With Range("Aircraft1")

If .Value = 1 Then
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17

View 5 Replies View Related

Resize Cells To The Largest Word In Cell In VBA

Jul 16, 2014

In the attached workbook the classify macro reformats data. Can a line of code resize the cells to the largest word in the cell?


View 6 Replies View Related

SUMPRODUCT Of Columns Based On Matrix Position

Mar 20, 2009

I have created the following attachment to illustrate my problem. I have a square matrix -- say a 10x10 matrix (with data in B2:K11, and column headings on row 1, row headings on column A). I would like to get the sum of products of two columns in the result section below -- cells B14:K23, based on the position of the formula.

For example, cell B14 is the 1,1 item of the matrix so I would like to get the sumproduct of B2:B11 and B2:B11 (or SUMSQ(B2:B11) in this case); alternatively cell I16 is element 8,3 in the matrix so I need to get sumproduct of I4:I11 and D4:D11.
I tried the following formula using the OFFSET function (for cell B14 which is then copy/pasted throughout B14:K23)

View 5 Replies View Related

Looking Up Column Header Value Based On Color And Position In Row

Dec 12, 2009

I am trying to create a schedule using colors. The colors will indicate the start time, break time, lunch time, break time (second break) and end time. see attached document.

Thank you for attempting to solve this puzzle using formulas and or VBA code. I am trying to automate the schedule so the numerical times automatically appear when I "paint" the schedule.

View 5 Replies View Related

How To Identify Column Position Based On Lookup Value

Nov 10, 2011

I will be working with an array of data approx 500 rows by 50 columns (eg Sheet1!$A$1:$CV$500) in which I will need to check if approx 1000 unique values exist and in which column they appear in. Each unique value should not appear in more than 1 column. Once identified, I need to return the header value at the top of the relevant column.

My thoughts on approach would be perform a lookup of some sort to identify the column number and then use that within an index formula to return the header value based on the column number.

However, I'm not sure how to perform a lookup to get the column number. A match formula can, as far as I am aware, only lookup against a single column or single row.

View 3 Replies View Related

Fill Range Based On Button Position

Jul 3, 2007

I'm trying to find out how can I fill a range of cells when clicking in a Command button in Excel. The process should be:

1) The user opens my XLS worksheet.
2) Goes to the next empty row and fill some specific fields
3) Then he should fill up 15 parameters on the right as Y or N
4) In case all of them are correct instead of going one by one typing "Y" I want he clicks in a button (with VBA code associated) and then 15 cells on the right should be filled with "Y" value

View 8 Replies View Related

Return Number Based On Letter Position In Alphabet

Aug 27, 2007

i wish to number the cells bases on the cell on the right.

A Total
B Total
C Total
D Total


1A Total
2B Total
3C Total
4D Total

The values in place of A,B...can range between 1 to 6.

View 9 Replies View Related

Excel 2003 :: Renaming Textboxes Based On Their Position In Sheet?

Jun 14, 2013

I have an excel file (2003 version) with one sheet called sheet1.

On sheet1 I have multiple text boxes, however each text box has the same text box number "Text Box 1244" (this number appears in the top left-hand side in excel when I click the textbox).

I was wondering if it's possible to rename the text boxes based on their location on the sheet.

Eg. If i had a sheet with 5 rows of text boxes and 3 columns of text boxes (15 text boxes in total).

The top left-hand box gets renamed to "Text Box 1", then the text box below that gets renamed to "Text Box 2" and so on to the bottom of the sheet to "Text Box 5". Then the vba script would move to the text box that was to the right of the first text box (1st row again but 2nd column), and rename all the text boxes in that column ("Text Box 6" onward).

The outcome would look something like:
TB1 TB6 TB11
TB2 TB7 TB12
TB3 TB8 TB13
TB4 TB9 TB14
TB5 TB10 TB15

I'm stuck on this problem a while now and cannot find any scripts to solve it. My VBA is non-existent, I usually get by on bits of code I find on the web.

I hope each textbox has a hidden co-ordinate associated with it, then it might be possible to loop through all the textboxes based on their positions and rename them.

View 3 Replies View Related

Resize Visible Rows Based Only On Visible Columns Text

Apr 22, 2009

Need to correct code to resize all visible rows on a sheet based only on the text in the visible columns. I have tried the below code but when it resizes it is using the largest amount of text in the rows including that in the hidden columns.

View 3 Replies View Related

Change The Shape Of Cell

Jun 23, 2008

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

Copyrights 2005-15, All rights reserved