what does (ByVal Target As Range) mean

[Zygoid]

what does (ByVal Target As Range) mean

 

[Chip Pearson]

It means that a variable named Target is passed in to the
procedure. The Target variable is a Range type variable, meaning
that it refers to (points to) a cell or range of cells.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Zygoid]

Thanks for your reply. I am new and learning.

How could I change this target to a range of A1:A50 rather than just
the single cell?

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
If .Address = "A1" Then
If .Value <> "" Then

 

[Doug Glancy]

Zygoid,

Perhaps this will help. The first test looks to see if the changed
selection is completely within the range A1:A50. The second checks to see
whether the changed selection intersects the range at all:

Private Sub Worksheet_Change(ByVal Target As Range)

If Union(Target, Range("A1:A50")).Address = "$A$1:$A$50" Then
MsgBox "target is completely within A1:A50"
ElseIf Not Intersect(Target, Range("A1:A50")) Is Nothing Then
MsgBox "target intersects A1:A50"
Else
MsgBox "target and A1:A50 don't intersect"
End If

End Sub

hth,

Doug

 

[Zygoid]

Thanks again for a reply! someone had given a suggestion to change the
code to this

With Target(1)
If Union(Target, Range("A1:A50")).Address = "$A$1:$A$50" Then
If .Value <> "" Then
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open("path\to\workbook")
ActiveWorkbook.Sheets("1").Activate
If Sheets("1").Range("A1:A50").Find( _
What:=.Value) Is Nothing Then
SourceWB.Close True
FORM.Show 'rename form to fit
End If
End If
End If
End With

This works great to open the form if text in cell is not listed, but I
have discovered another problem..

if the text in the cell is listed, the sourceWB will not close. it
stays open and is activated.

i figured i would need to add something like

If Sheets("1").Range("A1:A50").Find( _
What:=.Value) Is Something Then
SourceWB.Close True
end if

I have tried many variations by changing the word "nothing"
but it doesn't work. Any suggestions?

 

[JE McGimpsey]

One way:

Dim found As Range
With Target(1)
If Not Intersect(.Cells, Range("A1:A50")) Is Nothing Then
If Not IsEmpty(.Value) Then
Application.ScreenUpdating = False
With Workbooks.Open(pathtoworkbook)
Set found = .Sheets("1").Cells.Find( _
What:=Target(1).Value)
.Close True
End With
If found Is Nothing Then _
FORM.Show 'rename form to fit
End If
End If
End With

 

[Zygoid]

thanks! works great. i have spent hours pulling my hair out trying t
figure this out. :-