Function parameters: trying to pass a control

[Mole Hunter]

I am having trouble trying to pass a control (textbox) object to a
function I wrote. I want to pass the textbox so that I can manipulate
its properties more easily without having to worry about which textbox
I am working with. (It is some repetitive code and I am trying to be
good by creating subroutines to do things in a generic manner.)

I realize that the passed "callername" parameter in the function is
probably redundant, I just haven't taken it out yet. (It can be
replaced by using the control's .NAME property...if it worked right.
It is a bit cleaner to use "callername".)

When I debug the code the value of the passed parameter shows up as
the controls value. For example, if the textbox contains "11" the
control CALLER in the function shows as having the value of "11" and
not as the name of the text box control that I sent it.

However, if you look in the function, there is a msgbox that I build
that uses a property of the control thusly: caller.Name. In debug
mode that does have the correct value, i.e. it returns the name of the
control that I passed to the function. But it is the other attempts
to use the control that don't appear to be working: caller.value.
They don't evaluate to anything an my ISDATE check fails. The whole
purpose of this routine is to do some data validation of what was
entered into the box. I am trying to capture a time value from the
user, hours at a minimum and maybe some minutes.

There may be typos further down in the code, I noticed a few and
corrected them while pasting it into this message. But the code is
failing near the top at the IF NOT ISDATE line, so they shouldn't
matter at this point.

Here is my code for the function call:
---------------------
<...>
numChkResult = NumberCheck(txtbxEndTime, "End")
<...>
---------------------
Function NumberCheck(caller As Control, callername As String) As
Integer

Dim splArray As Variant
Dim convArr

Dim arrCallerName(2) As String
Dim callingbox As Object 'Want to be able to return to source
control

Dim arrayCount As Integer 'Don't exactly have an array.count
property, so using this instead w/ UBOUND

splArray = Split(caller.Value, ":")

If Not IsDate(caller.Value) Then
MsgBox "The value of the calling box is: " & caller.Value &
vbCrLf & _
"The calling text box, " & caller.Name & ", doesn't appear to have
a valid time in it."
NumberCheck = 1 'setting return value to 1, indicating a
problem
Exit Function
End If

arrayCount = UBound(splArray) + 1 'Just setting the variable
here to clean up the code a bit

If arrayCount > 2 Then
MsgBox "There were too many colons in the " & caller.Name & " text
box. Go back and try again."
MsgBox "Trying to set the focus back to the original text box that
made the call: " & caller.Name
NumberCheck = 1 'setting return value to 1, indicating a
problem
Exit Function
End If

If Not IsEmpty(splArray(0)) Then
Select Case splArray(0)
Case 1 To 12
MsgBox "The first portion of the array appears to be
between 1 and 12, and is therefore valid."
Case Else
MsgBox "The hours entered for " & caller.Name & " Time
don't appear to be actual numbers or are not between 1 and 12. " &
vbCrLf & _
"This input only accepts numbered hours from 1 to 12.
Then use the AM/PM selector to choose the time of day." & vbCrLf & _
"Go back and try again."
NumberCheck = 1 'set return value =1, indicates a
problem
End Select
Else
MsgBox "There doesn't appear to be anything entered into the " &
caller & "."
End If

If arrayCount > 1 Then 'get array index errors if they don't
enter colon and minutes, so put this check in
If Not IsEmpty(splArray(1)) Then
Select Case splArray(0)
Case 0 To 59
MsgBox "The first portion of the array appears to be
between 0 and 59, and is therefore valid."
Case Else
MsgBox "The MINUTES entered for " & caller.Name & "
Time don't appear to be actual numbers or are not between 0 and 59. "
& vbCrLf & _
"This input only accepts numbered hours from 0 to 59.
Then use the AM/PM selector to choose the time of day." & vbCrLf & _
"Go back and try again."

NumberCheck = 1 'set return value =1, indicates a
problem

End Select
End If
End If
End Function

 

[NickHK]

I didn't go through all your code, but if you want to pass a textbox, try:

Function NumberCheck(TextBoxToCheck As MSForms.Textbox) As Integer
......
or the more generic Control
Private Function Checker(argTBox As MSForms.Control) As Integer
......

NickHK

 

[Mole Hunter]

I didn't go through all your code, but if you want to pass a textbox, try:

Function NumberCheck(TextBoxToCheck As MSForms.Textbox) As Integer
.....
or the more generic Control
Private Function Checker(argTBox As MSForms.Control) As Integer
.....

NickHK


news:[email protected]...

Thanks for the tip. It did sort of seem to help. I still do not get
any response from the editor window in debug when I hover over
"caller.Name". But when I start typing the object name "caller." it
does come up with the various properties and methods of a textbox. So
it may have been working all along.

I guess I am still running into a problem with the ISDATE function.
If my textbox value is "11" the ISDATE fails and says it is a
problem. But if it is "11:0" then the ISDATE check works fine.
("11:" fails, too.) Even forcing the string "11" into a number "11"
doesn't make ISDATE happy. Darn it all.

But this ended up working:
... IsDate (Format (caller.value, "Hh:Nn")) Then...

I don't know how robust it is.

Thanks for the feedback. I will keep plugging away at it.

 

[Helmut Weber]

Hi Mole Hunter,

help on IsDate:
Returns a Boolean value indicating whether an expression
*can* be converted to a date.

Unless you want to invent the wheel again,
use something like calender control for inputting a date,
or date and time picker control,
available depending on office or office pro or
Windows-version or whatsoever.

--
Cheers

Helmut Weber

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[NickHK]

I would avoid using "caller" as the name of the argument, as
Application.Caller is used by VBA to indicate the source of a call to
routine.
That why I did not use that name in my example, although I didn't expand on
it admittedly.
I doubt that is your problem though; follow helmut's advice and provide a
better for your user to input a valid date.

NickHK

 

[Mole Hunter]

I see what you mean about using the name. I will change that.

I have things working mostly as I had started out (I haven't tried
Helmut's suggestion yet). But now I have created a new problem for
myself.

I want to do a secondary pass of the object. So here is the example:

-----------------------
'The first call to the function:
Sub
<...>
numChkResult = NumberCheck(txtbxEndTime, "End") 'Passing the calling
object and an identifier string
<...>
End sub

Function NumberCheck(boxcaller As MSForms.TextBox, callername As
String) As Integer
<...>
colorset (boxcaller)
<...>

sub colorset (boxcaller2 as msforms.textbox)
'Now in here I want to use the passed textbox and set some colors on
it.
boxcaller2.backcolor = rgb (255,0,0)
end sub
----------------

When I hover over the boxcaller object in the function shown above, it
just tells me that the value is whatever text has been entered into
the textbox (like I mentioned originally). And then when it reaches
the function call I get an "object required" error.

So how do I pass on the passed control?


Thanks,
J

 

[NickHK]

In the first call you are using the return value of the function; so the
brackets around the argument(s) are required.
In the second call, you are NOT using the return value (it's a Sub, so there
is not one); so no brackets.

Check the help for the use of Call also.

NickHK

 

[Mole Hunter]

Thanks for the pointer. I managed to get it to work, but I went ahead
and used the CALL keyword (I like the look of the parenthesis - makes
it cleaner I think).


-- J

 

[NickHK]

Yes, I prefer the use of Call, as it make the intent obvious.

NickHK