1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - User Form

Discussion in 'Business Applications' started by Dreambringer, Jan 13, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. Dreambringer

    Dreambringer Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    1,351
    I know this can be done in Access, but was wondering if it can be done in excel.

    What I am looking for is a User form that is always open, and it has an "imput field" where I can enter a series of numbers.

    For example, the user form would be up, and when I enter S10131 it would run a macro, without having to hit enter, or anything, I guess so once 6 letters/numbers are entered, it would run a macro.

    Any ideas? Sorry I am being kinda vague, but I dont really know what I want yet LOL! :D
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Yes, this can be done. It would have to be done post XL 97 though, as it would have to be application modal and 97 and prior does not support modal userforms.

    A little more description would help. If you can't really, I'm sure one of us could whip up an example and you could tell us if it is what you are thinking or not..
     
  3. Dreambringer

    Dreambringer Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    1,351
    Oh I am sorry.

    I am use'n Excel03.

    Well here is what I was thinking.

    I want to come up with an "easy" way to track work codes.

    So for example.

    Lets say the work code is S10137.

    Once I entered that into the form, it would look up the vaule of S10137 and Time Stamp it. Once We were done on the project, S10137 would be entered again, and it would Time Stamp the completion time.

    Really ultimatly this would be with barcodes, which is why I was looking for a user form.

    The Speical Project would have a barcode attached to it, we would scan the barcode, and begin working on it....

    I know there is software out there that will do this, but I was just wondering if this can be done using a User Form?
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I believe it can, yes. Like I said, if you load the userform as vbApplicationModal then you can interact with your program as you normally would, but you cannot run any other code than the userforms code, as it is still loaded. I am real hesitant though to say anything before you have your barcode program. There are a lot out there, and interraction may be different.
     
  5. Dreambringer

    Dreambringer Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    1,351
    Actually, the barcorde reader just reads the barcodes and imputs it as text, as if the key were being pressed. It is powered off a Y connector with the keyboard. So I allready have it loaded and it works like a champ with Excel.

    I am not sure what vbApplicatinModal is but I am going to start reading up on it.

    So anymore guideance would be helpful!

    Thanx in advance!
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Maybe you should look at a worksheet change event instead of a userform then.
     
  7. Dreambringer

    Dreambringer Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    1,351
    I thought about that, but then I was thinking, that is for what is used if a value of a Cell is changed correct?

    What I was looking for was something that for example:

    Lets say the code is S10137, when entered into the Userform, it would just put the vaule in A1, and when the next person scans it, it would go down to A2, and so on and so forth.

    Again I know this can be done is Access and other programs can do it for me, but I was wondering how it can be done in Excel :)
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Correct.

    From what I understand, the scanner scans a barcode and enters the value into the activecell in Excel. After this is entered, where does it go from there? Does the activecell stay the same, or does it function as Excel natively does and move the cursor position Down/Right/Up/Left?

    If the value is input into Excel, you can have the change event trigger a macro and have the next cell (down) be activated and ready for input/scan.
     
  9. Dreambringer

    Dreambringer Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    1,351
    Thats just it, as of right now it does not go anywhere, thats what I am try'n to develope.

    Would you mind explaing the "event trigger"?
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    When you open an Excel spreadsheet, you are in (what I consider, not sure if there is a technical term for it or not) "normal mode". When you start to type in a cell, or press F2, you enter "edit mode". When you are in edit mode no code runs and nothing happens.

    From this point you have two options. You can either cancel out of your current action, which will discredit what you have entered into the cell prior to any last entries and will not trigger any events of any kind. Or you can confirm the entry, generally performed by hitting the Enter key.

    If you confirm, this triggers the worksheet_change event and it will fire. This will not fire if a cell entry was cancelled.

    There are many events that are associated with the worksheet. Some examples are: activate, deactivate, selectionchange, change, beforedoubleclick, beforerightclick, etc. You can use the events in the worksheet module to trap these events. By "trap" we generally mean to perform some action when the event is triggered.

    When dealing with the change event in particular, if you are performing an entry in a cell (such as changing the value of another cell), one pitfall to really watch out for is just knowing that if you change a cells value from within a change event, that will - in itself - trigger another change event. This is why sometimes people will experience worksheet code running twice. To quelch that, enclose the changing of anything withing the EnableEvents property ...

    Code:
    Application.Enableevents = False
    '..
    'your code
    '..
    Application.enableevents = True
    Re your scanning software. If after you scan an item and it puts the entry into Excel, the entry must be confirmed. I don't know if your software does this on it's own or if you would have to manually hit the Enter button. In either of those cases, we can write some worksheet_change code to perform whatever it is that you wanted to perform. If by "doesn't go anywhere" you mean that it doesn't confirm the entry post-scan, you will have to manually hit the enter button.
     
  11. Dreambringer

    Dreambringer Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    1,351
    Thank you very much for the explination! :) Great HELP!

    I do not have any "scanning software".

    The scanner is on a "Y" adapter with the keyboard, so it just reads the bar code and enters it as keyboard strokes. :)

    So for example, when I create a a barcode with the code S10137 or what ever, and then open word/excel or any other program and scan the barcode, it enters the text that it reads off the barcode.

    I am trying my best to explain what I have, but I guess what I am saying is as of right now I have nothing.

    I have a barcode scanner, a program to create barcodes, a coke, a smile, and alot of time.
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    LOL! Gotcha.

    If/when you get it, I think we could get whatever you wanted to work in Excel, yes. It may require (as mentioned above) an additional keystroke after every scan, but if you're already there it shouldn't be too bad. :)
     
  13. Dreambringer

    Dreambringer Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    1,351
    Made some progress...

    Attached is what I have drawn up.

    Basicly a userform is up, called with a macro for now.

    The text will be scanned into the first field, which will be the project number.

    The second field will be populated with the current date/time. Which I have not figure out yet. (So if you can help me with that great)

    Then you have Ok, Cancel and Clear.

    Now..

    When I scan the code in, I have it set up to enter to the next line on the barcode reader, so it goes down to OK, but it will not enter the text.

    It works like a champ when "OK" is clicked, but I dont want OK to have to be clicked.

    I am stumped as to how I can get this to work, I have tried:
    Code:
    Private Sub txtName_Change()
    
    But does not work, it will actually go down every char till it gets the full one, for example.

    S10137 comes out
    Code:
    S
    S1
    S10
    S101
    S1013
    S10137
    
    So now I am stumpped :confused: :confused: :confused:

    Thanx in advance...
     

    Attached Files:

  14. Dreambringer

    Dreambringer Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    1,351
    Still no ideas?
     
  15. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I honestly do not know how to do this with a UserForm. You can however use a change event like I mentioned.

    Maybe something like this ...

    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        'exit if more than one cell
        If Target.Cells.Count > 1 Then Exit Sub
        
        'exit if not entering in column A
        If Target.Column <> 1 Then Exit Sub
        
        'perform any syntax checks you like
        If Len(Target.Value) <> 6 Then Exit Sub
        If Left(Target.Value, 1) <> "S" Then Exit Sub
        
        'enter time in column B
        Target.Offset(0, 1).Value = Time
        
        'move selection down one cell
        Target.Offset(1).Activate
        
    End Sub
    I'd highly recommend you not use a UserForm for this and go with the change event.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/433881

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice