vba - Validate textbox input -


i created userform in excel , have range data validation.

i want check user's input before user clicks "save" button.

ex: value input "x", if 1 <= x <= 10, data textbox mark red highlight , popup message "out of control", form request user continue input in "re test" text box within data range (in excel file).

here code , attachment file http://www.mediafire.com/view/fl1nk84nu62wg34/control_chart.xlsm

private sub cbca_dropbuttonclick()     cbca.additem ("ca 1")     cbca.additem ("ca 2")     cbca.additem ("ca 3")     cbca.additem ("") end sub  private sub cbtype_dropbuttonclick()     cbtype.additem ("set up")     cbtype.additem ("production") end sub  private sub commandbutton1_click()     'copy input values sheet.     dim lrow long     dim ws worksheet      set ws = worksheets("input data")     lrow = ws.cells(rows.count, 8).end(xlup).offset(1, 0).row     ws         .cells(lrow, 2).value = me.txngay.value         .cells(lrow, 3).value = me.txgio.value         .cells(lrow, 4).value = me.cbca.value         .cells(lrow, 5).value = me.txnv.value         .cells(lrow, 6).value = me.txsolo.value         .cells(lrow, 7).value = me.txma.value         .cells(lrow, 8).value = me.txdata.value         .cells(lrow, 9).value = me.txretest.value         .cells(lrow, 10).value = me.txlydo.value         .cells(lrow, 11).value = me.cbtype.value     end         'hien thong bao nhac nho if txngay.text = ""         msgbox "quen cho ngay kia thim", vbokonly + vbinformation,                   "thÔng bÁo"      txngay.backcolor = &hff& ' change color of textbox      elseif txgio.text = ""         msgbox "quen nhap gio kia thim", vbokonly + vbinformation,                    "thÔng bÁo"          txgio.backcolor = &hff& ' change color of textbox      elseif txnv.text = ""         msgbox "ten cua thim la gi vay", vbokonly + vbinformation,                   "thÔng bÁo"          txnv.backcolor = &hff& ' change color of textbox      elseif txma.text = ""         msgbox "quen nhap ma san pham kia thim", vbokonly + vbinformation,                    "thÔng bÁo"          txma.backcolor = &hff& ' change color of textbox      elseif txsolo.text = ""         msgbox "quen nhap lo kia thim", vbokonly + vbinformation, "thÔng                    bÁo"          txsolo.backcolor = &hff& ' change color of textbox      elseif txdata.text = ""         msgbox "quen nhap data kia thim", vbokonly + vbinformation, "thÔng                    bÁo"          txdata.backcolor = &hff& ' change color of textbox      end if      thisworkbook.save      '    end if  end sub  private sub commandbutton2_click()  'clear input controls.     me.txngay.value = ""     me.txgio.value = ""     me.cbca.value = ""     me.txnv.value = ""     me.txsolo.value = ""     me.txma.value = ""     me.txdata.value = ""     me.txlydo.value = ""  end sub  private sub commandbutton3_click()   'close userform.     unload me end sub  private sub commandbutton4_click() thisworkbook.sheets("control_chart").visible = true thisworkbook.sheets("control_chart").select userform1.hide end sub  private sub commandbutton5_click() calendar1.visible = true end sub  private sub label15_click() label15.caption = sheet2.range("f2").value end sub  private sub label16_click() label16.caption = sheet2.range("f4").value end sub  private sub label17_click() label17.caption = sheet2.range("f3").value end sub  private sub label18_click() label18.caption = sheet2.range("f6").value end sub  private sub label20_click() label20.caption = sheet2.range("f5").value end sub  private sub label23_click()  end sub  private sub label8_click() range("f2").select end sub  public idate long private sub calendar1_click()   idate = calendar1.value   txngay.value = format(idate, "dd/mm/yyyy")   calendar1.visible = false end sub  private sub txdata_enter() me.txdata         if .value >= 0.315 or .value <= 0.33             .value = ""             msgbox prompt:="must # between 1 , 30000!",                  buttons:=vbcritical, title:="invalid entry"             cancel = true        end if     end end sub  private sub txdata_keypress(byval keyascii msforms.returninteger)  if instr("1234567890." + chr$(vbkeyback), chr$(keyascii)) = 0   keyascii = 0  end if end sub  private sub txma_keypress(byval keyascii msforms.returninteger)  if instr("1234567890." + chr$(vbkeyback), chr$(keyascii)) = 0   keyascii = 0  end if end sub   private sub userform_initialize()   calendar1.visible = false     label15.visible = true     label16.visible = true     label17.visible = true     label18.visible = true     label20.visible = true end sub

