info@manfridayit.co.ukinfo@manfridayit.co.uk
message ManFridayITSend Message
LinkedIn ManFridayIT
phone ManFridayIT01332 949783

VBA-Check All Form TextBoxes And Replace Specific Characters

If you need to check VBA textboxes for input of a specific character or string (or to replace the same) , or check that textboxes have input then here are a couple of simple functions to help carry it out.

Here are examples of checking all textboxes on a userform for single quotation mark (or apostrophe) and replace it with a ` (eg for sql inputs/updates if you don’t use sql parameters), and also for empty textboxes.

Have this line or so of code called before the sql update / commit command

Dim l_dummy As Variant
l_dummy = checkText(Me)
(for pre-determined input)
or
l_dummy = checkEmptyText(Me) (checking for empty textboxes)

It calls a function (checkText or checkEmptyText) in your Globals section and passes the current Form as a parameter.

Have this function in your Globals Section for replacing pre-determined input

Public Function checkText(ByRef frmName As MSForms.UserForm) As Boolean
Dim Ctrl As Control
For Each Ctrl In frmName.Controls
If TypeName(Ctrl) = "TextBox" Then
Ctrl.Text = Replace(Ctrl.Text, "'", "`")
End If
Next Ctrl
checkText = True
End Function

The parameter (Me) is the form that the function call originates from, and is transferred ByRef to the userform variable ‘frmName’

Then each Control on the originating form (ie textbox, label, frame, combobox etc) is checked to see if it is a textbox.
If it is a textbox then its contents are checked to see if it contains the character ‘, if found then ALL instances will be replaced with the character `.
These changes are reflected on the userform itself and the function ends.

——————

Have this function in your Globals Section for highlighting empty textboxes

Public Function checkEmptyText(ByRef frmName As MSForms.UserForm) As Boolean
Dim Ctrl As Control
For Each Ctrl In frmName.Controls
If TypeName(Ctrl) = "TextBox" Then
If Ctrl.Text = "" Or IsNull(Ctrl.Text) = True Then
MsgBox ("No input in textbox " & Ctrl.Name)
checkEmptyText = True
Exit Function
End If
End If
Next Ctrl
checkEmptyText = True
End Function

The parameter (Me) is the form that the function call originates from and is transferred ByRef to the userform variable ‘frmName’
Then each Control on the originating form (ie textbox, label, frame, combobox etc) is checked to see if it is a textbox.
If it is a textbox then its contents are checked to see if it contains an empy string or if it has a Null value.
If either of these conditions is met then a messagebox displays the message “No input in textbox (and displays the textbox name)”
The function ends on the first instance of a textbox that meets the checked conditions.

NB It appears if this could be solved by using a sub-routine rather than a function, but by using a function you can utilise the ByRef on the passed parameter.
Without using ByRef an error occurs.

Also the function does not need to return a value as it is not utilised in anyway, however it is good practise to always use a return value in a function.

ManFridayIT excels in VBA Coding and Macro Development, please email info@manfridayit.co.uk for further information.






Comments

ManFridayIT welcomes a Reply or Comment

Your email address will not be published. Required fields are marked *