I have a graph paper template. I would like to draw a rectangle and have
Excel figure out square feet and other measurments from the drawing. Is there
a way to build a formula to see what I am drawing and take it from there. An
example would be say I have a 10 x 22 foot room and I need to fiure out how
many 2'x4' ceiling panels are required to do the ceiling. Along with other
factors that are needed for the work. This seems like a valuable tool to
build and I can't see how to start.
Any ideas?

As a starting point...

1. Clearly define your template (use borders)
2. Select the cells that make up the template & name the range "rangeROOM"
3. Set drawing "Snap" mode to Snap To Grid
4. Draw a clear rectangle the EXACT size of the template cells
5. Set the rectangle order "Send to Back"
6. Name the rectangle "rectROOM"

Add the following macro...

Sub ItemSize()
Const GridItemWide As Integer = 12 ' inches
Const GridItemHigh As Integer = 12 ' inches

Dim shpSelection As ShapeRange
Dim shpRoom As Shape
Dim rngRoom As Range
Dim w, h, rw, rh, rwf, rhf

Set shpSelection = Selection.ShapeRange
Set shpRoom = Sheet1.Shapes("rectROOM")
Set rngRoom = Sheet1.Range("rangeROOM")

rw = rngRoom.Columns.Count * GridItemWide
rh = rngRoom.Rows.Count * GridItemHigh

rwf = rw / shpRoom.Width
rhf = rh / Sheet1.Shapes("rectROOM").Height

w = shpSelection.Width * rwf
h = shpSelection.Height * rhf

MsgBox "Item H=" & h & " W=" & w
End Sub



now draw your shapes. Select them 1 at a time & then run this macro.

NOTES:
I have not added error handling / type checking. (you will get errors if you dont select shape)
It has fixed grid size (12).
It is not user friendly having to run macros & get results in a msgbox.

TIPS:
you could list all shapes in a list box & use that to measure the items
(i.e. the listbox lists all the shapes. When you click one, the ItemSize macro is called)

Sub ListShapes()
Dim shr As Shape
ListBox1.Clear
For Each shr In Sheet1.Shapes
If shr.Name < "ListBox1" Then
ListBox1.AddItem shr.Name
End If
Next
End Sub

Private Sub ListBox1_Click()
Sheet1.Shapes(ListBox1.Text).Select
Call ItemSize()
End Sub


Suggestions:
Put calculated Width & Height into cells on the sheet rather than msgbox.
Put grid size into named cells & use them instead of constants
display room size also (rh, rw) on sheet - for reference.


Good luck & hope this helps. Steve.



"Paul" wrote in message ...
I have a graph paper template. I would like to draw a rectangle and have
Excel figure out square feet and other measurments from the drawing. Is there
a way to build a formula to see what I am drawing and take it from there. An
example would be say I have a 10 x 22 foot room and I need to fiure out how
many 2'x4' ceiling panels are required to do the ceiling. Along with other
factors that are needed for the work. This seems like a valuable tool to
build and I can't see how to start.
Any ideas?

wouldnt it be easier just to have input cells for the width and length of the
room with a rectangle drawn as a representation.....you could draw several
different shapes to represent L or[] shaped rooms,with optional inputs for
areas to be excluded say for columns kitchen cabinets etc....
sorry if i have spoiled your fun...:)
I use a similar method to estimate rafters in hip end roofs
--
paul
remove nospam for email addy!



"Stevie_mac" wrote:

As a starting point...

1. Clearly define your template (use borders)
2. Select the cells that make up the template & name the range "rangeROOM"
3. Set drawing "Snap" mode to Snap To Grid
4. Draw a clear rectangle the EXACT size of the template cells
5. Set the rectangle order "Send to Back"
6. Name the rectangle "rectROOM"

Add the following macro...

Sub ItemSize()
Const GridItemWide As Integer = 12 ' inches
Const GridItemHigh As Integer = 12 ' inches

Dim shpSelection As ShapeRange
Dim shpRoom As Shape
Dim rngRoom As Range
Dim w, h, rw, rh, rwf, rhf

Set shpSelection = Selection.ShapeRange
Set shpRoom = Sheet1.Shapes("rectROOM")
Set rngRoom = Sheet1.Range("rangeROOM")

rw = rngRoom.Columns.Count * GridItemWide
rh = rngRoom.Rows.Count * GridItemHigh

