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 to be edited. If there is data in any of the 4, then all 4 must have data in them as well. If all 4 are empty, then the record on the spreadsheet is deleted.

My problem is if the user uses the spacebar instead of the Del key to remove data from the textboxes, then the cells aren't truely empty as 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!

Originally Posted by Ouka

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 to be edited. If there is data in any of the 4, then all 4 must have data in them as well. If all 4 are empty, then the record on the spreadsheet is deleted.

My problem is if the user uses the spacebar instead of the Del key to remove data from the textboxes, then the cells aren't truely empty as 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 the textbox. If it returns zero then your textbox is empty

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

--
Regards,
Tom Ogilvy

"Ouka" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>