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
:
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!
:
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.
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?