Related To Table In Excel

[prasaddn]

Hi All,


I am using Excel 2007 Version.


I have a set of columns and rows defined as Table.


Need help on following question:


1. I use formula like Index(Table1[Col2],rownum),

When I close and open the file, the "Table1[col2]" gets changed to cell reference like Sheet1!B1:B250, and when new rows are added to table, the formula doesn't get added with new row count. This happens some times, not always and not all the places i have used.


What am I doing wrong?


2. I do not want users to delete any column of the Table, but should be able to add/delete rows. Protection option does not seem to be really helpful here.


I know we can opt for macro to unprotect and protect before adding by some commandbutton etc. Any other suggestions please.


Let me know if any further details are required.


Regards,

Prasad DN

PS: The table is in different tab, and formulas are in another tab, but same workbook.

 

[SirJB7]

Hi, prasaddn!


I built a simple 5 rows by 3 columns table, in another sheet I used INDEX with Table[Field] addressing, closed the book, quit Excel, and when opening again everything was Ok. So I couldn't reproduce your issue. Maybe you could upload the file?


And about second item, well, bad new & good news. Which do you want first?


Ok, the bad ones: Excel hasn't an event nor Workbook neither Worksheet like BeforeDelete so you can't easily intercept user action by this way. Which would be very simple and clean.


Now the good ones: I first thought on something like SelectionChange event, but it was to restrictive, they wouldn't even be able to select for copying, formatting or whatsoever. After that crossed my mind inhibit selecting ranges higher than wider, but it was a similar situation. So finally I think as something as I applied on Friday to a group of workbooks, and that was working with protected and not user unprotectable worksheets, with the addition of this buttons functionality:

a) hide column of active cell's column

b) hide row of active cell's row

c) delete column of idem

d) delete row of idem

e) show all columns

f) show all rows


Each button at start performs an unprotect action on worksheet, the validates what row is being asked to delete (no problem with additions, but I have to build it from scratch), then does the action, and at last protects again the worksheet. The code behind this buttons is not hard at all, and if this structure is suitable for you I can send it to you on Wednesday afternoon when I'd be able to put my hands on those workbooks... otherwise I should write the code again, which if it's urgent for you, you'll get it tomorrow early afternoon (GMT-3).


Just let me know.


Regards!

 

[prasaddn]

Thank you for the response Pablo.


Well, even I am not able to reproduce the first question scenario. I will wait and see if it occurs again.


Regarding 2nd question, I am aware of the VBA Code that you have suggested. At the sametime, I have come up with another simple workaround.


In worksheet_change event, I have put a code to check if table's last col label <> "What ever I have Given" then application.undo and give appropriate msgbox.


note: when I say table's last col label, I use exact cell reference and not as table[column].


some thing like:

If sheet3.range("AI1") <> "MyGivenLabel" then

'Sheet3.range("AI1") has the last column of table.

Application.EnableEvent = False

Application.Undo

msgbox "Caught You Changing My Table Rule"

Application.EnableEvent = False

end if

<code/>


And the best part is its working and serving my purpose.


Regards,

Prasad DN

 

[SirJB7]

Hi, prasaddn!


Glad you still couldn't reproduce the first issue, because I've got no idea of whatsoever might be the cause... so one problem less


And more glad even you found the workaround for second question, but you first said you didn't want the user to eliminate columns, so I assumed that no column was delete-enabled. If I don't understand wrongly you later said table's last col level, so you only don't want users to delete the last one? In the first line of the code I interpret that you only restrict to deleting last column and not any column (in table).


Am I wrong or should you replace that first line for something like this?


-----

[pre]

If Application.Intersect(Range("ColumnUnremovableTable"), Selection) Is Nothing Then
' let the guys play, they're off the table
ElseIf Selection.Address = Selection.EntireColumn.Address Then
' cut user fingers, they know they shouldn't intend to remove a column
ElseIf Selection.Cells.Count <> Selection.EntireRow.Cells.Count Then
' fire users, how are they going to delete partially a row belonging to a table?
ElseIf Not (Application.Intersect(Range("ColumnUnremovableTable").Rows(1), Selection) Is Nothing) Then
' in your country is it allowed killing somebody for trying to delete table's title row? in mine not, but it should be!
Else
' let them go on, nothing harmful, just a row
End If

[/pre]
-----


Regards!

 

[prasaddn]

Hi Pablo,


Still trying to figure out why on those times the formula was not getting updated automatically with increased or decreased row size, and how it is now working correctly. Anyhow as you said, 1 problem less.


Regarding the table layout controlling, your understanding is correct, I didn't want any of the user to delete the column, but can delete or add rows.


My workaround with check on one last column's label can do all that you have suggested. If any of the user had deleted any column the last column would get shifted, or If they try to insert unauthorized column in my table, again the referred last column label gets shifted and so I will be able to catch that and undo.


Well, I like the comments part of yours, I will we could use such comments in msgbox and If killing was allowed, by now I would have either become a big time killer or got killed by now.


Thank you again.


Regards,

Prasad DN

 

[SirJB7]

Hi, prasaddn!


I hope your users:

a) can't write or record any macros at all, using Application.EnableEvents=false for example

b) perform operations of copying, pasting, or replacing where a large quantity of data is involved, that Excel prompts it can't be undone

c) run the workbook in a computer with not enough memory, same as b)

d) have access to File tab, Options, Advanced, General paragraph, and play with the "Undo" options

e) perform any action (that exists and I usually meet but I can't remember now one!) that pops up the same message as b)


Otherwise you'll be in trouble as Undo won't be available.


Regards!


PS: I still can't remember after what things that I frequently do Excel has no Undo option... if I remember I'll tell you.

 

[prasaddn]

Hi Again,


my code does not restrict users from doing any action like editing rows, deleting or adding. The only restriction it creates is deleting of any of the column in my table.


Yes, if the users really wanna mess with it they crack the code. There are not multiple users, only sensible and 2 or 3 users.


The purpose of figuring out the solution to restrict column deletion was due to fact that I have used table and users have general tendency of selecting more than one cells in same column and press "Cntrl + keypad Minus" expecting usual pop-up of whether to delete shift cells up or move left or entire row or entire column, but in table it gets deleted without any further prompts and they may not notice it. Hence, I wanted to control accidental deletion of column.


Regards,

Prasad DN.

PS: Sorry about delayed response.

 

[SirJB7]

Hi, prasaddn!


Oh... what a disappointment! It was only to prevent accidental deletion... and I thought I was going to see many user's fingers cut, well, maybe next time...


Regards!