none
How to stop Combobox Change? RRS feed

  • Question

  • Hi,

    I am using an unbound combobox in UserForm and I have problem with changing event. I would like to make sure that the user uploaded data for particular year before changing the year (using msgbox). How could I prevent combobox form changing if the answer is not and the form should keep unchanged (as well as the combobox which is connected). As far as I found it on forums there is a BeforeUpdate option but this applies only for bound combobox. So what could I do in a case of an unbound combobox?

    I would be very grateful for help

    regards

    Tuesday, September 17, 2013 5:43 PM

Answers

  • Zielik,

    I think this will work:

    Option Explicit
    Public disableChange As Boolean
    
    Private Sub ComboRok_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim Odpowiedz As Integer
    If disableChange Then
        disableChange = False
    Else
        Odpowiedz = MsgBox("Czy wczytano dane dla biezacego roku ?", vbYesNo + vbQuestion, "Uwaga!")
         If Odpowiedz = 7 Then
            Cancel = True
            SendKeys "{ESC}"
        Else
        Call LoadDataUserForm 'this is uploading userform with data loaded to the sheet so far
    End If
    End If
    End Sub
    
    'loading Combobox with first value when userform is loaded
    
    Private Sub UserForm_Initialize()
    Dim i As Integer
    For i = ActiveSheet.Cells(12, 14).Value To ActiveSheet.Cells(12, 14).End(xlToRight).Value Step 1
        Formularz_Dane.ComboRok.AddItem i
    Next i
    disableChange = True
    Formularz_Dane.ComboRok.ListIndex = 0
    End Sub

    Jan


    I forgot to say that BeforeUpdate only fires if you leave (try to leave) the control.
    So there has to be at least one other control that can get the focus.
    If you change the value of the control and then close the form, then also BeforeUpdate will be triggered.
    • Edited by jgkzdl Wednesday, September 18, 2013 12:56 PM
    • Marked as answer by Zielik Wednesday, September 18, 2013 2:54 PM
    Wednesday, September 18, 2013 12:47 PM
  • Zielik,

    If you normally hit tab, the focus goes to an other control.

    If you have changed the date in the combobox and you hit tab, then (and only then) BeforeUpdate will be triggered and you get your questions msgbox.

    I hope that does the trick.

    Jan

    • Marked as answer by Zielik Wednesday, September 18, 2013 2:54 PM
    Wednesday, September 18, 2013 2:31 PM
  • Zielik,

    If you use the next code, then changing the year and then closing the form will not show the question.
    I's this what you want?

    The other thing is, you can now have 1 year in the combobox-list.

    To your first question I do not see an answer at this moment except: no.

    Option Explicit
    Public disableChange As Boolean
    
    Private Sub ComboRok_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim Odpowiedz As Integer
    If disableChange Then
        disableChange = False
    Else
        Odpowiedz = MsgBox("Czy wczytano dane dla biezacego roku ?", vbYesNo + vbQuestion, "Uwaga!")
         If Odpowiedz = 7 Then
            Cancel = True
            SendKeys "{ESC}"
        Else
        Call LoadDataUserForm 'this is uploading userform with data loaded to the sheet so far
    End If
    End If
    End Sub
    
    'loading Combobox with first value when userform is loaded
    
    Private Sub UserForm_Initialize()
    Dim i, iStart, iEnd   As Integer
    iStart = ActiveSheet.Cells(12, 14).Value
    iEnd = CInt(ActiveSheet.Cells(12, 14).End(xlToRight).Value)
    If iEnd > iStart Then
        For i = iStart To iEnd Step 1
            Formularz_Dane.ComboRok.AddItem i
        Next i
    Else
        Formularz_Dane.ComboRok.AddItem iStart
    End If
    
    disableChange = True
    Formularz_Dane.ComboRok.ListIndex = 0
    End Sub
    
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        disableChange = True
    End Sub
    

    Jan
    • Marked as answer by Zielik Wednesday, September 18, 2013 10:11 PM
    Wednesday, September 18, 2013 9:49 PM

