I have a MSForm with many TextBox fields. I want them to have correct format and correct content so all communication to them goes through getters and setters, preNamed with TBfieldname
Suddenly there occured a situation where data read into the textbox had wrong format and content. I do not want to change my whole structure based on this odd situation, but just call a general function, and if the data is wrong, correct directly to the getters and setters.
'Initialize it
'Add Leader
If Not CheckForInconsistData("Leader", accounts.Cells(1, 1).Value, _
accounts.Cells(1, 2).Value, accounts.Cells(1, 3).Value, accounts.Cells(1, 4).Value) Then
TbLeader = accounts.Cells(1, 1).Value
...
Private Function CheckForInconsistData(sCtrlName As String, vCtrl1 As Variant, vCtrl2 As Variant, vCtrl3 As Variant, vCtrl4 As Variant) As Boolean
If vCtrl1 = "-" And (Not vCtrl2 = "-" Or Not vCtrl3 = "-" Or Not vCtrl4 = "-") Then
'Clear the fields for member and set it status to changed
Call SearchAndFillInBoardMember(sCtrlName, 0) 'Member 0 means clear the fields
Dim sFunctionName As String
sFunctionName = "UF_TableBoard.HasInconsistTb" & sCtrlName
Run (sFunctionName, True)
Run Application.ActiveWorkbook.Name & "!" & sFunctionName, True
End If
End Function
'Textbox TbLeader, representing a Variant
Private Property Get TbLeader() As Variant
TbLeader = txtBoxes(bwiTBLeader).GetValue
End Property
Private Property Let TbLeader(ByVal lNewValue As Variant)
txtBoxes(bwiTBLeader).SetReference lNewValue
txtBoxes(bwiTBLeader).SetValue lNewValue
End Property
Public Function HasInconsistTbLeader(ByVal bInconsist As Boolean)
txtBoxes(bwiTBLeader).InconsistData (bInconsist)
End Function
This function say to the userform that the field has changed even the user had not made any changes in GUI
In my general function I test if inputdata is valid, if so call the correct setter dependent on the name of the textbox. Build up a string ‘FunctionName’ representing the setter ” and then using: Run (FunctionName, parameters)
But when running the function through Run, i got error 1004.
I searched in the database on this issue, but did not found any good solution to the problem
My workbook has of course enabled Macroes, and the functions are public. One of the answers i fount to solve such problems was to put it in a module.
The functions is in a MSForm, i belive I could do the same in a MsForm
Tried:
Dim sFunctionName As String
sFunctionName = "UF_TableBoard.Tb" & sCtrlName
Run (sFunctionName, True)
Run Application.ActiveWorkbook.Name & "!" & sFunctionName, True
But none of them worked.
Is it problem with the notation or is it something compleatly different?
Is there a better way to think through the solution in general?
Thanks for any help that will solve my problem
Jo-Helge
Tried several ways to use the Run function
3
You did not answer my clarification question, so I will assume that you really need to call a function and use its return…
- Having such a function in a user form (
UserForm4) (It needs to NOT bePrivate!):
Public Function Multiplication(x As Long, y As Double) As Double
Multiplication = x * y
End Function
you can simple call it (from the same workbook) in the next way:
Sub testCallFunctionFromForm() 'it works.
Dim x As Double
x = UserForm4.Multiplication(3, 567.47)
Debug.Print x
End Sub
It will return 1702.41
- You can call a function from a different workbookk only if it exists in a standard module. So, you need a function wrapper in such a module, respectively:
Function UFMultiplicationWrapper(a As Long, x As Double) As Double
UFMultiplicationWrapper = UserForm4.Multiplication(a, x)
End Function
- Then activate the above used workbook and call the user form function trough the wrapper function in the next way:
Sub testCallFunctionFromFormOtherWb() 'it works...
Dim x As Double
Dim wb As Workbook: Set wb = ActiveWorkbook
x = Application.Run("'" & wb.Name & "'!" & "UFMultiplicationWrapper", 3, 867.47)
Debug.Print x
End Sub
It will return 2602.41…
Note: If it is not a matter of ActiveWorkbook, using wb.FullName will also work even if the workbook is not open (it opens it…)
So, in order to receive the returned function output the used parenthesis evaluates the run function output. And placing the workbook name (full name) between “‘” … “‘” takes care of spaces between words contained in the workbook name/full name. Otherwise, it will error, the string parameters being split according to existing spaces…
But, if you need to call a Sub from a user form (the same UserForm4 example) you need such a sub in its code module:
Public Sub MyMessage(strMess As String)
MsgBox strMess
End Sub
and a wrapper Sub as:
Sub UFSubMessageWrapper(x As String)
UserForm4.MyMessage x
End Sub
in the same workbook with the respective UserForm.
Then, activate the workbook call the sub it as:
Sub callSubFromOtherWBUserForm()
Dim wb As Workbook: Set wb = ActiveWorkbook
Application.Run "'" & wb.Name & "'!" & "UFSubMessageWrapper", "Hello!"
End Sub
It will raise a message containing “Hello!”…
As you can see, no need of parenthesis because nothing is to be evaluated…
7