Cannot Set Shape With Names From Cell Values

Mar 15, 2014

I have a couple of hundred string values in column A. I am trying to create the same number of rectangle shapes in column B, each one taking its name from the corresponding cell in column A. I keep hitting the dreaded 400 error code .

Attached File : test add shapes.xlsm

View 3 Replies


Remove Names Without Values From A List Of Names And Values

Mar 1, 2013

I have the list below and would like to create a new list which contains only names with corresponding values and lists them.

+ A B
1 James 3
2 Derek
3 Brett 6
4 Allan
5 Jess 7
6 Sam 10
7 frank 10

The solution should look like:

+ A B
1 James 3
2 Brett 6
3 Jess 7
4 Sam 10
5 frank 10

View 2 Replies View Related

Cell Values Used In Sheet Names

Mar 29, 2007

I am trying to allow for a cell selection to initiate a macro that would take the top cell in the current column and most left cell in the current row and placing those values into the names of the workbook and sheet that I want to move to.

I am tyring to allow for my users to drill down into data via selecting the cell in question that is referenced from another workbook and sheet. I want to place the value of the cell

= most left cell in current row - 1-1 to be placed in Active.sheet"1-1".select The work bood would pull the value from the most top cell in the current column and place that value in

View 4 Replies View Related

Sort Table (Names And Values) From Cell

Jun 7, 2014

I am keeping a spreadsheet for an auction

We have 20 people and each person gets a total bid for 48 items.

if person1 = 55,person2 = 100,person3 = 76,person4 = 67,person5 = 49 etc.

The person's names will be in column C while values in D in cell G3 will hold the total bid for the items each week updating.

What I am trying to do is sort the table (names & values) from cell G3.

So if each week G3 total gets bigger then the table sorts to match.

If the overall total bid after 48 weeks (G3) was 52 then the table sorts with 55 on top (because it's closer) 49 next.

The closer the person's bid to the total bid the further up the table the person will be.

I have uploaded an example with before and after sample.BidExample.xlsx

View 8 Replies View Related

How To Make Tab Names Dependent On Cell Values

Oct 17, 2013

Is it possible to make tab names dependent on Cell Values?

I have 12 Tabs in Workbook 1

Tab 1 = "COVER PAGE"

I would like Tab 2 ='COVER PAGE'!A1

Tab 3 ='COVER PAGE'!B1
Tab 4 ='COVER PAGE'!C1

Is this possible so that I can add cells (between B1 and C1) at a later date and have all the tab names change depending on the values I put in the cells?

View 9 Replies View Related

Define Names As Adjacent Cell Values

Dec 18, 2007

I would Like to define each row in a spreadsheet to the value of the first cell in each row and I have no idea how to go about this.

Code Customer
_0888 Williams
_0435 Smith

So basiclally I would like to name the first row "_0888" being 2:2. But without doing each row manually via Insert --> Names --> Define..

View 4 Replies View Related

Add Shape & Textbox Values For Coordinates

Jan 1, 2007

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

Pass Values From Cells To Shape

Nov 30, 2007

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. " & _

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

Assign Group Names Based On Cell Values

Feb 19, 2010

I have two tables below. First table contains the list of domain names (given just for example, actual list may contain several more such Domain names) with the group names assigned to them. Second table shows the criteria for assigning them the group names:


My concern is to automate this process using a VBA code. How can I search for the keyword given in Table 2 in the first column of Table 1 and assign the group names as it is assigned currently, considering the below conditions and criteria.

1) The correct group name should be assigned to the Domain name containing the keyword given as per criteria in Table 2

2) It is possible that the domain name will include more than one keywords given in Table 2. For example Cell number A7 contains both keywords NCG and EXC, in such cases the preferance should be given to EXC and we will assign the group name as EEC. And in Cell number A12 also contains both NCG and DMZ, again the preferance should be given to DMZ and we have to assign group name as ETIS.

That means we will give second preference to keyword NCG. If domain name contains none of the keywords except NCG as per given criteria then only we will assign grup name DOSS.

View 9 Replies View Related

Control Chart Shape Colors By Values?

May 27, 2007

Is there a way to make the colours in a chart (pie, column etc) correspond to a value? I'm using Excel 2002, soon to start using 2003.

For example, imagine a normal pie chart showing the population of each country in North, Central and South America. But for each country, colour gradations would be used to show levels of wealth (e.g., GDP per capita) as follows:

saturated red = bottom 20% (poorest)
pink = 21-40th percentile,
white = 41-60%,
gray = 61-80%,
black = 81-100% (richest)

Is this possible? And not to get too greedy, but can you use even finer gradations -- say, with ten categories, as opposed to the five in the example above?

View 9 Replies View Related

Assigning Label Names Based On A Range Of Cell Values

Feb 20, 2007

I searched and found that to assign a name to a label based on a cell value requires the following

Label1.Caption = Worksheets("Sheet1").Range("A1").Value

which would assign the value in cell A1 as the caption for Label1. I've got a range of values in cells:


and I'd like to assign them as names to labels 1 through 12. How would I do that using VBA?

View 6 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

Basic Cell Referencing - Return Correct Names And Values On Another Sheet?

