Inserting cell values as text into "autoforms"(squ

[rodolfosneto]

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

 

[erik.van.geit]

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]

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

 

[erik.van.geit]

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

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

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]

try this

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]

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]

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