#1
|
|||
|
|||
Delete Rows in Protected Table with Form Fields
I am creating a document where the users will enter information in a table. I created a macro that allows the user to add rows as needed. I am trying to create a second macros that will run when the user exits the end of the table that will delete any rows that have all blank form fields.
I found this on another forum. I sent it to run when exiting the last form field of the table, the document flashes a few times so I can tell that it is running something but the blank rows did not delete. I am also not getting an error message. Can anyone help? What am I missing?! Code:
Sub DeleteEmptyRows() ' 'Delete empty rows in the table after all equipment has been entered ' ActiveDocument.Unprotect Password:="a" Counter = ActiveDocument.Tables(1).Rows.Count While Counter > 0 If ActiveDocument.FormFields(Counter).Result = "" Then ActiveDocument.Tables(1).Rows(Counter).Delete End If Counter = Counter - 1 Wend ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="a" End Sub Last edited by macropod; 03-15-2013 at 05:08 PM. Reason: Added code tags & formatting |
#2
|
||||
|
||||
Hi Elan05,
The code works OK in my testing, but it relies on there being only one formfield per row. That's because, having got a row count, it then proceeds to apply that to a count of formfields. So, if your table has 10 rows, with two formfields per row, but only the formfields in the first 5 rows are completed, no rows will be deleted. Also, as coded, the macro doesn't stop at the first row containing a filled formfield - within the above limitations it tests every row and, if it doesn't find a filled formfield there, deltes the row. That means it will also delete rows that have no formfields (eg a header row). Try the following: Code:
Sub DeleteEmptyRows() 'Delete empty rows in the table after all equipment has been entered Application.ScreenUpdating = False Dim StrPwd As String StrPwd = "a" With ActiveDocument .Unprotect Password:=StrPwd With .Tables(1).Rows While .Last.Range.FormFields(1).Result = "" .Last.Delete Wend End With .Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=StrPwd End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thank you for responding back! Sorry I am just getting a chance to work with this doc again today. I am getting a debug error message on:
While .Last.Range.FormFields(1).Result = "" |
#4
|
||||
|
||||
Hi Elan05,
That suggests the last row in the table you're running the macro against doesn't have any formfields. As I trust you're getting to understand, the macros to do this sort of thing need to be tailored to the particular tables they're being used on. Can you attach a document to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab. If so, I'll look at tailoring the macro to your needs.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Hi Paul, thank you for all of your help. I just tried to upload it and got an error message saying "invald file". It is a docm file, obviously. Again, sorry for the delay, I don't get to work on this everyday but I will be checking the forum for the rest of today.
|
#6
|
||||
|
||||
You can't upload docm files. You can either save a copy as docx (ie without the macro) and upload that, or add the docm file in to a zip archive and upload that.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Here it is!
|
#8
|
||||
|
||||
Hi Elan05,
Your document has multiple tables. You could achieve what you're after simply by changing .Tables(1).Rows to .Tables(2).Rows, though if you had a schedule with no items, the code would produce an error as the 3rd row has no formfields. The modified code below runs without producing an error in such cases: Code:
Sub DeleteEmptyRows() 'Delete empty rows in the table after all equipment has been entered Application.ScreenUpdating = False Dim StrPwd As String StrPwd = "" With ActiveDocument If .ProtectionType = wdAllowOnlyFormFields Then .Unprotect Password:=StrPwd With .Tables(2).Rows Do While .Last.Range.FormFields.Count > 0 If .Last.Range.FormFields(1).Result = "" Then .Last.Delete Else Exit Do End If Loop End With .Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=StrPwd End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
I removed the last row of the table that had the "legend" (and no form fields) and put that information outside of the table. As a test, I had 4 rows of form fields, I filled in the 1st row and 3rd row of form fields. When I ran the macro only the 4th row was deleted... Not the 2nd row. I'm guessing if I am able to have the macro scan the entire table I will also need to separate the top three header rows of the table with the macro to prevent the macro from deleting the header rows that don't have form fields. I suppose to get around this I could create two separate tables in a row? What would need to change in the existing macro to accomplish a complete table scan?
|
#10
|
||||
|
||||
Perhaps you could clarify exactly what it is you're trying to achieve. I would have thought it illogical for your table to have just the 1st & 3rd rows completed, leaving the 2nd & 4th rows' fields empty. Certainly, a macro could be coded to take that into account, but there seems to be no end of possibilities for what might be done wrongly (eg someone leaves the first field empty, but fills in the others on the same row).
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
macropod: "
Quote:
For example, if it is logically important to retain information (say that row 2 does NOT have formfields), then it is 100% the responsibility of the coder to make that possible. VBA will not remember anything unless it is explicitly instructed to do that. These are logical processes, and not essentially code processes. The code is pretty trivial. or prehaps it is better to say, simple. The more you can think things it out - if I suspect something I am working on may be seriously complicated I actually write things on...gasp...paper - the better and faster you will get to where you want to be. BTW, thank you for detailing what you are working on in multiple threads. Some times people ask too many things for a single thread. |
#12
|
|||
|
|||
Thanks for the explanation. Basically all rows (except 3 header rows) will have form fields... It might be possible, that the user will fill it out (filling in fields in all rows), and then come back later and have to delete a row (in which case I figured if they deleted the information in the form fields, then the macro would delete the row for them when they exited the table). Perhaps my thinking is wrong. I am working with ‘practically’ computer-illiterate users, so I try to make things fool proof. In doing so, I may be the fool. I am contemplating removing all form fields from the table, making those rows of the table a separate section, and leaving those rows of the table unprotected in the document, so the user can manipulate as needed (this may backfire on me in the end too.)
|
#13
|
||||
|
||||
Try the following version of the macro. With this one, if any formfield on a given row between the 4th & last rows is not empty, that row will not be deleted.
Code:
Sub DeleteEmptyRows() 'Delete empty rows in the table Application.ScreenUpdating = False Dim StrPwd As String, i As Long, j As Long, bDel As Boolean StrPwd = "" With ActiveDocument If .ProtectionType = wdAllowOnlyFormFields Then .Unprotect Password:=StrPwd With .Tables(2) For i = .Rows.Count - 1 To 4 Step -1 bDel = False With .Rows(i) If .Range.FormFields.Count > 0 Then bDel = True For j = 1 To .Range.FormFields.Count If Trim(.Range.FormFields(j).Result) <> "" Then bDel = False Exit For End If Next If bDel = True Then .Delete End With Next End With .Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=StrPwd End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
Further Query
Hi there,
Apologies for going over old ground - I am in a very similar position to Elan05 in that I have tables as part of my form with multiple lines, all of which are not always required. The guidance in this thread has been super useful, but I seem to be falling at the final hurdle I have tried (unsuccessfully) to replicate the coding; all that happens is the relevant table is selected and rows searched individually, but nothing further i.e. no deletion. Is there a more simple method of running the code i.e. if a particular column is blank to delete the corresponding row? Any guidance would be greatly appreciated! |
#15
|
||||
|
||||
Hi Ian,
There have been multiple macros discussed in this thread, so I don't know which approach you're trying to take. It would also make it easier to diagnose the issue if you could attach a document to a post with some representative data (delete anything sensitive). You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
delete row, form fields, protected form |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Text Form Fields - Filling the table cell | simville02 | Word Tables | 1 | 01-31-2013 11:12 PM |
Using macro to add variable number of rows to a protected word table | Julia | Word Tables | 1 | 01-09-2013 06:04 AM |
Delete all rows but the last. | elky1967 | Word VBA | 14 | 09-21-2012 05:27 AM |
Adding table lines to protected form | razberri | Word Tables | 2 | 10-27-2010 05:58 PM |
Editing Password protected form fields in Word 2007 | tamilan | Word | 2 | 02-16-2010 09:45 AM |