Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 02-20-2020, 07:50 PM
macropod's Avatar
macropod macropod is offline Macro to highlight alternating sections of a table Windows 7 64bit Macro to highlight alternating sections of a table 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 Jennifer Murphy View Post
Do you have a way for the macro to read the line (paragraph) just before the table? If so, I could put the parameters there.
That seems a whole lot more bother for the user than simply answering a few input box prompts. It's also more error-prone. How would the parameters be differentiated there, and what would you have in the paragraph for the colouring?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #17  
Old 02-20-2020, 08:43 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Macro to highlight alternating sections of a table Windows XP Macro to highlight alternating sections of a table Office 2007
Competent Performer
Macro to highlight alternating sections of a table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Your code reads the first row of the table outside the loop then starts the loop with the second data line. This is probably the best solution. My only reluctance is that means the code to read the data is in the macro in two places, which always opens up the possibility of them not being identical. I always try to never duplicate code if I can help it.

The other solution that I have used is to check inside the loop if it's the first iteration, but that puts code inside the loop that is unnecessary after the first iteration, which I also don't like.

What if I change the datatype of the variable to Variant? Is there a value, like Null, that I can initialize it to that can never be a match for anything that could ever be in a Word table?
Reply With Quote
  #18  
Old 02-20-2020, 10:04 PM
macropod's Avatar
macropod macropod is offline Macro to highlight alternating sections of a table Windows 7 64bit Macro to highlight alternating sections of a table 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 Jennifer Murphy View Post
My only reluctance is that means the code to read the data is in the macro in two places, which always opens up the possibility of them not being identical.
But you still need to initialise it somehow. And, of course, the whole point of the comparison is to change the highlight when they cease to be identical. Rather more complicated code could be used to do that within the loop, but the code would then run more slowly overall, which brings us to:
Quote:
Originally Posted by Jennifer Murphy View Post
The other solution that I have used is to check inside the loop if it's the first iteration, but that puts code inside the loop that is unnecessary after the first iteration, which I also don't like.
Nor do I.
Quote:
Originally Posted by Jennifer Murphy View Post
What if I change the datatype of the variable to Variant? Is there a value, like Null, that I can initialize it to that can never be a match for anything that could ever be in a Word table?
I can't see the point of that - one could just as easily initialise it as:
StrTitle = ""
(or not even bother initialising it), but then the shading would necessarily start at the second data row (undesirable), though the code could be modified to make it start at the first data row.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #19  
Old 02-20-2020, 10:44 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Macro to highlight alternating sections of a table Windows XP Macro to highlight alternating sections of a table Office 2007
Competent Performer
Macro to highlight alternating sections of a table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
That seems a whole lot more bother for the user than simply answering a few input box prompts. It's also more error-prone. How would the parameters be differentiated there, and what would you have in the paragraph for the colouring?
Well, the user is me, so I guess one person's bother is another's simplicity.

I envision a line containing text something like this or whatever is easy to read and easy to parse in the macro. I'd play with it a bit.
Code:
Parms: Col=3, Hdrs=0, RGB=215,199,244
For me, typing this once in the Word document is far easier that typing it in each time I call the macro. In the application at hand, I will be needing to re-highlight the table fairly often. With the parameters next to the table, I can do that with a single keyboard shortcut.

Also, if I had more than one table in the document, or even in different documents, the parameters would be set and could be different for each one.

If the macro doesn't find a line that has the right syntax, it would use the default values.

Last edited by Jennifer Murphy; 02-20-2020 at 10:52 PM. Reason: Typo
Reply With Quote
  #20  
Old 02-20-2020, 10:51 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Macro to highlight alternating sections of a table Windows XP Macro to highlight alternating sections of a table Office 2007
Competent Performer
Macro to highlight alternating sections of a table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
I can't see the point of that
The point is, as I said, initializing it to something that cannot be in the table. That way, the compare in the first pass through the loop will always fail, which is what I need.

But even if you cannot see a point, do you know if there is a Variant value that will fail a compare with any possible text value?

Quote:
- one could just as easily initialise it as:
StrTitle = ""
That fails if the data in the first row of the table is "". I admit that that is highly unlikely, but as Murphy (or someone) said, anything that can go wrong will go wrong.
Reply With Quote
  #21  
