Save ComboBox values when closing workbook

Jeffrey Lebowski · Sep 3, 2013

Hi,

I have a workbook with lots of ComboBoxes. They are all "Yes/No".

When I save & close and then re-open the workbook, the ComboBoxes that previously had "Yes" or "No" values selected are now reset to blank. How can I retain the values in the ComboBoxes that have been changed so that the values are still there when the workbook is re-opened?

If vba is required I will need some sort of loop since there are a lot of these ComboBoxes.

Thanks for your help!

lohithsriram · Sep 4, 2013

Hi Jeffrey,
Its the code in your "ThisWorkbook" module emptying the comboboxes text. Comment out the below line from your code.
'.OLEObjects(i).Object.Text = ""

Here is how it should be on "ThisWorkbook" module.

Dim objControl() As ClsComboBox
Private Sub Workbook_Open()
Dim obj_num As Integer, i As Integer

ThisWorkbook.Worksheets("Input").Activate
With ActiveSheet
obj_num = .OLEObjects.Count
ReDim objControl(1 To obj_num)
For i = 1 To obj_num
'.OLEObjects(i).Object.Text = ""
Set objControl(i) = New ClsComboBox
objControl(i).Init .OLEObjects(i).Object
objControl(i).Name = .OLEObjects(i).Name
Next
End With
End Sub

SirJB7 · Sep 4, 2013

Hi, Jeffrey Lebowski!
Give a look to the attached file, it uses 2 combo boxes (one ActiveX and another Form) and they keep the last selected values, without doing nothing.
Regards!

Jeffrey Lebowski · Sep 5, 2013

Thanks to you both...all I needed to do was remove that one line of code that was in the "ThisWorkbook" module. Now the ComboBox values are retained when closing/re-opening.

So now I have gotten to the point of completion for this file (in terms of vba/functionality). The only downside is it is super slow!! It can take 15 seconds to fully load and also takes pleasure in crashing my computer when I need it most.

Is this due to the fact that there are 456 comboboxes that are tied to the ThisWorkbook Open() event? I would really like to speed up this file so I appreciate any guidance in this regard. Attached is a sample file with all the code included.

https://www.dropbox.com/s/1zwdj7s0obbwiau/slow file demo.xlsm

Cheers!

SirJB7 · Sep 5, 2013

