Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Active cell is in Range (xp)

    Hello all,

    I'm trying to verify that the currently selected cell is inside of a particular named range. There's GOT to be an easier way than I'm doing it now:

    iLowCol = range.columns(1).column
    iHiCol = iLowCol + range.columns.count - 1
    ditto with iLowRow & iHiRow

    if activecell.column is >= iLowCol and <= iHiCol _
    and activecell.row is >= iLowRow and <= iHiRow then activecell is in the range

    This seems quite cumbersome to do. Isn't there some easy way to decide if a cell is in a range?

    Thanks,

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active cell is in Range (xp)

    First, you should not name a variable the same as a VBA restricted word. Therefore, you should not have an object variable named range.

    From your use of "range" in your sample code I am assuming that it as an object variable Dimed as a Range. I will change that to oRange in the following code:

    <pre>Dim oRngTest As Range
    Set oRngTest = Intersect(ActiveCell, oRange)
    If Not oRngTest Is Nothing Then
    MsgBox "Active cell is in the range."
    Else
    Msgbox "Active cell is not in the range."
    End If
    </pre>

    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active cell is in Range (xp)

    Thanks so much for responding so quickly. I'm aware of the keyword issue, I was actually using psudocode. My names are different than what I first supplied. I looked all over the help to find a function like this, but just couldn't. Silly me, I was looking in the EXCEL help, rather than the VBA help. As you know, there are several things you can do in VBA but not in excel itself. For example, try to find a function which returns the active cell without using vba.

    Anyway, this works perfectly. I have a dialog sheet in my workbook which looks up a member type from a table on another sheet. This code brings up the dialog to choose a type, which ends up being put into a specific cell on the current row. I don't want that to happen if I'm not in the proper area when I implement the macro. Here's what I ended up with:
    <pre>Sub GetMemberType()

    Dim rng As Range
    Dim rngTemp As Range

    'Setup.
    Cancel = False
    MemberType = 0
    Sheets(1).Activate

    'See if selection is in the proper range.
    Set rng = Range("List" & ActiveSheet.Name)
    Set rngTemp = Application.Intersect(ActiveCell, rng)
    If Not rngTemp Is Nothing Then

    DialogSheets("Member_Types").Show
    If Not Cancel Then
    MemberType = ThisWorkbook _
    .DialogSheets("Member_Types") _
    .ListBoxes("lstMemberTypes").ListIndex
    ActiveCell.EntireRow.Cells(1, 2).Value = MemberType
    End If

    Else
    MsgBox "Active cell must be in the list area.", vbCritical, "Error"
    End If
    Set rng = Nothing
    Set rngTemp = Nothing

    End Sub
    </pre>


    Thanks again for your help.

Posting Permissions

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