Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass Defined Name to a Command Button
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass Defined Name to a Command Button
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pass a cell in sheet to macro or command button | Excel Programming | |||
VB's Command Button vs Form's Command Button | Excel Programming | |||
How to: Pass Command Line Parameter ??? | Excel Worksheet Functions | |||
Can't pass publically defined variable to a function | Excel Programming | |||
Command and pass value to Acces from Excel | Excel Programming |