Pass Defined Name to a Command Button
I got it to work, however I have a couple of questions/comments:
In answer to your question, Oracle_No is defined (insert-name-define)
on the sheet with the code. LookUpRange is defined in the code:
Private Sub Check_For_Existing_Oracle_No_Click()
Dim rng As Range
Dim Test As Variant
Sheets("Upload Data").Select
Set rng = GetRealLastCell(ActiveSheet)
lookuprange = ("$C$2:" + rng.Address)
Test = Application.VLookup(Me.Range("oracle_no").Value, _
Sheets("Upload Data").Range(lookuprange), 1, False)
If IsError(Test) Then
MsgBox "It wasn't found"
Else
MsgBox "it was found"
End If
End Sub
When I use the debugger to trace oracle_no (or even print a message
box), oracle_no is blank. Why is it not returning the oracle_no?
Is there an easy way to delete all rows in LookUpRange containing the
oracle_no?
Thanks, Tom.
Connie
Tom Ogilvy wrote:
Test = Application.VLookup(Me.Range("oracle_no").Value, _
Sheets("Upload Data").Range("lookuprange"), 1, False)
If you have a defined name (insert =name=Define) with name of oracle_no
and that is located on the sheet with the code and another defined name
(insert=name=Defined) with a name of Lookuprange found on Sheet Upload
Data, then you would use the above.
Adjust to match you actual situation.
If it isn't a defined name (insert=Name=Define), then what do you mean by
defined name - do you mean a variable named Oracle_no as in
Oracle_No = "B123"
Then you would need to declare that as a public variable at the top of a
general module (insert=Module in the VBE) above any procedures
Public Oracle_No as String
Pubic Lookuprange as String
some procedure would have to set the value of the varibles before you
clicked the button.
--
Regards,
Tom Ogilvy
"Connie" wrote in message
ups.com...
I am trying to pass a user defined name (Oracle_no) to a command
button. In the following code, I am doing a VBLOOKUP on oracle_no, but
when I debug the code, I find that oracle_no is not being passed.
Should I do this in a function instead? I guess I can't utilize
defined field names in a command button?
Private Sub Check_For_Existing_Oracle_No_Click()
Dim rng As Range
Dim Test As Variant
Sheets("Upload Data").Select
Set rng = GetRealLastCell(ActiveSheet)
lookuprange = ("$C$2:" + rng.Address)
MsgBox "oracle no follows"
MsgBox oracle_no
Test = Application.VLookup(oracle_no, Range(lookuprange), 1, False)
MsgBox IsError(Test)
MsgBox lookuprange
If IsError(Test) Then
MsgBox "It wasn't found"
Else
MsgBox "it was found"
End If
End Sub
|