Restrict area where I can use button with macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rafael_SP - 26 Sep 2007 20:47 GMT

Hi, everybody.

I'm trying to implement two macros associated with two respective buttons.
The first button ("Add Row") adds rows and copies the formulae and format
from the row immediately above.
The second one ("Delete Row") deletes the row the cursor is on.

However, I'd like to restrict the area where I can use these 2 buttons.
I want my worksheet to start with only one row (row 7, containing the
formulae and format) where I can use the button "Add Row" (the "Delete Row"
button shouldn't work with row 7).
Let's say the user adds 3 more rows (8, 9, 10). Then, the buttons should
work whenever I place the cursor on any of these new rows and hit the
buttons. They shouldn't work anywhere else.

Just to emphasize, row 7 cannot be deleted, for it contains the formulae and
format.

How can I accomplish that? Can anybody help me?

Thanks in advance.

Reply to this Message

JW - 26 Sep 2007 21:19 GMT

This will stop your delete from happening if the active cell is in
rows 1:7. Is that what you are after?
Sub thishere()
If Not ActiveCell.Row <= 7 Then
'your delete code here
End If
End Sub

> Hi, everybody.
>
[quoted text clipped - 17 lines]
>
> Thanks in advance.

Reply to this Message

Rafael_SP - 27 Sep 2007 21:43 GMT

JW,

Thanks for your prompt answer, but what you stated below is just one of my
objectives.
I'll try to make myself clear.
Rows 1:6 will remain immutable, containing headers. Row 7 contains some
cells where the user will insert data pertaining to a specific product and
some cells with formulae that use these data.
If the user wants to add new data pertaining to a second product, he will
have to use row 8. However, I don't want him to drag down the formulae from
row 7. The user will place the cursor on any cell on row 7 and then hit the
"Add Row" button that adds a row right below row 7 and copies the formulae
and format from it.
If the user wants to add data pertaining to a third product, he may either
add a row right below row 7, by placing the cursor on row 7 and hitting the
"Add Row" button, or below row 8, by placing the cursor on row 8 and hitting
the button.
And so on.

The button "Add Row" becomes active only on row 7 and on the rows created
from row 7. What I mean is: if the user places the cursor on row 30 and hit
the button, it won't work, unless rows 8:30 have been originated from row 7.
The same for the "Delete Row" button, except that this button must not work
with row 7 either, because it's the origin of all my formulae and format.

I hope you can help me now. If there is any point you didn't understand very
well, please let me know.

Regards,
Rafael.

> This will stop your delete from happening if the active cell is in
> rows 1:7. Is that what you are after?
[quoted text clipped - 24 lines]
> >
> > Thanks in advance.

Reply to this Message