Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-15-2013, 09:21 AM
Elan05 Elan05 is offline Delete Rows in Protected Table with Form Fields Windows 7 64bit Delete Rows in Protected Table with Form Fields Office 2010 64bit
Novice
Delete Rows in Protected Table with Form Fields
 
Join Date: Mar 2013
Posts: 13
Elan05 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 03-16-2013, 01:35 PM
macropod's Avatar
macropod macropod is offline Delete Rows in Protected Table with Form Fields Windows 7 64bit Delete Rows in Protected Table with Form Fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 03-22-2013, 09:05 AM
Elan05 Elan05 is offline Delete Rows in Protected Table with Form Fields Windows 7 64bit Delete Rows in Protected Table with Form Fields Office 2010 64bit
Novice
Delete Rows in Protected Table with Form Fields
 
Join Date: Mar 2013
Posts: 13
Elan05 is on a distinguished road
Default

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 = ""
Reply With Quote
  #4  
Old 03-22-2013, 04:47 PM
macropod's Avatar
macropod macropod is offline Delete Rows in Protected Table with Form Fields Windows 7 64bit Delete Rows in Protected Table with Form Fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 03-28-2013, 06:09 AM
Elan05 Elan05 is offline Delete Rows in Protected Table with Form Fields Windows 7 64bit Delete Rows in Protected Table with Form Fields Office 2010 64bit
Novice
Delete Rows in Protected Table with Form Fields
 
Join Date: Mar 2013
Posts: 13
Elan05 is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 03-28-2013, 12:54 PM
macropod's Avatar
macropod macropod is offline Delete Rows in Protected Table with Form Fields Windows 7 64bit Delete Rows in Protected Table with Form Fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #7  
Old 04-05-2013, 09:43 AM
Elan05 Elan05 is offline Delete Rows in Protected Table with Form Fields Windows 7 64bit Delete Rows in Protected Table with Form Fields Office 2010 64bit
Novice
Delete Rows in Protected Table with Form Fields
 
Join Date: Mar 2013
Posts: 13
Elan05 is on a distinguished road
Default

Here it is!
Attached Files
File Type: docx 2013 CAP For Forum.docx (72.1 KB, 26 views)
Reply With Quote
  #8  
Old 04-05-2013, 02:10 PM
macropod's Avatar
macropod macropod is offline Delete Rows in Protected Table with Form Fields Windows 7 64bit Delete Rows in Protected Table with Form Fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #9  
Old 04-15-2013, 01:02 PM
Elan05 Elan05 is offline Delete Rows in Protected Table with Form Fields Windows 7 64bit Delete Rows in Protected Table with Form Fields Office 2010 64bit
Novice
Delete Rows in Protected Table with Form Fields
 
Join Date: Mar 2013
Posts: 13
Elan05 is on a distinguished road
Default

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?
Reply With Quote
  #10  
Old 04-15-2013, 06:34 PM
macropod's Avatar
macropod macropod is offline Delete Rows in Protected Table with Form Fields Windows 7 64bit Delete Rows in Protected Table with Form Fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #11  
Old 04-15-2013, 09:42 PM
fumei fumei is offline Delete Rows in Protected Table with Form Fields Windows 7 64bit Delete Rows in Protected Table with Form Fields Office XP
Expert
 
Join Date: Jan 2013
Posts: 440
fumei is on a distinguished road
Default

macropod: "
Quote:
no end of possibilities for what might be done wrongly
Elan05, while not LITERALLY true (there is - eventually - an end), macropod's comment does point out something very important. To make some of the things we want to happen, actually happen fully and accurately, can take a complexity of logic (i.e. "possibilities")...NOT programming. The actual code required is not always more complicated; there is just MORE of it, and the relationship between logical elements becomes critical.

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.
Reply With Quote
  #12  
Old 04-26-2013, 09:02 AM
Elan05 Elan05 is offline Delete Rows in Protected Table with Form Fields Windows 7 64bit Delete Rows in Protected Table with Form Fields Office 2010 64bit
Novice
Delete Rows in Protected Table with Form Fields
 
Join Date: Mar 2013
Posts: 13
Elan05 is on a distinguished road
Default

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.)
Reply With Quote
  #13  
Old 04-26-2013, 06:34 PM
macropod's Avatar
macropod macropod is offline Delete Rows in Protected Table with Form Fields Windows 7 64bit Delete Rows in Protected Table with Form Fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #14  
Old 09-08-2014, 01:00 PM
IanMC IanMC is offline Delete Rows in Protected Table with Form Fields Windows Vista Delete Rows in Protected Table with Form Fields Office 2013
Novice
 
Join Date: Sep 2014
Posts: 6
IanMC is on a distinguished road
Default 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!
Reply With Quote
  #15  
Old 09-08-2014, 03:02 PM
macropod's Avatar
macropod macropod is offline Delete Rows in Protected Table with Form Fields Windows 7 64bit Delete Rows in Protected Table with Form Fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Tags
delete row, form fields, protected form

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete Rows in Protected Table with Form Fields 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 Rows in Protected Table with Form Fields 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:34 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft