Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-28-2012, 12:39 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline How to select the first row of the current table Windows XP How to select the first row of the current table Office 2007
Competent Performer
How to select the first row of the current table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default How to select the first row of the current table

I am trying to write a macro to change some table settings. It's sorta working, but I could use a little help.

1. I can't figure out how to select the first row of the current table so I can set the font and flag it as a header row.

As I understand it, this code selects the second row in the first table of a document:
Code:
ActiveDocument.Tables(1).Rows(2).Select
Can I then use this code to select the first row in the table where the cursor is?
Code:
Selection.Tables(1).Rows(1).Select
I am also using this code to ensure that the cursor is actually in a table.


Code:
If Selection.Information(wdWithInTable) <> True Then
  MsgBox "The cursor is not in a table", , "MyTableSettings macro"
  Exit Sub
End If
Reply With Quote
  #2  
Old 01-28-2012, 03:41 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline How to select the first row of the current table Windows XP How to select the first row of the current table Office 2007
Competent Performer
How to select the first row of the current table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

I think I got it. Here's the macro. It changes three settings:
  • It turns off the Break Across Pages setting (for rows).
  • It turns off the Auto Fit setting (for columns).
  • It set row 1 as a header row if the user says "Yes".
Here's the code. Comments appreciated.

Code:
'===========================================================================
'                        My Table Settings
' Correct the table settings to what Word should have made them.
' 07/23/10  Recorded and modified.
' 01/28/12  Add code for header row
'===========================================================================
Sub MyTableSettings()
Const MyName = "MyTableSettings"
Dim SettingBreakOld                 'Old setting
Dim SettingAutoFitOld               'Old setting
Dim SettingHeaderRow As Boolean     'True = set first row as header
Dim SettingHeaderRowOld As Boolean  'Old setting
Dim Msg
'Abort if the cursor is not in a table
If Selection.Information(wdWithInTable) <> True Then
  MsgBox "The cursor is not in a table", vbOKOnly, MyName
  Exit Sub
End If
 
'Ask if they want a header row?
Select Case MsgBox("Set row 1 as header?", vbYesNoCancel, MyName)
  Case vbYes
    SettingHeaderRow = True
  Case vbNo
    SettingHeaderRow = False
  Case vbCancel
    MsgBox "Aborted", vbOKOnly, MyName
    Exit Sub
End Select
'Save old settings
SettingBreakOld = Selection.Rows.AllowBreakAcrossPages
If SettingBreakOld = -1 Then   '-1 = On, 0 = Off
  SettingBreakOld = "On"
ElseIf SettingBreakOld = 0 Then
  SettingBreakOld = "Off"
End If
SettingAutoFitOld = Selection.Tables(1).AllowAutoFit
If SettingAutoFitOld = "True" Then   'True = On, False = Off
  SettingAutoFitOld = "On"
ElseIf SettingAutoFitOld = "False" Then
  SettingAutoFitOld = "Off"
End If
SettingHeaderRowOld = Selection.Tables(1).Rows(1).HeadingFormat
'Set the new ones
Selection.Tables(1).Select                                    'Select the entire table
Selection.Rows.AllowBreakAcrossPages = False                  'Stop rows from breaking
Selection.Tables(1).AllowAutoFit = False                      'Stop auto-resizing
Selection.Tables(1).Rows(1).HeadingFormat = SettingHeaderRow  'Set the heading setting
'Report the results
Msg = "Autofit = Off (was " & SettingAutoFitOld & "), " & vbCrLf & _
      "Break = Off (was " & SettingBreakOld & "), " & vbCrLf & _
      "Header row = " & SettingHeaderRow & " (was " & SettingHeaderRowOld & ")"
MsgBox Msg, vbOKOnly, MyName
End Sub
Reply With Quote
  #3  
Old 01-28-2012, 07:03 PM
macropod's Avatar
macropod macropod is offline How to select the first row of the current table Windows 7 64bit How to select the first row of the current table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,065
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

Hi Jennifer,

Your code could be simplified a bit. In particular, there's no need to select the whole table before working on it. Aside from executing faster, not selecting it has the advantage of leaving the selected range unchanged:
Code:
Sub MyTableSettings()
Const MyName = "MyTableSettings"
Dim SettingBreakOld                 'Old setting
Dim SettingAutoFitOld               'Old setting
Dim SettingHeaderRowOld As Boolean  'Old setting
Dim Msg
'Abort if the cursor is not in a table
If Selection.Information(wdWithInTable) = False Then
  MsgBox "The cursor is not in a table", vbOKOnly, MyName
  Exit Sub
