MrExcel.com - Your One Stop for Excel Tips & Solutions

Linking input cells of macros to other pages of workbook.

Posted by STEVE on February 04, 2001 9:27 AM

I need some help with some VB code. I wrote a function that changes the size and color of about 30 autoshapes. The problem is that I cannot link the input cells of the function to another page, I keep getting a #value error. I finnally figuired out that I could write another macro to copy data from another page and paste it on the input cells, but it takes a while for it to run. Any one have any ideas!

Posted by Dave Hawley on February 04, 2001 5:21 PM


Hi Steve

If you could post your code here, or E-mail me I'd be happy to modify it for you.

Dave

OzGrid Business Applications

Posted by steve on February 04, 2001 11:23 PM

here's a sample of my code

Thanks again dave, I emailed you the whole code.


Function headrail(size1, size2, size3, size4, size5, size6, size7, size8, size9, size10) As Single


Select Case size1
Case True

ActiveSheet.Shapes("AutoShape 352").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 9
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 8
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 6#
Selection.ShapeRange.Width = 7.5
Selection.ShapeRange.Rotation = 0#

ActiveSheet.Shapes("AutoShape 353").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 9
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 8
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 6#
Selection.ShapeRange.Width = 7.5
Selection.ShapeRange.Rotation = 0#

ActiveSheet.Shapes("AutoShape 354").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 9
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 8
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 6#
Selection.ShapeRange.Width = 7.5
Selection.ShapeRange.Rotation = 0#


Case False

ActiveSheet.Shapes("AutoShape 352").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 9
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 9
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 0#
Selection.ShapeRange.Width = 0#
Selection.ShapeRange.Rotation = 0#

ActiveSheet.Shapes("AutoShape 353").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 9
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 9
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 0#
Selection.ShapeRange.Width = 0#
Selection.ShapeRange.Rotation = 0#

ActiveSheet.Shapes("AutoShape 354").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 9
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 9
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 0#
Selection.ShapeRange.Width = 0#
Selection.ShapeRange.Rotation = 0#
End Select


End Function

Posted by Dave Hawley on February 05, 2001 2:32 AM

Thanks again dave, I emailed you the whole code. ActiveSheet.Shapes("AutoShape 352").Select

End Function

Steve you could probably modify each Case statement in your code to something like below:

With Sheet1.Shapes("AutoShape 352")
.Fill.Visible = msoTrue
.Fill.Solid
.Fill.ForeColor.SchemeColor = 9
.Fill.Transparency = 0#
.Line.Weight = 0.75
.Line.DashStyle = msoLineSolid
.Line.Style = msoLineSingle
.Line.Transparency = 0#
.Line.Visible = msoTrue
.Line.ForeColor.SchemeColor = 8
.Line.BackColor.RGB = RGB(255, 255, 255)
.LockAspectRatio = msoFalse
.Height = 6#
.Width = 7.5
.Rotation = 0#
End With


Where "Sheet1" is the code name of the sheet containing the Shape. You can see a Sheets code name in the Project explorer (Ctrl+R) while in the VBE.


Dave

OzGrid Business Applications