adding values to a combobox from a specific range of cells

blindzero678;

I'm coming in late and I can't figure out where you're at. The workbook that I downloaded didn't have any entry form that I could find.

But I did find this to work to put a date in cboDate.

Please Login or Register  to view this content.

In the file I was looking at, it was right above "Private Sub cboPeriod_Change()"

If you post your workbook now, I may be able to help.

I actually don't see that line of code anywhere in either of the files that were posted before? Anyway, I tried pasting the code into my form, but the date was still displayed as the number "40382" once it was selected from the drop down menu.

I thought it might be easier if i attached the actual userform that I am trying to develop though.

There are three comboboxes on the userform that are not in the multipage control. In the first of these, I want to be able to select a period (last month, last week, today), and for the dates in that range that appear in the database to appear in the second combobox. From what i've done so far, i've been able to get today's date to come up in the second combobox. However, if i select today's date, 7/23/2010 isn't displayed in teh box. instead "40382" is displayed.

I also still can't figure out why the dates for "last month" and "last week" do not appear in the second combobox

also, if this makes a difference, once I select the date, i would like the third combobox to display the names that appear in the database that correspond to that date.

I really appreciate your help!

Hi blindzero678;

In the file you just uploaded, ComboBox_Period_Change() is populating ComboBox_StudyDate with names. So I'm putting dates into it. Then putting Names into ComboBox_Name. Should be ready within the hour.

Ok, here's a file that populates the date combobox with correct dates, and name combobox with correct names.

I put in #s as names for testing, and dates from 45 days ago for testing. I had it disable the date combobox if you choose today.

I added a module named "FoxGuy". In it is a function "Definitions") where you can change the variables that were used in the date and name combobox_change(). If you redesign your workbook (change sheet names, move columns, insert rows above the headings, etc), you just need to change the addresses in that function and the change events will automatically adjust.

blindzero678;

Here's a new file.

It didn't even occur to me that the dates would have duplicates. That's what debugging is for.

I like to use the same sub to copy data from the spreadsheet to the form, and from the form to the spreadsheet. That way it's easier to verify that I have not missed any fields. I just pass it a parameter that tells it what direction to transfer data.

In the Foxguy module the sub "Transfer_Data" illustrates what I mean. There are 2 alternative methods to transfer data. You pick the one you like and delete the other one.
It's called from the ComboBox_Name_Change() to load the form. I would call it from the end of CommandButton_OK_Click() to save the data to the sheet.

I personally don't like having comboboxes empty. When ComboBox_StudyDate is filled by ComboBox_Period_Change(), I would like to fill ComboBox_Name also, but it triggers the ComboBox_Name_Change() and fills the form. I commented it out in ComboBox_StudyDate_Change(), so ComboBox_Name is blank when the dates are put in. If it was my form, I might consider having it automatically loading the form anyway, but that's your decision.

I'm really at a loss for compile errors. It compiles on my computer, and I believe it worked on your computer at one time. So I'm guessing here.

If "Date" is highlighted by itself, then it's saying that your computer can't determine what the date is (I think). Try rebooting.

If the whole line is highlighted, but it used to work, then somehow the file got corrupted (I think). Try downloading from the forum again.

Also, if it's not too much trouble, would you mind explaining to me what you did? This is the first userform I've made, and I'd really like to understand for future reference.

It would be easier for me if you post the sections that you don't understand. Trying to go through one line at a time to explain what it's doing could take quite a bit, especially since I don't know how much you already know. I would assume you knew nothing and spend a lot of time trying to explain things you might already know.

I found the problem with the compile error. There was a reference listed as missing; all I had to do was remove it from the tools menu.

As for what I don't understand, could you explain these two pieces of code? I think that if I can understand these, I should be able to figure out the rest on my own.

Please Login or Register  to view this content.

Once again, thanks for being so helpful!!

Please Login or Register  to view this content.

Let me know how I did at explaining things.

That explanation helped a lot, thanks!!

I did run across one more issue though. In transferring the data from Textbox_Min and Textbox_Sec to the spreadsheet, I used a formula to convert the minutes to seconds and to enter the sum as one value in the spreadsheet.

To transfer this data back to the userform, I need to be able to convert this back to a value in minutes and seconds. I was thinking to use a RoundDown function to divide the seconds value in the spreadsheet (column "AD") by 60, rounding down to the total number of minutes. And after that, I was thinking to use another formula (i'm actually not sure which?) to retrieve the decimal from the value and multiply that by 60, to return the number of seconds. could you show me how to do this?

thank you!

Minor Improvement

Please Login or Register  to view this content.

1) Makes it easier to debug.
2) Try not to do the same calculation twice. Most of the time it won't make a difference, but forming the habit will serve you well when the calculations are more complex.

I am also now getting a "Run time error '13': Type Mismatch" whenever I click CommandButton_OK

the line

Please Login or Register  to view this content.

is selected under ComboBox_StudyDate_Change()


do you have any idea of what could be causing this?

It was working fine before, I don't know what went wrong...

Hi blindzero678;

Sorry it took so long to get back to you. I've been sick in bed all week.

If you're getting "Type Mismatch" error then try using this:

Please Login or Register  to view this content.

When a checkbox is grayed out, it means that it has a Null Value. I looked at your code, and it looks like you're setting the value to False (but I didn't really study it), so I don't understand why, but somehow your code is setting the value to null.

Hi blindzero678;

My bad.
I believe it's crashing when the name doesn't exist (I assumue you're adding a new name). I'm kind of guessing without studying the whole workbook, but I believe this is the problem.
It should be changed to this:

Please Login or Register  to view this content.

Originally Posted by blindzero678

It seems to me that it shouldn't be setting the checkbox values to Null, if I'm not mistaken?

You're right, the code looks right on the checkboxes. On the date, 0 printed with a date format is Dec 31 1899, so you might have to change the formatting to have it be blank. It does seem to me that assigning a value of "" would be enough, but obviously it isn't.

I would have to see your workbook to track down what is doing it.

You can try this:
Put a breakpoint on the line

Please Login or Register  to view this content.

( or put a "Stop" just after it).
When the code stops, you can bounce back and forth between Excel and VBE to see the effect of every line.
1st look to see if the checkboxes have been cleared. If they haven't then you know there is something in this code that does it.
Assuming that they look alright then go back to the VBE and click F8. This will step you through the code one line at a time. And every time it does something go look on the worksheet to see the effect.

Other than that you will have to post your workbook. Leave a few names and data in the file, but change the names so they are not recognized.