dcsimg
Results 1 to 21 of 21

Thread: [RESOLVED] Wishing to code a Form with a counter

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Location
    Kentucky
    Posts
    22

    Resolved [RESOLVED] Wishing to code a Form with a counter

    Hello gang,
    VBA newbie here. Hope I am posting in the correct section.

    I have an idea for an onscreen counting form that I would like to see if I can build.
    I don't quite understand the "keypress" method, but believe it is what I want/need in order to code for what I wish to build.

    1. I am creating a single FORM with the name of "UserForm1"

    2. On "UserForm1", I am creating 3 Labels named - "Label1", "Label2", and "Label3". These will be in first Column.

    3. On "UserForm1", I am also creating 3 text boxes named - "TextBox1", "TextBox2", and "TextBox3". These will be in the second column and will line up the their corresponding label boxes.

    4. On UserForm1", I am placing two commandbuttons - "CommandButton1" and "CommandButton2". These will be at the bottom of the form and will be side-by-side to each other.

    5. "CommandButton1" will have the "Caption" of "CLEAR SCREEN" - I can code this.
    6. "CommandButton2" will have the "Caption of "EXIT" - I can code this.
    7. "Label1" will have the "Caption" of "Press the letter O"
    8. "Label2" will have the "Caption" of "Press the letter P"
    9. "Label3" will have the "Caption" of "Grand Total"

    When I press the Letter O or P, I want to be able to have the corresponding "TextBox1" or "TextBox2" increment and display a number that will corresond to how many times the letter has been pressed. In other words, if I press the letter O six times, "TextBox1" will have the number 6 displayed as its value. If I also press the letter P 4 times then "TextBox2" will have the number 4 displayed as its value.

    At the same time I am incrementing and updating the display for "Textbox1" and "TextBox2", I want to keep a running total which will be seen in "TextBox3".

    Can anyone provide some guidance on how I can code for the appropriate KEYPRESS method that will allow me to increment the corresponding O or P key and to update the Grand Total after each key is pressed?


    Thank You.

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    23,139

    Re: Wishing to code a Form with a counter

    It depends. Looking at your question and object names, it appears you’re using VBA. Is that so? Also why use TextBoxes if you only want them as display controls? You could set readonly = true, but Labels would be more appropriate.
    To capture keypress, set your form keypreview Property, and use the form_keypress event.

  3. #3
    Member
    Join Date
    Jan 2012
    Posts
    55

    Re: Wishing to code a Form with a counter

    The first suggestion I have is to "properly" name your objects. E.g. btnClearScreen, btnExit, lblPressO, lblPressP, lblGrandTotal, txtOPressCounter, etc. That will make your code a lot easier to understand.

    Then in the form_keypress event you do something like this: ("pseudo-code" but I guess you'll get the idea)

    Code:
    If <keypressed = O> Then
       txtOPressedCounter.text = Val(txtOPressedCounter.text) + 1
    ElseIf <keypressed = P> Then
       txtPPressedCounter.text = Val(txtPPressedCounter.text) + 1
    End If

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Location
    Kentucky
    Posts
    22

    Re: Wishing to code a Form with a counter

    .paul,
    Yes, I am using VBA. (The program is called Micro Focus - Reflections) They have VBA as a component to their product.

    Use Labels instead of Textboxes? - Do you mean that it might be simpler to use a label to "display" my Read out by setting the "caption" property to display the incrementing number?

    "To capture keypress, set your form keypreview Property, and use the form_keypress event." This is the part where being a newbie leaves me a little bewildered at times. I know some parts, but this does not yet click for me as to what and where to code this. Sorry, but just not up to speed on writing code.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Location
    Kentucky
    Posts
    22

    Re: Wishing to code a Form with a counter

    Erwin,
    Thanks for the suggestions on naming my items to make them more readable both for me and for future people who look at the code. I have named my items now in a standard format. As for the code example you gave, I believe I understand the basics of what you have laid out. I would want to place any code in the proper place - In the USERFORM_KEYPRESS?

    Code:
    Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    
    'any code goes here.
    
    End Sub

    I would not want to place it in something like:
    Code:
    Private Sub lblPressO_Click()
    
    End Sub
    Name:  Capture.JPG
Views: 50
Size:  21.2 KB

    Here is my complete code as it stand now. Very basic, but does not seem to tally a value in the box when key is pressed

    Code:
    Private Sub CMDBTN_CLEARSCREEN_Click()
    
        Unload UserForm1
        UserForm1.Show
        
    End Sub
    '----------------------------------------------------------------
    Private Sub CMDBTN_EXIT_Click()
    
    Unload UserForm1
    
    End Sub
    '----------------------------------------------------------------
    Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    ' asc 80 = P    (capital letter)
    ' asc 112 = p   (small letter)
    ' asc 79 = O    (capital letter)
    ' asc 111 = o   (small letter)
    
    '================================================================
    If Asc("O") Or Asc("o") Then
            UserForm1.TXTBOX_COUNTER_O.text = Val(UserForm1.TXTBOX_COUNTER_O.text + 1)
        ElseIf Asc("P") Or Asc("p") Then
            UserForm1.TXTBOX_COUNTER_P.text = Val(UserForm1.TXTBOX_COUNTER_P.text + 1)
       
    End If
    '===============================================================
    
    UserForm1.TXTBOX_COUNTER_GRANDTOTAL.text = Val(UserForm1.TXTBOX_COUNTER_O.text) + Val(UserForm1.TXTBOX_COUNTER_P.text)
    
    End Sub
    '----------------------------------------------------------------

    Thanks for any words to set me straight

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,852

    Re: Wishing to code a Form with a counter

    keypreview Property
    vba userforms do not have this property, they are available for vb6 forms, but not vba
    that being the case you would need to keep the focus to some control that has keydown events, possibly a frame that takes the entire form
    you can use the setfocus method to keep the focus to the frame so all keyboard events go to the frame
    if you at any time need the focus to go to any other control, then it gets more difficult, if you click any command button then you would need to set focus to the frame again afterwards
    i have not tested this suggestion to see if it will work as you require

    code something like
    Code:
    Private Sub Frame1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii = 79 Then lblO = lblO + 1
    If KeyAscii = 80 Then lblP = lblP + 1
    lbltot = lblO + lblP
    KeyAscii = vbNullString
    Frame1.SetFocus
    End Sub
    you will need to setfocus when the userform is initialized and anytime the focus might be removed from it
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7
    Member
    Join Date
    Jan 2012
    Posts
    55

    Re: Wishing to code a Form with a counter

    You're pretty close.

    For starters, as you can see in westconn1's comment, you'll need to evaluate the "KeyAscii" variable in the keypress routine. That variable holds the ascii code of the key you pressed.

    Code:
    If KeyAscii = 79 Then 'User pressed O
    …
    ElseIf KeyAscii = 111 Then 'User pressed o
    …
    ElseIf KeyAscii = 80 Then 'User pressed P
    …
    ElseIf KeyAscii = 112 Then 'User pressed p
    …
    End If
    etc.


    Second, you'll need to move the closing bracket in the calculation lines.

    Technically, the textbox text property holds a string value. So, you'll have to convert it to a value first, before you can add 1 to it. Otherwise you'll be adding strings, and you'll get something like 11111 after you pressed a key 5 times. If you want to do it completely correct, you'll have to convert the counter back to a string before assigning it to the text property, but in VBA you'll get away with not doing that.

    So, the line should be

    Code:
    UserForm1.TXTBOX_COUNTER_O.text = Val(UserForm1.TXTBOX_COUNTER_O.text) + 1
    or if you want to go "official"

    Code:
    UserForm1.TXTBOX_COUNTER_O.text = CStr(CInt(UserForm1.TXTBOX_COUNTER_O.text) + 1)

    One last comment on coding conventions: in the international software development world where I work, we use lowercase with capitalisation for objects and variables. So, txtCounterO for a textbox, or sFirstName for a string variable. All caps with underscores are used for constants. So, instead of commenting the KeyAscii evaluations with an explanation which key the code corresponds with like I did above, we could have defined constants like this.

    Code:
    Private Const KEYPRESS_CAPITAL_O = 79
    Private Const KEYPRESS_CAPITAL_P = 80
    
    Private Sub Frame1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
       If KeyAscii = KEYPRESS_CAPITAL_O Then
          ...
       ElseIf KeyAscii = KEYPRESS_CAPITAL_P Then
          …
    Etc.


    Hope this helps.

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Location
    Kentucky
    Posts
    22

    Re: Wishing to code a Form with a counter

    Erwin & Westconn,
    Thanks... As with any beginner, I am experimenting and learning at the same time. I was able to add a Frame to my UserForm in the hopes this would allow me to get further in my project. After looking at your suggestions, I have added a simple command button to my Form/Frame and wanted to see if I could get the command button to increment the text box. I have this very rudimentary click counter working. So at least I have learned something. I deleted all my other code except for my Clear Screen and EXIT buttons. Just doing baby steps as time permits. Will keep plugging along with your suggestions and hints.


    Name:  Capture2.JPG
Views: 44
Size:  27.0 KB

    Code:
    Private Sub CMDBTN_CLEARSCREEN_Click()
        
        Unload UserForm4
        UserForm4.Show
        
    End Sub
    
    Private Sub CMDBTN_EXIT_Click()
    
        Unload UserForm4
    
    End Sub
    
    Private Sub CMDBUTTON_1_Click()
    
        UserForm4.TXTBOX_COUNTER.text = Val(UserForm4.TXTBOX_COUNTER.text) + 1
    
    End Sub
    
    Private Sub Frame1_Click()
    
    End Sub
    
    Private Sub UserForm_Click()
    
    End Sub
    Last edited by marshallgrads; May 17th, 2020 at 10:04 AM.

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Location
    Kentucky
    Posts
    22

    Re: Wishing to code a Form with a counter

    Erwin,
    Thanks for pointing out the following;

    1. Moving my parenthesis - I think I was just typing too fast. Even though it worked in the above example, I understand completely why it should only be around the first portion.

    2. I believe the val(xxx) function is more appropriate as you seem to indicate. I like using it as I only intend to have numerical values in the display/answer.

    3. Capitalization - Our normal use of the Micro Focus - Reflection program that I am building this in usually has us typing in all caps, this is just laziness on my part for not typing in small letters. Will try to adhere to suggestions if I get a working prototype up and running...

    wew… so much for beginners to digest, but time is on my side...

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Location
    Kentucky
    Posts
    22

    Re: Wishing to code a Form with a counter

    Erwin,
    Thanks for pointing out the following;

    1. Moving my parenthesis - I think I was just typing too fast. Even though it worked in the above example, I understand completely why it should only be around the first portion.

    2. I believe the val(xxx) function is more appropriate as you seem to indicate. I like using it as I only intend to have numerical values in the display/answer.

    3. Capitalization - Our normal use of the Micro Focus - Reflection program that I am building this in usually has us typing in all caps, this is just laziness on my part for not typing in small letters. Will try to adhere to suggestions if I get a working prototype up and running...

    wew… so much for beginners to digest, but time is on my side...

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Location
    Kentucky
    Posts
    22

    Thumbs up Re: Wishing to code a Form with a counter

    Ok,
    Here is what I have and it appears to work, with some bugs of course....

    Labels are titled: lblPressO, lblPressP, lblGrandTotal
    Text Boxes are titled: TextBoxCounterO, TextBoxCounterP, TextBoxCounterGrandtotal, textboxcounter
    Command Buttons are titled: commanbutton1, commandbuttonClearScreen, commandbuttonExit

    Pairs that go together
    1. commandbutton1 >> textboxcounter
    2. lblPressO >> TextBoxCounterO
    3. lblPressP >> TextBoxCounterP
    4. lblGrandTotal >> TextBoxCounterGrandtotal


    Name:  Capture3.JPG
Views: 44
Size:  30.5 KB

    Code:
    Private Sub commanbutton1_Click()
    'this command button simply shows how simple it is to have the corresponding text box
    'increment in value each time the Command Button is pressed.
    'setting the text value property allows for the display to update.
     
        UserForm4.textboxcounter.text = Val(UserForm4.textboxcounter.text) + 1
    
    End Sub
    
    Private Sub commandbuttonClearScreen_Click()
        
        Unload UserForm4
        UserForm4.Show
        
    End Sub
    
    Private Sub commandbuttonExit_Click()
    
        Unload UserForm4
        
    End Sub
    
    Private Sub Frame1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    
    ' asc 79 = O    (capital letter)
    ' asc 111 = o   (small letter)
    ' asc 80 = P    (capital letter)
    ' asc 112 = p   (small letter)
    
    If KeyAscii = 79 Or KeyAscii = 111 Then
            UserForm4.TextBoxCounterO.text = Val(UserForm4.TextBoxCounterO.text) + 1
            UserForm4.TextBoxCounterGrandtotal.text = Val(UserForm4.TextBoxCounterGrandtotal.text) + 1
        ElseIf KeyAscii = 80 Or KeyAscii = 112 Then
            UserForm4.TextBoxCounterP.text = Val(UserForm4.TextBoxCounterP.text) + 1
            UserForm4.TextBoxCounterGrandtotal.text = Val(UserForm4.TextBoxCounterGrandtotal.text) + 1
    
    End If
    
    'KeyAscii = vbNullString
    
    Frame1.SetFocus
    
    End Sub

    As you can see, I have commented out the statement/code - KeyAscii = vbNullString.
    I don't understand its importance to the sub??

    Question: How do I prevent a user from placing the cursor in the TEXT Boxes and entering random characters... Is their an entry within the textbox properties that can control if a user can be locked out from data entry?

    Thanks to all who have gotten me this far... It is working but will continue to refine and improve before I mark this as complete. If more suggestions, please add them as part of this is learning on my part as to what works..

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Location
    Kentucky
    Posts
    22

    Thumbs up Re: Wishing to code a Form with a counter

    After doing a little more testing, I have come up with this simple edit to prohibit the users from placing the cursor/mouse in the TextBoxes of the above versions. Instead of having a TextBox to hold your incrementing value, I am taking the suggestion of having a Label hold the value in the "caption" property... At least that is how I interpreted the suggestion. It appears to work.

    NOTE: I am using Visual Basic for Applications that is contained within the program called "Micro Focus -Reflections". It is a screen terminal emulator that we use to connect to our main computer. I wanted a simple counter for some future projects. So far, this little snippet of code and the FORM are working without too many bugs.

    The Column on the LEFT (Green, Blue, Purple) are LABELS and are simply there to inform the user what key to press in order to increment the counter. The Column on the RIGHT (same colors - Green, Blue, Purple) are used to display the number, which corresponds to how many times I press either the P,p, O, or o key on my keyboard.

    Left column label (Green - "Press the letter O or o") named=> lblPressO
    Left column label (Blue - " Press the letter P or p") named=> lblPressP
    Left column label (Purple - "Grand Total") named=> lblGrandTotal
    Left column label (Yellow - "COMMAND BUTTON -CLICK HERE") named=> commanbutton1

    Right column label (Green - "Press O or o") named => lblDisplayO
    Right column label (Blue - "Press P or p") named => lblDisplayP
    Right column label (Purple - "Total will appear here!") named => lblDisplayGrandTotal
    Right column label (Yellow - "0") named => textboxcounter

    I also have 3 other textboxes which are hidden in this screen capture. I am keeping these in my test Form but will most likely remove them in future versions. It allows anyone else testing to make them visible again and experiment to see what affect/effect they have and how a user can place the cursor in a textbox and interrupt the counting... This happens presumably because the FRAME looses focus and it is the FRAME code that allows for the KEYPRESS event to be interpreted????

    The Yellow colored command button was my first attempt at coding to see if I could simply have a incrementing number display in a textbox. It worked so I have left it here for all others to see.

    Hope it helps someone in the future who is also learning..

    Name:  Capture4.JPG
Views: 42
Size:  32.9 KB


    Code:
    Private Sub commanbutton1_Click()
    'this command button simply shows how simple it is to have the corresponding text box
    'increment in value each time the Command Button is pressed.
    'setting the text value property allows for the display to update.
     
        UserForm4.textboxcounter.text = Val(UserForm4.textboxcounter.text) + 1
        UserForm4.Frame1.SetFocus ' THIS DOES NOT WORK to set the FOCUS back and allow the counter to continue.
        
    End Sub
    '------------------------------------------------------
    Private Sub commandbuttonClearScreen_Click()
        
        Unload UserForm4
        UserForm4.Show
        
    End Sub
    ------------------------------------------------------
    Private Sub commandbuttonExit_Click()
    
        Unload UserForm4
        
    End Sub
    ------------------------------------------------------
    Private Sub Frame1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    
    ' asc 79 = O    (capital letter)
    ' asc 111 = o   (small letter)
    ' asc 80 = P    (capital letter)
    ' asc 112 = p   (small letter)
    
    If KeyAscii = 79 Or KeyAscii = 111 Then
            UserForm4.lblDisplayO.Caption = Val(UserForm4.lblDisplayO.Caption) + 1
            UserForm4.TextBoxCounterO.text = Val(UserForm4.TextBoxCounterO.text) + 1
            '
            UserForm4.lblDisplayGrandTotal.Caption = Val(UserForm4.lblDisplayGrandTotal.Caption) + 1
            UserForm4.TextBoxCounterGrandtotal.text = Val(UserForm4.TextBoxCounterGrandtotal.text) + 1
        ElseIf KeyAscii = 80 Or KeyAscii = 112 Then
            UserForm4.lblDisplayP.Caption = Val(UserForm4.lblDisplayP.Caption) + 1
            UserForm4.TextBoxCounterP.text = Val(UserForm4.TextBoxCounterP.text) + 1
            '
            UserForm4.lblDisplayGrandTotal.Caption = Val(UserForm4.lblDisplayGrandTotal.Caption) + 1
            UserForm4.TextBoxCounterGrandtotal.text = Val(UserForm4.TextBoxCounterGrandtotal.text) + 1
    End If
    
    'KeyAscii = vbNullString
    
    Frame1.SetFocus
    
    End Sub
    ------------------------------------------------------
    Private Sub UserForm_Initialize()
    
    'UserForm4.textboxcounter.Visible = False
    UserForm4.TextBoxCounterGrandtotal.Visible = False
    UserForm4.TextBoxCounterO.Visible = False
    UserForm4.TextBoxCounterP.Visible = False
    
    UserForm4.lblDisplayO.Caption = "PRESS O/o"
    UserForm4.lblDisplayP.Caption = "PRESS P/p"
    UserForm4.lblDisplayGrandTotal.Caption = "Total will appear here!"
    
    End Sub
    Last edited by marshallgrads; May 17th, 2020 at 11:40 AM.

  13. #13
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    23,139

    Re: Wishing to code a Form with a counter

    Does UserForm4 have a KeyPreview property in VBA? If you set that to true, the form receives the keypress before the TextBoxes does...

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Location
    Kentucky
    Posts
    22

    Re: Wishing to code a Form with a counter

    hello .paul,
    I checked, or at least I think I did and I don't see this option in my drop down list of controls?

    Here is how I drilled down. Open my program > Select Visual Basic Editor > open my Project Folder > Select the UserForm4 >
    Place cursor on the form and then double-click to bring up the code window. I then have two drop down boxes at the top and the left one allows me to select UserForm. I then move to the other drop down box and search for the items but I don't see KeyPreview. I do see KeyDown and KeyUp.

    Thanks for the suggestion.. Right now my test form seems to be working, so I am now going to try and scale up a little to see if I can accomplish my original intent. I started with the smaller version FORM seen above, but now I am increasing the number of keys that can be pressed to increment the grand total and also their respective tally box. I can do pretty good with the 14 different keys I have code for with the FRAME keypress event.

    Right now, I am fooling around with how to get the counting to stop at 100. I have simply inserted a new line in the multiple if/elseif statements
    Code:
    If KeyAscii = 50 Then
            'code for neutrophils as the "2" key                ascii = 50
            CELLCOUNT_FORM.LabelNeutrophilAbs.Caption = Val(CELLCOUNT_FORM.LabelNeutrophilAbs.Caption) + 1
            CELLCOUNT_FORM.LabelTotalCellsDisplay.Caption = Val(CELLCOUNT_FORM.LabelTotalCellsDisplay.Caption) + 1
            If Val(CELLCOUNT_FORM.LabelTotalCellsDisplay.Caption) = 100 Then MsgBox "Great Job, You're Done!"
    This seems to work pretty well as I wish to stop when my grand total equals 100.


    Thanks for taking the time to read the post and provide the suggestion.. Much appreciated.

  15. #15
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    23,139

    Re: Wishing to code a Form with a counter

    They this...

    Code:
    Private Sub UserForm_Initialize()
    
    'UserForm4.textboxcounter.Visible = False
    UserForm4.TextBoxCounterGrandtotal.Visible = False
    UserForm4.TextBoxCounterO.Visible = False
    UserForm4.TextBoxCounterP.Visible = False
    
    UserForm4.lblDisplayO.Caption = "PRESS O/o"
    UserForm4.lblDisplayP.Caption = "PRESS P/p"
    UserForm4.lblDisplayGrandTotal.Caption = "Total will appear 
    UserForm4.KeyPreview = True
    
    End Sub

  16. #16
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    23,139

    Re: Wishing to code a Form with a counter

    KeyPreview is a Property. You were looking for an Event

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,852

    Re: Wishing to code a Form with a counter

    @ .paul, see post 6

    @OP
    at the end of every procedure (Sub) you should set the focus back to the frame, so that all keystrokes are received by the frame
    you should also set focus to the frame when the form initializes
    if you have all labels and no textboxes it will simplify as you can't type into a label and you will never type into a text box by mistake
    if you set the tabstop of all controls except the frame to false it may also help to ensure that all characters typed would go to the frame

    KeyAscii = vbNullString
    this was to stop any characters from keypresses showing in the frame, but was incorrect and not needed anyway

    i had assumed that you had already changed your counter displays to labels, when i wrote the sample code
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  18. #18
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    23,139

    Re: Wishing to code a Form with a counter

    @ westconn1 - I wasn’t sure about VBA...

  19. #19

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Location
    Kentucky
    Posts
    22

    Re: Wishing to code a Form with a counter

    I wanted to post a reply now that I have what I think is a working version of what I was attempting. A big thank you to all users who posted suggestions or examples of code. Your help was invaluable. As a newbie who just does this as a sidenote, some of this is hard to comprehend. I am posting my form and background code that goes along with it in hopes that others will be able to learn a little if you are a newbie..

    A little background - Working in a hospital laboratory, we are required to count the number of White Blood Cells and arrive at a percent of cells for the different types. This form allow me to count the cells and then display a "Percentage" based on the total cells counted. Some cells, NRBC's, are not included in the grand total and therefore they do not increment the Total number of cells display.

    I attempted to try and use the standard labeling suggested to me but I probably didn't succeed entirely.

    And for arguments sake, I am using Visual Basic for Applications as provided by MICRO FOCUS - Reflections.

    Thanks to all who helped.
    Good luck to any newbie trying to learn.

    Name:  DIFF FORM_OFFICIAL.JPG
Views: 20
Size:  69.1 KB


    Code:
    Private Sub cboCellCountDropdownbox_Change()
    
       frmCellCount.fraDiffCount.SetFocus
       
    End Sub
    Sub fraDiffCount_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    '
    ' each time a key is pressed, no matter what key, this sub will run.  It will start with the first line and work downward
    ' until a TRUE if/then is found.  Once found it will then exit the IF statement and go the "end if" line.  Code will
    ' continue from that point if there is any.
    '
    If KeyAscii = 50 Then
            'code for neutrophils as the "2" key                ascii = 50
            If Val(frmCellCount.lblTotalCellsDisplay.caption) >= Val(frmCellCount.cboCellCountDropdownbox.text) Then
                    MsgBox "Great Job, You're Done *1*"
                Else
                    frmCellCount.lblNeutrophilAbs.caption = Val(frmCellCount.lblNeutrophilAbs.caption) + 1
                    frmCellCount.lblTotalCellsDisplay.caption = Val(frmCellCount.lblTotalCellsDisplay.caption) + 1
            End If
        ElseIf KeyAscii = 49 Then
            'code for bands as the "1" key                      ascii = 49
            If Val(frmCellCount.lblTotalCellsDisplay.caption) >= Val(frmCellCount.cboCellCountDropdownbox.text) Then
                    MsgBox "Great Job, You're Done!"
                Else
                    frmCellCount.lblBandAbs.caption = Val(frmCellCount.lblBandAbs.caption) + 1
                    frmCellCount.lblTotalCellsDisplay.caption = Val(frmCellCount.lblTotalCellsDisplay) + 1
            End If
        ElseIf KeyAscii = 51 Then
            'code for lymphs as the "3" key                     ascii = 51
            If Val(frmCellCount.lblTotalCellsDisplay.caption) >= Val(frmCellCount.cboCellCountDropdownbox.text) Then
                    MsgBox "Great Job, You're Done!"
                Else
                    frmCellCount.lblLymphAbs.caption = Val(frmCellCount.lblLymphAbs.caption) + 1
                    frmCellCount.lblTotalCellsDisplay.caption = Val(frmCellCount.lblTotalCellsDisplay) + 1
            End If
        ElseIf KeyAscii = 46 Then
            'code for mono as the "." key                       ascii = 46
            If Val(frmCellCount.lblTotalCellsDisplay.caption) >= Val(frmCellCount.cboCellCountDropdownbox.text) Then
                    MsgBox "Great Job, You're Done!"
                Else
                    frmCellCount.lblMonoAbs.caption = Val(frmCellCount.lblMonoAbs.caption) + 1
                    frmCellCount.lblTotalCellsDisplay.caption = Val(frmCellCount.lblTotalCellsDisplay) + 1
            End If
        ElseIf KeyAscii = 53 Then
            'code for eos as the "5" key                        ascii = 53
            If Val(frmCellCount.lblTotalCellsDisplay.caption) >= Val(frmCellCount.cboCellCountDropdownbox.text) Then
                    MsgBox "Great Job, You're Done!"
                Else
                    frmCellCount.lblEosAbs.caption = Val(frmCellCount.lblEosAbs.caption) + 1
                    frmCellCount.lblTotalCellsDisplay.caption = Val(frmCellCount.lblTotalCellsDisplay) + 1
                End If
        ElseIf KeyAscii = 52 Then
            'code for baso as the "4" key                       ascii = 52
            If Val(frmCellCount.lblTotalCellsDisplay.caption) >= Val(frmCellCount.cboCellCountDropdownbox.text) Then
                    MsgBox "Great Job, You're Done!"
                Else
                    frmCellCount.lblBasoAbs.caption = Val(frmCellCount.lblBasoAbs.caption) + 1
                    frmCellCount.lblTotalCellsDisplay.caption = Val(frmCellCount.lblTotalCellsDisplay) + 1
            End If
        ElseIf KeyAscii = 54 Then
            'code for meta as the "6" key                       ascii = 54
            If Val(frmCellCount.lblTotalCellsDisplay.caption) >= Val(frmCellCount.cboCellCountDropdownbox.text) Then
                    MsgBox "Great Job, You're Done!"
                Else
                    frmCellCount.lblMetaAbs.caption = Val(frmCellCount.lblMetaAbs.caption) + 1
                    frmCellCount.lblTotalCellsDisplay.caption = Val(frmCellCount.lblTotalCellsDisplay) + 1
            End If
        ElseIf KeyAscii = 57 Then
            'code for myelo as the "9" key                      ascii = 57
            If Val(frmCellCount.lblTotalCellsDisplay.caption) >= Val(frmCellCount.cboCellCountDropdownbox.text) Then
                    MsgBox "Great Job, You're Done!"
                Else
                    frmCellCount.lblMyeloAbs.caption = Val(frmCellCount.lblMyeloAbs) + 1
                    frmCellCount.lblTotalCellsDisplay.caption = Val(frmCellCount.lblTotalCellsDisplay) + 1
            End If
        ElseIf KeyAscii = 56 Then
            'code for pro as the "8" key                        ascii = 56
            If Val(frmCellCount.lblTotalCellsDisplay.caption) >= Val(frmCellCount.cboCellCountDropdownbox.text) Then
                    MsgBox "Great Job, You're Done!"
                Else
                    frmCellCount.lblProAbs.caption = Val(frmCellCount.lblProAbs.caption) + 1
                    frmCellCount.lblTotalCellsDisplay.caption = Val(frmCellCount.lblTotalCellsDisplay) + 1
            End If
        ElseIf KeyAscii = 55 Then
            'code for blast as the "7" key                      ascii = 55
            If Val(frmCellCount.lblTotalCellsDisplay.caption) >= Val(frmCellCount.cboCellCountDropdownbox.text) Then
                    MsgBox "Great Job, You're Done!"
                Else
                    frmCellCount.lblBlastAbs.caption = Val(frmCellCount.lblBlastAbs.caption) + 1
                    frmCellCount.lblTotalCellsDisplay.caption = Val(frmCellCount.lblTotalCellsDisplay) + 1
            End If
        ElseIf KeyAscii = 65 Or KeyAscii = 97 Then
            'code for atyp lymph as the "A" or "a" key          ascii = 65(A) or ascii = 97(a)
            If Val(frmCellCount.lblTotalCellsDisplay.caption) >= Val(frmCellCount.cboCellCountDropdownbox.text) Then
                    MsgBox "Great Job, You're Done!"
                Else
                    frmCellCount.lblAtypLymphAbs.caption = Val(frmCellCount.lblAtypLymphAbs.caption) + 1
                    frmCellCount.lblTotalCellsDisplay.caption = Val(frmCellCount.lblTotalCellsDisplay) + 1
            End If
        ElseIf KeyAscii = 80 Or KeyAscii = 112 Then
            'code for plasma cells as the "P" or "p" key        ascii = 80(P) or ascii = 112(p)
            If Val(frmCellCount.lblTotalCellsDisplay.caption) >= Val(frmCellCount.cboCellCountDropdownbox.text) Then
                    MsgBox "Great Job, You're Done!"
                Else
                    frmCellCount.lblPlasmaAbs.caption = Val(frmCellCount.lblPlasmaAbs) + 1
                    frmCellCount.lblTotalCellsDisplay.caption = Val(frmCellCount.lblTotalCellsDisplay) + 1
            End If
        ElseIf KeyAscii = 79 Or KeyAscii = 111 Then
            'code for Other as the "O" or "o" key               ascii = 79(O) or ascii = 111(o)
            If Val(frmCellCount.lblTotalCellsDisplay.caption) >= Val(frmCellCount.cboCellCountDropdownbox.text) Then
                    MsgBox "Great Job, You're Done!"
                Else
                    frmCellCount.lblOtherAbs.caption = Val(frmCellCount.lblOtherAbs.caption) + 1
                    frmCellCount.lblTotalCellsDisplay.caption = Val(frmCellCount.lblTotalCellsDisplay) + 1
            End If
        ElseIf KeyAscii = 78 Or KeyAscii = 110 Then
            'code for NRBC as the "N" or "n" key                ascii = 78(N) or ascii = 110(n)
            If Val(frmCellCount.lblTotalCellsDisplay.caption) >= Val(frmCellCount.cboCellCountDropdownbox.text) Then
                    'stop
                Else
                    frmCellCount.lblNrbcAbs.caption = Val(frmCellCount.lblNrbcAbs.caption) + 1
            End If
            ' DO NOT INCLUDE THE NRBC's IN THE GRAND TOTAL.
    End If
    '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    'calculate the percent column for all cell types here.
    'I was getting a error when the NRBC key was hit first if the Total cell count was blank.
    'I have set the caption for the lblTotalCellDisplay = "total" so that the user will see this on the form.
    'need to check and see if it says "total" or if it has a number because of a cell being counted.  If it says
    '"total" and you hit the NRBC button because it was the first cell you saw, then this will prevent the error by
    'bypassing the code in the "else" statement.
    
    If frmCellCount.lblTotalCellsDisplay = "total" Then
            'do nothing, bypass below code because no WBC cells have been counted to increment the Total caption label.
        Else
            'opted to use variable to keep the code readable on one line.
                TOTALDIFFCELLS = Val(frmCellCount.lblTotalCellsDisplay.caption)
            'segs %
            CELLSCOUNTED_seg = Val(frmCellCount.lblNeutrophilAbs.caption)
            frmCellCount.lblNeutrophiPerc.caption = Round(((CELLSCOUNTED_seg / TOTALDIFFCELLS) * 100), 1) & " %"
            'band %
            CELLSCOUNTED_band = Val(frmCellCount.lblBandAbs.caption)
            frmCellCount.lblBandPerc.caption = Round(((CELLSCOUNTED_band / TOTALDIFFCELLS) * 100), 1) & " %"
            'lymph %
            CELLSCOUNTED_lymph = Val(frmCellCount.lblLymphAbs.caption)
            frmCellCount.lblLymphPerc.caption = Round(((CELLSCOUNTED_lymph / TOTALDIFFCELLS) * 100), 1) & " %"
            'mono %
            CELLSCOUNTED_mono = Val(frmCellCount.lblMonoAbs.caption)
            frmCellCount.lblMonoPerc.caption = Round(((CELLSCOUNTED_mono / TOTALDIFFCELLS) * 100), 1) & " %"
            'eos %
            CELLSCOUNTED_eos = Val(frmCellCount.lblEosAbs.caption)
            frmCellCount.lblEosPerc.caption = Round(((CELLSCOUNTED_eos / TOTALDIFFCELLS) * 100), 1) & " %"
            'baso %
            CELLSCOUNTED_baso = Val(frmCellCount.lblBasoAbs.caption)
            frmCellCount.lblBasoPerc.caption = Round(((CELLSCOUNTED_baso / TOTALDIFFCELLS) * 100), 1) & " %"
            'meta %
            CELLSCOUNTED_meta = Val(frmCellCount.lblMetaAbs.caption)
            frmCellCount.lblMetaPerc.caption = Round(((CELLSCOUNTED_meta / TOTALDIFFCELLS) * 100), 1) & " %"
            'myelo %
            CELLSCOUNTED_myelo = Val(frmCellCount.lblMyeloAbs.caption)
            frmCellCount.lblMyeloPerc.caption = Round(((CELLSCOUNTED_myelo / TOTALDIFFCELLS) * 100), 1) & " %"
            'pro %
            CELLSCOUNTED_pro = Val(frmCellCount.lblProAbs.caption)
            frmCellCount.lblProPerc.caption = Round(((CELLSCOUNTED_pro / TOTALDIFFCELLS) * 100), 1) & " %"
            'blast %
            CELLSCOUNTED_blast = Val(frmCellCount.lblBlastAbs.caption)
            frmCellCount.lblBlastPerc.caption = Round(((CELLSCOUNTED_blast / TOTALDIFFCELLS) * 100), 1) & " %"
            'atyplymph %
            CELLSCOUNTED_atyplymph = Val(frmCellCount.lblAtypLymphAbs.caption)
            frmCellCount.lblAtypLymphPerc.caption = Round(((CELLSCOUNTED_atyplymph / TOTALDIFFCELLS) * 100), 1) & " %"
            'plasma %
            CELLSCOUNTED_plasma = Val(frmCellCount.lblPlasmaAbs.caption)
            frmCellCount.lblPlasmaPerc.caption = Round(((CELLSCOUNTED_plasma / TOTALDIFFCELLS) * 100), 1) & " %"
            'other %
            CELLSCOUNTED_other = Val(frmCellCount.lblOtherAbs.caption)
            frmCellCount.lblOtherPerc.caption = Round(((CELLSCOUNTED_other / TOTALDIFFCELLS) * 100), 1) & " %"
            
    End If
    
    
    If Val(frmCellCount.lblTotalCellsDisplay.caption) = Val(frmCellCount.cboCellCountDropdownbox.text) Then
        'calculate the NRBC's/100 WBC.
        nrbccount = Val(frmCellCount.lblNrbcAbs.caption)
        '
            If Val(frmCellCount.lblTotalCellsDisplay.caption) = 50 Then
                    frmCellCount.lblNrbcPerc.caption = Round((nrbccount * 2), 1) & " NRBC's/100wbc"
                ElseIf Val(frmCellCount.lblTotalCellsDisplay.caption) = 100 Then
                    frmCellCount.lblNrbcPerc.caption = Round(nrbccount, 1) & " NRBC's/100wbc"
                ElseIf Val(frmCellCount.lblTotalCellsDisplay.caption) = 200 Then
                    frmCellCount.lblNrbcPerc.caption = Round((nrbccount * 0.5), 1) & " NRBC's/100wbc"
                ElseIf Val(frmCellCount.lblTotalCellsDisplay.caption) = 400 Then
                    frmCellCount.lblNrbcPerc.caption = Round((nrbccount * 0.25), 1) & " NRBC's/100wbc"
            End If
        Else
            'do nothinbg
    End If
    
    '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    
    frmCellCount.fraDiffCount.SetFocus
    
    End Sub
    Private Sub cmdClearScreen_Click()
    
    'CLEAR THE SCREEN AND REFRESH THE FORM TO BEGIN ANEW.
        Unload frmCellCount
        frmCellCount.Show
        
    End Sub
    Private Sub cmdExit_Click()
    
    'CLEAR AND CLOSE THE FORM
        Unload frmCellCount
        
    End Sub
    
    Private Sub lblPctCount_Click()
    
    End Sub
    
    Public Sub UserForm_Initialize()
    ' Must declare the Following variables as PUBLIC VARIABLES in the top portion of the code window.  Otherwise,
    ' the variable can NOT be used in the various subroutines since you are moving between the FORM and the FRAME.
    ' They are treated as separate even though it appears as though they should be one and the same.
    
    '========================================================================================================
        ' Populate the combobox list with the various differential counts you want.  Normally, users will
        ' want to count a 100 cell differential.  Low counts may be 50 cell counts.  Patients with high WBC counts
        ' might require a 200 or 400 cell count to arrive at a accurate percentage.
        '
        frmCellCount.cboCellCountDropdownbox.AddItem "50"
        frmCellCount.cboCellCountDropdownbox.AddItem "100"
        frmCellCount.cboCellCountDropdownbox.AddItem "200"
        frmCellCount.cboCellCountDropdownbox.AddItem "400"
        '
        ' set the default value for the dropdown box at 100 cells.
        'frmCellCount.cboCellCountDropdownbox.text = "4"    'this is just a test line for me to see
        'frmCellCount.cboCellCountDropdownbox.text = "50"
        frmCellCount.cboCellCountDropdownbox.text = "100"
        'frmCellCount.cboCellCountDropdownbox.text = "200"
        'frmCellCount.cboCellCountDropdownbox.text = "400"
        
        'set variable upon initializing the form to control when to end count.  This variable is used later on
        'in multiple parts of the keypress evaluations.
        'If doing a 50 cell differential (as selected for in the dropdown box) then the diff should STOP when the total
        'number of cells is 50.  Same for 100, 200, 400.
        '
        'MsgBox "You will be required to count:  **" & cboCellCountDropdownbox_cellstocount & "**  Cells"
    '=======================================================
        
    End Sub

    list of name for labels, dropdownboxes, commandboxes, etc for Diff Counting form
    B. Jones Lexington, Ky

    UserForm name: frmCellCount
    UserForm frame name: fraDiffCount
    Combobox name: cboCellCountDropdownbox
    CommandButtons names: cmdClearScreen & cmdExit


    Labels:
    ============================================
    lblCellType lblAbsCount lblPctCount.
    lblNeut lblNeutrophilAbs lblNeutrophiPerc
    lblBand lblBandAbs lblBandPerc
    lblLymph lblLymphAbs lblLymphPerc
    lblMono lblMonoAbs lblMonoPerc
    lblEos lblEosAbs lblEosPerc
    lblBaso lblBasoAbs lblBasoPerc
    lblMeta lblMetaAbs lblMetaPerc
    lblMyelo lblMyeloAbs lblMyeloPerc
    lblPro lblProAbs lblProPerc
    lblBlast lblBlastAbs lblBlastPerc
    lblAtypLymph lblAtypLymphAbs lblAtypLymphPerc
    lblPlasma lblPlasmaAbs lblPlasmaPerc
    lblOther lblOtherAbs lblOtherPerc
    lblTotalCells lblTotalCellsDisplay
    lblNrbc lblNrbcAbs lblNrbcPerc
    Last edited by marshallgrads; Yesterday at 04:34 AM.

  20. #20

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Location
    Kentucky
    Posts
    22

    Re: [RESOLVED] Wishing to code a Form with a counter

    In addition, I have set one of the properties for the combobox.

    cboCellCountDropdownbox >> Style > Set to frmStyleDropDownList.
    This had the affect of not allowing a user to type in the dropdown box. Works like a charm...

  21. #21
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    23,139

    Re: [RESOLVED] Wishing to code a Form with a counter

    @marshallgrads - Looks good, glad you got it working. It might be worth asking one of the VBA experts here how you could use (for example) Shift + O to decrease the counter by one in case of error?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width