Posts by DrGuru

Re: Adding records to a multi-sheet database

My most sincere thanks for your assistance in this. You have saved me hours of work as a lot of what I am doing is getting information from a business registration site, and when I would paste from the site directly into a cell, it would inherit the hyperlink, which is what I didn't need nor want. Any given that I have just under 200 worksheets to work with so far, having to scroll through them all would have taken a great deal of time.

So again, I thank you.

Hello,

It's been a couple of years since I was last on here, but thought I'd come back before doing something rash, like throwing my system out the window.

I am setting up a database in Excel for one of my businesses. This database has multiple worksheets, one for each business category.

Basically, I am trying to design a simple userform that will allow me to add records to the registry. The userform contains three labels, two textboxes, a combobox, and two command buttons.

The combobox is how I select the business category that the new entry needs to be entered in. What I am trying to do is when I enter the data on the form and select the category, then when I click the Add Record button, it will automatically go to the worksheet for that business category, find the next empty row and paste the data from the form into the appropriate cells.

I would post a copy of the workbook except that it is quite large already (over 1MB). Any assistance the could be rendered would be most appreciated. Oh, also, not sure if you need to know, but I am using Excel 2003.

Thank you for your reply. I played around a bit using the DATEDIF function, but it lacks the versatility that I am looking for. I might play with it some more once I get home, but I would still like for having a VBA solution as well so that I can compare between the two.

Good evening everyone,

I am working on developing an employee evaluation module that will be used during employee performance reviews to determine raises and such. In an employees first year, they are called in for three such reviews - 3 months, 6 months and 1 year point, then every year on the anniversary thereof. Basically, what I would like to do, at least in line with the topic of this posting is this:

First, I would like to have a reminder system so that the program compares each employee's start date against the current date and will pop up a msgbox or something similar reminding one month prior of their review date so that it can be scheduled in.

Second, upon the date of any given employee's review, it will again be comparing between the employee's start date and the current date to determine which review session they are in for.

I know that I had seen similar posts in the past, but I did a couple of searches using different search parameters and was unable to find them. Any help on this will be greatly appreciated.

Hi Richie,

Yes, I understood that from your initial reply. Is there no way to just have one form that resizes itself based on the system screen resolution as opposed to being based on the application window parameters?

Good afternoon,

Sorry for me delayed reply. I suffered a system crash that took out my internet for the weekend.

Richie - I tried both your methods, but was only able to get the second one to work. Is the code in your first example specific to a particular version of windows or excel? I am running Windows XP Pro and Excel 2002 at home and at work, it is Windows 95 and Excel 2000. It is my home system that I attempted your first example with. Any suggestions?

Good morning everyone,

Just a quick question here. This is somewhat related to a post that I had made on here some time ago relating to adding controls to a userform using VBA.

My question is this:

Is it possible to to code your form in such a way so that when activated, it will detect the users screen resolution and resize itself so that it is always say 80% of both the x and y axis? For example, is the user has their screen resolution set at 800X600, then the userform would resize itself to 640X480.

There is a second part to this query, but I will leave that for a little later. My thanks to anyone who is able offer any insight on this.

Good morning Richie,

Thank you so much. That is exactly what I was looking for. And it was easily modified to act as an elapsed time counter instead. There are some other things that I want to try with this, but I am going to do my best to figure those out on my own. My thanks again for your help.

Yes, I suppose that could be a problem... unless everything necessary for the completion of projects and the resulting accounting of such were all contained within the same file, each user having his/her own login and such. When a user logs in, the timer begins; then, when the user logs out, either for a break or when they are done for the day, the timer stops and the timer value is recorded for that user and then the timer is reset back to zero.

Of course, the method that you use gives me yet another option that I can look at. Eventually, I know that I will have to find another forum for the completion of this project as, upon the successful completion of it using Excel, I am planning on trying to port it out to be compiled using a combination of both VB and VF(Visual FoxPro). But that is, at best, a long way down the road for me... hehehe. :biggrin:

Good morning,

