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

Unique Id

aryan

Member
Hi Team,

Please help me in coding this dynamic userform

I want in every entry a unique Id will generate in userform and that data in excel sheet.

If I enter that Unique Id in userform that Unique Id data is upload in userform or make some changes and add data on that unique id.

till the time i do not save the data no unique id should be genrated.

name format should come in proper case whether i put it in small letters or capital letters.

date format should pick i.e. 14th Marck 2015. whether i put in mm/dd/yy or yy/mm/dd format.

Thanks in Advance
 

Attachments

  • HR ASSESSMENT SHEET -2.xlsm
    34.5 KB · Views: 13
Hi:

The unique ID can be anything it can be a simple serial no to a combination of any fields available in your data base. From your post what I understood is that the operation you want to perform is to search and update based on a unique id, this is quite simple to achieve. Firstly you decide on the logic on the basis of which you want your unique Id to be generated the coding for the rest can be done.

Thanks
 
First of all thanks for replying me Dear Nebu.

What i want when i open this userform . A unique id will generate example like no 1,2,3 etc. If i do not save the data no id will be created.

second thing i need a button when i want to modify into save data. then by entering this unique id i can pull that unique id data and and modifying the data and save the data same unique id.

Thanks
 
Hi:

Find the attached. I have added buttons for Search, Update, Add & Delete

1. The unique Id is auto generated while a new record is added, No need to enter the Unique Id.

2. For updating a record , enter the unique ID is the proper format and hit search it will pull data for that unique ID, after making necessary changes hit update, the specific row will be updated.

3. For deleting a record first give a search and hit delete the record will be deleted from the database.

Let me know with questions if any.

Thanks
 

Attachments

  • HR ASSESSMENT SHEET -2.xlsm
    28.4 KB · Views: 18
Hi Nebu,

Thanks, Awesome work.

I checked the the tool. like some id's were repeating like 0001, 0002 then i made the change in the Me.txtUniqueId = Format(Val(Cells(Rows.Count, 2).End(xlUp)) + 1, "0000") instead of rows.count,2, i made it 1 because it was repeating the same no again. then it was showing unique id

can it be possible when i open the userform unique id show in userform tool. in unique id textbox


when i search the some unique id then it is picking the data on entering incomplete unique id like 004 or 00 . it should not give the data on putting incomplete no.
please set a range that no duplicate entry should be made.

if it is date format then it should have a format ie. no text no should be there.

Thanks
 
Hi:

  • I have fixed the issue of unique ID.
  • The form will display the next ID on load now.
  • I have put data validation for DOB filed , you can replicate the same for other fields.
  • The search criteria has been change it will search for the whole text in the field will not give results for partial entries(0000 format is just for display purpose even if you give 1 it will bring up results for 0001 which is right)

Thanks
 

Attachments

  • HR ASSESSMENT SHEET -2.xlsm
    36.6 KB · Views: 16
Last edited:
Dear Nebu,

I was testing the tool, when i open the userform it is uploading the unique id and when i save the first record and without closing it when i want to add another record it is updating another record with another entry why it is not showing the unique id on text box.
means when i open userform first time it is showing unique id on the top, when i add another record without closing it, second unique id should come on unique id text box without closing the button. although it is showing on database.

and second thing is it right to search 1 for a unique id 0001 ? i am confusing about it.

Dear Nebu i want date format in like format 22 march 2015. i tried but no capture the right format and where i made the combox i am unable to put anything please make a validation there that nobody could right anything except dropdown

please make changes in attached file

Thanks
 

Attachments

  • HR ASSESSMENT SHEET new 2nd.xlsm
    32.2 KB · Views: 6
Hi:

