Userform listbox controls- 1 always recognized as empty, 2 don't copy selected value

[dmqueen]

I have been struggling with form controls for some time. I've created a practice form with 3 listboxes.1 is now always recognized as being unselected while the other 2 don't trigger the msgboxes, the selected values are not copying to the sheet. I cannot determine why. code below. Any help appreciated and grants you good karma! :D<code>Option ExplicitPrivate Sub Worksheet_Activate()EnterForm.ShowEnd SubPrivate Sub CancelButton_Click()Unload EnterFormEnd SubPrivate Sub EnterButton_Click()Dim bCancel As Boolean: bCancel = FalseDim ws As Worksheet: Set ws = Sheets("Sheet1")Dim lastrow As Long, i As Long'''''''''''''''''''''''''''' Check for no selections'''''''''''''''''''''''''''If LBType.ListIndex = -1 Then MsgBox ("You must select a type!") bCancel = TrueElseIf LBNumber.ListIndex = -1 Then MsgBox ("You must select a number!") bCancel = TrueElseIf LBRotation.ListIndex = -1 Then MsgBox ("You must select a rotation!") bCancel = TrueEnd IfIf bCancel = True Then Exit SubEnd Iflastrow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1'the below is meant more for multiple select listboxes but will work just fine on single select'you could just say ws.Range("A" & lastrow).Value = LBType.Value and be done with it.For i = 0 To LBType.ListCount - 1 If LBType.Selected(i) Then ws.Range("A" & lastrow).Value = LBType.List(i) End IfNext iFor i = 0 To LBNumber.ListCount - 1 If LBNumber.Selected(i) Then ws.Range("B" & lastrow).Value = LBNumber.List(i) End IfNext iFor i = 0 To LBRotation.ListCount - 1 If LBRotation.Selected(i) Then ws.Range("C" & lastrow).Value = LBRotation.List(i) End IfNext iEnd SubPrivate Sub LBNumber_Click()LBNumber.ControlSource = "B3"End SubPrivate Sub LBRotation_Click()LBRotation.ControlSource = "C3"End SubPrivate Sub LBType_Click()LBType.ControlSource = "A3"End SubPrivate Sub UserForm_Initialize()'turn all labels black, will b red if missingLblRotation.ForeColor = RGB(0, 0, 0)LblType.ForeColor = RGB(0, 0, 0)LblNumber.ForeColor = RGB(0, 0, 0)'clear and then populate listboxes hereLBRotation.ClearLBType.ClearLBNumber.ClearLBRotation.AddItem "CW"LBRotation.AddItem "CCW"LBRotation.AddItem "CW/CCW"LBType.AddItem 111LBType.AddItem 120LBType.AddItem 121LBNumber.AddItem "111 - 1 - 1"LBNumber.AddItem "120-1-2"LBNumber.AddItem "121-1-32"End Sub</code>

 

[steve the fish]

Hi Could you try posting the code again?

 

[dmqueen]

