Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-22-2015, 01:32 PM
jc491's Avatar
jc491 jc491 is offline VBA Word Table - Select More than 1 Column at a time - Apply Formatting Windows 7 64bit VBA Word Table - Select More than 1 Column at a time - Apply Formatting Office 2013
VBA Novice
VBA Word Table - Select More than 1 Column at a time - Apply Formatting
 
Join Date: Sep 2015
Location: UK
Posts: 55
jc491 is on a distinguished road
Default VBA Word Table - Select More than 1 Column at a time - Apply Formatting

Hi all,




I apolgise if there is a solution already on here, I have not been able to locate it.

I have been trying to solve this problem.

I have tables that I need to format.

I would like to select a number of columns.

I know how to select 1 column and format it.

Can any one advise on how I may be able to select for

example


columns 3, 7 ,9 or any other columns I need to select.

I have looked at and tested various code snippets from:
https://msdn.microsoft.com/en-us/lib.../ff195142.aspx



I still can't work it out.
Code:
Sub FormatColumns()
ActiveDocument.Tables(1).Columns(6).Select
With Selection.Borders(wdBorderLeft)
  .ColorIndex = wdWhite
End With
With Selection.Borders(wdBorderRight)
  .ColorIndex = wdWhite
End With
With Selection.Borders(wdBorderTop)
  .ColorIndex = wdWhite
End With
With Selection.Borders(wdBorderBottom)
  .ColorIndex = wdWhite
End With
With Selection.Borders(wdBorderHorizontal)
  .ColorIndex = wdWhite
End With
End Sub
I am pretty sure that I'm misunderstanding the syntax somewhere.

is this possible:
ActiveDocument.Tables(1).Columns(3,6,9).Select


Later I would like to use the same logic to select multiple rows.


Any advice I would appreciate

Thank you in advance for your time

J

Last edited by macropod; 09-22-2015 at 10:04 PM. Reason: Added code tags & formating
Reply With Quote
  #2  
Old 09-22-2015, 10:13 PM
macropod's Avatar
macropod macropod is offline VBA Word Table - Select More than 1 Column at a time - Apply Formatting Windows 7 64bit VBA Word Table - Select More than 1 Column at a time - Apply Formatting Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,214
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Try something along the lines of:
Code:
Sub FormatColumns()
Dim i As Long, StrCols As String
If Selection.Information(wdWithInTable) = False Then Exit Sub
StrCols = InputBox("Please input the column #s to process." & vbCr & "(e.g. 1,2,3,5)")
On Error Resume Next
With Selection.Tables(1)
  If .Uniform = False Then Exit Sub
  For i = 0 To UBound(Split(StrCols, ","))
    With .Columns(Trim(Split(StrCols, ",")(i)))
      .Borders(wdBorderLeft).ColorIndex = wdWhite
      .Borders(wdBorderRight).ColorIndex = wdWhite
      .Borders(wdBorderTop).ColorIndex = wdWhite
      .Borders(wdBorderBottom).ColorIndex = wdWhite
      .Borders(wdBorderHorizontal).ColorIndex = wdWhite
    End With
  Next
End With
End Sub
As coded, the macro will process any single selected table, provided it doesn't have merged columns.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 09-23-2015, 04:17 AM
jc491's Avatar
jc491 jc491 is offline VBA Word Table - Select More than 1 Column at a time - Apply Formatting Windows 7 64bit VBA Word Table - Select More than 1 Column at a time - Apply Formatting Office 2013
VBA Novice
VBA Word Table - Select More than 1 Column at a time - Apply Formatting
 
Join Date: Sep 2015
Location: UK
Posts: 55
jc491 is on a distinguished road
Default

Hi Paul,
Thank you for the code.
This looks ideal.

I have put it in a module and run it. The message box comes up. I input the columns, nothing happens

any advice?
thank you
J
Reply With Quote
  #4  
Old 09-23-2015, 04:30 AM
jc491's Avatar
jc491 jc491 is offline VBA Word Table - Select More than 1 Column at a time - Apply Formatting Windows 7 64bit VBA Word Table - Select More than 1 Column at a time - Apply Formatting Office 2013
VBA Novice
VBA Word Table - Select More than 1 Column at a time - Apply Formatting
 
Join Date: Sep 2015
Location: UK
Posts: 55
jc491 is on a distinguished road
Default

Hi Paul,

If I create a new table it works perfectly.

When I try to run it on existing tables - the macro does not run.
When I press the run button nothing happens - the message box does not come up

Other than that it works perfectly on new created tables and exactly what I wanted.

I can't understand why it will not run on my existing tables. I am checking for merged columns.

J
Reply With Quote
  #5  
Old 09-23-2015, 04:38 AM
macropod's Avatar
macropod macropod is offline VBA Word Table - Select More than 1 Column at a time - Apply Formatting Windows 7 64bit VBA Word Table - Select More than 1 Column at a time - Apply Formatting Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,214
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

As I said, the macro won't process tables with merged columns (i.e. cells spanning more than one column); that's because such tables can't be process on a column-by-column basis - processing such tables requires knowing which cells to select for processing. If you don't believe that's the issue, attach a document to a post containing a representative problem table (no data needed). You do that via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

PS: Instead of using 'ColorIndex = wdWhite' I'd suggest using 'LineStyle = wdLineStyleNone'. That way, at least, the cell gridlines remain visible on-screen.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #6  
Old 09-23-2015, 05:53 AM
jc491's Avatar
jc491 jc491 is offline VBA Word Table - Select More than 1 Column at a time - Apply Formatting Windows 7 64bit VBA Word Table - Select More than 1 Column at a time - Apply Formatting Office 2013
VBA Novice
VBA Word Table - Select More than 1 Column at a time - Apply Formatting
 
Join Date: Sep 2015
Location: UK
Posts: 55
jc491 is on a distinguished road
Default

Hi Paul,

please have a look at simple table attached. The macro is in the module.

I don't think there are any merged cells?

thank you

J
Attached Files
File Type: docm Table Column Format.docm (24.7 KB, 6 views)
Reply With Quote
  #7  
Old 09-23-2015, 01:46 PM
macropod's Avatar
macropod macropod is offline VBA Word Table - Select More than 1 Column at a time - Apply Formatting Windows 7 64bit VBA Word Table - Select More than 1 Column at a time - Apply Formatting Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,214
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Works for me! I clicked in your table, ran the macro, choosing 2,4, and columns 2 & 4 got white borders. Same when I used 1,3,5.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 09-23-2015, 03:04 PM
jc491's Avatar
jc491 jc491 is offline VBA Word Table - Select More than 1 Column at a time - Apply Formatting Windows 7 64bit VBA Word Table - Select More than 1 Column at a time - Apply Formatting Office 2013
VBA Novice
VBA Word Table - Select More than 1 Column at a time - Apply Formatting
 
Join Date: Sep 2015
Location: UK
Posts: 55
jc491 is on a distinguished road
Default

Hi Paul,

Yes I tested it again works beautifully. Some times word is very temperamental.
This will be a life saver.

Having to fiddle about formatting columns and rows can be a real pain in word. Especially with a long table.

I am looking to fiddle about with the code and see if I can do a version for selecting multiple rows.

Many thanks again
J
Reply With Quote
  #9  
