Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Restrict area where I can use button with macro
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Restrict area where I can use button with macro
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 Rafael_SP wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Restrict area where I can use button with macro
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. "JW" escreveu: 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 Rafael_SP wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Restrict Command Button | Excel Programming | |||
Macro - Set Print Area for Changing Data Area | Excel Programming | |||
Restrict Scroll Area | Excel Discussion (Misc queries) | |||
How to restrict scroll area | Excel Programming | |||
Message box display or something in the status area after macro button is pressed? | Excel Programming |