Active cell is in Range (xp)

Topic

New Reply

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 = 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,

Reply | Quote

Replies

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:

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

Reply | Quote

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:

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

Thanks again for your help.

Reply | Quote

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:

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

Thanks again for your help.

Reply | Quote

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:

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

Reply | Quote