macro enabled 2007 excel

[khalil]

Good evening all,

please help me in this one;

i have cells A1 B1 C1 contains formulas, they are not used yet,

when they are used and having data, I want just the formulas to jump

automatically to A2 B2 C2 cells,

and so on to jump to A3 B3 C3....... ( Just the formulas because the values will differ)

One other thing please; i want to create a user name for this macro enabled workbook so no body can copy it, and to hide the code so they cant disable
it.


many thanks
Khalil

 

[Smitty]

What's the trigger for the functions to be copied? Will it be data entry? If so, what cell(s)?

As for keeping people from being able to copy the workbook, you can't, although you can protect the VBA project in the Tools menu.

HTH,

 

[khalil]

thanks for reply

data will be entered in other blank cells , data could be Numbers and text,

let us work on these cells A1 B1 C1 containing formulas in any kind as an example to start with,

thanks in advance

 

[Smitty]

I asked the question because you need a trigger to copy the formulas to the next row.

Essentially you monitor a range of cells for a change with a Change event, and when a change is made to that range, you copy the formulas, but it means we need to know which cells to monitor.

You could use a Calculate event, but there's a whole lot more overhead involved with that method because you can't specifically monitor a defined range of cells.

 

[khalil]

good morning

all cells are in one row (the one with formulas and the blank ones),

is this help,
if not ; i have to go and find out which cells have formulas and which cells have not,they are too many.

thanks

 

[Smitty]

Here's some boilerplate change event code.

If you define which cell(s) to monitor for changes you can use something like the next procedure to copy the formulas to the next row.

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("xxx")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>        lr = Cells(Rows.Count, "A").End(xlUp).Row<br>                <br>        Range(Cells(lr, "A"), Cells(lr, "C")).Copy Cells(lr + 1, "A")<br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>