• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto-generate 6-digit ID number

M

Maneesh Massey

Member
Hi,
I am using the code below for auto-generating a 6-digit ID number in a text box on a userform. The code goes into the button-click event of a button on the form. The only problem with the code is, sometimes the button has to be clicked twice to generate the ID number in the text box. I fail to understand why this is happening ? Does anyone else have the same experience ?
Code: 
Private Sub cmdGenerateID_Click()
Dim Low As Long
Dim High As Long
Dim r As Long
Low = 1
High = 999999
r = Int((High - Low + 1) * Rnd() + Low)
Me.Textbox1.Value = r
End Sub
 
Last edited:
Try adding a doEvents line as

Code: 
Private Sub cmdGenerateID_Click()
Dim Low As Long
Dim High As Long
Dim r As Long
Low = 1
High = 999999 
r = Int((High - Low + 1) * Rnd() + Low)
DoEvents
Me.Textbox1.Value = r
End Sub
 
Hui said:
Try adding a doEvents line as

Code: 
Private Sub cmdGenerateID_Click()
Dim Low As Long
Dim High As Long
Dim r As Long
Low = 1
High = 999999
r = Int((High - Low + 1) * Rnd() + Low)
DoEvents
Me.Textbox1.Value = r
End Sub
Click to expand...
Hi Hui,
The problem still remains even after adding the DoEvents line. Please test this code yourself and you will experience the same.
Thank you.
 
Strangely of the 5,817 posts I have made here I have only not tested solutions about 5 or 6 times, and believe it or not the solution I posed for you wasn't in those 5 or 6 times.

It works fine for me and so i suspect that it is how you are calling the code that is at fault

Can you please post a sample workbook with instructions on what your doing and what should happen
 
Hui said:
Strangely of the 5,817 posts I have made here I have only not tested solutions about 5 or 6 times, and believe it or not the solution I posed for you wasn't in those 5 or 6 times.

It works fine for me and so i suspect that it is how you are calling the code that is at fault

Can you please post a sample workbook with instructions on what your doing and what should happen
Click to expand...
Hi Hui,
I have uploaded the file, called GenerateID Demo with this post. Please open it and you will find a blank worksheet. Go to the Developer tab and you will find a Userform1 in it. Please run the form by clicking the GenerateID button. You will find that you have to double click on the button sometimes in order to generate the ID.
Thank you for helping.
 

Attachments

  • GenerateID Demo.xlsm
    12.5 KB · Views: 105
Can you try the following

Code: 
Private Sub cmdGenerateID_Click()
Dim Low As Long
Dim High As Long
Dim r As Double 'Note r should be a double


Low = 1
High = 999999
r = Int((High - Low + 1) * Rnd() + Low)

UserForm1.TextBox1.Value = r
Debug.Print r this forces a small delay

End Sub
 
Hui said:
Can you try the following

Code: 
Private Sub cmdGenerateID_Click()
Dim Low As Long
Dim High As Long
Dim r As Double 'Note r should be a double


Low = 1
High = 999999
r = Int((High - Low + 1) * Rnd() + Low)

UserForm1.TextBox1.Value = r
Debug.Print r this forces a small delay

End Sub
Click to expand...
Hi Hui,
Perfect !!! It works, at last !!! Thank you so much !!!
 
Trickery... It still has me stumped

I started doing it to watch the random values and see what was going on, in the odd chance that there were actually two equivalent random numbers being generated in a row
The slight pause seemed to do the trick
I had tried a 0.5 second wait as well as a Doevents command and a Me.repaint command and neither solved the problem
 
Maneesh

I consulted with Daniel Ferry of ExcelHero fame

He advised to change from the buttons _Select event to the buttons _MouseUp event

This has been done in the attached file

It appears to work properly all the time

Hui...
 

Attachments

  • Generate ID _excelhero2.xlsm
    19.7 KB · Views: 154
Hui said:
Maneesh

I consulted with Daniel Ferry of ExcelHero fame

He advised to change from the buttons _Select event to the buttons _MouseUp event

This has been done in the attached file

It appears to work properly all the time
Hui...
Click to expand...
Thanks Hui, appreciate your concern and so much time and effort you give in solving other's problems ! You're a HERO !!!! CHEERS !!!
Best Wishes,
Maneesh
 
What is wrong with Smallman's response ?
That how I would have done it

Also no need to remind us of a post
We watch all threads
 
You must log in or register to reply here.
Back
Top