Move AutoShape in Sync with Date
 

I am looking for a way to move an AutoShape, automatically, to be in sync
with the current date. Basically, I have a GanttChart and Id like to see
the AutoShape moving to the right as time passes. Does anyone know of a good
sample of such a thing?

Thanks,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Move AutoShape in Sync with Date
 

You may be able to change the .left property of the shape, but it's tough to
know how much you want to move it based on the date. You'll have to figure
that out yourself.

"ryguy7272" wrote:

I am looking for a way to move an AutoShape, automatically, to be in sync
with the current date. Basically, I have a GanttChart and Id like to see
the AutoShape moving to the right as time passes. Does anyone know of a good
sample of such a thing?

Thanks,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Move AutoShape in Sync with Date
 

Your question isn't entirely clear, but the following may get you
going in the right direction. First, put some shape on the worksheet
and name it "TheShape". Then, name some cell "BaseDate" and put the
date from which the shape should be offset, e.g,. 1-Jan-2009. Then,
name another cell "CurrentDate". In that cell, put the value which,
when offset from BaseDate, indicates how far to the right the shape
should be moved. To set the vertical position of the shape, select a
cell in column A in the row in which you want TheShape to appear and
name that cell "ShapeAnchor". When moved, the top of the shape will be
equal to the top of the ShapeAnchor cell.

Now, right-click on the sheet tab, choose View Code, and paste the
following in the code module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim N As Long
Dim R As Range

If StrComp(Target.Address, Range("CurrentDate").Address, _
vbBinaryCompare) = 0 Then
N = Range("CurrentDate").Value - Range("BaseDate").Value
Set R = Range("ShapeAnchor").Offset(0, N)
With Me.Shapes("TheShape")
.Top = R.Top
.Left = R.Left
End With
End If
End Sub

Close the VBA editor and return to Excel.

As an example, assume that BaseDate is cell A3 and contains the date
1-Jan-2009. Next, put 8-Jan-2009 in the CurrentDate cell. The code
finds the difference between BaseDate and CurrentDate (7 days), and
moves TheShape such that the left edge of the shape is that many
columns to the right of BaseDate, in the same row as ShapeAnchor. If
BaseDate is 1-Jan and CurrentDate is 8-Jan, the left edge of TheShape
will be at the left edge of column H, 7 columns to the right of
BaseDate. The top of TheShape will be equal to the top of the cell
named "ShapeAnchor".

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sun, 16 Aug 2009 16:58:02 -0700, ryguy7272
wrote:

I am looking for a way to move an AutoShape, automatically, to be in sync
with the current date. Basically, I have a GanttChart and I’d like to see
the AutoShape moving to the right as time passes. Does anyone know of a good
sample of such a thing?

Thanks,
Ryan--

Move AutoShape in Sync with Date
 

Perfect!!! Thanks for the help Chip!
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Chip Pearson" wrote:

Your question isn't entirely clear, but the following may get you
going in the right direction. First, put some shape on the worksheet
and name it "TheShape". Then, name some cell "BaseDate" and put the
date from which the shape should be offset, e.g,. 1-Jan-2009. Then,
name another cell "CurrentDate". In that cell, put the value which,
when offset from BaseDate, indicates how far to the right the shape
should be moved. To set the vertical position of the shape, select a
cell in column A in the row in which you want TheShape to appear and
name that cell "ShapeAnchor". When moved, the top of the shape will be
equal to the top of the ShapeAnchor cell.

Now, right-click on the sheet tab, choose View Code, and paste the
following in the code module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim N As Long
Dim R As Range

If StrComp(Target.Address, Range("CurrentDate").Address, _
vbBinaryCompare) = 0 Then
N = Range("CurrentDate").Value - Range("BaseDate").Value
Set R = Range("ShapeAnchor").Offset(0, N)
With Me.Shapes("TheShape")
.Top = R.Top
.Left = R.Left
End With
End If
End Sub

Close the VBA editor and return to Excel.

As an example, assume that BaseDate is cell A3 and contains the date
1-Jan-2009. Next, put 8-Jan-2009 in the CurrentDate cell. The code
finds the difference between BaseDate and CurrentDate (7 days), and
moves TheShape such that the left edge of the shape is that many
columns to the right of BaseDate, in the same row as ShapeAnchor. If
BaseDate is 1-Jan and CurrentDate is 8-Jan, the left edge of TheShape
will be at the left edge of column H, 7 columns to the right of
BaseDate. The top of TheShape will be equal to the top of the cell
named "ShapeAnchor".

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sun, 16 Aug 2009 16:58:02 -0700, ryguy7272
wrote:

I am looking for a way to move an AutoShape, automatically, to be in sync
with the current date. Basically, I have a GanttChart and Id like to see
the AutoShape moving to the right as time passes. Does anyone know of a good
sample of such a thing?

Thanks,
Ryan--