Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 05-04-2020, 09:34 AM
shanerolle shanerolle is offline Auto Create/Format a Word Document based on Check Boxes Windows 10 Auto Create/Format a Word Document based on Check Boxes Office 2019
Novice
Auto Create/Format a Word Document based on Check Boxes
 
Join Date: Apr 2020
Posts: 10
shanerolle is on a distinguished road
Default

Hello again,

I apologize for the delay, I have been quite busy with other projects but finally found the time today to dedicate some time to this. I was working through it and doing some editing of my document and decided to take the time to stop and test it using the macros we had discussed, and ran into a few issues.

In order to give you a better idea of what I am doing, I have provided a rough copy of the document I have created and will be using (I removed some information and shortened it, it is typically 16 pages but I removed a lot to keep the example a little shorter). The first 3 pages are the cover section, and everything beyond page 4 would be the scope section. Only Division 1 is formatted properly in the scope section, but the rest of the divisions will look very similar to Division 1, just to give examples.


The idea is that each section will be its own table, so that I can use the code suggested by macropod in his sample he provided me with last time I needed help. This will allow the ability to both select a header checkbox to remove the entire section, or the ability to select specific check boxes in a section to remove those rows.

The code is as follows:
Code:
Sub DeleteCheckedContent()
Application.ScreenUpdating = False
Dim Tbl As Table, r As Long
For Each Tbl In ActiveDocument.Tables
  With Tbl
    If .Cell(1, 1).Range.ContentControls(1).Checked = True Then
      For r = .Rows.Count To 3 Step -1
        .Rows(r).Delete
      Next
      .Cell(2, 2).Range.Text = "Not applicable"
    Else
      For r = .Rows.Count To 2 Step -1
        If .Cell(r, 1).Range.ContentControls(1).Checked = True Then .Rows(r).Delete
      Next
    End If
    .Columns(1).Delete
  End With
Next
Application.ScreenUpdating = True
End Sub
However, when I run that code in the attached document (I selected the checkboxes for the Summary of Work header, Unit Prices Header, and miscellaneous options in the Exclusions sections as a test), I get "Run-time error '5941': The requested member of the collection does not exist", and I am not sure what is causing it. I attempted to read back through the code and work through to document to determine the issue, but cannot figure out what exactly would be causing this error.



The other issue I noticed, and this is mostly my own fault, is that in tables with more than 2 columns (I recently had to create these due to specific formatting requests), the "Not applicable" addition goes into row 2, column 2, as specified in the code, but it is not centered or formatted properly since there are 3 or 4 rows (as seen below Unit Prices in the example). Is there any way to format this so that it merges the cells, or to simply create a new row with only 1 column so "Not Applicable" will display properly?

Once again, I apologize for what are probably dumb questions, but I appreciate you giving me the opportunity to learn and understand Word and macros more as you have helped me through this.

Thank you so much,
Shane
Attached Files
File Type: docx Official Sample for Forum.docx (39.8 KB, 15 views)
Reply With Quote
  #17  
Old 05-04-2020, 03:23 PM
macropod's Avatar
macropod macropod is offline Auto Create/Format a Word Document based on Check Boxes Windows 7 64bit Auto Create/Format a Word Document based on Check Boxes 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

The problem is that your document contains tables other than the ones with checkboxes. The code fails because the expected checkbox isn't found in those tables. Try:
Code:
Sub DeleteCheckedContent()
Application.ScreenUpdating = False
Dim Tbl As Table, r As Long
For Each Tbl In ActiveDocument.Tables
  With Tbl
    If .Cell(1, 1).Range.ContentControls.Count = 1 Then
      If .Cell(1, 1).Range.ContentControls(1).Checked = True Then
        For r = .Rows.Count To 3 Step -1
          .Rows(r).Delete
        Next
        .Cell(2, 2).Range.Text = "Not applicable"
      Else
        For r = .Rows.Count To 2 Step -1
          If .Cell(r, 1).Range.ContentControls(1).Checked = True Then .Rows(r).Delete
        Next
      End If
      .Columns(1).Delete
    End If
  End With
Next
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #18  
Old 05-06-2020, 01:59 PM
shanerolle shanerolle is offline Auto Create/Format a Word Document based on Check Boxes Windows 10 Auto Create/Format a Word Document based on Check Boxes Office 2019
Novice
Auto Create/Format a Word Document based on Check Boxes
 
Join Date: Apr 2020
Posts: 10
shanerolle is on a distinguished road
Default

Okay I will for sure give that a try in the next few days! That makes sense and I didn't even think about the extra tables being a potential issue.

How can I fix my formatting issue, where the "Not Applicable" is sometimes put into Cell(2,2) and that cell is smaller depending on the formatting? I want it so that it looks professional and is on a single line, like if you typed on a new excel document, and not bunched up or wrapped in a smaller column. I know that this is my own fault due to having to have 3-4 columns in certain tables (my coworkers are very specific on how they want their formatting ) but I would like to find a way to fix this if possible, so any input or pointing me in the right direction would be great!!

Thank you a lot!
Shane
Reply With Quote
  #19  