rwf = rw / shpRoom.Width
rhf = rh / Sheet1.Shapes("rectROOM").Height

w = shpSelection.Width * rwf
h = shpSelection.Height * rhf

MsgBox "Item H=" & h & " W=" & w
End Sub



now draw your shapes. Select them 1 at a time & then run this macro.

NOTES:
I have not added error handling / type checking. (you will get errors if you dont select shape)
It has fixed grid size (12).
It is not user friendly having to run macros & get results in a msgbox.

TIPS:
you could list all shapes in a list box & use that to measure the items
(i.e. the listbox lists all the shapes. When you click one, the ItemSize macro is called)

Sub ListShapes()
Dim shr As Shape
ListBox1.Clear
For Each shr In Sheet1.Shapes
If shr.Name < "ListBox1" Then
ListBox1.AddItem shr.Name
End If
Next
End Sub

Private Sub ListBox1_Click()
Sheet1.Shapes(ListBox1.Text).Select
Call ItemSize()
End Sub


Suggestions:
Put calculated Width & Height into cells on the sheet rather than msgbox.
Put grid size into named cells & use them instead of constants
display room size also (rh, rw) on sheet - for reference.


Good luck & hope this helps. Steve.



"Paul" wrote in message ...
I have a graph paper template. I would like to draw a rectangle and have
Excel figure out square feet and other measurments from the drawing. Is there
a way to build a formula to see what I am drawing and take it from there. An
example would be say I have a 10 x 22 foot room and I need to fiure out how
many 2'x4' ceiling panels are required to do the ceiling. Along with other
factors that are needed for the work. This seems like a valuable tool to
build and I can't see how to start.
Any ideas?

Steve,
This sounds very complicated. But if it works it would be a start. I will
play with it and see how it works. Thank you.

paul,
I like your idea also it seems less complex but I am not capable of
writing the formulas to make it happen. Can you be more specific?
Thanks.
Paul

"paul" wrote:

wouldnt it be easier just to have input cells for the width and length of the
room with a rectangle drawn as a representation.....you could draw several
different shapes to represent L or[] shaped rooms,with optional inputs for
areas to be excluded say for columns kitchen cabinets etc....
sorry if i have spoiled your fun...:)
I use a similar method to estimate rafters in hip end roofs
--
paul
remove nospam for email addy!



"Stevie_mac" wrote:

As a starting point...

1. Clearly define your template (use borders)
2. Select the cells that make up the template & name the range "rangeROOM"
3. Set drawing "Snap" mode to Snap To Grid
4. Draw a clear rectangle the EXACT size of the template cells
5. Set the rectangle order "Send to Back"
6. Name the rectangle "rectROOM"

Add the following macro...

Sub ItemSize()
Const GridItemWide As Integer = 12 ' inches
Const GridItemHigh As Integer = 12 ' inches

Dim shpSelection As ShapeRange
Dim shpRoom As Shape
Dim rngRoom As Range
Dim w, h, rw, rh, rwf, rhf

Set shpSelection = Selection.ShapeRange
Set shpRoom = Sheet1.Shapes("rectROOM")
Set rngRoom = Sheet1.Range("rangeROOM")

rw = rngRoom.Columns.Count * GridItemWide
rh = rngRoom.Rows.Count * GridItemHigh

rwf = rw / shpRoom.Width
rhf = rh / Sheet1.Shapes("rectROOM").Height

w = shpSelection.Width * rwf
h = shpSelection.Height * rhf

MsgBox "Item H=" & h & " W=" & w
End Sub



now draw your shapes. Select them 1 at a time & then run this macro.

NOTES:
I have not added error handling / type checking. (you will get errors if you dont select shape)
It has fixed grid size (12).
It is not user friendly having to run macros & get results in a msgbox.

TIPS:
you could list all shapes in a list box & use that to measure the items
(i.e. the listbox lists all the shapes. When you click one, the ItemSize macro is called)

Sub ListShapes()
Dim shr As Shape
ListBox1.Clear
For Each shr In Sheet1.Shapes
If shr.Name < "ListBox1" Then
ListBox1.AddItem shr.Name
End If
Next
End Sub

Private Sub ListBox1_Click()
Sheet1.Shapes(ListBox1.Text).Select
Call ItemSize()
End Sub


Suggestions:
Put calculated Width & Height into cells on the sheet rather than msgbox.
Put grid size into named cells & use them instead of constants
display room size also (rh, rw) on sheet - for reference.


Good luck & hope this helps. Steve.



"Paul" wrote in message ...
I have a graph paper template. I would like to draw a rectangle and have
Excel figure out square feet and other measurments from the drawing. Is there
a way to build a formula to see what I am drawing and take it from there. An
example would be say I have a 10 x 22 foot room and I need to fiure out how
many 2'x4' ceiling panels are required to do the ceiling. Along with other
factors that are needed for the work. This seems like a valuable tool to
build and I can't see how to start.
Any ideas?

________
l l
l l W=10say cell G10
l ________l
L=22say cell N5
ok above is a representative rectangle L is the length w is the width
area in sq feet is L*W=220 =G10*N5
if we say we use a 2 foot grid each way then N5/2= 11,G10/2=5 11*5=55 half
tiles
=55/2 =27.5 tiles,we can use the same sort of thing for labour,to work out
the perimeter of the room for new cornice,the quantity of timber required for
new battens etc etc
paul
remove nospam for email addy!



"Paulmin" wrote:

Steve,
This sounds very complicated. But if it works it would be a start. I will
play with it and see how it works. Thank you.

paul,
I like your idea also it seems less complex but I am not capable of
writing the formulas to make it happen. Can you be more specific?
Thanks.
Paul

"paul" wrote:

wouldnt it be easier just to have input cells for the width and length of the
room with a rectangle drawn as a representation.....you could draw several
different shapes to represent L or[] shaped rooms,with optional inputs for
areas to be excluded say for columns kitchen cabinets etc....
sorry if i have spoiled your fun...:)
I use a similar method to estimate rafters in hip end roofs
--
paul
remove nospam for email addy!



"Stevie_mac" wrote:

As a starting point...

1. Clearly define your template (use borders)
2. Select the cells that make up the template & name the range "rangeROOM"
3. Set drawing "Snap" mode to Snap To Grid
4. Draw a clear rectangle the EXACT size of the template cells
5. Set the rectangle order "Send to Back"
6. Name the rectangle "rectROOM"

Add the following macro...

Sub ItemSize()
Const GridItemWide As Integer = 12 ' inches
Const GridItemHigh As Integer = 12 ' inches

Dim shpSelection As ShapeRange
Dim shpRoom As Shape
Dim rngRoom As Range
Dim w, h, rw, rh, rwf, rhf

Set shpSelection = Selection.ShapeRange
Set shpRoom = Sheet1.Shapes("rectROOM")
Set rngRoom = Sheet1.Range("rangeROOM")

rw = rngRoom.Columns.Count * GridItemWide
rh = rngRoom.Rows.Count * GridItemHigh

rwf = rw / shpRoom.Width
rhf = rh / Sheet1.Shapes("rectROOM").Height

w = shpSelection.Width * rwf
h = shpSelection.Height * rhf

MsgBox "Item H=" & h & " W=" & w
End Sub



now draw your shapes. Select them 1 at a time & then run this macro.

NOTES:
I have not added error handling / type checking. (you will get errors if you dont select shape)
It has fixed grid size (12).
It is not user friendly having to run macros & get results in a msgbox.

TIPS:
you could list all shapes in a list box & use that to measure the items
(i.e. the listbox lists all the shapes. When you click one, the ItemSize macro is called)

Sub ListShapes()
Dim shr As Shape
ListBox1.Clear
For Each shr In Sheet1.Shapes
If shr.Name < "ListBox1" Then
ListBox1.AddItem shr.Name
End If
Next
End Sub

Private Sub ListBox1_Click()
Sheet1.Shapes(ListBox1.Text).Select
Call ItemSize()
End Sub


Suggestions:
Put calculated Width & Height into cells on the sheet rather than msgbox.
Put grid size into named cells & use them instead of constants
display room size also (rh, rw) on sheet - for reference.


Good luck & hope this helps. Steve.



"Paul" wrote in message ...
I have a graph paper template. I would like to draw a rectangle and have
Excel figure out square feet and other measurments from the drawing. Is there
a way to build a formula to see what I am drawing and take it from there. An
example would be say I have a 10 x 22 foot room and I need to fiure out how
many 2'x4' ceiling panels are required to do the ceiling. Along with other
factors that are needed for the work. This seems like a valuable tool to
build and I can't see how to start.
Any ideas?

