Detecting spaces in UserForm textboxes
 

Hi all,

I'm writting error code to search for empty textboxes in a userform.

Basically I have 4 textboxes that are to be associated with each other
These textboxes represent data pulled off of a spreadsheet that is t
be edited. If there is data in any of the 4, then all 4 must have dat
in them as well. If all 4 are empty, then the record on the spreadshee
is deleted.

My problem is if the user uses the spacebar instead of the Del key t
remove data from the textboxes, then the cells aren't truely empty a
far as VBA is concerned.

How do you make VBA count spacebar values as null value entries?

i.e. make "" = " " = " " = " ", etc?

I'm currently using:

If Controls("Textbox" & N).value < "" and _
(Controls("Textbox" & O).value = "" or _
Controls("Textbox" & P).value = "" or _
Controls("Textbox" & Q).value = "") then

MsgBox "Error, partially completed treatment detected!"
Exit Sub
End if

Repeated for each of the different textboxes.


Thanks

--
Ouk
-----------------------------------------------------------------------
Ouka's Profile: http://www.excelforum.com/member.php...fo&userid=2398
View this thread: http://www.excelforum.com/showthread.php?threadid=39794

Detecting spaces in UserForm textboxes
 

Ouka Wrote:
Hi all,

I'm writting error code to search for empty textboxes in a userform.

Basically I have 4 textboxes that are to be associated with each other
These textboxes represent data pulled off of a spreadsheet that is t
be edited. If there is data in any of the 4, then all 4 must have dat
in them as well. If all 4 are empty, then the record on the spreadshee
is deleted.

My problem is if the user uses the spacebar instead of the Del key t
remove data from the textboxes, then the cells aren't truely empty a
far as VBA is concerned.

How do you make VBA count spacebar values as null value entries?

i.e. make "" = " " = " " = " ", etc?

I'm currently using:

If Controls("Textbox" & N).value < "" and _
(Controls("Textbox" & O).value = "" or _
Controls("Textbox" & P).value = "" or _
Controls("Textbox" & Q).value = "") then

MsgBox "Error, partially completed treatment detected!"
Exit Sub
End if

Repeated for each of the different textboxes.


Thanks!

I'd suggest using LEN to return the length of the string in th
textbox. If it returns zero then your textbox is empt

--
ronthedo
-----------------------------------------------------------------------
ronthedog's Profile: http://www.excelforum.com/member.php...fo&userid=2650
View this thread: http://www.excelforum.com/showthread.php?threadid=39794

Detecting spaces in UserForm textboxes
 

If len(trim(textbox1)) = 0 then
'textbox is empty
End if

--
Regards,
Tom Ogilvy

"Ouka" wrote in message
...

ronthedog Wrote:
I'd suggest using LEN to return the length of the string in the textbox.
If it returns zero then your textbox is empty

But I want to define anything with only space characters as empty as
well.

Doing a LEN on a textbox with a space in it would return as 1, would it
not?


--
Ouka
------------------------------------------------------------------------
Ouka's Profile:
http://www.excelforum.com/member.php...o&userid=23988
View this thread: http://www.excelforum.com/showthread...hreadid=397942