End If
'Save old settings
With Selection.Tables(1)
  SettingBreakOld = (.Rows.AllowBreakAcrossPages = True)
  SettingAutoFitOld = (.AllowAutoFit = True)
  SettingHeaderRowOld = (.Rows(1).HeadingFormat = True)
  'Set the new ones
  .Rows.AllowBreakAcrossPages = False                  'Stop rows from breaking
  .AllowAutoFit = False
  'Ask if they want a header row?
  Select Case MsgBox("Set row 1 as header?", vbYesNoCancel, MyName)
    Case vbYes
      .Rows(1).HeadingFormat = True
    Case vbNo
      .Rows(1).HeadingFormat = False
    Case vbCancel
      MsgBox "Aborted", vbOKOnly, MyName
      Exit Sub
  End Select
  'Report the results
  Msg = "Autofit = True (was " & SettingAutoFitOld & "), " & vbCrLf & _
      "Break = True (was " & SettingBreakOld & "), " & vbCrLf & _
      "Header row = " & (.Rows(1).HeadingFormat = True) & " (was " & SettingHeaderRowOld & ")"
  MsgBox Msg, vbOKOnly, MyName
End With
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 01-28-2012, 08:12 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline How to select the first row of the current table Windows XP How to select the first row of the current table Office 2007
Competent Performer
How to select the first row of the current table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Paul,

Thanks for the suggestions.

The reason I selected the whole table was because I wanted the "Do not break" option to be applied to all rows. From your code, it appears that the

Code:
With Selection.Tables(1)
 
   ...
 
End With
acts as if the table were selected within that With block. Is that correct? This is indeed much better code.

The other change that I noticed is you changed my test from "<> True" to "= False". Is there a reason for that? I wrote it my way in case there is some other possibility besides True and False, such as Null or Empty. I have been burned before, especially with VBA, when the application (Word) returns odd results. I want to do something if it's true only.
Reply With Quote
  #5  
Old 01-28-2012, 08:24 PM
macropod's Avatar
macropod macropod is offline How to select the first row of the current table Windows 7 64bit How to select the first row of the current table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,065
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

Hi Jennifer,

In a sense the 'With Selection.Tables(1) .. End With' block does act as if the table were selected. Since your selection point is already within the table, that's all that's needed.
Quote:
The other change that I noticed is you changed my test from "<> True" to "= False". Is there a reason for that?
Only that your SettingHeaderRowOld variable returns True/False, whilst the others were returning On/Off - which amounted to the same thing. AllowAutoFit can only be True/False, though .Rows.AllowBreakAcrossPages (and, hence SettingBreakOld) could be True/False/Undefined - the latter if some rows were allowed to break and others weren't.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #6  
Old 01-29-2012, 11:41 AM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline How to select the first row of the current table Windows XP How to select the first row of the current table Office 2007
Competent Performer
How to select the first row of the current table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

I am making progress, but have run into a couple of puzzling results.

I wanted to use this same syntax to change the font and the borders. I started by capturing the code using the recorder. This is what I got:

Code:
Sub Macro1()
  Selection.Font.Name = "Calibri"
  Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone
  Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
  Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
  Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone
  Selection.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
  Selection.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
  Selection.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
End Sub
Then I tried to adapt it to my macro.

For the font change line, I got a Compile error: "Methor or data member not found".

For the last two lines of the border settings, I got a Runtime error '5941': "The requested member of the collection does not exist".

These lines are shown in red and commented out.

Code:
With Selection.Tables(1)
  .Rows.AllowBreakAcrossPages = False
  .AllowAutoFit = False
  .Rows(1).HeadingFormat = SettingHeaderRow
'  .Font.Name = "Calibri"
  If SettingHeaderBorderOff Then  'Turn all but bottom border off
    With Selection.Tables(1).Rows(1)
      .Borders(wdBorderTop).LineStyle = wdLineStyleNone
      .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
      .Borders(wdBorderRight).LineStyle = wdLineStyleNone
      .Borders(wdBorderVertical).LineStyle = wdLineStyleNone
'      .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
'      .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
    End With
  End If