download attachment file

here updated code 

'khai bao bien data khi nguoi dung nhap lieu private sub txdata_afterupdate() dim x double 'bien nhi phan dim f2 double dim f3 double dim f4 double dim f5 double dim f6 double  x = me.txdata.value ' set gia tri cho bien f2 = sheet2.range("f2").value f3 = sheet2.range("f3").value f4 = sheet2.range("f4").value f5 = sheet2.range("f5").value f6 = sheet2.range("f6").value  'kiem tra hop data input co trong hay khong     if me.txdata = empty         msgbox "please input data", vbcritical         txdata.backcolor = &hff& ' change color of textbox          ' kiem tra hop data input co phai la hay khong         elseif not isnumeric(me.txdata)         msgbox "numberic only", vbcritical         txdata.backcolor = &hff& ' change color of textbox         else         'xet dieu kien nguoi dung nhap vao         select case x             case x = 0                 msgbox "data sao bang 0 duoc", vbcritical                 txdata.backcolor = &hff& ' change color of textbox                 txdata.setfocus              case < f6                 msgbox "lower out of control. ban hay nhap gia tri vao o re test", vbcritical                 txdata.backcolor = &hff& ' change color of textbox                 txretest.setfocus              case > f4                 msgbox "upper out of control. ban hay nhap gia tri vao o re test", vbcritical                 txdata.backcolor = &hff& ' change color of textbox                 txretest.setfocus              case f5                 msgbox "database correct", vbinformation                 end select         thisworkbook.save     end if  end sub

since you're setting x value of textbox, don't need if statements in case select. 

select case x     case 0.3         msgbox "correct"     case < 0.3         msgbox "lower limit"     case > 0.3         msgbox "upper limit" end select

note limits i've used not correspond you're limits.

to validate textbox, write afterupdate sub triggers when deselect textbox pressing tab key example. might sub below, aware either write sub each input want check, if have lot of similar inputs possibly write private sub called afterupdate sub checks if value of textbox numeric, , lies within paramaters.

private sub txngay_afterupdate()     if me.txngay = empty         'code sets the of textbox normal.     elseif not isnumeric(me.txngay)         msgbox "the textbox doesn't contain numerical value.", vbcritical         'code sets of textbox faulty state     elseif me.txngay < 1 or me.txngay > 10         msgbox "the value out of range"         'code sets of textbox faulty state     else         'code sets of textbox normal state     end if end sub

you might include private sub in module called every time field updated checks inputs , activates ok button when conditions satisfied.

you can use case select execute code based on values

select case x     case 0         'code perform actions need if x 0     case 1 10         'code perform actions need if x between 1 , 10     case else         'code perform actions need if x < 0 , x > 10 end select

Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm...
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } ...
Read more

android - Keyboard hides my half of edit-text and button below it even in scroll view -

i having trouble keyboard covers edit-text , half of button in scroll view. here's layout , please tell me how should resolve problem. many questions on topic none of them s working me. <?xml version="1.0" encoding="utf-8"?> <linearlayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:background="@drawable/bg" android:layout_height="match_parent"> <scrollview android:layout_width="match_parent" android:fillviewport="true" android:layout_height="match_parent"> <relativelayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical"> <linearlayout android:layout_width="match_parent" android:layout_height="w...
Read more