If function won't work when testing for empty combo box

[bconner]

I am using a form to prompt a User for a Group Number and Referring Location. Once the User Enters a Group Number and Selects a Referring Location they click a button and an Excel workbook is created and named using the Group and Location they selected on the form.

I am using an If function to check if the Referring Location Combo box is empty If it is I want to change the name of the file to only include the Group number else Name the File using Group Number and Referring Location Name.

I keep getting an error saying "Invalid use of Null"

Below is the code I am using:

Private Sub Command3_Click()
Dim User As String
Dim xlBook As Object
Dim xlApp As Object
Dim GrpNumber As String
Dim RefLoc As String

'Capture User Name from Windows Login
User = Environ$("USERNAME")

' Create Excel Workbook to Export DeepDive Queries into
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.workbooks.Add
GrpNumber = Form_FrmDeepDiveConsolidatedReports.Txt_Grp.Value
RefLoc = Form_FrmDeepDiveConsolidatedReports.Combo_LocationName.Value

If IsEmpty(RefLoc) = False Then
xlBook.SaveAs ("C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls")
 
xlBook.Close
 
Else
 
If IsEmpty(RefLoc) = True Then
xlBook.SaveAs ("C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & "DeepDive Reports.xls")
 
xlBook.Close
 
 
End If
End If

I even tried changing it to Read

If RefLoc = Null then

else

If RefLoc <> Null then

and I still get the same error message.

 

[rainman89]

Have you tried

if refloc ="" then

or maybe

if len(refloc)=0 then

 

[HiTechCoach]

TIP: If ... Then is not a function. IsEmpty() is a function . A function returns a value.


To avoid the invalid use of Null try changing this:

RefLoc = Form_FrmDeepDiveConsolidatedReports.Combo_LocationName.Value

to

RefLoc = Nz(Form_FrmDeepDiveConsolidatedReports.Combo_LocationName ,"")

 

[Kafrin]

You've defined RefLoc as a string, therefore it can't be null and you need to test for:

If RefLoc = ""

(and conversely, If RefLoc <> "")

I'd recommend code something like:

Private Sub Command3_Click()
    Dim xlBook As Object, xlApp As Object
    Dim User As String, GrpNumber As String, RefLoc As String, Path As String
 
'Capture User Name from Windows Login
User = Environ$("USERNAME")
 
    'Create Excel Workbook to Export DeepDive Queries into
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.workbooks.Add
    GrpNumber = Form_FrmDeepDiveConsolidatedReports.Txt_Grp.Value
    RefLoc = Form_FrmDeepDiveConsolidatedReports.Combo_LocationName.Value
 
    Path = "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " "
 
    If RefLoc <> "" Then Path = Path & RefLoc & " "  
 
    Path = Path & "DeepDive Reports.xls"
    xlBook.SaveAs Path
    xlBook.Close

 

[HiTechCoach]

Also, there is another environ variable you may want to use.

User = Environ$("USERPROFILE")

xlBook.SaveAs (User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls")

 

[bconner]

I used the NZ() Function and it worked like a charm.....

Thank you all for your suggestions, I appreciate it......