none
Form with combo box changes value to Null RRS feed

  • Question

  • I have a form with 3 sets of a combo box and 3 text boxes. All 3 combo boxes contains the same list loaded on initialization of the form and it contains 5 columns. The bound column is 1 and the only columns displayed are 2 and 3. The combo box has been coded with a change event that copies the content of columns 4 and 5 of the selected item to 2 of the text boxes. All 3 sets of combo box/text boxes have the same code. The first time the form is opened and an item is selected, from any of the combo boxes, the only text box updated by the code is the first one executed in the code and the combo box does not show the value selected from the list.

    When stepping through the code in debug mode, it appears that as soon as the value of the selected item from the combo box (column 4) is assigned to the first text box, the combo box value changes to Null. There are no other events triggered (text boxes do not contain any events.)

    The next time you select an item from the combo box every thing works as coded. It only happens when the form is opened for the first time. The same behavior is found on the other combo boxes if an item is selected for the first time after the form is opened.

    I have tried deleting the combo boxes, recompiling and saving encel, and then recreating the combo boxes with the change event code and still the same problem.

    I also tried copying a hard coded value to the first text box instead of the column value from the combo box and works OK until the second text box assignment from the combo box. It only happens when assigning a value from the combo box that somehow causes the combo box value to change to Null.

    Is this a bug in excel? or can someone point out if there is any thing wrong with what I am trying to do

    The code for the change event is as follows:

    Private Sub cboReqOP1_Change()
        With Me
            If .cboReqOP1.ListIndex > -1 And .cboReqOP1.Value > 0 Then
                If mvarOldValue <> .cboReqOP1.Value Then
                    .txtReqOPstat1.Value = .cboReqOP1.Column(4)
                    .txtReqTaskStat1.Value = .cboReqOP1.Column(5)
                    .txtReqMinTask1.Value = Empty
                    .txtReqMinTask1.ControlTipText = "Double click to edit the minimum task(s) that must be satisfied for OK to build for the 1st selected operation."
                End If
            Else
                .txtReqOPstat1.Value = Empty
                .txtReqTaskStat1.Value = Empty
                .txtReqMinTask1.Value = Empty
                .txtReqMinTask1.ControlTipText = "Select the operation number for the 1st required operation first."
            End If
        End With
    End Sub

    problem happens on this line .txtReqOPstat1.Value = .cboReqOP1.Column(4)

    Thursday, June 7, 2018 11:57 AM

Answers

  • Hello LALZ,

    Just a simple workaround for the issue, add below code to UserForm_Initialize,

    ActiveSheet.Range(Me.txtReqOPstat1.ControlSource) = ActiveSheet.Range(Me.txtReqOPstat1.ControlSource)

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by LALZ Thursday, June 14, 2018 1:53 AM
    Wednesday, June 13, 2018 9:56 AM