Well, I had a chance to look at both examples more in depth, but unfortunately, I am no further ahead. Aaron's active clock file looks good, but in looking at his code, I was not able to figure out how to reverse it so that it would act as a countdown timer instead of a clock.

Another way that I would like to be able to use this is as an elapsed time counter so that when you start the timer, it starts at zero and counts up from there. The elapsed time counter is something that I would like for inclusion in a large-scale accounting, invoicing, and billings project I am working on.

Anyways, if anyone out there is able to offer some insight into this, I would be truly grateful.

Good morning Richie,

Thanks for your reply. I looked briefly at both links you provided, but being that I still have some work to wrap up before the end of my shift, I will have to wait til I get home to look at these more in depth.

Will let you know how I make out.

Good morning everyone,

As you can see from the subject, I am playing around with creating a simple countdown timer in excel. The user inputs the hours, minutes, and seconds on the worksheet and then clicks the start button. This action calls up a userform that has the timer as well as two buttons - pause and stop. I have yet been able to figure out how to code within the timer loop to check for either of these two buttons getting clicked.

The stop button is self-explanatory. When the user clicks on the pause button, I want it to call up a message box that states "Timer Paused", which, of course, will suspend the timer loop until the user clicks the OK button on the message box. Also, I have found that the timer freezes is the mouse is clicked or any key on the keyboard is pressed, so is there any way to code in so that the only way the timer can be disrupted is pressing either the pause or stop button? Right now the timer part works perfectly except for the fact that it enters into an endless loop until the hours counter reaches zero or the user presses CTRL-Break.

Thanks to any and all who are able to assist in this. I have posted a copy of the file for your convenience.

Hi Will,

Ya beat me to the punch... hehehe. You state though in your reply that you can specify up to 3 "initial" sort criteria. So does this mean that there is a way to specifiy more than 3?

Hi James,

Glad I was able to help. I took a look at Richie's file, and it appears that his example solely employs the built-in conditional formatting without the use of VBA.

Hi James,

I have attached a modified copy of your file. Hope it does what you are looking for.

Hi James,

As Neale has pointed out, your formula is changing the reference because your code is physically deleting the columns instead of just the data.

The following line should do the trick for you. I would suggest putting it into the Workbook_Open procedure.

Sheets("WorkSheetName").Range("A1:F300").Clear

The range reference of course can be changed according to your actual workbook. The above line of code will just clear the contents of the specified range without actually deleting the entire columns.

Hope this helps.

Good morning holming,

Welcome to the forum. I have attached a small sample file with some test figures and a simple userform that only contains a single combobox.

The list that the combobox fills from is in column A. When you select an item from the list, it is then posted into the cell C1.

I hope this is along the lines of what you were looking for.

Good morning Andy,

Thanks so much for your help. It is working perfectly. I manually double checked on the figures that it produced and they are all bang on target.

Hopefully the upper dudes will be happy with it this time as I had spent close to between 30 to 40 hours designing and compiling the first file only to be told that they didn't like it. Grrr.

Again, my thanks for your help in this.

Good morning Dave,

I have switched this back to unsolved as I am once again getting the same error, but with a new file as upper management wanted the file redone in a different format. With the previous file, I was finally able to get this working with the Select Case Month statement. Now I am needing to beak this down further by having a nested Select Case series. The first Select Case argument looks at the year, and if the year matches, then it goes into the next stage Select Case to match the months. I hope this all is making sense. I have queried the column that is being looked at for the dates and every single entry IS a valid date, so I am completely bewildered as to why I am once again getting this error.

I have attached a copy of the file as it is important that I have this completed and ready ty send to upper management by the end of my shift. Any help by you or others reading this post will be greatly appreciated.

Good morning,

My apologies to Derk and Andy for my delayed reply. I usually only log on here from work as my current projects are for work.

Anyways, I looked through my workbook based upon your replies and found the cause of the error. There were two customer lines where the dates wasn't known, and so was entered as "?", which, not being a date, was enough to cripple the code. I took the two ?'s out and now it works perfectly. Thanks so much for your help.