Hi, Jeffrey Lebowski!
In an environment as of the attached file (2 browsers, 15 pages, 5 explorers, 3 skypes, 6 excels, 1 notepad, 1 task manager, 1 help, plus 23 apps in systray, and please don't ask what else is running), it took less than 3 seconds to open the file and get the workbook operational and didn't crash anything: Intel core i7, 6 Gb memory, so check your hardware, maybe trying to use the workbook on other computers.
Regards!

lohithsriram · Sep 5, 2013

Hi Jeffrey,

In first place, I still do not understand the logic behind having so many comboboxes. You could have easily achieved the same by using datavalidation since the values displayed are only "YES" & "NO". It makes a difference in the file size when you have activex objects on the worksheet and also the time consumption while loading the file.

Give a try with few datavalidation cells instead and see if that works out for you.

Jeffrey Lebowski · Sep 5, 2013

Lohith,

I did not think I could perform the same with data validation...I am very interested in pursuing this option now.

Will I need to tie the code to the Worksheet_Change procedure?

Jeffrey Lebowski · Sep 5, 2013

Correct me if I am wrong but I am trying this out in the Worksheet_Change procedure.

I am not sure how to reference the data validation lists so that when "No" is selected in rows 5,10,15...etc Userform2 shows and when "Yes" is selected in rows 7,12,17...etc Userform 1 shows.

I am thinking that once I accomplish this I will be able to copy the row_index and col_index to the userforms so that the userform textbox values are entered into the appropriate cell on the "Notes" sheet.

I would really appreciate some guidance in how to refer to the data validation boxes.

Jeffrey Lebowski · Sep 5, 2013

Lohith (or whomever is gracious enough to help!),

I have taken my first stab at revising the code to work with data validation cells instead of combo boxes. I can tell that the code runs but it fails to show the userform. Please advise as to how to proceed with this revision.

I apologize for posting two big blocks of code, but I wanted to show the original code(used with the 400+ combo boxes) and where I have gotten with the new code (for the data validation).

Here is the code from my first attempt:

Code:

Option Explicit
 
Dim WithEvents objOLEControl As msforms.ComboBox
 
Private CB_Name As String
 
Property Let Name(cboname As String)
            CB_Name = cboname
End Property
 
Property Get Name() As String
            Name = CB_Name
End Property
 
Public Sub Init(oleControl As msforms.ComboBox)
          Set objOLEControl = oleControl
End Sub
 
Private Sub objOLEControl_Change()
            If objOLEControl.Text = "" Then Exit Sub
'            If objOLEControl.Text = "NO" Then Exit Sub
 
            Dim cbo_s As String
            Dim row_num As Integer, col_num As Integer
           
            Application.ScreenUpdating = False
           
'          The following arrays are used to derive the row number and column number
'          of the matrix of combo boxes on the worksheet.
'          Since the UserForm is supposed to appear when the combobox in rows 3 , 6 , 9 , 12 and 15
'          are changed , we use the numbers 1 , 2 , 3 , 4 and 5 for these rows , and 0 for comboxes
'          in all other rows. Thus , to display the UserForm , we check to see whether the row number is not 0.
            Dim lookup_array As Variant, output_array As Variant
            lookup_array = Array(0, 17, 25, 41, 49, 65, 73, 89, 97, 113, 121, 137, 145, 161, 169, _
                185, 193, 209, 217, 233, 241, 257, 265, 281, 289, 305, 313, 329, 337, 353, 361, 377, _
                385, 401, 409, 425)
            output_array = Array(0, 1, 0, 2, 0, 3, 0, 4, 0, 5, 0, 6, 0, 7, 0, 8, 0, 9, 0, 10, 0, _
                11, 0, 12, 0, 13, 0, 14, 0, 15, 0, 16, 0, 17, 0, 18)
           
'          Since the combobox numbering starts from 1 and goes till 456 ,
'          we can have numbers of 1 , 2 or 3 digits.
'          If the number is of 1 digit , we will have ox? as the initial value of cbo_s ;
'          If the number is of 2 digits , we will have x?? as the initial value of cbo_s
'          where ? ranges from 0 through 9
            cbo_s = Right(objOLEControl.Name, 3)
            If Asc(cbo_s) > 57 Then cbo_s = Right(cbo_s, 2)
            If Asc(cbo_s) > 57 Then cbo_s = Right(cbo_s, 1)
           
            row_num = Application.WorksheetFunction.Lookup(Val(cbo_s), lookup_array, output_array)
            col_num = ((Val(cbo_s) - 1) Mod 8) + 2
            If (row_num <> 0 And UCase(objOLEControl.Text) = "YES") Then
'              The row number and column number are passed as parameters to the UserForm procedures.
              With UserForm1
                    .RowIndex = row_num + 1
                    .ColIndex = col_num
                    .Show
              End With

And here is the revised code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
 
'FOR COLUMNS "B" through "I"
'  each time "YES" selected in rows 7,12,17,22,27,32....to 92
'---------------------------->  userform1.show
 
 
Dim row_num As Integer
Dim col_num As Integer
Dim thisRow As Long
 
 
Dim lookup_array As Variant, output_array As Variant
    lookup_array = Array(0, 7, 9, 12, 14, 17, 19, 22, 24, 27, 29, 32, 34, 37, 39, _
    42, 44, 47, 49, 52, 54, 57, 59, 62, 64, 67, 69, 72, 74, 77, 79, 82, 84, 87, 89, 92)
    output_array = Array(0, 1, 0, 2, 0, 3, 0, 4, 0, 5, 0, 6, 0, 7, 0, 8, 0, 9, 0, 10, 0, _
    11, 0, 12, 0, 13, 0, 14, 0, 15, 0, 16, 0, 17, 0, 18)
   
thisRow = Target.Row
   
  row_num = Application.WorksheetFunction.Lookup(thisRow, lookup_array, output_array)
  col_num = (thisRow Mod 8) + 1
 
If (row_num <> 0 And Target.Text = "Yes") Then
    With UserForm1
        .RowIndex = row_num + 1
        .ColIndex = col_num
        .Show
    End With
End If
 
End Sub

Thanks and have a great day!

NARAYANK991 · Sep 6, 2013

Hi Jeff ,

Please clarify one point :

Are you replacing all the 400+ comboboxes , or will you be having some of them ?

If all the comboboxes will be eliminated , then things become simple ; you are dealing with only worksheet cells , and one Worksheet_Change procedure will do the job ; you don't need a class module , and the code will be much shorter.

All that is needed to be done is to see where the cursor is when the DV drop-down selection is done , and based on the row and column number of the active cell , the userform can be displayed ; actions based on clicking of the OK ( SUBMIT ) button on the userform will remain the same , and that part of the code will not need to be changed.

Can you upload your workbook , with probably the initial 5 questions and 120 choices ?

Narayan

Jeffrey Lebowski · Sep 6, 2013

That's correct.

As per lohith's suggestion, all comboboxes are now replaced with vba. Here is a link to the file.

https://www.dropbox.com/s/plxp653t3512cte/slow file demo_v3.xlsm

thanks!

NARAYANK991 · Sep 6, 2013

Hi Jeff ,

Can you please check back after an hour or two ?

Narayan

lohithsriram · Sep 6, 2013

Hi jeffrey,

Replace the below line of code where you compare the target text and the KsTrigger. This should do the comparison correctly and gets into the loop.

If LCase(.Text) = LCase(ksTrigger) Then

Code:

With Target
    If LCase(.Text) = LCase(ksTrigger) Then
   
    lookup_array = Array(0, 7, 9, 12, 14, 17, 19, 22, 24, 27, 29, 32, 34, 37, 39, _
    42, 44, 47, 49, 52, 54, 57, 59, 62, 64, 67, 69, 72, 74, 77, 79, 82, 84, 87, 89, 92)
    output_array = Array(0, 1, 0, 2, 0, 3, 0, 4, 0, 5, 0, 6, 0, 7, 0, 8, 0, 9, 0, 10, 0, _
    11, 0, 12, 0, 13, 0, 14, 0, 15, 0, 16, 0, 17, 0, 18)
   
thisRow = Target.Row
thisCol = Target.Column
 
  row_num = Application.WorksheetFunction.Lookup(thisRow, lookup_array, output_array)
  col_num = thisCol
 
    With UserForm1
        .RowIndex = row_num + 1
        .ColIndex = col_num
        .Show
    End With
    End If
End With

Jeffrey Lebowski · Sep 6, 2013

of course. thanks for taking the time to help

NARAYANK991 · Sep 6, 2013

Hi Jeff ,

You now have 2 userforms ; can you clarify which userform should be displayed under which condition ?

Narayan

Jeffrey Lebowski · Sep 6, 2013

Hi Narayan,

Userform1 is displayed when "Yes" is selected in rows 7,12,17,22...to 92.
Userform2 is displayed when "No" is selected in rows 5,10,15,20...to 90.

Below is the code which Lohith supplied that worked great for a single userform. I have tried to modify it so it accomodates the second userform, but it currently will prompt a userform for any DV selection...even for the DV in rows 4,9,14,19...to 89 (which I do not want to prompt either of the two userforms).

Thank you for your help!

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
 
 
'FOR COLUMNS "B" through "I"
'  each time "YES" selected in rows 7,12,17,22,27,32....to 92
'---------------------------->  userform1.show
 
'  each time "NO" selected in rows 5,10,15,20,25,30....to 90
'---------------------------->  userform2.show
 
Dim row_num As Integer
Dim col_num As Integer
Dim thisRow As Long
Dim thisCol As Long
Dim wsNotes As Worksheet
 
 
Const ksRange = "DataList"
Const ksTrigger = "Yes"
Const ksTriggerNo = "No"
Dim rng As Range
Set wsNotes = Sheets("Notes")
Set rng = Range(ksRange)
 
Dim lookup_array As Variant, output_array As Variant
 
If Application.Intersect(Target, rng) Is Nothing Then GoTo Worksheet_Change_Exit
If Target.Cells.Count > 1 Then GoTo Worksheet_Change_Exit
 
With Target
'  YES answer to Endangered?
    If LCase(.Text) = LCase(ksTrigger) Then
   
    lookup_array = Array(0, 7, 9, 12, 14, 17, 19, 22, 24, 27, 29, 32, 34, 37, 39, _
    42, 44, 47, 49, 52, 54, 57, 59, 62, 64, 67, 69, 72, 74, 77, 79, 82, 84, 87, 89, 92)
    output_array = Array(0, 1, 0, 2, 0, 3, 0, 4, 0, 5, 0, 6, 0, 7, 0, 8, 0, 9, 0, 10, 0, _
    11, 0, 12, 0, 13, 0, 14, 0, 15, 0, 16, 0, 17, 0, 18)
   
thisRow = Target.Row
thisCol = Target.Column
 
  row_num = Application.WorksheetFunction.Lookup(thisRow, lookup_array, output_array)
  col_num = thisCol
 
    With UserForm1
        .RowIndex = row_num + 1
        .ColIndex = col_num
        .Show
    End With
    Exit Sub
    End If
   
'  NO answer to Hunted?
    If LCase(.Text) = LCase(ksTriggerNo) Then
   
    lookup_array = Array(0, 5, 7, 10, 12, 15, 17, 20, 22, 25, 27, 30, 32, 35, 37, _
    40, 42, 45, 47, 50, 52, 55, 57, 60, 62, 65, 67, 70, 72, 75, 77, 80, 82, 85, 87, 90)
    output_array = Array(0, 1, 0, 2, 0, 3, 0, 4, 0, 5, 0, 6, 0, 7, 0, 8, 0, 9, 0, 10, 0, _
    11, 0, 12, 0, 13, 0, 14, 0, 15, 0, 16, 0, 17, 0, 18)
   
thisRow = Target.Row
thisCol = Target.Column
 
  row_num = Application.WorksheetFunction.Lookup(thisRow, lookup_array, output_array)
  col_num = thisCol
 
    With UserForm2
        .RowIndex = (row_num * 5) + 1
        .ColIndex = col_num
        .Show
    End With
    End If
End With
 
Worksheet_Change_Exit:
    Set rng = Nothing
 
End Sub

lohithsriram · Sep 6, 2013

Hi Jeffrey,

Here it is. Give a try and let me know, if you need any further change. I am leaving office now and would be able to check this back at home.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
 
 
'FOR COLUMNS "B" through "I"
'  each time "YES" selected in rows 7,12,17,22,27,32....to 92
'---------------------------->  userform1.show
 
'  each time "NO" selected in rows 5,10,15,20,25,30....to 90
'---------------------------->  userform2.show
 
Dim row_num As Integer
Dim col_num As Integer
Dim thisRow As Long
Dim thisCol As Long
Dim wsNotes As Worksheet
 
 
Const ksRange = "DataList"
Const ksTrigger = "Yes"
Const ksTriggerNo = "No"
Dim rng As Range
Set wsNotes = Sheets("Notes")
Set rng = Range(ksRange)
 
Dim lookup_array As Variant, output_array, Yes_Array, No_Array, Check_Value As Variant
Dim CellValue As String
 
If Application.Intersect(Target, rng) Is Nothing Then GoTo Worksheet_Change_Exit
If Target.Cells.Count > 1 Then GoTo Worksheet_Change_Exit
 
'Check_Row = Split("7, 12, 17, 22, 27, 32, 37, 42, 47, 52, 57, 62, 67, 72, 77, 82, 87, 92,5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 60, 65, 70, 75, 80, 85, 90", ",")
No_Array = Split("5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 60, 65, 70, 75, 80, 85, 90", ",")
Yes_Array = Split("7, 12, 17, 22, 27, 32, 37, 42, 47, 52, 57, 62, 67, 72, 77, 82, 87, 92", ",")
 
 
For Check_Value = LBound(Yes_Array) To UBound(Yes_Array)
    If Target.Row = Yes_Array(Check_Value) Then
        If LCase(Target.Value) = LCase("Yes") Then
        CellValue = "Yes"
        GoTo FormShow
        End If
    End If
Next Check_Value
 
For Check_Value = LBound(No_Array) To UBound(No_Array)
    If Target.Row = No_Array(Check_Value) Then
        If LCase(Target.Value) = LCase("No") Then
            CellValue = "No"
            GoTo FormShow
        End If
    End If
Next Check_Value
 
FormShow:
 
With Target
'  YES answer to Endangered?
    If LCase(CellValue) = LCase(ksTrigger) Then
 
    lookup_array = Array(0, 7, 9, 12, 14, 17, 19, 22, 24, 27, 29, 32, 34, 37, 39, _
    42, 44, 47, 49, 52, 54, 57, 59, 62, 64, 67, 69, 72, 74, 77, 79, 82, 84, 87, 89, 92)
    output_array = Array(0, 1, 0, 2, 0, 3, 0, 4, 0, 5, 0, 6, 0, 7, 0, 8, 0, 9, 0, 10, 0, _
    11, 0, 12, 0, 13, 0, 14, 0, 15, 0, 16, 0, 17, 0, 18)
 
thisRow = Target.Row
thisCol = Target.Column
 
  row_num = Application.WorksheetFunction.Lookup(thisRow, lookup_array, output_array)
  col_num = thisCol
 
    With UserForm1
        .RowIndex = row_num + 1
        .ColIndex = col_num
        .Show
    End With
    Exit Sub
    End If
 
'  NO answer to Hunted?
    If LCase(CellValue) = LCase(ksTriggerNo) Then
 
    lookup_array = Array(0, 5, 7, 10, 12, 15, 17, 20, 22, 25, 27, 30, 32, 35, 37, _
    40, 42, 45, 47, 50, 52, 55, 57, 60, 62, 65, 67, 70, 72, 75, 77, 80, 82, 85, 87, 90)
    output_array = Array(0, 1, 0, 2, 0, 3, 0, 4, 0, 5, 0, 6, 0, 7, 0, 8, 0, 9, 0, 10, 0, _
    11, 0, 12, 0, 13, 0, 14, 0, 15, 0, 16, 0, 17, 0, 18)
 
thisRow = Target.Row
thisCol = Target.Column
 
  row_num = Application.WorksheetFunction.Lookup(thisRow, lookup_array, output_array)
  col_num = thisCol
 
    With UserForm2
        .RowIndex = (row_num * 5) + 1
        .ColIndex = col_num
        .Show
    End With
    End If
End With
 
Worksheet_Change_Exit:
    Set rng = Nothing
 
End Sub

Jeffrey Lebowski · Sep 7, 2013

Nailed it!

Thanks so much to all for helping!! Have a great weekend.

Save ComboBox values when closing workbook

Jeffrey Lebowski

Member

lohithsriram

Active Member

SirJB7

Excel Rōnin

Attachments

Jeffrey Lebowski

Member

SirJB7

Excel Rōnin

Attachments

lohithsriram

Active Member

Jeffrey Lebowski

Member

Jeffrey Lebowski

Member

Jeffrey Lebowski

Member

NARAYANK991

Excel Ninja

Jeffrey Lebowski

Member

NARAYANK991

Excel Ninja

lohithsriram

Active Member

Jeffrey Lebowski

Member

NARAYANK991

Excel Ninja

Jeffrey Lebowski

Member

lohithsriram

Active Member

Jeffrey Lebowski

Member