Find the attached.

  • I was testing the tool, when i open the userform it is uploading the unique id and when i save the first record and without closing it when i want to add another record it is updating another record with another entry why it is not showing the unique id on text box.
    -Taken care.
  • second thing is it right to search 1 for a unique id 0001 ? i am confusing about it.
    as long as when you search for 1 it is retrieving the record for 0001 and if you search for 11 it is retrieving letter for 0011, the search is working right.
  • i want date format in like format 22 march 2015.
    The work book will now show the date in the format you need , but the entry in the userform should be dd/mm/yyyy format.
  • i tried but no capture the right format and where i made the combox i am unable to put anything please make a validation there that nobody could right anything except dropdown
    - Take care
Thanks
 

Attachments

  • HR ASSESSMENT SHEET -2.xlsm
    37.4 KB · Views: 14
Dear Nebur, I was facing issues like after saving record and i do not close it and enter another record entry another unique id was not showng. when i search for save record i am unable to make changes.
Thanks
 
Dear Nebu,
Due to below code search option was not working i am unable to make changes in search button so i make non functional this code. Request to you please check and confirm this.
Private Sub txtName_Change()
'Me.txtUniqueId = Format(Val(Application.WorksheetFunction.Max(Sheet2.Range("A2:A" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row)) + 1), "0000")
End Sub

now i made some changes in delete code so after deleting that row userform may empty and also made changes in add button so after making one entry new userform unique id can be see in textbox.
Request to you please check whether the code are right or appropriate?

If I select yes in combobox in textbox fresher then other field related to its like name of the current company, total experience or last drawn salary be inactive because fresher person do not need this field for making entry.
still testing it and share more information with you


Thanks
 

Attachments

  • HR ASSESSMENT SHEET 6 new one.xlsm
    36.5 KB · Views: 10
Hi:

Find the attached.

I am not sure why you are saying search button is not working, It is working fine. While searching say for the record 1 do not give 0001 just give 1 and hit search it will bring up the results.

I have coded for conditional enabling and disabling of the text boxed based on fresher combo box.

I am not sure whether you will have to unload the form after delete. Rest all the code looks fine to me.

Thanks
 

Attachments

  • HR ASSESSMENT SHEET 6 new one.xlsm
    31.9 KB · Views: 32
Dear Nebu ,

When I enter wrong date or put any text then cursor goes forward to next field it is not static on that field.

same goes for other dates field.

Second thing can multiple users open this userform at same time and data will save in excel sheet for all of them. different user from different system will acess this userform and save the information.
or I have to make database in access?
please suggest here.

Regards
Aryan
 
Hi:

Use the following code for the DOB text box try to replicate the logic for other date fields.

Code:
Private Sub cmbGENDER_DropButtonClick()
If Not IsDate(txtDOB.Value) And txtDOB.Value <> vbNullString Then
    txtDOB = vbNullString
    If (MsgBox("Only Date allowed.", vbCritical)) = vbOK Then Me.txtDOB.SetFocus
End If
End Sub
Regarding the sharing of workbook with multiple users , you will have to test it out and check whether the data entry is happening appropriately.

Thanks
 
Dear Nebu,

I tried above mentioned syntex for date function it is taking date but also taking text in it.

Thanks
Aryan
 
Dear Nebu,

new code will be like this

Private Sub txtDOINTERVIEW_DropButtonClick()

If Not IsDate(txtDOB.Value) <> vbNullString Then
txtDOB = vbNullString
If (MsgBox("Only Date allowed.", vbCritical)) = vbOK Then Me.txtDOB.SetFocus
End If
EndSub
End Sub

I tried it but is working same as my old code(txtDOB_AfterUpdate)
when i entered wrong date format then my cursor goes forward in next field in gender text box.After entering the wrong format it should be in same field.
Please suggest.

Thanks
Aryan
 
Hi:

It is difficult to get cursor back since the excel has already determined where the focus is going to go next, but hasn't yet moved the focus there. So it does no good to set the focus back to Text box, because it will still move
on to the next control as soon as that event is over.
the only work around is the following code

Code:
Private Sub txtDOB_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsDate(txtDOB.Value) Then
    MsgBox "Enter Date.", vbCritical, "Error"
    Cancel = True
    txtDOB = vbNullString
    Exit Sub
End If
End Sub

Thanks
 
Back
Top