Automatic Add/Delete Rows or Columns
Hi all,
I have a worksheet contains 2 spreadsheets. One is for user to enter (sheet1), the other (protected sheet2) is for calculation and summary. Right now when user modifies Sheet1, I have to go to Sheet2 and adjust/move data around to look exactly like Sheet1.
My question is: When the user add/delete rows or columns. Is there anyway the sheet2 automatic add/delete rows and columns?
Please share your wealth of knowledge with me.
Thanks.
LV
Could you post an example without any real information? There could be lots of ways around your problem but it would be easier if we could see it.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Hi Lucas,
Here is the file.
Thanks,
LV
Needs a bit more work, but here's one method.
Last edited by mdmackillop; 06-17-2006 at 06:09 AM. Reason: Attachment updated
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I was thinking along the line of users just clear contents or a form for input but that is really pretty slick Malcolm...even the formula's on sheet 2 follow
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Thanks for your help MD. But when i click on Delete/Add rows/cols... it says "invalid selection" and when i debug it. This vba code was highlighted. Please take a look:
[vba]
Sub DoProtect()
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingHyperlinks:=True, AllowSorting:= _
True, AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub
[/vba]
Thanks,
LV
Edited 24-Jun-06 by geekgirlau. Reason: insert vba tags
take a look at the first line of the addcolumns sub and compare it to the first line of the addrows sub....see if you can figure out what is going on. Malcolm did most of the work for you(to get sheet 2 to mirror sheet1 especially) but it still needs a little tweaking.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
You have a range named Boxes. In order to keep this range "intact", you need to add rows/columns within the range; adding to the boundary of the range will not do this (without much more coding). The simplesr method was to limit this by controlling where the buttons would work, hence the "Invalid Selection"
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Hello guys,
What is DoProtect()? I'm new with these codes. Sorry.
I can add/delete columns/rows, but one error about DoProtect(). When i click on add/delete buttons, it works but the error message appears "Run-time error "1004': Application-defined for object-defined error".
A big thanks to MDmackillop for helping me with these codes.
Giving up on me?
DoProtect is just a sub to do the protecting. If there is a problem with it, record a macro protecting the sheet, and subsitute it for my code.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
