disable command button once data is recorded

**stoey** · 08-07-2008, 11:46 AM

hi everyone,

I am new with using excel vba and I need your expertise on this one. I am making a timesheet which has a UserForm with textbox and 3 command buttons namely Search, IN and OUT. The program works like this, user type his id no., then clicks on search, once id match command buttons for IN and OUT are enabled. User clicks on IN button then current time which tags as NOW() will be pasted on the worksheet. Same goes with the OUT button. I was able to make them work that way. The problem here now is, the data being entered are vulnerable for data-re entry. What I mean is, if a certain user has already a recorded IN time, if he clicks on the IN time again, the data will be overwritten. Is there a way that once the user has already a recorded IN time, the IN button will be disabled for that user since he has already a recorded IN data?

I appreciate your assistance.

Cheers,

Stoey

**ptm0412** · 08-07-2008, 12:11 PM

Try this:

PHP Code:


Private Sub CmdB1_Click()

'Your code here

---------'

CmdB1.Enabled = False

End Sub

**stoey** · 08-07-2008, 12:56 PM

hi,

I appreciate your immediately reply. I tried your code on the IN button, it work for the first user but, when the next user will use it, the IN button is disabled same goes to succeeding users. What I want to do is that once a specific user click the IN button and his time in was recorded or pasted on the worksheet, then the IN button will be disabled since he has already entered his time IN. But for other users who dont have time in record yet on the worksheet, the IN button should be enabled for them. Same goes for them OUT button.

Any other ideas?

cheers,

Stoey

**royUK** · 08-07-2008, 01:05 PM

Your code should check if the user has an entry for that date in the cell & if it has exit the code.

**stoey** · 08-07-2008, 01:17 PM

Hi roy,

thanks for the idea but how am I suppose to do that? I am new with excel vba. Can you show me how? Thanks

Cheers,

Stoey

**royUK** · 08-07-2008, 01:22 PM

I don't know what your existing code is, so I can't tell you how to code it. Post your code or attach the workbook so I can see exactly what you are doing.

**stoey** · 08-07-2008, 01:30 PM

Hi roy,

thanks for the quick response. here is the attached workbook. some of the codes there were taken from some of the threads here. Thanks again.

Cheers,

Stoey

**royUK** · 08-07-2008, 02:05 PM

Where's the code from, you don't seem to use myarray?

Why are you searching for multiple entries? Do you expect more than one listing for each employee?

**royUK** · 08-07-2008, 02:38 PM

assuming you only have unique entries for each employee, this form eliminates the search by using a Combbox linked to a Dynamic Ranged Name containing the employee names. It also uses comboboxes for time entry.

**stoey** · 08-07-2008, 02:42 PM

hi roy,

I appreciate the time you spent in checking my test file. Im not sure with regards to the multiple listing. But I want to have the search in any form. I mean the search code is also being used in other part of the program. the file I have attached is a test file. The file I am creating is more than the alloted filesize for attachement. The search code comprise multiple listings for searching . Like I have a part on the program to be used by supervisors only in which they can us the search function to find any data of the employee like Name, ID no or designation. My only problem now is how can I disable the IN and OUT button once the specific user has already a recorded data. Thanks.

Cheers,

Stoey

**stoey** · 08-07-2008, 02:48 PM

hi again roy,

I have checked the file attachment you sent but there seem no modifications on the file you attached? Its the same as my sample file.

Cheers,

Stoey

**royUK** · 08-07-2008, 03:32 PM

No it's not there is a new userform in it

**stoey** · 08-07-2008, 03:53 PM

hi,

i have checked the added userform you have place and i am having difficulty rechanging the format codes. any other thoughts you have which is similar to what I am looking for? I'm sorry for all the troubles.

Cheers,

Stoey

**stoey** · 08-07-2008, 04:55 PM

hi roy,

I was sudying the code you place from the attached worksheet and I was able to observe that once the specific user has a complete recorded data, the END button is still active. It should be disabled as well right? since the data for the specific user are already complete. How do I change that to make all the buttons disabled once the data on the IN time and OUT time are already complete? Thanks.

Cheers,

Stoey

**royUK** · 08-07-2008, 05:36 PM

The user enters a start time & the start button is disabled. later he enters an End time 7 that button is disabled. Once a user has a start or end time entered the relevant button cannot be enabled. Have you tried making sme entries?

**stoey** · 08-07-2008, 05:59 PM

hi,

yes i did try making some entries that's why I noticed that although the data for specific user is complete still the END button is enabled. I got some ideas from your sample coding and here is what I have come up...but the problem is the END button is still enabled even though the Start Time and End Time has already data.
Here is the code for FIND button

Please Login or Register  to view this content.

Here is the code for the Start button:

Please Login or Register  to view this content.

and Here is the code for the End button:

Please Login or Register  to view this content.

Please help correct any errors I have. Thanks

Cheers,

Stoey

**royUK** · 08-08-2008, 01:03 AM

You only beeded to change the code for he names combobox

Please Login or Register  to view this content.

I really cannot see why you want to use Find and textboxes which leaves the application open to input errors - misspelling or wrong case will cause the code to fail. Nor can I see why ou wold need to check for duplicated employees.

**stoey** · 08-08-2008, 11:24 AM

hi roy,

thanks for your reply. Well the reason why I need to use textbox instead of combobox is that, some users might use it for the benefit of others. What I mean is that, if I will be using combobox, the users will have the ability to just simply select the names nor the id number which is not applicable in this case. Say, i can clock in for somebody else since I can just simply select his name from the list. But if I will use textbox and find. then the user will need to enter their individual identification ID's making it more efficient in our case. Also, in the case of checking for duplicate data, the workbook I am working on is a large workbook not enough bytes to attach it here. The find button is also use for editing in some areas of the program that's why there is a need to check duplicate entries as well.

Do you have any ideas on how to use textbox instead of combobox?

Cheers,

Stoey

**royUK** · 08-08-2008, 02:04 PM

I can't see how that helps. If one employee tells another his ID# then the other guy can log in for him.

**stoey** · 08-08-2008, 02:09 PM

hi again roy,

yeah you're right. that would be possible as well but I think it will help if I place a password for the ID number. Like entering ID number plus password for confirmation. I am about to do the program for that as well but I'm not that good in excel vba yet. still learning. I may be posting that part as another thread but as of now. I would like to stick with the current topic I have on this thread. Any idea on how to do that on textboxes? Thanks

Cheers,

Stoey