Old 02-20-2020, 11:28 PM
macropod's Avatar
macropod macropod is offline Macro to highlight alternating sections of a table Windows 7 64bit Macro to highlight alternating sections of a table 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

OK, try the following:
Code:
Sub TblHiLite()
Application.ScreenUpdating = False
Dim Hdr As Long, S As Long, W As Long, C As Long, R As Long, H As Long
Dim StrTitle As String, StrParms As String, StrRGB As String
'Abort if the cursor is not in a table
If Selection.Information(wdWithInTable) = False Then
  MsgBox "The selection is not in a table!", vbOKOnly, "TblHiLite"
  GoTo ErrExit
End If
'Get Parameters
On Error GoTo ErrExit
StrParms = Split(Selection.Tables(1).Range.Previous.Paragraphs.Last.Range.Text, vbCr)(0)
C = Trim(Split(Split(StrParms, "=")(1), ",")(0))
Hdr = Trim(Split(Split(StrParms, "=")(2), ",")(0))
StrRGB = Trim(Split(StrParms, "=")(3))
S = RGB(Split(StrRGB, ",")(0), Split(StrRGB, ",")(1), Split(StrRGB, ",")(2))
On Error GoTo 0
W = RGB(255, 255, 255)
'process the table
With Selection.Tables(1)
  If C > .Columns.Count Then
    MsgBox "There is no column " & C & " in the table!", vbOKOnly, "TblHiLite"
    End
  End If
  If Hdr > .Rows.Count Then
    MsgBox "There is no row " & Hdr & " in the table!", vbOKOnly, "TblHiLite"
    End
  End If
  StrTitle = Split(.Cell(1 + Hdr, C).Range.Text, vbCr)(0)
  .Rows(1 + Hdr).Shading.BackgroundPatternColor = W: H = W
  For R = 2 + Hdr To .Rows.Count
    If Split(.Cell(R, C).Range.Text, vbCr)(0) <> StrTitle Then
      If H = W Then H = S Else H = W
      StrTitle = Split(.Cell(R, C).Range.Text, vbCr)(0)
    End If
    .Rows(R).Shading.BackgroundPatternColor = H
  Next
End With
Application.ScreenUpdating = True
End
ErrExit:
MsgBox "Invalid Parameter", vbExclamation
End Sub
Note that your parameters can specify any +ve value for the number of header rows. As coded, the sub simply aborts if the parameters are invalid.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #22  
Old 02-21-2020, 07:09 AM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Macro to highlight alternating sections of a table Windows XP Macro to highlight alternating sections of a table Office 2007
Competent Performer
Macro to highlight alternating sections of a table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
OK, try the following:
Wow! Thank you very much. I'll work on that today.
Quote:
Note that your parameters can specify any +ve value for the number of header rows.
Yes, I'll do some validity checking.

What is "+ve"?

Reply With Quote
  #23  
Old 02-21-2020, 02:49 PM
macropod's Avatar
macropod macropod is offline Macro to highlight alternating sections of a table Windows 7 64bit Macro to highlight alternating sections of a table 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 Jennifer Murphy View Post
What is "+ve"?
The opposite of -ve (i.e. 0 or greater)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #24  
Old 02-21-2020, 03:20 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Macro to highlight alternating sections of a table Windows XP Macro to highlight alternating sections of a table Office 2007
Competent Performer
Macro to highlight alternating sections of a table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
OK, try the following:
Code:
StrParms = Split(Selection.Tables(1).Range.Previous.Paragraphs.Last.Range.Text, vbCr)(0)
Is there a way for me to test whether there is a line before the table? That is, whether the table is the first thing in the document?
Reply With Quote
  #25  
Old 02-21-2020, 03:47 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Macro to highlight alternating sections of a table Windows XP Macro to highlight alternating sections of a table Office 2007
Competent Performer
Macro to highlight alternating sections of a table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
The opposite of -ve (i.e. 0 or greater)
Ok, but what does "ve" stand for? Maybe I'm too dense, but I've never seen that expression before? Is it an Aussie expression?
Reply With Quote
  #26  
