userForm assigning values

**bstubbs** · 07-29-2009, 10:05 AM

Howdy, again:

When starting up my UserForm, I'm trying to set a value within a listBox, using a command like userForm1.lbSelectDriver.value = "Bob". That selection is within the list data so the listBox shows "Bob" when the form is displayed. However, to make the selection "valid", it seems that I have to enter the listBox and "jiggle" the selection (move the cursor to a different selection and then back to the one I want). Should I be setting the desired value on the basis of listIndex? If so, how?

Any thoughts.

TIA.

Bob

**romperstomper** · 07-29-2009, 10:09 AM

Where exactly are you using that code?

**bstubbs** · 07-29-2009, 10:19 AM

I load the userForm, set the values that I want and then show. Is that what you mean?

Bob

**romperstomper** · 07-29-2009, 10:25 AM

It was the specific code I was after really. From what you describe, it ought to work.

**bstubbs** · 07-29-2009, 10:54 AM

Here's the code:

Sub editRecord()
    Load UserForm1
    UserForm1.tbEditNew = "Edit"
    UserForm1.txtDate = ActiveCell.Value
    Select Case ActiveCell.Offset(0, 3).Value
        Case "Cedar Hill"
            UserForm1.selectCedarHill = True
            UserForm1.selectDeSoto = False
            UserForm1.selectLancaster = False
        Case "DeSoto"
            UserForm1.selectCedarHill = False
            UserForm1.selectDeSoto = True
            UserForm1.selectLancaster = False
        Case "Lancaster"
            UserForm1.selectCedarHill = False
            UserForm1.selectDeSoto = False
            UserForm1.selectLancaster = True
    End Select
    Select Case ActiveCell.Offset(0, 8)
        Case "AM only"
            UserForm1.obAMonly = True
            UserForm1.obAMPM = False
            UserForm1.obPMonly = False
        Case "AM / PM"
            UserForm1.obAMonly = False
            UserForm1.obAMPM = True
            UserForm1.obPMonly = False
        Case "PM only"
            UserForm1.obAMonly = False
            UserForm1.obAMPM = False
            UserForm1.obPMonly = True
    End Select
    UserForm1.lbSelectDriver.Value = ActiveCell.Offset(0, 2)
    driverID = ActiveCell.Offset(0, 1)
    If ActiveCell.Offset(0, 9) <> "" Then
        UserForm1.lbReasons.Value = ActiveCell.Offset(0, 9)
    End If
    If ActiveCell.Offset(0, 10) <> "" Then
        UserForm1.lbSelectStandbyAM.Value = ActiveCell.Offset(0, 10)
    End If
    If ActiveCell.Offset(0, 11) <> "" Then
        UserForm1.lbSelectStandbyMD.Value = ActiveCell.Offset(0, 11)
    End If
    If ActiveCell.Offset(0, 12) <> "" Then
        UserForm1.lbSelectStandbyPM.Value = ActiveCell.Offset(0, 12)
    End If
    If ActiveCell.Offset(0, 13) <> "" Then
        UserForm1.lbSelectStandbyOther.Value = ActiveCell.Offset(0, 13)
    End If
    UserForm1.Show
End Sub

I'm pulling a "record" from the worksheet, putting the data into the userForm and allowing the user to further edit the data.

Bob

**romperstomper** · 07-29-2009, 11:10 AM

Do any of the listboxes work properly? Do their change events affect each other? Are they bound to ranges on the worksheet?

**bstubbs** · 07-29-2009, 12:15 PM

In looking at which fields come over ok and those that didn't, there was nothing in the listBox definitions that would cause some to work and some not. I changed the code, however, to first "setFocus" and then setting the desired value and it worked fine, consistently. Although this solves the problem, should that point me in some direction as to why some worked and some didn't?

Thanks.

Bob