Old 05-06-2020, 03:27 PM
macropod's Avatar
macropod macropod is offline Auto Create/Format a Word Document based on Check Boxes Windows 7 64bit Auto Create/Format a Word Document based on Check Boxes 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

Quote:
Originally Posted by shanerolle View Post
How can I fix my formatting issue, where the "Not Applicable" is sometimes put into Cell(2,2) and that cell is smaller depending on the formatting? I want it so that it looks professional and is on a single line, like if you typed on a new excel document, and not bunched up or wrapped in a smaller column. I know that this is my own fault due to having to have 3-4 columns in certain tables (my coworkers are very specific on how they want their formatting ) but I would like to find a way to fix this if possible, so any input or pointing me in the right direction would be great!!
That's because you've added extra columns to the tables concerned and, since their existence hadn't been mentioned in previous discussions. It seems pointless to me to have columns whose only content is a hyphen. That said, what is supposed to happen with that column when the 'Not Applicable' output is required?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #20  
Old 05-07-2020, 06:59 AM
shanerolle shanerolle is offline Auto Create/Format a Word Document based on Check Boxes Windows 10 Auto Create/Format a Word Document based on Check Boxes Office 2019
Novice
Auto Create/Format a Word Document based on Check Boxes
 
Join Date: Apr 2020
Posts: 10
shanerolle is on a distinguished road
Default

I completely understand, and I do apologize, I never planned to include them, but my coworkers want to be able to format 'easily' and this is the way it was done previously. I completely agree that there is no reason to have a column with just a hyphen, but this is the way they are used to seeing it and requested that I include it so that it looks similar to the old sales proposal.

With that being said - if the "Not Applicable" output is required, I have no preference as to what happens with the unwanted columns. I simply want the output to show in a single line, regardless of what happens to the other columns in the table. My initial thought was to take row 2, and merge any cells in row 2, no matter how many columns, and then have "Not Applicable" be displayed in row 2. My thought was that there would be no issues with how it displays then, since there is only 1 column left (in row 2, since they are all merged).

Is it possible to merge the columns in a table prior to the display of "not applicable'? I believe I understand how to merge two columns together using VBA, but I am not sure how I would do it for a dynamic number of columns, or if it should be done before or after displaying the "not applicable" text.

I agree with your statement about some of this being pointless (columns with a hyphen), but some of the formatting is completely out of my control and is up to the individuals who will be using the document once I create it for them. Hopefully this clears up some of your questions.

Thank you so much,
Shane
Reply With Quote
  #21  
Old 05-07-2020, 02:30 PM
macropod's Avatar
macropod macropod is offline Auto Create/Format a Word Document based on Check Boxes Windows 7 64bit Auto Create/Format a Word Document based on Check Boxes 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. In this case, if a 'Not Applicable' result is required, the macro deletes the original empty second column and widens the original third column to compensate.
Code:
Sub DeleteCheckedContent()
Application.ScreenUpdating = False
Dim Tbl As Table, r As Long
For Each Tbl In ActiveDocument.Tables
  With Tbl
    If .Cell(1, 1).Range.ContentControls.Count = 1 Then
      If .Cell(1, 1).Range.ContentControls(1).Checked = True Then
        For r = .Rows.Count To 2 Step -1
          .Rows(r).Delete
        Next
        .Rows.Add
        If .Columns.Count > 2 Then
          .Columns(3).Width = .Columns(2).Width + .Columns(3).Width
          .Columns(2).Delete
        End If
        .Cell(2, 2).Range.Text = "Not applicable"
      Else
        For r = .Rows.Count To 2 Step -1
          If .Cell(r, 1).Range.ContentControls(1).Checked = True Then .Rows(r).Delete
        Next
      End If
      .Columns(1).Delete
    End If
  End With
Next
Application.ScreenUpdating = True
End Sub
If you want to leave the columns intact, delete or comment-out:
Code:
        If .Columns.Count > 2 Then
          .Columns(3).Width = .Columns(2).Width + .Columns(3).Width
          .Columns(2).Delete
        End If
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #22  
Old 05-13-2020, 10:42 AM
shanerolle shanerolle is offline Auto Create/Format a Word Document based on Check Boxes Windows 10 Auto Create/Format a Word Document based on Check Boxes Office 2019
Novice
Auto Create/Format a Word Document based on Check Boxes
 
Join Date: Apr 2020
Posts: 10
shanerolle is on a distinguished road
Default

That seems to work perfect!! thank you for all the help, I think this might be all I need, but I will be sure to let you know if I need any further help! I even managed to write my own macros to turn on or off the table borders just in case they would want to manually edit the document (yes I know that is basic, but I feel like I'm picking up a lot from working through your code, so thank you!).

I appreciate all the help over the last few weeks, thank you so much!
Shane
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto-populate Text Field When I Click Check Boxes LobaBlanca Word VBA 2 03-14-2019 08:10 PM
Print documents based on boxes checked in word document eshiffman Word VBA 8 04-05-2018 06:26 AM
Need to create document that will auto insert text based on parameters parisfranco Word 13 07-25-2017 03:21 PM
Word document with check boxes JohnW Word 3 12-01-2016 07:43 AM
Table - Check Boxes - Create Select All lajohn1963 Word Tables 2 09-25-2010 11:18 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:00 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