paul,
Where do I put these W=10say cel G10 and L=22say cell N5? Are these
formulas that would be placed in the cells G10 and L22? If I had to manually
add them to cells it would seem to defeat the purpose.
I'm sure it's just that I don't understand.

Paul

"paul" wrote:

________
l l
l l W=10say cell G10
l ________l
L=22say cell N5
ok above is a representative rectangle L is the length w is the width
area in sq feet is L*W=220 =G10*N5
if we say we use a 2 foot grid each way then N5/2= 11,G10/2=5 11*5=55 half
tiles
=55/2 =27.5 tiles,we can use the same sort of thing for labour,to work out
the perimeter of the room for new cornice,the quantity of timber required for
new battens etc etc
paul
remove nospam for email addy!



"Paulmin" wrote:

Steve,
This sounds very complicated. But if it works it would be a start. I will
play with it and see how it works. Thank you.

paul,
I like your idea also it seems less complex but I am not capable of
writing the formulas to make it happen. Can you be more specific?
Thanks.
Paul

"paul" wrote:

wouldnt it be easier just to have input cells for the width and length of the
room with a rectangle drawn as a representation.....you could draw several
different shapes to represent L or[] shaped rooms,with optional inputs for
areas to be excluded say for columns kitchen cabinets etc....
sorry if i have spoiled your fun...:)
I use a similar method to estimate rafters in hip end roofs
--
paul
remove nospam for email addy!



"Stevie_mac" wrote:

As a starting point...

1. Clearly define your template (use borders)
2. Select the cells that make up the template & name the range "rangeROOM"
3. Set drawing "Snap" mode to Snap To Grid
4. Draw a clear rectangle the EXACT size of the template cells
5. Set the rectangle order "Send to Back"
6. Name the rectangle "rectROOM"

Add the following macro...

Sub ItemSize()
Const GridItemWide As Integer = 12 ' inches
Const GridItemHigh As Integer = 12 ' inches

Dim shpSelection As ShapeRange
Dim shpRoom As Shape
Dim rngRoom As Range
Dim w, h, rw, rh, rwf, rhf

Set shpSelection = Selection.ShapeRange
Set shpRoom = Sheet1.Shapes("rectROOM")
Set rngRoom = Sheet1.Range("rangeROOM")

rw = rngRoom.Columns.Count * GridItemWide
rh = rngRoom.Rows.Count * GridItemHigh

rwf = rw / shpRoom.Width
rhf = rh / Sheet1.Shapes("rectROOM").Height

w = shpSelection.Width * rwf
h = shpSelection.Height * rhf

MsgBox "Item H=" & h & " W=" & w
End Sub



now draw your shapes. Select them 1 at a time & then run this macro.

NOTES:
I have not added error handling / type checking. (you will get errors if you dont select shape)
It has fixed grid size (12).
It is not user friendly having to run macros & get results in a msgbox.

TIPS:
you could list all shapes in a list box & use that to measure the items
(i.e. the listbox lists all the shapes. When you click one, the ItemSize macro is called)

Sub ListShapes()
Dim shr As Shape
ListBox1.Clear
For Each shr In Sheet1.Shapes
If shr.Name < "ListBox1" Then
ListBox1.AddItem shr.Name
End If
Next
End Sub

Private Sub ListBox1_Click()
Sheet1.Shapes(ListBox1.Text).Select
Call ItemSize()
End Sub


Suggestions:
Put calculated Width & Height into cells on the sheet rather than msgbox.
Put grid size into named cells & use them instead of constants
display room size also (rh, rw) on sheet - for reference.


Good luck & hope this helps. Steve.



"Paul" wrote in message ...
I have a graph paper template. I would like to draw a rectangle and have
Excel figure out square feet and other measurments from the drawing. Is there
a way to build a formula to see what I am drawing and take it from there. An
example would be say I have a 10 x 22 foot room and I need to fiure out how
many 2'x4' ceiling panels are required to do the ceiling. Along with other
factors that are needed for the work. This seems like a valuable tool to
build and I can't see how to start.
Any ideas?

Once you have set up your page you can use it over and over again
the cell g10 is always the cell for your width,and the cell n5 is always for
your length,the other formulas would all work through from your initial
length and width measurements in exactly the same way you work it out
now...except excel will do the sums....if you email me i will send you a
sheet with my initial working s.What version excel do you use???
--
paul
remove nospam for email addy!



"MinConst" wrote:

paul,
Where do I put these W=10say cel G10 and L=22say cell N5? Are these
formulas that would be placed in the cells G10 and L22? If I had to manually
add them to cells it would seem to defeat the purpose.
I'm sure it's just that I don't understand.

Paul

"paul" wrote:

________
l l
l l W=10say cell G10
l ________l
L=22say cell N5
ok above is a representative rectangle L is the length w is the width
area in sq feet is L*W=220 =G10*N5
if we say we use a 2 foot grid each way then N5/2= 11,G10/2=5 11*5=55 half
tiles
=55/2 =27.5 tiles,we can use the same sort of thing for labour,to work out
the perimeter of the room for new cornice,the quantity of timber required for
new battens etc etc
paul
remove nospam for email addy!



"Paulmin" wrote:

Steve,
This sounds very complicated. But if it works it would be a start. I will
play with it and see how it works. Thank you.

paul,
I like your idea also it seems less complex but I am not capable of
writing the formulas to make it happen. Can you be more specific?
Thanks.
Paul

"paul" wrote:

wouldnt it be easier just to have input cells for the width and length of the
room with a rectangle drawn as a representation.....you could draw several
different shapes to represent L or[] shaped rooms,with optional inputs for
areas to be excluded say for columns kitchen cabinets etc....
sorry if i have spoiled your fun...:)
I use a similar method to estimate rafters in hip end roofs
--
paul
remove nospam for email addy!



"Stevie_mac" wrote:

As a starting point...

1. Clearly define your template (use borders)
2. Select the cells that make up the template & name the range "rangeROOM"
3. Set drawing "Snap" mode to Snap To Grid
4. Draw a clear rectangle the EXACT size of the template cells
5. Set the rectangle order "Send to Back"
6. Name the rectangle "rectROOM"

Add the following macro...

Sub ItemSize()
Const GridItemWide As Integer = 12 ' inches
Const GridItemHigh As Integer = 12 ' inches

Dim shpSelection As ShapeRange
Dim shpRoom As Shape
Dim rngRoom As Range
Dim w, h, rw, rh, rwf, rhf

Set shpSelection = Selection.ShapeRange
Set shpRoom = Sheet1.Shapes("rectROOM")
Set rngRoom = Sheet1.Range("rangeROOM")

rw = rngRoom.Columns.Count * GridItemWide
rh = rngRoom.Rows.Count * GridItemHigh

rwf = rw / shpRoom.Width
rhf = rh / Sheet1.Shapes("rectROOM").Height

w = shpSelection.Width * rwf
h = shpSelection.Height * rhf

MsgBox "Item H=" & h & " W=" & w
End Sub



now draw your shapes. Select them 1 at a time & then run this macro.

NOTES:
I have not added error handling / type checking. (you will get errors if you dont select shape)
It has fixed grid size (12).
It is not user friendly having to run macros & get results in a msgbox.

TIPS:
you could list all shapes in a list box & use that to measure the items
(i.e. the listbox lists all the shapes. When you click one, the ItemSize macro is called)

Sub ListShapes()
Dim shr As Shape
ListBox1.Clear
For Each shr In Sheet1.Shapes
If shr.Name < "ListBox1" Then
ListBox1.AddItem shr.Name
End If
Next
End Sub

Private Sub ListBox1_Click()
Sheet1.Shapes(ListBox1.Text).Select
Call ItemSize()
End Sub


Suggestions:
Put calculated Width & Height into cells on the sheet rather than msgbox.
Put grid size into named cells & use them instead of constants
display room size also (rh, rw) on sheet - for reference.


Good luck & hope this helps. Steve.



"Paul" wrote in message ...
I have a graph paper template. I would like to draw a rectangle and have
Excel figure out square feet and other measurments from the drawing. Is there
a way to build a formula to see what I am drawing and take it from there. An
example would be say I have a 10 x 22 foot room and I need to fiure out how
many 2'x4' ceiling panels are required to do the ceiling. Along with other
factors that are needed for the work. This seems like a valuable tool to
build and I can't see how to start.
Any ideas?