Solved: How to add new range in the VBA

[winxmun]

Hi,
I hv a VBA code as below that was written by somebody else. I am trying to add another range other than the existing range, ie "Claimed", "Closed". However I encounterred error by adding another new range. Anybody know how to overcome this error. Thanks a lot!

[VBA]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
'When the worksheet is double clicked return the range using the variable name 'target'

'------ Update UserName on DoubleClick ------
Dim s
'Dim n As String

Set s = Application.Intersect(Target, Range("Claimed", "Closed"))

's is the overlap between the double clicked range and our two pre-defined ranges

If Not s Is Nothing Then 'if there is an overlap

ActiveCell.Value = usernameLookup(Application.UserName)
'update the cell with the username
ActiveCell.Offset(0, 1).Value = Now()
'update the adjacent cell with the date and time
ActiveCell.Offset(0, 1).Activate
'move to the adjacent cell so that double clicked cell is not left in edit mode
End If
'/----- Update UserName on DoubleClick ------

Application.EnableEvents = True
End Sub
[/VBA]

[Bob Phillips]

Try it like this

[vba]

Dim s As Range
'Dim n As String

Set s = Application.Intersect(Target, Union(Range("Claimed"), Range("Closed")))
[/vba]

[p45cal]

The way it's currently written:
Range("Claimed", "Closed")
doesn't represent 2 distinct areas but one rectangular block. For example, if Claimed was just a single cell D3 and Closed was a single cell K33, Range("Claimed", "Closed") would be D3:K33. It makes no sense to add another range to this.

However, if it were written:
Range("Claimed, Closed")
(note how the quote marks have gone in the middle) this does refer to 2 distinct areas. Now it does make sense to add a third area:
Range("Claimed, Closed, AnotherOne")
but be aware that the code will only write something to the sheet if you double-click in one of these areas and won't if you double-click between them.

ps.
Cancel=True
somewhere in the code, preferably within the If statement if you want users to be able to edit the cell if they double click outside the designated area(s), will stop the cell remaining in Edit mode, so you won't need the ActiveCell.Offset(0, 1).Activate.

[mdmackillop]

There is a default variable Target which will be your active cell (unless you selected a range of cells), and can be referenced in the code.
[VBA]
If Not s Is Nothing Then 'if there is an overlap
Cancel = True
With Target
.Value = usernameLookup(Application.UserName)
'update the cell with the username
.Offset(0, 1).Value = Now()
'update the adjacent cell with the date and time
End With
End If
[/VBA]

[winxmun]

Dear All,

It works! Thanks to all of you...