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 Repliesi 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
I want to resize a shape and set the height to a specific cell. How can I achieve that?
View 7 Replies View RelatedI need the cell address where the cursor is (in a non active worksheet).
View 5 Replies View RelatedI'm having some difficulties in defining my formula. I'm pretty sure that this should be possible.
I have a table containing values and dates. For each change in dates, I should summarize every value for that date. I cannot use the autosummarize function, because I do not want the sum to be beneath the values, rather in a column to the right of the values.
I have created a formula which checks for a change in date: =IF(C15=C16;0;1) where the C column contains the dates. Basically I say that the Index column should contain a 0 if the date is the same as previously registered, 1 if it is a new date.
The index column is used in some conditional formatting rules, so I would like to keep it anyway.
trying to find the value of column A (date field) if it exists on the row that the cursor is presently on. I'm trying to check for the value before performing the next function to make sure the user is on a valid row. Cursor could be on any column A to F. Columns are always 6 but the rows can vary.
View 7 Replies View RelatedI'm trying to add a textbox at the current position (selected cell) with a set size, fill color, and border color. I found this: http://msdn.microsoft.com/en-us/libr...8(VS.80).aspx:
View 2 Replies View RelatedI 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 RelatedI 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?
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.
Code:
Sub IamUseless()
Rem this is work in progress and in NOT WORKING!!!!!!!
Dim xDistance As Double ' is inital x position of object
[Code].....
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.
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
application.caller.topleftcell.row
to get the row number and
application.caller.topleftcell.column
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")
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 RelatedI have a series of shapes (circles) that I wish to use as a substitute to the radio buttons found in the form controls (too small).
I've drawn the shapes, but wish to manipulate their properties in VBA. For example, when the user clicks on the shape, the macro includes VBA which will change the properties to include colour and fill. Of course, clicking it again, will send it back to the default.
How can I get the VBA of the current shape's (default) properties? Where can I find a list of the VBA properties available to any particular shape so I know what fields are available to set attributes to?
I Have several images on Sheet 1, I can copy the needed Images to sheet 2 as needed.
Once the image is pasted to sheet2 how do I assign it to a shape variable so that I can position it as desired.
I do not want to have to use Sheet2.(Range("B2").activate or similar
I'm looking to use
Shp.top = 25
Shp.left =15
Code:
Sub Macro1()
Dim Shp As Shape
Sheet1.Shapes("Picture 1").Copy
Sheet2.Paste
'To set the shape variable to the shape on sheet2
Set Shp = "WHAT GOES HERE"
Shp.Top = 25
Shp.Left = 15
End Sub
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)?
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
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
The user has multiple Excel documents available [which all contain this macro, as they were generated from the same template by a third-party software (LabView)].
The user is already working on a Word document, typing text until he decides that he wants to add some information from one of the Excel sheets. To do this, he changes to Excel, clicks on the button running the script, and the required information is pasted into the Word document at the present cursor position.
I have a tracking sheet that is used to show where a specific project is within the lifecycle and would like to automatically set a summary value depending on the last data entry within a range of cells in a row and also set it to RAG status depending on the value.....
View 2 Replies View RelatedI need to make a cell display the contents of the cell immediately below it, regardless of if there are rows inserted at the referenced row or above or below it on the worksheet. ALSO I need to do this at several additional cell locations down the worksheet. Such as : A1 displays A2, A4 displays A5, A7 displays A8 etc.
Example: I want A1 to display whatever is in the cell immediately below it. In this case A2. I need to be able to insert several rows at row 2 and have whatever new value is now in the A2 position displayed in A1.
After the rows are inserted I need the cell that was in position A4 to continue to display the contents of the cell below it.
I tried using the formula in A1 of: =INDIRECT("A2") and it works good except when I insert the rows my similar formulas below the area where the rows were inserted continue to refer to the specific original cell and not the one positioned now below it.
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.
Doing a search function where the cursor is on a row some where (unknown).
I would like to move the cursor to the first column of the spreadsheet.
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.
I have a spreadsheet with two columns and 39900 rows, from that data I want to create two new columns so cell C1 = A143 and D1 = B143, C2=A286, D1=B286, and so on. The row location increases by 143.
The simplest thing I tried to do was to say C1=A1+142, but (knowing it is wrong) I get a #value error.
How can I tell excel to grab these values located every 144 rows below and create two new columns?
When I use the mouse pointer to select a cell I can't use the arrow keys to move to another cell while the pointer is over the cell and I can't edit the cell while the pointer is over the cell. If I move the pointer away from the cell then I can move around and edit as normal therefore I don't think this is a scroll lock issue.
This issue also happens when I select a tab. If I select a tab and then leave the pointer over the tab I selected then I can't use the arrow keys to move around the worksheet or edit a cell; if I move the pointer away from the cell then I can move around and edit as normal.
I am using MS Excel 2010.
how can i define, in vba that after click on button in sheet1, it looks at value in cell B1 in this sheet and move my cursor exactly on this value in sheet2 where i have data in range (B4:C6000).
View 2 Replies View RelatedMy OS is Windows 7. My version of Excel is 2007. I have read how to get a macro to move around a page in a relative way, but is there a way to move from page to page in a relative way as well?
View 5 Replies View RelatedThe 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
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?
I would like to know if there is a way to expand the selection of cells from the current cell you are on to the very bottom cell available in the spreadsheet.
For example, I want to apply a formula from A12 - the very last A cell row in the spreadsheet.