All replies

  • Hi,

    I am using an unbound combobox in UserForm and I have problem with changing event. I would like to make sure that the user uploaded data for particular year before changing the year (using msgbox). How could I prevent combobox form changing if the answer is not and the form should keep unchanged (as well as the combobox which is connected). As far as I found it on forums there is a BeforeUpdate option but this applies only for bound combobox. So what could I do in a case of an unbound combobox?

    I would be very grateful for help

    regards

    Hi,

    anyone to help ? This is very important to me.

    I have userform joined with spreadsheet. If I change the year the form loads data from particular year column into the userform to see what have been inputed so far. So it is important for me to prevent situation when somebody change the year in userform before uploading data to the spreadsheet because he will lose his work done on the userform. How to stop changing combobox before answering the question wheter the data form userform were uploaded for particular year.

    regards

    Zielik


    • Edited by Zielik Tuesday, September 17, 2013 10:04 PM
    Tuesday, September 17, 2013 9:28 PM
  • Zielik,

    I don't know how to formulate the condition, but I have used: If Choice>2011 then that choice is invalid.

    So, if the combobox on the form has the name cboYear, you can use the next code under BeforeUpdate:

    Private Sub cboYear_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
        If Me.cboYear > 2011 Then
            Cancel = True
            MsgBox "The value " & Me.cboYear.Value & " is invalid." & vbNewLine & "The previous value will be restored", vbInformation, "Invalid choice"
            SendKeys "{ESC}"
        End If
    End Sub

    Jan

    Wednesday, September 18, 2013 7:31 AM
  • Hi,

    I think that the problem is with my combobox being unbound. If it is unbound - BeforeUpdate does not apply to this kind of Combo.

    Regards

    Zielik

    Wednesday, September 18, 2013 8:49 AM
  • Zielik,

    Did you try the code, because the ControlSource of the combobox cboYear in my example is empty, so it is unbound. And still the BeforeUpdate event works. Although in VBA under Excel 2010 and older (and also under Word I guess).

    In which programm you are using the UserForm?

    Jan

    (even under Acces 2010 it's working; the declaration of the event BeforeUpdate is slightly different)

    • Edited by jgkzdl Wednesday, September 18, 2013 9:51 AM
    Wednesday, September 18, 2013 9:34 AM
  • Jan,

    I use MsExcel 2007. THere is something wrong with it (or I just can't use it) because BeforeUpdate is activating only when loading and closing userform not when changing the date in Combobox.

    Zielik

    Wednesday, September 18, 2013 9:57 AM
  • Zielik,

    Do you use the events UserForm_Initialize, UserForm_Activate, UserForm_Deactivate and/or UserForm_Terminate?

    When not, then it seems something is going on that someone has to see the file for.

    Jan

    Wednesday, September 18, 2013 10:48 AM
  • This is how I do this:

    'a flag:

    Public disableChange As Boolean

    'loading Combobox with first value when userform is loaded

    Private Sub UserForm_Initialize()
    Dim i As Integer
    For i = ActiveSheet.Cells(12, 14).Value To ActiveSheet.Cells(12, 14).End(xlToRight).Value Step 1
    Formularz_Dane.ComboRok.AddItem i
    Next i

    disableChange = True

    Formularz_Dane.ComboRok.ListIndex = 0

    disableChange = False

    End Sub

    ' this is asking a question wheter the data for current year were uploaded

    Private Sub ComboRok_Change()

    If disableChange Then Exit Sub

    Odpowiedz = MsgBox("Czy wczytano dane dla bieżącego roku ?", vbYesNoCancel + vbQuestion, "Uwaga!")

    If Odpowiedź = 7 Then

    Exit Sub

    Else

    Call LoadDataUserForm 'this is uploading userform with data loaded to the sheet so far

    End If

    End Sub

    and this is working but even if I answer that the data were not uploaded the combobox change and new year is loaded from sheet to the userform (and data entered for the current / previous year disappear - this is the risk that you do the work and lose it)

    Zielik

    Wednesday, September 18, 2013 11:52 AM
  • Zielik,

    I think this will work:

    Option Explicit
    Public disableChange As Boolean
    
    Private Sub ComboRok_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim Odpowiedz As Integer
    If disableChange Then
        disableChange = False
    Else
        Odpowiedz = MsgBox("Czy wczytano dane dla biezacego roku ?", vbYesNo + vbQuestion, "Uwaga!")
         If Odpowiedz = 7 Then
            Cancel = True
            SendKeys "{ESC}"
        Else
        Call LoadDataUserForm 'this is uploading userform with data loaded to the sheet so far
    End If
    End If
    End Sub
    
    'loading Combobox with first value when userform is loaded
    
    Private Sub UserForm_Initialize()
    Dim i As Integer
    For i = ActiveSheet.Cells(12, 14).Value To ActiveSheet.Cells(12, 14).End(xlToRight).Value Step 1
        Formularz_Dane.ComboRok.AddItem i
    Next i
    disableChange = True
    Formularz_Dane.ComboRok.ListIndex = 0
    End Sub

    Jan


    I forgot to say that BeforeUpdate only fires if you leave (try to leave) the control.
    So there has to be at least one other control that can get the focus.
    If you change the value of the control and then close the form, then also BeforeUpdate will be triggered.
    • Edited by jgkzdl Wednesday, September 18, 2013 12:56 PM
    • Marked as answer by Zielik Wednesday, September 18, 2013 2:54 PM
    Wednesday, September 18, 2013 12:47 PM
  • Jan,

    thank you. This code does not make msgbox asking me about the change while changing the year. I see comment you added but I think I am too weak to understand what it is going on with this ohter control. Could you extend what you mean. How could if at all?

    regards

    Zielik

    Wednesday, September 18, 2013 2:22 PM
  • Zielik,

    If you normally hit tab, the focus goes to an other control.

    If you have changed the date in the combobox and you hit tab, then (and only then) BeforeUpdate will be triggered and you get your questions msgbox.

    I hope that does the trick.

    Jan

    • Marked as answer by Zielik Wednesday, September 18, 2013 2:54 PM
    Wednesday, September 18, 2013 2:31 PM
  • Unfortunately not,

    maybe this is also due to my english or more VBA slang. I do not know what hitting tab means. Ok I think that I am not able to do it but many thanks for your time.

    regards

    Zielik

    Wednesday, September 18, 2013 2:54 PM
  • Zielik,

    With my last code:

    If you change the date in the combobox and then leave the combobox what happens then?

    Jan

    Wednesday, September 18, 2013 5:03 PM
  • Oh,

    now I know what you mean. What a shame on me. So it works but I have to hit the TextBox. Many thanks. Can it be omitted? And if I change the year and click on X to close form instead of clicking on some texbox the question appears but if I answer No the form is being closed anyway. Can it be changed?

    If you do not mind I would like to ask you one more question. If I declare the period (which is then loaded to Combobox) like from 2005 to 2005 so I have only one year. Then I got an error" could not set the listindex property. invalid property value". So it is required for the combobox to have at least 2 elements or what ?

    many thanks

    Zielik

    Wednesday, September 18, 2013 9:04 PM
  • Zielik,

    If you use the next code, then changing the year and then closing the form will not show the question.
    I's this what you want?

    The other thing is, you can now have 1 year in the combobox-list.

    To your first question I do not see an answer at this moment except: no.

    Option Explicit
    Public disableChange As Boolean
    
    Private Sub ComboRok_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim Odpowiedz As Integer
    If disableChange Then
        disableChange = False
    Else
        Odpowiedz = MsgBox("Czy wczytano dane dla biezacego roku ?", vbYesNo + vbQuestion, "Uwaga!")
         If Odpowiedz = 7 Then
            Cancel = True
            SendKeys "{ESC}"
        Else
        Call LoadDataUserForm 'this is uploading userform with data loaded to the sheet so far
    End If
    End If
    End Sub
    
    'loading Combobox with first value when userform is loaded
    
    Private Sub UserForm_Initialize()
    Dim i, iStart, iEnd   As Integer
    iStart = ActiveSheet.Cells(12, 14).Value
    iEnd = CInt(ActiveSheet.Cells(12, 14).End(xlToRight).Value)
    If iEnd > iStart Then
        For i = iStart To iEnd Step 1
            Formularz_Dane.ComboRok.AddItem i
        Next i
    Else
        Formularz_Dane.ComboRok.AddItem iStart
    End If
    
    disableChange = True
    Formularz_Dane.ComboRok.ListIndex = 0
    End Sub
    
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        disableChange = True
    End Sub
    

    Jan
    • Marked as answer by Zielik Wednesday, September 18, 2013 10:11 PM
    Wednesday, September 18, 2013 9:49 PM
  • Jan,

    many thanks for your help. That was really huge.

    regards

    Zielik

    Wednesday, September 18, 2013 10:12 PM