Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    142
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am working in Project 2007 outputting data to Excel 2007. Part of the process involves formatting the data that is output. Below is a subroutine that writes a piece of data and then attempts to format the row in two sections. The only problem is that the selection statement fails. Here's the code...

    Code:
    Private Sub sInsertRsrcName(strName As String, _
                                ByVal xlRange As Excel.Range, _
                                xlSheet As Excel.Worksheet)
        ' Insert the name.
        xlRange.Value = strName
        Set xlRange = xlRange.Offset(0, -1)
        Set xlRange = xlSheet.Range(ActiveCell, xlRange.Offset(0, 3)).Select        '<------------------
        ' Bold the font.
        xlRange.Font.Bold = True
        ' Fill the background yellow.
        xlRange.Interior.Color = 65535
        xlRange.Interior.Pattern = xlSolid
    
        ' Move over to the time entry area and fill the cells with a dark gray.
        Set xlRange = xlRange.Offset(0, 2)
        Set xlRange = xlSheet.Range(ActiveCell, xlRange.Offset(0, 98)).Select
        xlRange.Interior.Pattern = xlSolid
        xlRange.Interior.PatternColorIndex = xlAutomatic
        xlRange.Interior.ThemeColor = xlThemeColorLight1
        xlRange.Interior.TintAndShade = 0.349986266670736
    End Sub
    It fails at the line marked with the arrow. It is important to note that since this event can occur on random rows in the worksheet, absolute cell references won't work. That's why I use the ActiveCell and Offset statements excusively in the routine - and other similar ones that process the rest of the Project data. So, I need to know the correct way to select the cells that I need to format.

    Any help that the group can offer would be greatly appreciated.
    Bill Lugg

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,888
    Thanks
    0
    Thanked 188 Times in 172 Posts
    Hi Bill,

    There shouldn't be any need to Select the range. Are tou trying to do that so you can Activate the designated cell? If so, you could use:
    xlRange.Activate
    after:
    Set xlRange = xlSheet.Range(ActiveCell, xlRange.Offset(0, 3))
    (you'll need to make the same change a bit further down also. But I don't really see the need to do that. If everything is keyed to the initial range, you you be able to do everything via xlRange.Offset, without ever changing xlRange itself. I also can't see the point of either:
    Set xlRange = xlRange.Offset(0, -1)
    or:
    Set xlRange = xlRange.Offset(0, 2)
    since both are immediately over-ridden by Set xlRange = xlSheet.Range(ActiveCell, xlRange.Offset(0, #)).

    Try something along these lines:
    Code:
    Private Sub sInsertRsrcName(strName As String, _
      ByVal xlRange As Excel.Range, xlSheet As Excel.Worksheet)
      ' Insert the name.
      xlRange.Value = strName
      With xlSheet.Range(ActiveCell, xlRange.Offset(0, 2))
        ' Bold the font.
        .Font.Bold = True
        ' Fill the background yellow.
        With .Interior
          .Color = 65535
          .Pattern = xlSolid
        End With
      End With
      ' Move over to the time entry area and fill the cells with a dark gray.
      With xlSheet.Range(ActiveCell, xlRange.Offset(0, 100)).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0.349986266670736
      End With
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    142
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by macropod View Post
    Hi Bill,

    There shouldn't be any need to Select the range. Are tou trying to do that so you can Activate the designated cell? If so, you could use:
    xlRange.Activate
    after:
    Set xlRange = xlSheet.Range(ActiveCell, xlRange.Offset(0, 3))
    (you'll need to make the same change a bit further down also. But I don't really see the need to do that. If everything is keyed to the initial range, you you be able to do everything via xlRange.Offset, without ever changing xlRange itself. I also can't see the point of either:
    Set xlRange = xlRange.Offset(0, -1)
    or:
    Set xlRange = xlRange.Offset(0, 2)
    since both are immediately over-ridden by Set xlRange = xlSheet.Range(ActiveCell, xlRange.Offset(0, #)).

    Try something along these lines:
    Code:
    Private Sub sInsertRsrcName(strName As String, _
      ByVal xlRange As Excel.Range, xlSheet As Excel.Worksheet)
      ...
    End Sub
    To some extent this makes sense. I need to do the Offsets since I enter the sub in the second column in the row, but I want to highlight from the first column to the fourth. It turns out that ActiveCell is what was causing my problem, though. When I changed your With statement to "With xlSheet.Range(xlRange.Offset(0, -1), xlRange.Offset(0, 3))", in the first block for example, everything started workign as designed. So, I did the same with the other block of code and in one other place in another sub where I did something similar and we're now golden.

    Thanks for the help.
    Bill Lugg

Posting Permissions

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