Old 09-23-2015, 03:10 PM
macropod's Avatar
macropod macropod is offline VBA Word Table - Select More than 1 Column at a time - Apply Formatting Windows 7 64bit VBA Word Table - Select More than 1 Column at a time - Apply Formatting Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,214
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Try:
Code:
Sub FormatRows()
Dim i As Long, StrRows As String
If Selection.Information(wdWithInTable) = False Then Exit Sub
StrRows = InputBox("Please input the row #s to process." & vbCr & "(e.g. 1,2,3,5)")
On Error Resume Next
With Selection.Tables(1)
  For i = 0 To UBound(Split(StrRows, ","))
    With .Rows(Trim(Split(StrRows, ",")(i)))
      .Borders(wdBorderLeft).ColorIndex = wdWhite
      .Borders(wdBorderRight).ColorIndex = wdWhite
      .Borders(wdBorderTop).ColorIndex = wdWhite
      .Borders(wdBorderBottom).ColorIndex = wdWhite
      .Borders(wdBorderVertical).ColorIndex = wdWhite
    End With
  Next
End With
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #10  
Old 09-24-2015, 04:19 AM
jc491's Avatar
jc491 jc491 is offline VBA Word Table - Select More than 1 Column at a time - Apply Formatting Windows 7 64bit VBA Word Table - Select More than 1 Column at a time - Apply Formatting Office 2013
VBA Novice
VBA Word Table - Select More than 1 Column at a time - Apply Formatting
 
Join Date: Sep 2015
Location: UK
Posts: 55
jc491 is on a distinguished road
Default

Hi Paul,

that's very generous of you thank you very much.

I have tested it and it works a charm.

Regarding the earlier issue of previous macro not running - I worked out the problem, albeit a minor detail:

You need to put the cursor in the selected table THEN run macro

When I was opening documents and running previous macro it would not respond, and I couldn't work out why word was being so temperamental.

I am very happy with the solutions and I am sure many people will benefit from it as I spent hours researching macros -and could not find one that allowed me to select more than 1 column or get one to work when testing and creating my own code.

Again Thank you

J

PS - please can admin mark as SOLVED
Reply With Quote
  #11  
Old 09-24-2015, 05:48 AM
macropod's Avatar
macropod macropod is offline VBA Word Table - Select More than 1 Column at a time - Apply Formatting Windows 7 64bit VBA Word Table - Select More than 1 Column at a time - Apply Formatting Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,214
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Quote:
Originally Posted by jc491 View Post
Regarding the earlier issue of previous macro not running - I worked out the problem, albeit a minor detail:

You need to put the cursor in the selected table THEN run macro
In post 2# I did say:
Quote:
As coded, the macro will process any single selected table
Likewise, in post # I said:
Quote:
I clicked in your table, ran the macro...
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 09-24-2015, 05:57 AM
jc491's Avatar
jc491 jc491 is offline VBA Word Table - Select More than 1 Column at a time - Apply Formatting Windows 7 64bit VBA Word Table - Select More than 1 Column at a time - Apply Formatting Office 2013
VBA Novice
VBA Word Table - Select More than 1 Column at a time - Apply Formatting
 
Join Date: Sep 2015
Location: UK
Posts: 55
jc491 is on a distinguished road
Default



It's been a long week looking at the screen.
Reply With Quote
  #13  
Old 09-24-2015, 06:03 AM
macropod's Avatar
macropod macropod is offline VBA Word Table - Select More than 1 Column at a time - Apply Formatting Windows 7 64bit VBA Word Table - Select More than 1 Column at a time - Apply Formatting Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,214
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

I know the feeling...
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Word Table - Select More than 1 Column at a time - Apply Formatting Table formatting- column widths to percent dherr Word VBA 2 03-17-2015 03:50 PM
Want a quotient using a constant to show up in column c each time a number is added to column b fibbermcghee Excel 2 12-09-2014 05:48 PM
VBA Word Table - Select More than 1 Column at a time - Apply Formatting how to apply table style WITHOUT setting it as a table? dylansmith Excel 9 05-16-2014 07:25 PM
Conditional Formatting: How to dynamically apply to an expanding table? tinfanide Excel 0 10-18-2013 06:34 AM
Apply formatting html version Kreol2013 Word VBA 0 08-26-2013 05:08 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:16 AM.


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