PDA

View Full Version : validating textbox input upon TAB or ENTER



tp724
06-13-2015, 10:56 AM
Hello,

Well, first post to this, or any, forum for that matter. I'm aware of threads but unsure where to post my question, or questions. Hopefully I haven't missed the mark and I'm in the right place.

There are so many question that I have that I guess I'll have to take it slow and post something, review the responses, try out a few things, and then move on. I do want to learn, and try to do this myself, which is why I'm here.

I've been trying to help myself by combing the web for hints, tricks, and sample code but I cannot seem to accomplish what I'd like to do. Before posting my question, I purchased and read Microsoft Excel VBA Programming for Dummies 3rd Edition by John Walkenbach and it was a fair primer to getting started with VBA - and programming in general - but it falls short with respect to more detail on items like VBA events. I can find a list and definition of events for VBA but understanding the many options with respect to wring the code, and the MSDN assumes too much. For instance, I search for and find on the web the event LostFocus but its the is the stuff in parentheses that confuses me (eg (ByVal sender As System.Object, ByVal e As System.EventArgs). Can anyone recommend a short list of resources for learning more about VBA - the Hows and Whys of things like Events?

My project is a VBA/macro enabled spreadsheet for our annual inventory. We have A LOT of WIP (work in progress) that are just parts for what will eventually become a complete unit at some point. These parts cannot be identified by unique part numbers as they are just too diverse and there are way too many. We can capture some qualities about these parts and fairly accurately assess the value of these items for our FYE (fiscal year end) financial statements. I'm using a Wasp Barcode Scanner as an input device where possible to reduce time keying in data.

Thus far I have a worksheet with textboxes and commandbuttons. I've placed them directly onto the worksheet opting for activex over userforms because someone in another forum mentioned that there is more flexibility with activex.

FYI - the image below, as I am typing this post, looks quite small. Hopefully it will display a little bigger once I've actually posted.

13681

The Wasp barcode scanner works just fine so far with the following code. Each time I scan a barcode it captures the data and then produces a hard return (like pressing enter on the keyboard after typing, for example, the letter H into a cell on a worksheet). That said, some data will be manually entered into the forms as well so I want the functionality of TAB or ENTER for this method. Much of the data captured should be fairly predictable, and within certain ranges, or have very specific values. For example, the first textbox has the label ITEM. This is just to assign one of four letters to indicate what time of inventory item is going to be recorded. For this field, I want to limit the entries to only "H", "B", "I", or "M".

Once the barcode has been scanned, or H,B,I or M has been manually entered into the texbox, I want to check entry to validity before the code moves the cursor to the next textbox (move the focus?). The only field that would not be checked against a fixed set of known values would be QTY. For quantity, i just want to make sure that the entry is a whole integer, not negative or zero.

I have a rough looking section that moves the cursor to the next textbox after TAB or ENTER but as mentioned above, I want to validate before moving. If the data is invalid, I want the focus(?) to stay remain in the current textbox until good data has been entered. Below [gulp] is the code I have so far. Some of the field a just a few possible "values" others I'd like to check against a list in worksheet using vlookup.

I'm not sure how folks are posting their code. Below I just cut/pasted it in to the body of this post.


Option Explicit


Dim myvarHubItem As String
Dim myvarHubArea As String
Dim myvarHubFinish As String
Dim myvarHubStyle As String
Dim myvarHubCondition As String
Dim myvarHubSize As String
Dim myvarHubThickness As String
Dim myvarHubQuantity As String


' begin by placing cursor in first textbox to reduce keystrokes and be ready to receive data

Private Sub Worksheet_Activate()
ActiveSheet.TextBoxItem.Activate
End Sub


'on tab or enter move to next textbox, at end, return to top

Private Sub TextBoxItem_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If (KeyCode = vbKeyTab Or KeyCode = vbKeyReturn) Then
TextBoxArea.Activate
End If
End Sub


'on tab or enter move to next textbox, at end, return to top

Private Sub TextBoxArea_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
TextBoxFinish.Activate
End If
End Sub


'on tab or enter move to next textbox, at end, return to top

Private Sub TextBoxFinish_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
TextBoxStyle.Activate
End If
End Sub


'on tab or enter move to next textbox, at end, return to top

Private Sub TextBoxStyle_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
TextBoxCondition.Activate
End If
End Sub


'on tab or enter move to next textbox, at end, return to top

Private Sub TextBoxCondition_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
TextBoxSize.Activate
End If
End Sub


'on tab or enter move to next textbox, at end, return to top

Private Sub TextBoxSize_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
TextBoxThickness.Activate
End If
End Sub


'on tab or enter move to next textbox, at end, return to top

Private Sub TextBoxThickness_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
TextBoxQuantity.Activate
End If
End Sub


'on tab or enter move to next textbox, at end, return to top

Private Sub TextBoxQuantity_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
TextBoxItem.Activate
End If
End Sub





Private Sub userSubmit_Click()


myvarHubItem = ThisWorkbook.Sheets("HUB_INPUT").TextBoxItem.Value
myvarHubArea = ThisWorkbook.Sheets("HUB_INPUT").TextBoxArea.Value
myvarHubFinish = ThisWorkbook.Sheets("HUB_INPUT").TextBoxFinish.Value
myvarHubStyle = ThisWorkbook.Sheets("HUB_INPUT").TextBoxStyle.Value
myvarHubCondition = ThisWorkbook.Sheets("HUB_INPUT").TextBoxCondition.Value
myvarHubSize = ThisWorkbook.Sheets("HUB_INPUT").TextBoxSize.Value
myvarHubThickness = ThisWorkbook.Sheets("HUB_INPUT").TextBoxThickness.Value
myvarHubQuantity = ThisWorkbook.Sheets("HUB_INPUT").TextBoxQuantity.Value


' message boxes below just confirm data assigned to correct values
' next phase is to capture data and populate a spreedsheet
MsgBox "you entered " & myvarHubItem
MsgBox "you entered " & myvarHubArea
MsgBox "you entered " & myvarHubFinish
MsgBox "you entered " & myvarHubStyle
MsgBox "you entered " & myvarHubCondition
MsgBox "you entered " & myvarHubSize
MsgBox "you entered " & myvarHubThickness
MsgBox "you entered " & myvarHubQuantity


End Sub



Private Sub userClearForm_Click()
'clear userform manually if needed
ThisWorkbook.Sheets("HUB_INPUT").TextBoxItem.Value = Null
ThisWorkbook.Sheets("HUB_INPUT").TextBoxArea.Value = Null
ThisWorkbook.Sheets("HUB_INPUT").TextBoxFinish.Value = Null
ThisWorkbook.Sheets("HUB_INPUT").TextBoxStyle.Value = Null
ThisWorkbook.Sheets("HUB_INPUT").TextBoxCondition.Value = Null
ThisWorkbook.Sheets("HUB_INPUT").TextBoxSize.Value = Null
ThisWorkbook.Sheets("HUB_INPUT").TextBoxThickness.Value = Null
ThisWorkbook.Sheets("HUB_INPUT").TextBoxQuantity.Value = Null




End Sub



Private Sub userClearLastSubmission_Click()


MsgBox "you clicked the 'clear last submission box'"
' write code to clear last submission due to a mistake
End Sub

I will post more as I move forward but for right now I'd like to get help with the validation part. I know some of these things are interrelated and that over time I'll be able to consolidate some code. But for know, I guess this is the learning process, small steps forward.

Thanks all.

Tony

mancubus
06-14-2015, 11:19 PM
welcome to VBAX.

i would start with deleting all labels and textboxes and use cells (with data validation) instead.

Labels (item, area, etc) will be in column A, values will be in Column B.

thus when you hit userSubmit button:


myvarHubItem = ThisWorkbook.Sheets("HUB_INPUT").Range("B1").Value
myvarHubArea = ThisWorkbook.Sheets("HUB_INPUT").Range("B2").Value
...
...
...



ps: a friendly advice: :)
https://en.wikipedia.org/wiki/Wikipedia:Too_long;_didn't_read

tp724
06-15-2015, 04:10 AM
thank you, macubus.

sorry for the overly long post. when I read many posts researching the answer for this one, many of the experts on the forms are asking for "more information" or "more detail." so i thought i'd be quite specific. based on the lack of responses, it's way too much detail.

thank you for your responses as well. i will consider it. i did want to use the activex buttons imbedded in the sheet for two reasons. one, it has become a challenge - albeit a very frustrating one. and two, i like the look of it. it looks like a more engineered system.

thank you for responding.

mancubus
06-15-2015, 04:50 AM
you are welcome.

you can format cells for a better look: Home tab, Font group.

imo, the simpler the better! :)