All replies

  • If you say the combo has five columns I'm surprised your code doesn't error on this line 

     .txtReqTaskStat1.Value = .cboReqOP1.Column(5)

    The 1st column is .Column(0) and the 5th is Column(4), Column(5) would refer to the 6th column

    Thursday, June 7, 2018 9:04 PM
    Moderator
  • My mistake. The combo box has 6 columns.

    The point about my question is why the combo box value changes to null as soon as the value of column 5 is assigned to the text box.

    Friday, June 8, 2018 1:00 AM
  • Hello LALZ,

    It seems that the value of the combo box column is null while its on change event triggering first time, right?

    Have you ever checked all the column index? Will index from 0 to 5 all return null?

    How do you bind source to the combo box?

    In case of any misunderstanding, I would suggest you share a simple workbook so we could try to use it to reproduce your issue directly.

    For sharing files, you could share it via cloud storage, such as One Drive, and then put the link address here.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 8, 2018 6:10 AM
  • No, the combo box has a value until the assignment of the combo box value to the text box. Checked this by breakpoint on the code and as I step through the code. All the columns have values. As soon as the assignment happens, the value of the combo box changes to Null and no other event is trigger. Like I stated before, this only happens when selecting a value for the first time when the form is opened and when combo boxes have no values. Selecting the same item, or a different one, right after does not show the same behavior.

    The combo boxes are bound source on the properties, not on the code.

    I will try to upload the file soon. 

    Friday, June 8, 2018 7:20 AM
  • Exactly what do you mean by the value of the combo box changes to Null. The combo's value refers to the value of column(0) in the current listindex, is that what you mean or the value returned to the textbox. Or do you mean all values of all columns in the current row change to Null. Have you debugged these values, eg returned the entire list to an array and looked at the array values in Locals.

    Does this work for you -

    ' in a userform add a combo and a textbox
    
    Private Sub ComboBox1_Change()
        Me.TextBox1.Text = Me.ComboBox1.Column(4)
    End Sub
    
    Private Sub UserForm_Initialize()
    Dim arr(0 To 5, 0 To 5)
    Dim i As Long, j As Long
    
        For j = 0 To 5
            For i = 0 To 5
                arr(i, j) = i + j / 10
            Next
        Next
    
        Me.ComboBox1.ColumnCount = 6
        Me.ComboBox1.List = arr
        Me.ComboBox1.ListIndex = 0
    
    End Sub

    Friday, June 8, 2018 7:59 AM
    Moderator
  • The bound column is 1 in the properties of the combo box. The value property of the combo box when an item is selected is the one that changes to null as soon as the text box assignment is executed. and therefore once this happens the next assignment to the second text box is assigned nothing.
    One note is that when I step through the code, the second text box assignment errors out because there is a null value on the combo box value property. This does not happen if the form is running without breakpoints to figure it out what is happening.
    There are no events programmed for the text boxes.

    The code looks similar to this

    Private Sub ComboBox1_Change()
    if combobox1.listindex>-1 and combobox1.value>0 then
    Me.TextBox1.value = Me.ComboBox1.Column(4)
    Me.TextBox2.value = me.combobox1.column(5)
    else
    Me.TextBox1.value = empty
    Me.TextBox1.value = emptyend ifEnd Sub

    Private Sub UserForm_Initialize() Dim arr(0 To 5, 0 To 5) Dim i As Long, j As Long For j = 0 To 5 For i = 0 To 5 arr(i, j) = i + j / 10 Next Next End Sub

    Properties of the combo box

    column count is 6
    bound column is 1
    column widths is 0 pt;30 pt;20 pt;150 pt;0 pt;0 pt
    control source is HB12

    As I said before. This only happens when opening the form and there are no selections on the combo boxes. If any of the combo boxes have an item selected or after the first time you select an item and the problem occurs, subsequent selections from the combo box do not show the problem. Closing the form and opening the form again have the same result, first time selecting an item, if no combo boxes contain a selection.

    Friday, June 8, 2018 9:06 AM
  • Hello LALZ,

    I failed to reproduce your issue on my side. 

    Have you ever launch Excel in safe mode to test the workbook?

    Have you ever test the workbook on another pc?

    If the issue still exists, I would suggest you share a simple workbook for testing. Do you have any issue sharing a workbook?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, June 8, 2018 9:12 AM
  • I have tested it in multiple PCs (XP, win10) and different versions of excel (2010 and 2016) with the same results.

    I can not upload the file since it is work being done for a company I work for.

    I will try to create a copy with only the form/code that has the issue. I have checked the values for each of the column items and items, and there is no issue there.

    I am thinking this might be related to a corrupted code and/or a bug in Excel.

    I have not tried loading excel in safe mode.

    It is just puzzling that only happens one time when the form is opened for the first time and no selections are on the combo boxes. After that first selection, form runs as expected.

    Friday, June 8, 2018 9:25 AM
  • Hello LALZ,

    Sure, we suggest you create a new workbook for testing. If you could reproduce the issue on the new workbook, then your could share the new workbook. If you could only reproduce the issue on original workbook, make a copy of it and remove any sensitive information.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 8, 2018 9:40 AM
  • Here is the link to a copy of the file for 
    https://1drv.ms/x/s!Ag8uxVyCmzyvqg3tjEWSrfzeG4mf

    • Edited by LALZ Friday, June 8, 2018 10:45 PM
    Friday, June 8, 2018 1:34 PM
  • After more debugging, I have determined that the problem is when assigning either the value of the combo box (bounded column), or any of the other columns, to a text box that is configured with the control source property.

    If I removed the control source property of the text boxes, the problem disappears. Also, if instead of passing the values of the combo box to the text boxes, I programmed to assign the values to variables, the problem disappears. If I assign then the value of the variables to the text boxes, the problem comes back.

    Somehow the assignment to the text box is causing the combo box selection to clear.

    Wednesday, June 13, 2018 12:36 AM
  • Problems like that can arise changing values of interdependent controls with each with a controlsource before the change event has completed.

    The simplest solution is to update the changes in the combo's AfterUpdate event. However that only fires when focus has moved to another control. (also n/a with WithEvents)

    If you really need to update in the combo's change event try something like this (air code)

    ' combo change event

    Dim arr, idx AS Long, s as String

    arr = myCombo.List
    idx = myCombo.ListIndex
    s = myTextbox.Controlsource
    myTextbox.Controlsource = ""
    myTextbox.Text = arr(idx, column-number)
    Range(s) = myTextbox.Text 
    mtTextbox.Controlsource = s

    ' you might also need to reset the combo
    myCombo.ListIndex = idx

    These changes will cause the combo's change event to trigger possibly multiple times, so include 

    Private mbExit As Boolean ' at module level

    'start of combo change event
    On Error goto errExit
    If mbExit Then
        ExitSub
    Else
        mbExit = True
    End If

    'end of combo change event
    errExit:
        mbExit = False
    End Sub


    Wednesday, June 13, 2018 8:55 AM
    Moderator
  • Hello LALZ,

    Just a simple workaround for the issue, add below code to UserForm_Initialize,

    ActiveSheet.Range(Me.txtReqOPstat1.ControlSource) = ActiveSheet.Range(Me.txtReqOPstat1.ControlSource)

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by LALZ Thursday, June 14, 2018 1:53 AM
    Wednesday, June 13, 2018 9:56 AM
  • This solved the problem, but still don't understand why it happens only the first time the form is opened and there are no selections on the combo boxes. It must be an Excel bug.

    Thank you.

    Thursday, June 14, 2018 1:57 AM