<code>Option ExplicitPrivate Sub Worksheet_Activate()EnterForm.ShowEnd SubPrivate Sub CancelButton_Click()Unload EnterFormEnd SubPrivate Sub EnterButton_Click()Dim bCancel As Boolean: bCancel = FalseDim ws As Worksheet: Set ws = Sheets("Sheet1")Dim lastrow As Long, i As Long'''''''''''''''''''''''''''' Check for no selections'''''''''''''''''''''''''''If LBType.ListIndex = -1 Then MsgBox ("You must select a type!") bCancel = TrueElseIf LBNumber.ListIndex = -1 Then MsgBox ("You must select a number!") bCancel = TrueElseIf LBRotation.ListIndex = -1 Then MsgBox ("You must select a rotation!") bCancel = TrueEnd IfIf bCancel = True Then Exit SubEnd Iflastrow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1'the below is meant more for multiple select listboxes but will work just fine on single select'you could just say ws.Range("A" & lastrow).Value = LBType.Value and be done with it.For i = 0 To LBType.ListCount - 1 If LBType.Selected(i) Then ws.Range("A" & lastrow).Value = LBType.List(i) End IfNext iFor i = 0 To LBNumber.ListCount - 1 If LBNumber.Selected(i) Then ws.Range("B" & lastrow).Value = LBNumber.List(i) End IfNext iFor i = 0 To LBRotation.ListCount - 1 If LBRotation.Selected(i) Then ws.Range("C" & lastrow).Value = LBRotation.List(i) End IfNext iEnd SubPrivate Sub LBNumber_Click()LBNumber.ControlSource = "B3"End SubPrivate Sub LBRotation_Click()LBRotation.ControlSource = "C3"End SubPrivate Sub LBType_Click()LBType.ControlSource = "A3"End SubPrivate Sub UserForm_Initialize()'turn all labels black, will b red if missingLblRotation.ForeColor = RGB(0, 0, 0)LblType.ForeColor = RGB(0, 0, 0)LblNumber.ForeColor = RGB(0, 0, 0)'clear and then populate listboxes hereLBRotation.ClearLBType.ClearLBNumber.ClearLBRotation.AddItem "CW"LBRotation.AddItem "CCW"LBRotation.AddItem "CW/CCW"LBType.AddItem 111LBType.AddItem 120LBType.AddItem 121LBNumber.AddItem "111 - 1 - 1"LBNumber.AddItem "120-1-2"LBNumber.AddItem "121-1-32"End Sub</code>

 

[dmqueen]

Trying to repost code as requested.<code>Option ExplicitPrivate Sub Worksheet_Activate()EnterForm.ShowEnd SubPrivate Sub CancelButton_Click()Unload EnterFormEnd SubPrivate Sub EnterButton_Click()Dim bCancel As Boolean: bCancel = FalseDim ws As Worksheet: Set ws = Sheets("Sheet1")Dim lastrow As Long, i As Long'''''''''''''''''''''''''''' Check for no selections'''''''''''''''''''''''''''If LBType.ListIndex = -1 Then MsgBox ("You must select a type!") bCancel = TrueElseIf LBNumber.ListIndex = -1 Then MsgBox ("You must select a number!") bCancel = TrueElseIf LBRotation.ListIndex = -1 Then MsgBox ("You must select a rotation!") bCancel = TrueEnd IfIf bCancel = True Then Exit SubEnd Iflastrow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1'the below is meant more for multiple select listboxes but will work just fine on single select'you could just say ws.Range("A" & lastrow).Value = LBType.Value and be done with it.For i = 0 To LBType.ListCount - 1 If LBType.Selected(i) Then ws.Range("A" & lastrow).Value = LBType.List(i) End IfNext iFor i = 0 To LBNumber.ListCount - 1 If LBNumber.Selected(i) Then ws.Range("B" & lastrow).Value = LBNumber.List(i) End IfNext iFor i = 0 To LBRotation.ListCount - 1 If LBRotation.Selected(i) Then ws.Range("C" & lastrow).Value = LBRotation.List(i) End IfNext iEnd SubPrivate Sub LBNumber_Click()LBNumber.ControlSource = "B3"End SubPrivate Sub LBRotation_Click()LBRotation.ControlSource = "C3"End SubPrivate Sub LBType_Click()LBType.ControlSource = "A3"End SubPrivate Sub UserForm_Initialize()'turn all labels black, will b red if missingLblRotation.ForeColor = RGB(0, 0, 0)LblType.ForeColor = RGB(0, 0, 0)LblNumber.ForeColor = RGB(0, 0, 0)'clear and then populate listboxes hereLBRotation.ClearLBType.ClearLBNumber.ClearLBRotation.AddItem "CW"LBRotation.AddItem "CCW"LBRotation.AddItem "CW/CCW"LBType.AddItem 111LBType.AddItem 120LBType.AddItem 121LBNumber.AddItem "111 - 1 - 1"LBNumber.AddItem "120-1-2"LBNumber.AddItem "121-1-32"End Sub</code>

 

[GTO]

Hi there,

I notice that you have 50 posts, so I am wondering if you have had this problem of your posted code somehow losing all the linefeeds? I see that it is in Courier New, which is not the default font. As a try, type the below in a new post:

[CODE]

' Your code pasted here

[/CODE]

Between the starting and ending CODE tags, paste your code, directly copied from VBE.

Hope that works

Mark