Old 02-21-2020, 07:12 PM
macropod's Avatar
macropod macropod is offline Macro to highlight alternating sections of a table Windows 7 64bit Macro to highlight alternating sections of a table 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 Jennifer Murphy View Post
Is there a way for me to test whether there is a line before the table? That is, whether the table is the first thing in the document?
For example:
Code:
Sub Demo()
If Selection.Tables(1).Range.Start = ActiveDocument.Range.Start Then
 ' code for table at top of document
End If
End Sub
Quote:
Originally Posted by Jennifer Murphy View Post
Ok, but what does "ve" stand for? Maybe I'm too dense, but I've never seen that expression before? Is it an Aussie expression?
+ve = positive; -ve = negative
I'd have thought -ve was fairly common, +ve less so. See: What do -ve and +ve mean? - Quora
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #27  
Old 02-21-2020, 07:59 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Macro to highlight alternating sections of a table Windows XP Macro to highlight alternating sections of a table Office 2007
Competent Performer
Macro to highlight alternating sections of a table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
For example:
Code:
If Selection.Tables(1).Range.Start = ActiveDocument.Range.Start Then
 ' code for table at top of document
End If
Perfect, thanks

Quote:
+ve = positive; -ve = negative
I'd have thought -ve was fairly common, +ve less so. See: What do -ve and +ve mean? - Quora
Aha! Now I get it.
Reply With Quote
  #28  
Old 02-22-2020, 08:04 AM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Macro to highlight alternating sections of a table Windows XP Macro to highlight alternating sections of a table Office 2007
Competent Performer
Macro to highlight alternating sections of a table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

It just occurred to me that there would be no need to pass a parameter to declare the number of header rows if the macro could test for it. After a little research, I came up with this code, which seems to work:

Code:
Dim Row as long
With Selection.Tables(1)              
  'Skip over any header rows
  For Row = 1 To .Rows.Count
    If .Rows(Row).HeadingFormat <> -1 Then
      Exit For
  Next Row
Are there any problems with this code or is there a better way?

Last edited by Jennifer Murphy; 02-22-2020 at 08:26 AM. Reason: Typo: Change "=" to "<>"
Reply With Quote
  #29  
Old 02-22-2020, 02:28 PM
macropod's Avatar
macropod macropod is offline Macro to highlight alternating sections of a table Windows 7 64bit Macro to highlight alternating sections of a table 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 Jennifer,
An approach like that could be made to work for tables that do have the 'Repeat Heading Rows' option checked, but I'd question whether that's likely on tables that fit wholly within a page. In any event, it's a once-off exercise to add that parameter and you'd still have to supply the others.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #30  
Old 02-22-2020, 03:58 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Macro to highlight alternating sections of a table Windows XP Macro to highlight alternating sections of a table Office 2007
Competent Performer
Macro to highlight alternating sections of a table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Hi Jennifer,
An approach like that could be made to work for tables that do have the 'Repeat Heading Rows' option checked, but I'd question whether that's likely on tables that fit wholly within a page. In any event, it's a once-off exercise to add that parameter and you'd still have to supply the others.
Aha! Good point!!

I started on this "little" project for a table that spans a lot of pages, currently 10 and growing. So I had that in mind. And I always check that option because I never know if a table might grow and eventually split across a page.

I think I'll include the Headers=n parameter. If it's present, I'll use it. If not, I'll check for the Repeat option and use that. If neither are present, I'll assume no headers and start highlighting with row 1.

Thanks again
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to highlight alternating sections of a table Macro to highlight a list of words bakerkr Word VBA 4 10-19-2017 02:23 PM
Macro to highlight alternating sections of a table Word 2010 VBA Print Macro - Specified Sections Benbon Word VBA 3 03-30-2017 02:31 PM
Macro to highlight alternating sections of a table Macro Question: Need help making a macro to highlight the first word in every sentence LadyAna Word 1 12-06-2014 10:39 PM
Macro to highlight alternating sections of a table Macro to highlight words bertietheblue Word VBA 9 07-01-2013 12:39 PM
find - reading highlight - highlight all / highlight doesn't stick when saved bobk544 Word 3 04-15-2009 03:31 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:13 AM.


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