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.

Private Sub cboDate_Change()
    cboDate.Value = Format(cboDate.Value, "MM/DD/YYYY")
End Sub

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.

For lRow = 1 To 10
        If Sheets("LOVs").Cells(lRow, 1).Value = sPeriod Then
            iDays = Sheets("LOVs").Cells(lRow, 2).Value
        End If
    Next lRow

    With shDates.Range("A" & Rows.Count).End(xlUp)
        lDatesNextRow = .Row
        If .Value <> "" Then
            lDatesNextRow = .Row + 1
        End If
    End With

Once again, thanks for being so helpful!!

    'Find out how many days forward or backward to locate dates.
    For lRow = 1 To 10
        'Look at 1st column in each row to see if it matches sPeriod
        '    sPeriod = ComboBox_Period.Value
        If Sheets("LOVs").Cells(lRow, 1).Value = sPeriod Then
            'found the row that matched sPeriod
            '   so take the value from 2nd column
            iDays = Sheets("LOVs").Cells(lRow, 2).Value
        End If
    Next lRow

    'Common method to find last cell in column A that has a value
    '    Range("A" & Rows.Count) is same as Range("A1048576")
    '    .End(xlUp) is the same as clicking {End}{Up} from Range("A1048576")
    '        finds last cell with a value in it
    With shDates.Range("A" & Rows.Count).End(xlUp)
        'put row # into variable
        lDatesNextRow = .Row
        If .Value <> "" Then
            'Only way that .Value = ""
            '    is if the entire column was empty,
            '    so the cell should always have something in it,
            '    so take the next row
            '
            'If for some reason the entire column was empty
            '    then this line would not execute
            '    and leave lDatesNextRow as Row(1)
            lDatesNextRow = .Row + 1
        End If
    End With

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

Dim iSecs as Integer
Dim iMins as Integer

If Not rDataRow.Cells(1, "AD").Value = "" Then
    .CheckBox_Exercise.Value = True
    iSecs = rDataRow.Cells(1, "AD").Value
    iMins = Int(iSecs/ 60)
    .TextBox_Sec.Value = iSecs - iMins * 60
    .TextBox_Min.Value = iMins
End If

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

        dt = .Value

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:

dt = 0
On Error Resume Next
dt = cdate(.Value)
On Error Goto 0     'or whatever error checking you're using
If dt = 0 Then
    msgbox "Data in " & .Name & " is not a date"
End If

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:

    With shDataBase
        Set rNames = .Columns(1)
        Set rDataRow = rNames.Find(sName).EntireRow
        If rDataRow Is Nothing Then
            Set rDataRow = .Cells(.Rows.Count, iIDCol).End(xlUp).Offset(1, 0).EntireRow
        End If
    End With

Change to:

    With shDataBase
        Set rNames = .Columns(1)
        Set rDataRow = rNames.Find(sName)
        If rDataRow Is Nothing Then
            'Adding new name
            Set rDataRow = .Cells(.Rows.Count, iIDCol).End(xlUp).Offset(1, 0).EntireRow
        Else
            Set rDataRow = rDataRow.EntireRow
        End If
    End With

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

    ActiveWorkbook.Save

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