flag empty value in textbox

slapman · Feb 26th 2017

hello all
im not that strong in writing vba codes , only know stuff from reading around here as spactator.

i would like to get help as i need to creat a code in my userform that check if the opreator entering any value to text box other then 0 or blank . if he is trying to enter 0 or Blank it prevent from moving FW.

textboxs is copying its profit multipliers values to a certain sheet in the workbook that reflacts the pricing per s\e after i press command button, this generating forcasting tables later on.

so i must make sure that the end user wont be able to entere 0 or blank value for any textbox given

thats my code so far:

HTML

Private Sub CommandButton1_Click()
s1.Range("F2") = uf1.TextBox43.Value
s1.Range("F3") = uf1.TextBox44.Value
s1.Range("F4") = uf1.TextBox45.Value
s1.Range("F5") = uf1.TextBox46.Value
s1.Range("F6") = uf1.TextBox47.Value
s1.Range("F7") = uf1.TextBox48.Value
s1.Range("F8") = uf1.TextBox49.Value
s1.Range("F9") = uf1.TextBox50.Value
s1.Range("F10") = uf1.TextBox51.Value
s1.Range("F11") = uf1.TextBox52.Value
s1.Range("F12") = uf1.TextBox53.Value
s1.Range("F13") = uf1.TextBox54.Value
s1.Range("F14") = uf1.TextBox55.Value
s1.Range("F15") = uf1.TextBox56.Value


Unload Me
End Sub

Display More

thanks guys

**Carim** · Feb 26th 2017

Re: flag empty value in textbox

Hi,

You can take at the following solution with all the necessary explanations ... :wink:

http://www.ozgrid.com/VBA/validate-text.htm

Hope this will help

slapman · Feb 27th 2017

Re: flag empty value in textbox

Quote from Carim;786480

Hi,

You can take at the following solution with all the necessary explanations ... :wink:

http://www.ozgrid.com/VBA/validate-text.htm

Hope this will help

Display More

thanks,
its partly helping me as i need to make sure numbers will be inserted.

edit : found the same manual with numbers

thank you very much

**Carim** · Feb 27th 2017

Re: flag empty value in textbox

Glad you could fix your problem ...:wink:

Thanks for your thanks ...:smile:

slapman · Feb 27th 2017

Re: flag empty value in textbox

