Userform vba error for empty cell or non-numeric

KDS14589

Board Regular
Joined
Jan 10, 2019
Messages
180
Office Version
  1. 2016
Platform
  1. Windows
i have a userform that I made recently and it works fine, but now I wish it to check the text boxes if they're blank or if the ones for numerical entry have letters in them. This code I hoped to work but I keep getting this error (image)

The "controls_check_blank" and "controls_check_numbers" are dynamic from lists on sheets("controls") that I type the name of the textbox into.

Next thing I'm working on is how to set the different numerical format for each textbox.


VBA Code:
Option Explicit
Sub Check_Blanks()

Dim Ken_Cell As Range

For Each Ken_Cell In Sheets("controls").Range("controls_check_blanks")
    
    If Card_Name_UF.Controls(Ken_Cell.Value) = "" Then
    MsgBox "Make sure all textboxes have entries"
    Exit Sub
    
    End If
    
Next Ken_Cell

For Each Ken_Cell In Sheets("Controls").Range("Controls_check_number")
    If IsNumeric(Card_Name_UF.Controls(Ken_Cell.Value)) = False Then
    
    MsgBox "Make sure all Numeric boxes have only numbers in them"
    Exit Sub
     End If
     
Next Ken_Cell

Call Transfer_Data

End Sub
 

Attachments

  • MrExcel 8.3.21   .png
    MrExcel 8.3.21 .png
    36.6 KB · Views: 12

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Which is the error message?
When you debug the failing instruction, open the Vba Immediate windows (Contr-g should do the job, or use Menu /View /Immediate window) and type these commands:
VBA Code:
?Ken_Cell.Value
?Card_Name_UF.Controls(Ken_Cell.Value)
The first one will return the name of the control under test; the second the content of the examined control

Examine the result of these commands, maybe it will turn the light on
Or publish what is in the Immediate window and we will check with you

As far as distinguish between numerical and text entries, maybe you might use a different suffix for the names of the controls (ex: TBNameA, TBBirthD, TBRevenueN; A=alpha, D=Date, N=Number) and excecute the specifich check according thi suffix)

Bye
 
Upvote 0
Which is the error message?
When you debug the failing instruction, open the Vba Immediate windows (Contr-g should do the job, or use Menu /View /Immediate window) and type these commands:
VBA Code:
?Ken_Cell.Value
?Card_Name_UF.Controls(Ken_Cell.Value)
The first one will return the name of the control under test; the second the content of the examined control

Examine the result of these commands, maybe it will turn the light on
Or publish what is in the Immediate window and we will check with you

As far as distinguish between numerical and text entries, maybe you might use a different suffix for the names of the controls (ex: TBNameA, TBBirthD, TBRevenueN; A=alpha, D=Date, N=Number) and excecute the specifich check according thi suffix)

Bye
I tried that but get a 'Real Time' error #424 object required(see image)
 

Attachments

  • Anthony47.png
    Anthony47.png
    147.8 KB · Views: 6
Upvote 0
Sorry I gave you wrong information:
First type the command ?Ken_Cell.ValueThis will give you the name of the control that is under examination
Secondo type the command ?Card_Name_UF.Controls(Ken_Cell.Value)This will give you the content of the examined control

Give the error I think that the first command will return the name of a non existant textbox

Bye
 
Upvote 0
Sorry I gave you wrong information:
First type the command ?Ken_Cell.ValueThis will give you the name of the control that is under examination
Secondo type the command ?Card_Name_UF.Controls(Ken_Cell.Value)This will give you the content of the examined control

Give the error I think that the first command will return the name of a non existant textbox

Bye
I still get that error #424 object required after the ken_cell.value
 
Upvote 0
Can you explain what is in the range named "controls_check_blanks"?

Add the two "Debug.Print" lines in these position:
VBA Code:
'previous instructions
Dim Ken_Cell As Range
Debug.Print ">>>"
For Each Ken_Cell In Sheets("controls").Range("controls_check_blanks")
    Debug.Print Ken_Cell.Address, Ken_Cell.Value
    If Card_Name_UF.Controls(Ken_Cell.Value) = "" Then
'next instructions
When you get the error, open the "vba Immediate windows", copy its content from ">>>" on, and paste it in your next message.

Bye
 
Upvote 0
Can you explain what is in the range named "controls_check_blanks"?

Add the two "Debug.Print" lines in these position:
VBA Code:
'previous instructions
Dim Ken_Cell As Range
Debug.Print ">>>"
For Each Ken_Cell In Sheets("controls").Range("controls_check_blanks")
    Debug.Print Ken_Cell.Address, Ken_Cell.Value
    If Card_Name_UF.Controls(Ken_Cell.Value) = "" Then
'next instructions
When you get the error, open the "vba Immediate windows", copy its content from ">>>" on, and paste it in your next message.

Bye


I cheated and did some 'work arounds' involving setting a format and text length requirement for each textbox. This solves my problems (I HOPE) and I'll just 'delete' the problematic code. I appreciate you working on this code, but because I'm still a newbie at VBA Code I going the easy route.

THANKS for the help
 
Upvote 0
Well done (doing it the way you know is always the best approach)
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top