May 13, 2013

Here's the data table being referenced

Week Ending


[Code] ........

Now on another sheet, I want to return the top two 'Name's and their values like below:


There is a fluctuating amount of rows in the first table, too. So what formula can I use to return the correct names and values on another sheet?

I'm thinking it will use some form of concatenate for the first and use a sumifs function for the value column..

View 1 Replies View Related

List Files & Cell Values With Specific Sheet Names From ListBox

Dec 4, 2008

I have this script (below, Krishnakumar orignally provided this script).

I'm trying to edit it to add cell values from cells C16 and E16 along with the worksheet name it displays in listbox2. So lets say worksheet "rollover" is the sheet being displayed in the listbox2.

I would like for it to look something like this:

rollover TT:'data from cell C16' TA:'data from cell E16'

Public FilePath As String
Public dic As Object
Public oWB As String
Public oWS As String
Public aWS As Worksheet

Private Sub CommandButton1_Click()
Dim i As Long, wb As Workbook, n As Long
With Me.ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
oWS = .list(i)
Set wb = Workbooks.Open(FilePath & oWB, UpdateLinks:=0)
Exit For
End If
End With
End Sub

View 7 Replies View Related

Fill ListBox With Found Values Sheet Names & Cell Addresses

Feb 22, 2008

I have a spreadsheet containing a list of key fob numbers, key numbers, etc. I have 3 userforms with different for different options, i.e. Key fob no. search, key number search, room number search. I have set up the code for each of the userforms to search for the information entered in the textbox and then lists the info in the listbox which I can then click on the one of the listed items to take me directly to the place in the spreadsheet.

However since there are a range of different key numbers per key fob, I would like to know what code I can use to allow a range of information such as the key number and room number, etc to be displayed in the same listbox as the Key fob number i searched for.

Here is my code for one of the Userforms:

Option Explicit

Sub Locate(Name As String, Data As Range)

Dim rngFind As Range
Dim strFirstFind As String

With Sheet1.UsedRange
Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart)
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address

I cannot attach a example of my form since the site seems to have a limit on the size of the upload and my part of the file compressed in zip format is still at 168kb.

View 5 Replies View Related

Table Names Revert To Cell Names When File Is Reopened

Jan 4, 2013

I have a worksheet with many tables that I use in formulas.

I like tables for a couple reasons, one being the ability to insert/delete rows without affecting the rest of that worksheet row.

Also, automatic copy of formulas/formatting is great.

But, what I really like about tables is the ability to use the naming conventions in formulas.

Problem is when I save this worksheet, after I close it and open it back up, all table references in my formulas have been converted to cell references.



=IF((SUMIF('Quote 1'!$M$28:$M$43,">"&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&tblOSSRV[Min Order Cost])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],""&tblOSSRV[Min Parts Per Line])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],"

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

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

Getting Linked Cell Shape Property

Dec 8, 2012

With the following code I can get shape properties, but I can get it for the linkedcell of the shape.

Sub GetShapePropertiesSomeWs()
Dim sShapes As Shape, lLoop As Long
Dim WsNew As Worksheet
Dim wsLoop As Worksheet

Set WsNew = Sheets.Add

[Code] ....

View 1 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

Select Shape In Active Cell

Mar 30, 2014

I am trying to select a shape in a cell and place a cell reference into the shape. I know the cell address, but do not know the shape reference. (The row that the cell and shape reside came from copying the row from another sheet and inserting it into the current sheet). I can not seem to come up with the correct code to accomplish this. Right now I have:

Cells(r + 1, 48).Select
ActiveShapes.Formula = "$AW:" & r + 1

Where r+1 is the row where the line finally resides after insertion and "AW" is the column to the right of the cell where the shape resides.

I have tried to put the cell reference into the shape at the location where it is copied from but the reference is not dynamic and I can not seem to get it to be. Another issue that will arise is that the rows in the new sheet will probably be sorted and I question if the cell reference will follow the shape's new location.

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

Shape Size Dependent On Cell Value

Jun 14, 2007

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 dont think Im using the right terminology in my searches,

View 9 Replies View Related

Link Cell To Picture Or Shape

Oct 18, 2007

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

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

Hyperlink From A Shape To A Cell On Seperate Worksheets

Feb 10, 2010

I had getting a hyperlink from a Shape to a Cell on seperate worksheets and the macro works fine:

View 4 Replies View Related

Add Shape On Cell Formula Calculation Value Change

May 28, 2008

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

Locate A Shape Within A Shape

Jan 24, 2014

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‎

View 5 Replies View Related

Change Shape Fill To Picture When Another Cell Equals To 1

May 14, 2014

I have set up a rollover hyperlink so that when I hover over the cell it changes colour and creates a good effect. To do this I have another cell on the sheet that turns to 1 when the cell in question is hovered over.

I need a code so that when that cell displays the number 1. the object (which is rectangle 2) changes from white fill. to a picture I have saved on my desktop.

I cant quite figure out the coding and have been messing about with things like..

if cell range e.g a1 = 1 then rectangle 2 .userpicture " path " else .solid etc.

View 10 Replies View Related

Copyrights 2005-15, All rights reserved