One text box must not be blank

trsisko

Board Regular
Joined
May 20, 2008
Messages
176
I have eight textboxs. I need to ensure and entry exists in one text box when a member clocks next. How can I do this?


Code:
' Load CatID Panel
Private Sub cmdEnterCont_Click()
 
 
 
     Unload Me
        Sheets("catOutput").Select
    UserCatIDs.Show
 
End Sub

Code:
Private Sub cmdClear1_Click()
Me.txtDisplay1 = ""
End Sub
Private Sub cmdClear2_Click()
Me.txtDisplay2 = ""
End Sub
Private Sub cmdClear3_Click()
Me.txtDisplay3 = ""
End Sub
Private Sub cmdClear4_Click()
Me.txtDisplay4 = ""
End Sub
Private Sub cmdClear5_Click()
Me.txtDisplay5 = ""
End Sub
Private Sub cmdClear6_Click()
Me.txtDisplay6 = ""
End Sub
Private Sub cmdClear7_Click()
Me.txtDisplay7 = ""
End Sub
Private Sub cmdClear8_Click()
Me.txtDisplay8 = ""
End Sub
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
Hi,

The neatest way would be to enable / disable the command button, depending on whether there's any data in the textboxes.

If in form Activate, use
Code:
cmdEnterCont.Enabled = False

anf in each Textbox change event use code such as
Code:
cmdEnterCont.Enabled = len(txtDisplay1)+len(txtDisplay2)... +len(txtDisplay8)<>0
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

some viewpoints

1.
you could use a classmodule: new technique to learn
advantages:
avoid repeating the code
when adding textboxes: no need to edit your code

with 8 textboxes it might not be worth the effort, but I apply it when there are more than 3

documentation
http://groups.google.com/group/micr..._frm/thread/fb2a0cb8cdad568e/74c5fb2778c250e1
http://www.tushar-mehta.com/excel/vba/vba_oops_eg1.htm
http://www.cpearson.com/excel/ClassModules.htm
http://www.j-walk.com/ss/excel/eee/eee019.txt
For plenty of examples, try John Walkenbach's site, and enter class in the Search box:
http://www.j-walk.com/ss/

If you want an example applied to your case: I can try.

2.
instead of putting the same line in all textboxe codes, use another procedure and call it
Code:
Sub TextBox1_Change()
callme
End Sub
Code:
Sub callme()
cmdEnterCont.Enabled = len(txtDisplay1)+len(txtDisplay2)... +len(txtDisplay8)<>...........
End Sub

advantage
only one procedure to edit
disadvantage
still work to do when adding textboxes

3.
instead of
cmdEnterCont.Enabled = len(txtDisplay1)+len(txtDisplay2)... +len....
you can create a loop
For Each CTRL in Me.Controls
etcetera

I combined both ideas
instead of sub I made a function
Code:
Option Explicit
 
Private Function Btn1Enabled() As Boolean
Dim CTRL
 
    For Each CTRL In Me.Controls
    If Left(CTRL.Name, 7) = "TextBox" Then If Len(CTRL) > 0 Then Btn1Enabled = True
    Next CTRL
End Function
 
Private Sub TextBox1_Change()
CommandButton1.Enabled = Btn1Enabled
End Sub
Private Sub TextBox2_Change()
CommandButton1.Enabled = Btn1Enabled
End Sub
Private Sub TextBox3_Change()
CommandButton1.Enabled = Btn1Enabled
End Sub
Private Sub TextBox4_Change()
CommandButton1.Enabled = Btn1Enabled
End Sub
Private Sub TextBox5_Change()
CommandButton1.Enabled = Btn1Enabled
End Sub
Private Sub TextBox6_Change()
CommandButton1.Enabled = Btn1Enabled
End Sub
Private Sub TextBox7_Change()
CommandButton1.Enabled = Btn1Enabled
End Sub
Private Sub TextBox8_Change()
CommandButton1.Enabled = Btn1Enabled
End Sub
 
Private Sub UserForm_Initialize()
CommandButton1.Enabled = False
End Sub

4.
In fact I am not very happy with the idea to run code all time to enable or disable the button
I would only check it, once the button is pressed

advantage
less code
less events running
Code:
Option Explicit
 
Private Function TextOK() As Boolean
Dim CTRL
 
    For Each CTRL In Me.Controls
    If Left(CTRL.Name, 7) = "TextBox" Then If Len(CTRL) > 0 Then TextOK = True
    Next CTRL
End Function
 
Private Sub CommandButton1_Click()
    If TextOK Then
    'your code
    Else
    MsgBox "You should fill in at least one box", vbCritical, "WARNING!"
    End If
End Sub
kind regards,
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,328
Members
414,444
Latest member
lionking15

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
Top