Inserting cell values as text into "autoforms"(squ

rodolfosneto

New Member
Joined
Sep 6, 2006
Messages
3
Hello,

I´m having some hard work having to fill manually the results of a calculation between cells into "autoforms" as square boxes.

Once per week i have to fill the boxes with the results, they are almost fourty!

ís it possible to create a macro to handle this for me?

tks
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, rodolfosneto
Welcome to the Board !!!!!

Can you tell where the boxes are located in relation to the cells where they should get the caculations from ? What are the calculations
example
result of A1 + B1 in autoshape located in D1 (topleftcell)
repeat each 5th line till 201 (6,11,16,21,...,201)

Is it really necessary to store calculations in "pictures" ? Why are you doing this? perhaps the answer to this question will generate some new ideas to help...

enjoy the Board :)
kind regards,
Erik
 

rodolfosneto

New Member
Joined
Sep 6, 2006
Messages
3
Hi Erik, tks for your reply

The boxes are located in another sheet, it´s for a tool of management to control the status of some activities of a project.

It´s like this (inside autoshapes) because we use it to index in the presentation for the team project.

The calculations are like this:

1. In sheet2 we have the forecast for beginning and end of an activity (column F and G respectively, column B is the name of the activities).
2. In column C we calculate how many days have been passed until that day since the beginning of the activity.
--> =Networkdays(F1;Today())
3. In column D we calculate the total planned duration of the activity
--> =Networkdays(F1;G1)
4. Then, in column E, we divide these results to get the status (%) that activity should be
--> =C1/D1
5. In column H is the real status, this information is estimated and comes from the project team.

Get it?

With this percentages (columns E and H) i have to fill the boxes in sheet1. They are located vertically. This sheet is like this:

Column B: The same name activities of sheet2
Column D: There is a shape in each cell with the results (%) in column E from sheet2
Column E, F, G,...: Real status (%) of the activities by week, also in in shapes, they are filled with information stored in column H in sheet2.
Column C: the difference between column B and actual status (in that week).

The shapes and the background of the cell have some differente colors depending on the status, but this is no problem to do manually.

I hope you understood!!

TKS :oops:
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
I would recommend you posted in a more condensed way. Seems like there is a lot of redundant information here.

Aren't you just copying values from cells to shapes ?
That's the whole story in one sentence, else I'm missing something.

now as for the specifications:
to create good code there must be a pattern
1. regarding the names
what names did you give the autoshapes ?
if there is no pattern try out this code
Code:
Option Explicit

Sub rename_shapes()
Dim SH As Shape
Dim I As Long

Const msg = "This procedure will rename all your shapes on this sheet." & vbLf & _
"Do you want to proceed?"

If MsgBox(msg, 292, "RENAME SHAPES") = vbNo Then Exit Sub

        For Each SH In ActiveSheet.Shapes
        I = I + 1
        SH.OLEFormat.Object.Name = "SH_" & SH.TopLeftCell.Address(0, 0)
        Next SH

End Sub
or
Code:
Option Explicit

Sub rename_shapes()
Dim SH As Shape
Dim I As Long

Const msg = "This procedure will rename all your shapes on this sheet." & vbLf & _
"Do you want to proceed?"

If MsgBox(msg, 292, "RENAME SHAPES") = vbNo Then Exit Sub

        For Each SH In ActiveSheet.Shapes
        I = I + 1
            With SH.TopLeftCell
            SH.OLEFormat.Object.Name = "SH_R" & .Row & "C" & .Column
            End With
        Next SH

End Sub
we'll see what suits best
2. regarding the relationship between the sourcecells and the autoshape
perhaps they are on the same row ?

hmm, I will figure out some code
with these specifications
sourcecells in column E on sheet1
autoshapes in column H of sheet2 on corresponding rows

kind regards,
Erik
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

try this
Code:
Sub text_in_shapes()
'Erik Van Geit
'060914
'assuming you have renamed the shapes with the first procedure in previous post
'like: SH_A1
'sourcecells in column E on sheet1
'autoshapes in column H of sheet2
Dim SH As Shape
Dim I As Long
Dim R As String

        For Each SH In Sheets(2).Shapes
        I = I + 1
            With SH
                If .TopLeftCell.Column = 8 Then
                R = Mid(.Name, InStr(4, .Name, "H") + 1, 99)
                .OLEFormat.Object.Text = Sheets(1).Range("E" & R).Value
                End If
            End With
        Next SH

End Sub

create new workbook
2 sheets
fill sheet1 column E with some easy data (like E1, E2, ...)
create shapes on sheet 2, some must be in column H
run the first renamecode
run the above code
your shapes should contain the correct values
example
a shape with topleftcell H5 will be renamed SH_H5
the second code will put the text "E5" in the shape (assuming you have "E5" in sheet1 cell E5)

if you can get this to work, then try out in your workbook

best regards,
Erik
 

rodolfosneto

New Member
Joined
Sep 6, 2006
Messages
3
I´ve made it... just some modifications.

For the new workbook the rename routine went well but for my old workbook excel didn´t accept it. It´s more handly but i was able to do it by changing

"For Each SH In ActiveSheet.Shapes" for "For Each SH In ActiveWindow.Selection.ShapeRange"

The error message was:

"error in time execution: 40036
error on aplicative definition or on object definition"

I´m glad i found some usefull help in here!
To where should i send a case of beer?

Best regards to you too...
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
you're WELCOME :)
To where should i send a case of beer?
I do not drink beer.
Give something to eat to the first poor guy you meet on the street...
kind regards,
Erik
 

Forum statistics

Threads
1,136,354
Messages
5,675,291
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top