End With
What am I doing wrong?
Reply With Quote
  #7  
Old 01-29-2012, 03:35 PM
macropod's Avatar
macropod macropod is offline How to select the first row of the current table Windows 7 64bit How to select the first row of the current table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,065
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

Hi Jennifer,

For the font, you need to use:
.Range.Font.Name = "Calibri"

For the borders, I deleted the reference to 'Selection.Tables(1)'. I don't get any errors with:
Code:
  If SettingHeaderBorderOff Then  'Turn all but bottom border off
    With .Rows(1)
      .Borders(wdBorderTop).LineStyle = wdLineStyleNone
      .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
      .Borders(wdBorderRight).LineStyle = wdLineStyleNone
      .Borders(wdBorderVertical).LineStyle = wdLineStyleNone
      .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
      .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
    End With
  End If
I did have to comment out the 'If' test, as there doesn't seem to be a SettingHeaderBorderOff variable setting to test in the code you've supplied.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 01-29-2012, 05:40 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline How to select the first row of the current table Windows XP How to select the first row of the current table Office 2007
Competent Performer
How to select the first row of the current table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

OK. I changed the font code to use the ".range". That now works. Is there some way I should have known that or been able to figure it out?

I deleted the 'Selection.Tables(1)', but I still get the error.

I've posted the entire macro below. It has the right variables declared. I made a bunch of changes to the way the options are selected and reported, but the core code is the same.

Code:
Sub MyTableSettings()
 
Const MyName = "MyTableSettings"
Dim SettingBreakOld, SettingAutoFitOld, SettingFontSw, SettingFontOld, _
    SettingHdrRowSw, SettingHdrRowOld, SettingHdrBrdrOffSw, SettingSelectTableSw _
    As Boolean  'Setting variables
Dim Msg, MsgAutoFit, MsgBreak, MsgHdrRow, MsgFont, MsgBorder As String
 
'Abort if the cursor is not in a table
If Selection.Information(wdWithInTable) <> True Then
  MsgBox "The cursor is not in a table", vbOKOnly, MyName
  Exit Sub
End If
 
'Get user options
If vbYes = MsgBox("Take all defaults?", vbYesNo, MyName) Then
  SettingHdrRowSw = True
  SettingHdrBrdrOffSw = True
  SettingFontSw = True
  SettingSelectTableSw = True
Else
  SettingHdrRowSw = (vbYes = MsgBox("Set row 1 as header?", vbYesNo, MyName))
  SettingHdrBrdrOffSw = (vbYes = MsgBox("Header row borders off?", vbYesNo, MyName))
  SettingFontSw = (vbYes = MsgBox("Set font to Calibri?", vbYesNo, MyName))
  SettingSelectTableSw = (vbYes = MsgBox("Leave table selected?", vbYesNo, MyName))
End If
 
With Selection.Tables(1)
 
  'Save the old settings
  SettingBreakOld = .Rows.AllowBreakAcrossPages
  SettingAutoFitOld = .AllowAutoFit
  SettingHdrRowOld = .Rows(1).HeadingFormat
  SettingFontOld = .Range.Font.Name
 
  'Set the rest of the new settings
  .Rows.AllowBreakAcrossPages = False                 'Do not allow rows to break across pages
    MsgBreak = "Break = " & .Rows.AllowBreakAcrossPages & " (was " & SettingBreakOld & ")" & vbCrLf
  .AllowAutoFit = False                               'Do not autofit columns
    MsgAutoFit = "Autofit = " & .AllowAutoFit & " (was " & SettingAutoFitOld & ")" & vbCrLf
  .Rows(1).HeadingFormat = SettingHdrRowSw            'Set header row on/off
    MsgHdrRow = "Header row = " & .Rows(1).HeadingFormat & " (was " & SettingHdrRowOld & ")" & vbCrLf
 
  If SettingFontSw Then         'Set font?
    .Range.Font.Name = "Calibri"
    MsgFont = "Font = " & .Range.Font.Name & " (was " & SettingFontOld & ")" & vbCrLf
  Else
    MsgFont = ""
  End If
 
  If SettingHdrBrdrOffSw Then  'If no header row borders, turn all but bottom off
    With .Rows(1)
      .Borders(wdBorderTop).LineStyle = wdLineStyleNone
      .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
      .Borders(wdBorderRight).LineStyle = wdLineStyleNone
      .Borders(wdBorderVertical).LineStyle = wdLineStyleNone