hey , i dug deeper and found a very nice code (http://www.cpearson.com/excel/TextBox.htm)

problem is that this code wont allow me to enter more then 1 digit and any digit after decimel

i would like it to allow me enter up to: XXXXX.XX and blank is not allowed also , appriciate any help with it.

Code

Private Sub TextBox43_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
    Case Asc("0") To Asc("9")
 
        If InStr(1, Me.TextBox43.Text, "-") > 0 Or Me.TextBox43.SelStart > 0 Then
            KeyAscii = 0
        End If
    Case Asc(".")
        If InStr(1, Me.TextBox43.Text, ".") > 0 Then
            KeyAscii = 0
        End If
    Case Else
        KeyAscii = 0
End Select
End Sub

Display More

**Carim** · Feb 27th 2017

Re: flag empty value in textbox

Hello again,

You can test following...

Code

Private Sub TextBox43_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
    Case Asc("0") To Asc("9")
    Case Asc("-")
        If InStr(1, Me.TextBox43.Text, "-") > 0 Or Me.TextBox43.SelStart > 0 Then
            KeyAscii = 0
        End If
    Case Asc(".")
        If InStr(1, Me.TextBox43.Text, ".") > 0 Then
            KeyAscii = 0
        End If
    Case Else
        KeyAscii = 0
    End Select
End Sub

Display More

HTH

slapman · Feb 27th 2017

Re: flag empty value in textbox

thank you carim!
only thing not resolved is that blank is acceptable
there is any case that can be filled into the code that will prevent blank?
or another procedureshould be added?

**Carim** · Feb 27th 2017

Re: flag empty value in textbox

You are welcome ...

Blanks should not be allowed with above code... have you tried to add blanks ???

Otherwise, if you want to specifically exclude blanks, you can insert ...

Code

Case 32
  KeyAscii = 0

HTH

slapman · Feb 28th 2017

Re: flag empty value in textbox

my problem is that my update button will change cells values according what entered inside the textboxes. thats only thing it will do

i need a sulotion to prevent that update button to be active if any of the textboxs containing blanks

code per textbox (there are 20-30 boxes):

Code

Private Sub TextBox43_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
    Case Asc("0") To Asc("9")
    Case Asc("-")
        If InStr(1, Me.TextBox43.Text, "-") > 0 Or Me.TextBox43.SelStart > 0 Then
            KeyAscii = 0
        End If
    Case Asc(".")
        If InStr(1, Me.TextBox43.Text, ".") > 0 Then
            KeyAscii = 0
        End If
    Case Else
        KeyAscii = 0
End Select
End Sub

Display More

thats the code for the command button:

Code

Private Sub Updateb1_Click()




s1.Range("F2") = uf1.TextBox43.Value




s1.Range("F3") = uf1.TextBox44.Value




s1.Range("F4") = uf1.TextBox45.Value




s1.Range("F5") = uf1.TextBox46.Value




s1.Range("F6") = uf1.TextBox47.Value




s1.Range("F7") = uf1.TextBox48.Value




s1.Range("F8") = uf1.TextBox49.Value




s1.Range("F9") = uf1.TextBox50.Value




s1.Range("F10") = uf1.TextBox51.Value




s1.Range("F11") = uf1.TextBox52.Value




s1.Range("F12") = uf1.TextBox53.Value




s1.Range("F13") = uf1.TextBox54.Value




s1.Range("F14") = uf1.TextBox55.Value




s1.Range("F15") = uf1.TextBox56.Value








Unload Me




End Sub

Display More

**Carim** · Feb 28th 2017

Re: flag empty value in textbox

It would seem you have tackled a new issue ...no ???

Have you sorted out the issue related to preventing users from adding spaces in their inputs ???

slapman · Mar 1st 2017

Re: flag empty value in textbox

Quote from Carim;786674

It would seem you have tackled a new issue ...no ???

Have you sorted out the issue related to preventing users from adding spaces in their inputs ???

hey carim , that was never the issue, maybe i failed to explain myself better.

My finale issue im dealing now is to "check" the textboxes to see that all of them have any value <0 (= No blanks in the textboxes) while im pressing command button that supposed to unload the form.

"PrivateSub Updateb1_Click() "

i made that command button unavaliable but i need a code that check all the boxes i have from TextBox43 to Textbox56 with value >0 then allow command button to be available to unload the form while during this action Cells updating with
the values entered in the text boxes.

**Carim** · Mar 1st 2017

Re: flag empty value in textbox

Hello,

You could be testing the following ...

Code

Private Sub Updateb1_Click()
Dim ctl As Control
Dim i As Long
For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Then
            For i = 2 To 15
              If Len(Trim(ctl(i + 41).Value)) = 0 Then
                s1.Cells(i, 6) = ctl(i + 41).Value
              End If
            Next i
        End If
Next ctl
Unload Me
End Sub

Display More

Hope this will help

slapman · Mar 1st 2017

Re: flag empty value in textbox

carim thank you so much for helping me again.

Code

Private Sub Updateb1_Click()




s1.Range("F2") = uf1.TextBox43.Value
s1.Range("F3") = uf1.TextBox44.Value
s1.Range("F4") = uf1.TextBox45.Value
s1.Range("F5") = uf1.TextBox46.Value
s1.Range("F6") = uf1.TextBox47.Value
s1.Range("F7") = uf1.TextBox48.Value
s1.Range("F8") = uf1.TextBox49.Value
s1.Range("F9") = uf1.TextBox50.Value
s1.Range("F10") = uf1.TextBox51.Value
s1.Range("F11") = uf1.TextBox52.Value
s1.Range("F12") = uf1.TextBox53.Value
s1.Range("F13") = uf1.TextBox54.Value
s1.Range("F14") = uf1.TextBox55.Value
s1.Range("F15") = uf1.TextBox56.Value




 Dim ctl As Control
    Dim i As Long
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Then
            For i = 2 To 15
                If Len(Trim(ctl(i + 41).Value)) = 0 Then
                    s1.Cells(i, 6) = ctl(i + 41).Value
                End If
            Next i
        End If
    Next ctl
    Unload Me
















End Sub

Display More

and got:

[ATTACH=CONFIG]71553[/ATTACH]

what im doing wrong?

**Carim** · Mar 1st 2017

Re: flag empty value in textbox

Hello,

The loop does the input after the check ... :wink:

So ... as long as your Userform is called uf1 and your worksheet is called s1 ...

the code should do the job ...

Code

Private Sub Updateb1_Click() 
    Dim ctl As Control 
    Dim i As Long 
    For Each ctl In Me.Controls 
        If TypeName(ctl) = "TextBox" Then 
            For i = 2 To 15 
                If Len(Trim(ctl(i + 41).Value)) = 0 Then 
                    s1.Cells(i, 6) = ctl(i + 41).Value 
                End If 
            Next i 
        End If 
    Next ctl 
    Unload Me 
End Sub

Display More

slapman · Mar 1st 2017

Re: flag empty value in textbox

Quote from Carim;786748

Hello,

The loop does the input after the check ... :wink:

So ... as long as your Userform is called uf1 and your worksheet is called s1 ...

the code should do the job ...

Code

Private Sub Updateb1_Click() 
    Dim ctl As Control 
    Dim i As Long 
    For Each ctl In Me.Controls 
        If TypeName(ctl) = "TextBox" Then 
            For i = 2 To 15 
                If Len(Trim(ctl(i + 41).Value)) = 0 Then 
                    s1.Cells(i, 6) = ctl(i + 41).Value 
                End If 
            Next i 
        End If 
    Next ctl 
    Unload Me 
End Sub

Display More

I revised the cmd button to that above.

still getting that error

[ATTACH=CONFIG]71554[/ATTACH][ATTACH=CONFIG]71555[/ATTACH]

excuse me for my Noobiness

**Carim** · Mar 1st 2017

Re: flag empty value in textbox

The trouble with attaching jpg pictures lies in the fact it does not provide an understanding of all the circumstances which might interfere ...

Why don't you attach a sample workbook in your next post ...

slapman · Mar 1st 2017

Re: flag empty value in textbox

whish i could post my workbook i cant for confidentiality reasones

any ways ,debugger indicates on the "****" line i marked :

Code

Private Sub Updateb1_Click()






 Dim ctl As Control
    Dim i As Long
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Then
            For i = 2 To 15
                    If Len(Trim(ctl(i + 41).Value)) = 0 Then   *******
                    s1.Cells(i, 6) = ctl(i + 41).Value
                End If
            Next i
        End If
    Next ctl
    Unload Me
















End Sub

Display More

**Carim** · Mar 1st 2017

Re: flag empty value in textbox

This instruction

If Len(Trim(ctl(i + 41).Value)) = 0 Then

can bug
- if the procedure is not located in the userform module
- if the userform does not have a control named TextBox43 (i=2 + 41)

You could test revised code ...

Code

Private Sub Updateb1_Click()
    Dim ctl As Control
    Dim i As Long
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Then
            For i = 2 To 15
                If Len(Trim(Me.Controls("TextBox" & i + 41).Value)) = 0 Then
                    s1.Cells(i, 6) = Me.Controls("TextBox" & i + 41).Value
                End If
            Next i
        End If
    Next ctl
    Unload Me
End Sub

Display More

HTH

slapman · Mar 1st 2017

Re: flag empty value in textbox

the revised code now wont give any debugger error but it put the computer into loop and eventualy allow the target cells here (s1.Cells(i, 6)) to change to blank or 0 incase all textboxes (43-56) are left blank or 0

**Carim** · Mar 1st 2017

Re: flag empty value in textbox

You can always add more conditions ...

Code

Private Sub Updateb1_Click() 
    Dim ctl As Control 
    Dim i As Long 
    For Each ctl In Me.Controls 
        If TypeName(ctl) = "TextBox" Then 
            For i = 2 To 15 
                If Not IsEmpty(Me.Controls("TextBox" & i + 41)) And Len(Trim(Me.Controls("TextBox" & i + 41).Value)) = 0 Then 
                    s1.Cells(i, 6) = Me.Controls("TextBox" & i + 41).Value 
                End If 
            Next i 
        End If 
    Next ctl 
    Unload Me 
End Sub

Display More

HTH

flag empty value in textbox

Participate now!

Share