Excel - User Form

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
Joined
Jul 25, 2004
Messages
5,458
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..
 

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?
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

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!
 

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 :)
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

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"?
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

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.
 
Joined
Jul 25, 2004
Messages
5,458
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. :)
 

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...
 

Attachments

Joined
Jul 25, 2004
Messages
5,458
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top