cell selected in a named range. 2

[roadstain]

How would I determine when a cell is selected within a named cell range. I have a workbook in which A10:Y400 is named "Database." Assuming a user selected a cell somwehere in the workpage and launched a macro I need to determine if the cell is within the "Database" or outside to perform the macro with somewhat diffrent results.

Thoughts?

 

[DaleWatson123321]

Hi qritch,

Unfortunately, Excel VBA does NOT allow for the use of "Application.WorksheetFunction" for ALL of its functions.

However, I've put together the following "worksheet based" formulas, and a short VBA routine that tests the result of these formulas. Bottom line... it works.

On a SEPARATE sheet, I've entered these formulas, and assigned range names to each of the cells:

Cell A1 - entered label: cur_row
Cell B1 - assigned range name: cur_row

Cell A2 - entered label: cur_colm
Cell B2 - assigned range name: cur_colm

The above picks up the current row and column from the VBA routine.

Cell A3 - entered label: first_colm
Cell B3 - entered formula: =CELL("col",database)

Cell A4 - entered label: last_colm
Cell B4 - assigned range name: last_colm
Cell B4 - entered formula: =COLUMNS(database)+CELL("col",database)-1

Cell A5 - entered label: first_row
Cell B5 - assigned range name: first_row
Cell B5 - entered formula: =CELL("row",database)

Cell A6 - entered label: last_row
Cell B6 - assigned range name: last_row
Cell B6 - entereed formula: =ROWS(database)+CELL("row",database)-1

Cell A7 - entered label: cur_cell
Cell B7 - assigned range name: cur_cell
Cell B7 - entered formula: =AND(cur_row>=first_row,cur_row<=last_row,cur_colm>=first_colm,cur_colm<=last_colm)


The VBA routine is as follows...

Sub Check_CurrrentCell()
Range(&quot;cur_row&quot.Value = ActiveCell.Row
Range(&quot;cur_colm&quot.Value = ActiveCell.Column
curcell = Range(&quot;cur_cell&quot.Value

'If the cursor (Active Cell) is within the
'database, &quot;curcell&quot; will be TRUE and the
'following example code will activate.

If curcell Then
ActiveCell.Offset(2, 0).Activate
End If

End Sub


I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca

 

[DaleWatson123321]

P.S.

In double-checking the posting, I see I forgot to add the line...

Cell B3 - assigned range name: first_colm

...Dale Watson

 

[xlbo]

Okay, you need to use the Insersect method (only available in VBA...NOT worksheet)

Put this in the worksheet_Selection_Change event

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Set isect = Application.Intersect(Range(&quot;Database&quot, Range(Target.Address))
If isect Is Nothing Then
Exit Sub
Else
MsgBox &quot;Selected Cell is in Database&quot;
End If
End Sub
Rgds
~Geoff~

 

[bryanbayfield]

Geoff- intersect is global, no need to prefix with application.

Bryan.

 

[xlbo]

So it is......I've always used Application with intersect 'cos that's how it's shown in excel help
thanx for the info Bryan Rgds
~Geoff~

 

[roadstain]

Thank you of the input, this is starting to work out. What I am doing is I wrote a macro that would insert a new row in a manner in which the formulas that are needed and some formatting is put into the new row. The trick here is there are two diffrent named cell ranges. I refered to them as Database and Sub_Database. What I was trying to do specifically is when the &quot;CTRL-I&quot; macro is launched it would need to react diffrently depenting on which named cell range the new row would be inserted in to. And of course do just about nothing if some one tried to insert a row that was not within either of the two named cell ranges.

 

[xlbo]

Ok - might've helped if this'd been mentioned earlier....
try this
Sub WhichRange()
Set isectdb = Intersect(Range(&quot;Database&quot, Range(Activecell.Address))
Set isectSubdb = Intersect(Range(&quot;Sub_Database&quot,Range(Activecell.address))

If not isectdb Is Nothing Then
'code for intersect with &quot;Database&quot;
Else
If not isectSubdb is nothing then
'code for intersect with &quot;Sub_Database&quot;
Else
End If
End If
End Sub

This is dependant on a cell being selected to indicate where the row is going to be inserted. If an entire row is selected (ie by clicking on the row number on the left of the spreadsheet), you'll need to use different syntax for Range(Activecell.Address)
Rgds
~Geoff~

 

[roadstain]

Sorry about the lack of info, when I started there was only one named cell range (Datebase, SubDatabase did not exist yet) but of course things changed as the spec got more and more complicated.

Thanks for the info, worked out well. Will mark a few stars. Thanks.

-Q