'      .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
'      .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
    End With
    MsgBorder = "Header row borders off"
  Else
    MsgBorder = "Header row borders unchanged"
  End If
 
  If SettingSelectTableSw Then Selection.Tables(1).Select 'Leave table selected?
 
  'Report the results
  Msg = MsgAutoFit & MsgBreak & MsgHdrRow & MsgFont & MsgBorder
  MsgBox Msg, vbOKOnly, MyName
 
End With
End Sub
Reply With Quote
  #9  
Old 01-29-2012, 06:15 PM
macropod's Avatar
macropod macropod is offline How to select the first row of the current table Windows 7 64bit How to select the first row of the current table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,065
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

Hi Jennifer,

The code runs just fine for me, on both Word 2003 & 2010. Maybe there's a problem with your document or your Word 2007 installation. Repairing Word (Word Options|Resources|Diagnose) may help.

I note that you've declared your variables thus:
Code:
Dim SettingBreakOld, SettingAutoFitOld, SettingFontSw, SettingFontOld, _
    SettingHdrRowSw, SettingHdrRowOld, SettingHdrBrdrOffSw, SettingSelectTableSw _
    As Boolean  'Setting variables
Dim Msg, MsgAutoFit, MsgBreak, MsgHdrRow, MsgFont, MsgBorder As String
You should be aware that this means that all except SettingSelectTableSw and MsgBorder are Variants.

I also note that, with the borders for example, you have an option to delete them or leave them alone. IMHO it would be better to allow the user to choose to have or not have the borders, including on a table that presently lacks them. In that case, you'd need something like:
Code:
  Dim Bdr As Long
  If SettingHdrBrdrOffSw Then  'If no header row borders, turn all but bottom off
    Bdr = 0
    MsgBorder = "Header row borders off"
  Else
    Bdr = 1
    MsgBorder = "Header row borders on"
  End If
  With .Rows(1)
    .Borders(wdBorderTop).LineStyle = Bdr
    .Borders(wdBorderLeft).LineStyle = Bdr
    .Borders(wdBorderRight).LineStyle = Bdr
    .Borders(wdBorderVertical).LineStyle = Bdr
    .Borders(wdBorderDiagonalDown).LineStyle = Bdr
    .Borders(wdBorderDiagonalUp).LineStyle = Bdr
  End With
BTW, you could reduce:
If SettingSelectTableSw Then Selection.Tables(1).Select 'Leave table selected?
to:
If SettingSelectTableSw Then .Select 'Leave table selected?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #10  
Old 01-29-2012, 06:50 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline How to select the first row of the current table Windows XP How to select the first row of the current table Office 2007
Competent Performer
How to select the first row of the current table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Dang. You are right about the Dim statement. I looked on the MSFT website:

http://msdn.microsoft.com/en-us/library/7ee5a7s1(v=vs.80).aspx

and found this paragraph.

Different Types. You can specify different data types for different variables by using a separate As clause for each variable you declare. Alternatively, you can declare several variables to be of the same type by using a common As clause. Each variable takes the data type specified in the first As clause encountered after its variablename part.

It sure seems to me to say that I can group them as I did. They even give this example:

Code:
Dim a, b, c As Single, x, y As Double, i As Integer
' a, b, and c are all Single; x and y are both Double
I guess it works in VB but not in VBA. Another cute little gotcha from MSFT.

I ran the diagnostics. It found no errors. I don't need those 2 lines, so I'll just delete them.

As for the borders, I would do as you suggest it I were writing this for general use. It's just for me and mainly for one project where I need the tables to look a certain way. I don't need every option.

Anyway, thanks for all your help.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to select the first row of the current table How to call current PC date and/or current PC year KIM SOLIS Excel 2 11-04-2011 06:09 PM
How to select the first row of the current table Select Text in Table but Table Gets Selected Too RBusiness Word 1 06-07-2011 04:26 PM
How to select the first row of the current table Access to the property of the current table b0x4it Word VBA 2 05-26-2011 06:25 AM
Table - Check Boxes - Create Select All lajohn1963 Word Tables 2 09-25-2010 11:18 AM
How to select the first row of the current table Auto insert current month's name and current year Styler001 Word 4 01-25-2010 06:40 PM

Other Forums: Access Forums - Senior Forums

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


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