Ignoring an empty field on form

[Smee]

I've tried looking for a solution to this one, but just can't find anything with what I'm searching under.

I have a form with 2 boxes empty, awaiting user input.

The first box specifies which excel workbook to open, and the second box specifies which worksheet to goto once it has been opened.

On clicking on a button these 2 values are then passed into a module which opens the corresponding file.

It all works great, but how do I allow the lazy user to just enter data into the first box, and then leave the second box blank - allowing them to just open the workbook without having to specifiy the worksheet.

At the moment when I try to do this, it pops up with a type mismatch error as it is expecting to pass 2 values in, and can only find 1.

Can I somehow tell it to pass null if there is nothing there?

Or can I have another module that fills in any blank fields with some default value when the button is pressed?

I'm sure there is away to do, probably a simple way, but at the moment I can't find how.

Any, and all help much appreciated.

Thanks

 

[Kodo]

if you're passing these values to a function then you can use the OPTIONAL argument for that value.

i.e.

Function PassToExcel(ByRef val1 as string, optional val2 as string)

end function

or you can check the values before you use them

if isnull(val2) then
val2=""
end if

 

[Smee]

Thanks for your quick reply Kodo

if isnull(val2) then

I tried something along those lines before posting, but I put it in the called module, obviously not helping.

Where would those lines need to go? Thats whats stumping me.

The module is one I made, does that also come with an Optional argument, or is that a feature of included functions?

 

[Kodo]

well, how are you passing the values to the module?

are you calling Forms![formname]![textboxvalue]

perhaps if you posted some code so that we can see the process a little better?

 

[Smee]

Ok, this is the name of my function :-

Function Timesheet(Name_box As String, Period As String)

and I call it OnClick with :-

=Timesheet([Name_box],[Period])

It's Period that I want to be optional.

The rest of the function is :-

Function Timesheet(Name_box As String, Period As String)

Dim strFileToOpen
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

' File to open, created by adding the value typed in to the box on the form as well
strFileToOpen = "Z:\Job Search\Timesheets\" + Name_box + ".xls"

' Establish a connection to a excel application
Set objXL = New Excel.Application

'Open the correct file
With objXL
    .Visible = True
    
    'Set filepath to be opened. Filename specified by user input
    Set objWkb = .Workbooks.Open(strFileToOpen)
    
    'Set worksheet to move to within the file. Based on user input
    Set objSht = objWkb.Worksheets(Period)
    objSht.Select
    
    
End With
    
'Close connnections
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

End Function

Thanks

 

[Kodo]

you would do this

Function Timesheet(Name_box As String, OPTIONAL Period As String)

if isnull(period) or period="" then
Period="N/A"
end if

Dim strFileToOpen
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

' File to open, created by adding the value typed in to the box on the form as well
strFileToOpen = "Z:\Job Search\Timesheets\" + Name_box + ".xls"

' Establish a connection to a excel application
Set objXL = New Excel.Application

'Open the correct file
With objXL
.Visible = True

'Set filepath to be opened. Filename specified by user input
Set objWkb = .Workbooks.Open(strFileToOpen)

'Set worksheet to move to within the file. Based on user input
Set objSht = objWkb.Worksheets(Period)
objSht.Select


End With

'Close connnections
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

End Function

 

[Smee]

I did what you suggested. And it makes perfect sense. But it still displays the same error.

Whatever is triggering this error happens before that code you had me add.

The message I am getting is :-

The expression On Click you entered as the event property setting produced the following error: Type mismatch.

The expression may not result in the name of a macro, the name of a user-definied, or [Event Procedure].

There may have been an error evaluating the function, event, or macro

Do I need to add "Optional" to the function call? :confused

 

[Smee]

Found a solution

Hey Kodo

Thanks muchly for your help.

My solution was to include the code you gave me on the OnGotFocus for the button.

That way, it runs the code, and inserts the default value before it runs the OnClick code and brings up the error.

 Private Sub OpenTimesheet_GotFocus()

 If Me.Period = "" Or IsNull(Me.Period) Then
    Me.Period.value = "04-01"
 End If
 
 End Sub

Thanks again

Smee.