PDA

View Full Version : Automatic Add/Delete Rows or Columns



lacviet2005
06-16-2006, 01:57 PM
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. :banghead:

My question is: When the user add/delete rows or columns. Is there anyway the sheet2 automatic add/delete rows and columns? :doh:

Please share your wealth of knowledge with me. : pray2:

Thanks.
LV

lucas
06-16-2006, 02:05 PM
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.

lacviet2005
06-16-2006, 03:56 PM
Hi Lucas,
Here is the file.

Thanks,
LV

mdmackillop
06-16-2006, 05:43 PM
Needs a bit more work, but here's one method.

lucas
06-16-2006, 07:57 PM
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

lacviet2005
06-19-2006, 08:19 AM
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:

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


Thanks,
LV
Edited 24-Jun-06 by geekgirlau. Reason: insert vba tags

lucas
06-19-2006, 09:09 AM
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.

mdmackillop
06-19-2006, 10:09 AM
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"

lacviet2005
06-21-2006, 10:54 AM
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.

lacviet2005
06-23-2006, 02:25 PM
Giving up on me? :confused4 :stars:

mdmackillop
06-23-2006, 02:29 PM
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.