Results 1 to 8 of 8
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range Object - HOW TO with cells?

    Word97SR2/VBA

    I've spent a couple of hours muttering over a keyboard on this, plus an hour pondering it. I can't get ranges to do what I want with cells.

    The test SUB below fires up a new document and selects a little block of cells within a table. I want to format just those Cells. I want to avoid actually SELECTING them within the FUNCTION; I'll use the range, if I can.

    The FUNCTION below - obviously recorded and then modified - is supposed to set the GRID format for just the range of cells.

    Instead, it appears to treat the entire rows in which the cells are selected. If you set a breakpoint just after the EndWith, you'll see that the LHS of the rows, the LH border of the TABLE has the grid line set.

    I can't find anything in the HELP files that tell me that a range of cells (rngCells) won't behave as a range of cells.

    Either it's a bug, or more likely, I'm still to young to be playing with Range objects.

    I'd appreciate some hints on this one.



    <pre>Sub TESTCellsGrid()
    Documents.Add
    ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=8, NumColumns:=8
    Selection.Tables(1).Select
    With Selection.Tables(1)
    .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
    .Borders(wdBorderRight).LineStyle = wdLineStyleNone
    .Borders(wdBorderTop).LineStyle = wdLineStyleNone
    .Borders(wdBorderBottom).LineStyle = wdLineStyleNone
    .Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
    .Borders(wdBorderVertical).LineStyle = wdLineStyleNone
    .Borders.Shadow = False
    End With
    Selection.MoveLeft unit:=wdCharacter
    Selection.MoveDown unit:=wdLine, Count:=3
    Selection.MoveRight unit:=wdCell, Count:=2
    ' Selection.MoveRight unit:=wdCell
    Selection.MoveRight unit:=wdCharacter, Count:=4, Extend:=wdExtend
    Selection.MoveDown unit:=wdLine, Count:=2, Extend:=wdExtend

    Call CellsGrid(Selection.Range)
    End Sub


    Public Function CellsGrid(rngCells As Range)
    ' Procedure: CellsGrid
    ' Description: GRID the selected number of cells.
    ' Copyright: Chris Greaves Inc.
    ' Inputs: None
    ' Returns: None
    ' Assumes: Nothing
    ' Side Effects: The current table formatting may change.
    ' Tested: By the calls below.
    If rngCells.Information(wdWithInTable) Then
    With rngCells.Cells
    With .Borders(wdBorderLeft)
    .LineStyle = wdLineStyleSingle
    .LineWidth = wdLineWidth075pt
    .ColorIndex = wdAuto
    End With
    ' SET A BREAKPOINT HERE AND INSPECT THE EXTREME lhs OF THE TABLE.
    With .Borders(wdBorderRight)
    .LineStyle = wdLineStyleSingle
    .LineWidth = wdLineWidth075pt
    .ColorIndex = wdAuto
    End With
    With .Borders(wdBorderTop)
    .LineStyle = wdLineStyleSingle
    .LineWidth = wdLineWidth075pt
    .ColorIndex = wdAuto
    End With
    With .Borders(wdBorderBottom)
    .LineStyle = wdLineStyleSingle
    .LineWidth = wdLineWidth075pt
    .ColorIndex = wdAuto
    End With
    If rngCells.Rows.Count > 1 Then
    With .Borders(wdBorderHorizontal)
    .LineStyle = wdLineStyleSingle
    .LineWidth = wdLineWidth075pt
    .ColorIndex = wdAuto
    End With
    Else
    End If
    If rngCells.Columns.Count > 1 Then
    With .Borders(wdBorderVertical)
    .LineStyle = wdLineStyleSingle
    .LineWidth = wdLineWidth075pt
    .ColorIndex = wdAuto
    End With
    Else
    End If
    .Borders.Shadow = False
    End With
    Else
    Call errMustBeInTable
    End If
    End Function
    </pre>


  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Range Object - HOW TO with cells?

    Weird.

    (1) It works if you pass a selection. No problem.

    (2) This returns bizarre results:

    Dim aCell As Cell
    For Each aCell In Selection.Range.Cells
    Debug.Print "Row " & aCell.RowIndex & " - Column " & aCell.ColumnIndex
    Next

    Every cell from the first to the last (left to right, top to bottom) is part of the collection, rather than the actual selection. I think this is the problem.

    What is the workaround? Applying the border to each cell individually - starting at the upper left, traversing to the lower right, using nested For/Next loops - seems shamefully inefficient. I'm wondering: can you (a) create your own cells collection or ([img]/forums/images/smilies/cool.gif[/img] weed out undesired cells from an existing collection?

    Trying to do the latter first led to this, which completely wrecked the table - I guess I should have known that .Delete doesn't just remove a reference from a collection, it removes the actual object from existence.

    Dim rngSelected As Range, aCell As Cell
    Dim intColStart As Integer, intColEnd As Integer
    Set rngSelected = Selection.Range
    intColStart = rngSelected.Cells(1).ColumnIndex
    intColEnd = rngSelected.Cells(rngSelected.Cells.Count).ColumnI ndex
    For Each aCell In rngSelected.Cells
    If aCell.ColumnIndex < intColStart Then
    aCell.Delete 'ouch, bye bye cell
    ElseIf aCell.ColumnIndex > intColEnd Then
    aCell.Delete 'ouch, bye bye cell
    Else
    Debug.Print "Row " & aCell.RowIndex & " - Column " & aCell.ColumnIndex
    End If
    Next

    So, that leaves (i) constructing the range differently, or (ii) using some other construct. Starting with the way the range was created, a manual range of cells behaves the same way as Selection.Range:

    Dim rngCellsRange As Range
    Set rngCellsRange = ActiveDocument.Range(ActiveDocument.Tables(1).Cell (4, 3).Range.Start, _
    ActiveDocument.Tables(1).Cell(6, 6).Range.End)

    This implies that Selection.Range is not to be understood literally. In other words, it doesn't really work. That leaves, finally, "some other construct." I suggest this row-by-row approach. If you have multiple operations to perform, you could create an array of ranges each consisting of the relevant contiguous cells.

    Dim intRowStart As Integer, intRowEnd As Integer
    Dim intColStart As Integer, intColEnd As Integer, intCount As Integer
    intRowStart = 4 'test values only
    intRowEnd = 6
    intColStart = 3
    intColEnd = 6
    For intCount = intRowStart To intRowEnd
    Call CellsGrid(ActiveDocument.Range(ActiveDocument.Tabl es(1). _
    Cell(intCount, intColStart).Range.Start, _
    ActiveDocument.Tables(1). _
    Cell(intCount, intColEnd).Range.End))
    Next

    I have avoided ranges on the principle that I don't need them. I can see they are quite a different beast.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Object - HOW TO with cells?

    Thanks for the exhaustive (and exhausting?) research.

    My guess is that a 3x3 array of cells within a table has a RANGE whose start point is the (ULC) upper-left corner of the upper-left cell, and whose end-point is the (LRC) lower-right corner of the lower-right cell.

    Now, if I were to apply one of my trusty Bookmark macros to such a range, I would consider that everything between the ULC.Start and LRC.End. Thus if I were to say "all the stuff between character 3456 and character 6789" I'd be including cells on the first of the three rows to the right of my 3x3 array, all the cells on the middle row, and cells to the left of the 3x3 on it's third, bottom rowm.

    What to do about the orphans on the top and bottom row? Ah! Bring'em in out of the cold.

    I bet that is how it is coded by Bill Gates.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Object - HOW TO with cells?

    Here's another funny regarding cell-selection in Word97SR2

    In a new document, create an 8x8 table (Table, Insert, 8, 8).

    Select a block of sixteen cells (4x4) in the centre of the table.

    Choose Format, Numbering; select a numbering style and confirm.

    Note how every cell gets numbered (1. through 64.) and the trailing paragraph gets number 65.

    Choose Edit, Undo (or start a new document etc.) and this time for the 4x4 selected cells apply a numbered style (Format, Style, ListNumber2).

    Note how only the selected cells get numbered.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Object - HOW TO with cells?

    I can test for cells, table or rows, but not for columns.

    For those of you with time on your hands, how would you test for columns being selected?

    Perhaps Word makes no distinction between columns and rows.


    <pre>Public Sub TableSelection()
    If Selection.Information(wdWithInTable) = True Then
    ' Preserve the original selection
    Dim rngSel As Range
    Set rngSel = Selection.Range
    ' Prepare for tests based on the premise that when an entire row is selected
    ' A CR past the end of the row is also selected.
    ' This makes the row selection one character greater than
    ' when only the cells of the row are selected.

    'I know of no test for columns.

    ' Here we record the start/end of the user's selection.
    Dim lngSelStart As Long
    Dim lngSelEnd As Long
    lngSelStart = rngSel.Start
    lngSelEnd = rngSel.End

    ' Now prepare to compare it to the equivalent rows
    Selection.SelectRow
    Dim rngSelRow As Range
    Set rngSelRow = Selection.Range
    Dim lngSelRowStart As Long
    Dim lngSelRowEnd As Long
    lngSelRowStart = rngSelRow.Start
    lngSelRowEnd = rngSelRow.End

    ' Now prepare to compare it to the equivalent table
    Dim rngTable As Range
    Set rngTable = Selection.Tables(1).Range
    Dim lngSelTableStart As Long
    Dim lngSelTableEnd As Long
    lngSelTableStart = rngTable.Start
    lngSelTableEnd = rngTable.End

    ' restore the original selection.
    rngSel.Select

    Dim strMsg As String
    strMsg = "" ' default result is an empty string
    If (lngSelStart = lngSelTableStart) And (lngSelEnd = lngSelTableEnd) Then
    strMsg = "table was selected"
    Else
    If lngSelEnd = lngSelRowEnd Then
    strMsg = "rows were selected"
    Else
    strMsg = "cells were selected"
    End If
    End If
    Else
    strMsg = "not in table at all"
    End If
    MsgBox strMsg
    End Sub
    </pre>


  6. #6
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Range Object - HOW TO with cells?

    Does the code in <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=wrd&Number=25299&page=0 &view=expanded&sb=5&vc=1#Post25299>this post</A> do it for you

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Object - HOW TO with cells?

    Yes.

    And me being me I have dragged it to the back of the cave to digest it.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Object - HOW TO with cells?

    (burp!)

    Below is a working-model, displays a MsgBox. Final procedure will return a result, possibly an integer code.

    I can now distinguish between the entire table being selected (Alt-A,A) and the cells being selected. The visual distinction lies in the row-ends off to the RHS of a table. The trick for me was to test the .End of the range.


    (1) Table, Selecttable is "all the table"

    (2) click-and-drag off to the LHS of the table is "all the table"

    (3) click-and-drag from the lower RH cell to the upper LH cell is "all cells in the table"

    (4) click-and-drag across cells in the top row, followed by table, SelectColumn is "all cells in the table".


    Can you/anyone think of any further diostinctions that might be made in a selection in a table?

    I have even experimented on an 8x8 table with a block of 2x3 cells deleted from the centre. Still works.

    <pre>Sub GetTableSelectionDetails()
    Dim strMsg As String
    If Selection.Information(wdWithInTable) = True Then
    Dim rngCurTable As Range
    Set rngCurTable = Selection.Tables(1).Range
    Dim rngCurSelect As Range
    Set rngCurSelect = Selection.Range
    Dim lngTblCellsCt As Long
    Dim lngTblRowsCt As Long
    Dim lngTblColsCt As Long
    Dim lngSelCellsCt As Long
    Dim lngSelRowsCt As Long
    Dim lngSelColsCt As Long
    ' Count Cells, Rows and Columns in the selection.
    lngSelCellsCt = Selection.Cells.Count
    lngSelRowsCt = Selection.Rows.Count
    lngSelColsCt = Selection.Columns.Count
    ' Count Cells, Rows and Columns in the table.
    lngTblCellsCt = rngCurTable.Cells.Count
    lngTblRowsCt = rngCurTable.Rows.Count
    lngTblColsCt = rngCurTable.Columns.Count
    If lngSelCellsCt = lngTblCellsCt Then ' there's a difference_
    ' between all the cells and all cells in the table.
    If rngCurTable.End = rngCurSelect.End Then
    strMsg = "Entire table is selected (" & lngSelCellsCt & " cells)"
    Else
    strMsg = "All cells in table are selected (" & lngSelCellsCt & " cells)"
    End If
    Else
    If lngSelColsCt = lngTblColsCt Then
    strMsg = lngSelRowsCt & " rows are selected"
    Else
    If lngSelRowsCt = lngTblRowsCt Then
    strMsg = lngSelColsCt & " columns are selected"
    Else
    strMsg = lngSelCellsCt & " cells are selected"
    End If
    End If
    End If
    Else
    strMsg = "not in table at all"
    End If
    MsgBox strMsg
    End Sub
    </pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •