Label update value issue

[Dean Zuki]

Hi Guys, seek your expertise to look at below code. Label 5-10 value (breakdown by position)will update automatically once new data entered. But for Label 34 onward (breakdown by properties)will update the value after reset the UF. You may refer to attached file. Any idea?

Please Login or Register  to view this content.

If anybody can assist to amend the above code to more professional/ neat are welcome...

Thank you.

[Tsjallie]

In the userform_initialize event you could link the labels to their respective cells. This will automatically keep the labels up-to-date.

Please Login or Register  to view this content.

[Dean Zuki]

Hi Tsjallie, have try but... only worked for breakdown by position label. The rest nothing.

[Tsjallie]

Hmm, maybe something else going on. Can you post the workbook so I can have look?

[Dean Zuki]

Hi Tsjallie, here the attachment. tq.

[Tsjallie]

Can you upload a version without a password or PM the password?

[Tsjallie]

Looked into the workbook and have not a clue how it is supposed to work.
What I can see is that labels are filled in the sub Userform1_Activate(). But that is only when you activate the userform which in effect is only once.
The position breakbreakdown labels are also filled in the sub Textbox2_Change(), which is executed each time Textbox2 changes.
The other labels however are never updated. Also the cells the labels are filled from are never changed. That is, not by the userform.
All this makes that some labels seem to get updated and others not.

I'm attaching an edited workbook in which I made the following changes:
- Removed code from sub Userform1_Activate() because it has become obsolete
- In the sub Textbox2_Change() removed the code to fill position breakdown labels, because that has become obsolete
- Inserted sub Userform1_Initialize() with code to link all the labels to the corresponding cells

And some changes to enable you to see what's happening now.
- Schrinked the userform to fit on my small laptop screen
- Set Showmodal attribute of Userform1 to False. This makes that you can access and navigate the sheets while the userfrom is active.
These changes make that you can operate the userfrom while having Sheet3 visible and so see live what's happening.

Hope this helps as I can see you've got a long way yet to go


Edit: uploaded a version with fully qualified controlsource adresses. This makes that the sheet containing the referenced cells does not have to be the active sheet. With this version starting the userform from the ClickMe! sheet will show the correct data.

[Dean Zuki]

Hi Tsjallie, sorry for late reply. Yes its worked perfectly. I really appreciate for your explanation on the code. Thank you for your help & patience . Actually to much code inside the file that I've remove earlier & project still going on. Still long way to go.... Thanks again.

One more request; how to use for "Application.Wait Now() + TimeValue("00:00:03")". I did try but somehow the textbox only accept 11 digit instead of 12.

*Reputation added.

[Tsjallie]

One more request; how to use for "Application.Wait Now() + TimeValue("00:00:03")". I did try but somehow the textbox only accept 11 digit instead of 12.

Are these 2 questions or are you trying to have the textbox accept 12 characters by introducing a wait? If not at what stage would you need the app to wait? And what should it wait for?
Actually the Textbox_change event you're using fires each time the textbox changes, thus each time a character is entered. Preferably the event would only fire when the whole string is entered and you left the textbox by pressing [Enter] or [Tab]. This can be achieved by using the Textbox_AfterUpdate event or the Textbox_BeforeUpdate event instead. Will try to post an example.
However for that to work you would need another object on the form the proceed to which is not there.
BTW, debugged the textbox and it appears that it actually does accept 12 digits. Just - in the Textbox2_Change procedure - put a break on the line

Please Login or Register  to view this content.

When the event procedure stops you can then inspect the length of the textbox's content by putting your mouse pointer over "Len" in the line Len(TextBox2.Text) (a few lines higher).

[Dean Zuki]

Hi Tsjallie, yes the two question is related. Pls refer to below code for what I've done so far. As for the digit issue, if manually key in work fine. If use card reader then only show 11 digit.

Please Login or Register  to view this content.

Pls follow below link for better understanding.
http://www.excelforum.com/excel-prog...t=#post3817324

Thank you.

[Tsjallie]

The other thread indeed clarifies a lot.
I think the suggestions of Norie and Nathansav about the card reader adding one or more control characters are true. These machines do add data separation markers. But you cannot see them as they are so called "non-printable characters". I presume your card reader is adding a tab. That could explain why you're app is accepting only 11 characters.

If that's the case here then when using the card reader the length of Textbox2 will never be 12 and thus the condition

Please Login or Register  to view this content.

will never be true because the length of Textbox2 will be 12 plus the number of control characters added. With a tab that would be 13.
You can solve this by altering the condition above to

Please Login or Register  to view this content.

The Clean-function will delete any non-printable characters from a string.

About the use of

Please Login or Register  to view this content.

this makes Excel wait for 3 seconds before continuing execution with the line following the application.wait statement. Not sure what you're using it for.

[Dean Zuki]

Hi Tsjallie, Noted & thank you for your full detail clarification. Now all very clear & help a lot to a newbie like me. As for application.wait statement use for other label just to display value for 3sec then disappear.

Sorry for late reply as I was away from office for 2 days with no pc access. Thread closed.

Thanks again & Rgds
Dean

[Tsjallie]

Do you mean that the input from your card reader is now processed correctly?

[Dean Zuki]

Hi Tsjallie, nope. Still cannot. I just don't know what to do next. Just disable the application.wait statement I guest.

Rgds
Dean

[Tsjallie]

I don't think disabling the application.wait will get you any further.
Did you try that Clean-function?
Doesn't the manual of your card reader say anything about interfacing with applications like Excel?

[Dean Zuki]

Hi Tsjallie, yes I did use the clean-function but still cannot. When I disable the application.wait the card reader able to capture all 12 digit. Weird.. If enable then the textbox only showed 11 digit but at cell show full 12 digit? Can I send to you the whole file thru your email?

Rgds
Dean

[Tsjallie]

Without the card reader I can only look at the code. So for now no point in sending the whole file.
What I'd like you to do is adding the following sub to your module

Please Login or Register  to view this content.

and insert a call to it as the first statement in the Textbox2_change() procedure

Please Login or Register  to view this content.

This sub will then trap the content of Textbox2 and displays it in hexadecimal format, like this DumpTextboxMessage.JPG
Then use the card reader and post a screenshot of the message box.

If this doesn't give any clues yet, you can still send the whole file.
And preferably the card reader manual, if you have a digital copy of it.

[Dean Zuki]

Tsjallie, after copy down there was an error which is highlighted in yellow for below code;

Please Login or Register  to view this content.

*Code tested with other file.
*Magnetic Card Reader info: READER, SLOT, POMA MAGNETIC SLOT, TRACK 1 <(>&<)> 2 MODEL: TMSR-33-U
http://www.sovte.cz/ctecky/pdf/TMSR-33-U-SM.pdf
Attached the file with amended data.

Thank you
Dean

[Tsjallie]

there was an error which is highlighted in yellow for below code

Hmm, thought I solved that, but noop.
Here's the amended code. Should work now.
Can you try again.

Please Login or Register  to view this content.

[Dean Zuki]

Tsjallie, sorry overlook your reply. Have tested & please refer to attachment.

[Tsjallie]

Ok. That proves that the message box now works. Please try now with reading a card and post the resulting message box again.

[Dean Zuki]

Tsjallie, below latest code, each swipe only capture 1 digit then prompt message box about the characters.

Please Login or Register  to view this content.

[Tsjallie]

If the messagebox reappears with the next character after you clicked OK, then that looks like the card reader is sending the characters one-by-one. Is it reappearing?
If it stays away that would suggest the reader only sends one character. But in a previous post you mentioned the form was accepting 11 characters from the card iso of 12.
That encourages the idea of the card reader sending the whole string including an extra (control) character. Unfortunately I cannot find any documentation of this card reader explaining (or even mentioning) interfacing with an application like Excel.
How does the string read from the card get into the textbox?

I'm affraid for me the end line is coming in sight. If the problem is not in the sheet I'm out of options for helping you out. Very frustrating
May be it's a good thing to contact the supplier of card reader about this. There must be someone there having experience with this. It's very well possible that the card reader software (driver) needs a patch. The supplier should be able to advise you in that.

[Dean Zuki]

Tsjallie, the 1st number will appear 1 by 1 in the txtbox with msgbox for each number until 12 digit then dissappear after 3second. Btw, the reader did capture all 12 digit once I disable application.wait statement. That's ok my friend. You've helped me a lot. I do appreciate for your effort.

Thank you & Regards
Dean

[Tsjallie]

the reader did capture all 12 digit once I disable application.wait statement

Clean forget you mentioned that earlier.
So without the application.wait the form actually works.
Just to finalize this thread: what if you replace the application.wait with a for-next loop counting for abt. 3 secs?