• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combo box selection is lost upon initialization by reverting to default value

Y

Yodelayheewho

Member
Hello!
I have a list of products and each is aligned to an SCode.
Combo Box = cboSCode (list of product types, ex: Meter, Read, Beta, etc.)
Textbox = txtSCode (code for each product type, ex: S-13-5, S-13-15, S-13-16, etc.)
I set the combo box with a default value: "Select product type to get S-Code"
The textbox is disabled. The user selects the product type from the combo box, which populates the textbox with the corresponding product type code. Works great!
The issue: If the userform is saved, closed then reopened, the combo box value reverts back to the default value instead of showing the product type. However, the value of the textbox (product type code) is correct. How can I get the combo box value to appear correctly if the textbox is not blank?

Here is the code that's working fine.
Code: 
Private Sub UserForm_Initialize()
cboSCode.List=Sheets("SCODE").Range("A2:A10") '1 of 3 codes

'Populate txtSCode based on cboSCode selection '2 of 3 codes
Private Sub cboSCode_Click()
Me.txtSCode.Value = Sheets("SCODE").Range("B" & Me.cboSCode.ListIndex + 2).Value
End Sub

'Populate txtSCode using typing search in cboSCode '3 of 3 codes
Private Sub cboSCode_Change()
Me.txtSCode.Value = Sheets("SCODE").Range("B" & Me.cboSCode.ListIndex + 2).Value
End Sub

I tried adding the following code to Initialize with no luck.
Code: 
If txtSCode <>"" Then Sheets("SCODE").Range("B" & me.cboSCode.ListIndex+2).Value = me.txtSCode

I would greatly appreciate your help.
 
I hope I'm not keeping someone more knowledgeable from answering because they see my reply, but I do have one idea to offer. I know nothing about combo boxes specifically, but in one workbook I display a user form, and fill its values (checkboxes and radio buttons in my case) from a worksheet named Settings. After the operator makes any changes and clicks Go, the user form saves the latest values back to the Settings worksheet; then the program gets on with the function(s) requested. One of the checkboxes is to save the settings, so if the operator unchecks that box he can have a different run without having to see those options next time.

Normally I would say "well, sure, when you close it and then restart, naturally the form's going to revert to its default starting values". But in this case you say the text box has the correct value, and I presume you mean the value that it had before you closed, not some default starting value. So maybe there's something going on here that I don't understand. One other idea, then: Can your program look at the text box and fill in the combo box based on what it sees in the text box? That's so obvious that I expect you would already have thought if it, but as I said I don't fool with combo boxes much.
 
BobBridges said:
I hope I'm not keeping someone more knowledgeable from answering because they see my reply, but I do have one idea to offer. I know nothing about combo boxes specifically, but in one workbook I display a user form, and fill its values (checkboxes and radio buttons in my case) from a worksheet named Settings. After the operator makes any changes and clicks Go, the user form saves the latest values back to the Settings worksheet; then the program gets on with the function(s) requested. One of the checkboxes is to save the settings, so if the operator unchecks that box he can have a different run without having to see those options next time.

Normally I would say "well, sure, when you close it and then restart, naturally the form's going to revert to its default starting values". But in this case you say the text box has the correct value, and I presume you mean the value that it had before you closed, not some default starting value. So maybe there's something going on here that I don't understand. One other idea, then: Can your program look at the text box and fill in the combo box based on what it sees in the text box? That's so obvious that I expect you would already have thought if it, but as I said I don't fool with combo boxes much.
Click to expand...
99% of the time, the user knows the product type vs the product code. However, it would be a nice feature to be able to make it work the other way too.
 
Yodelayheewho said:
I tried adding the following code to Initialize with no luck.
Click to expand...

A few thoughts:

1. Try using Activate instead of Initialize.

2. Relying on an object's default property as you are doing with txtSCode is a mistake that will bite you one day. Try to get into the habit of always being explicit about the properties, even the default ones. So
Code: 
If me.txtScode.Value <> "" Then Sheets("SCODE").Range("B" & Me.cboSCode.ListIndex + 2).Value = Me.txtScode.Value

3. Lastly, you say you want to update the combo box value to the last selected product code instead of the default value, and you have suggested that you are hoping the above statement will do that. But your statement only modifies cell in col B on sheet SCODE so I don't follow how you expect to modify the combo box.
 
Aside from the two ways I already described? Just to be sure, did you see the two methods I suggested? (Well, the one and a half, because that second one might not work.)
 
BobBridges said:
but in one workbook I display a user form, and fill its values (checkboxes and radio buttons in my case) from a worksheet named Settings. After the operator makes any changes and clicks Go, the user form saves the latest values back to the Settings worksheet; then the program gets on with the function(s) requested. One of the checkboxes is to save the settings, so if the operator unchecks that box he can have a different run without having to see those options next time.
Click to expand...
I'm sorry, but I'm not sure what you're suggesting. The user needs to see the drop-down list of options all the time. I just need the option they selected to remain visible before and after they save their selection.
I've attached an image that may help. The top half shows the product type: "Disk" and the corresponding code: S-13-15. This is what I want to see. However, the bottom half shows after I save, exit and reopen the userform. 'Disk' is replaced with the default prompt.
 

Attachments

  • Image3.png
    Image3.png
    133 KB · Views: 5
rlv01 said:
1. Try using Activate instead of Initialize.
Click to expand...
I'm new and learning, can you explain? Are you suggesting all of the code under Initialize be under Activate instead? I thought I read the opposite - to be careful when using Activate. I would like to understand.
rlv01 said:
2. Relying on an object's default property as you are doing with txtSCode is a mistake that will bite you one day. Try to get into the habit of always being explicit about the properties, even the default ones. So
Click to expand...
I took your advice and removed the default text value from properties. So, now I have no default.
rlv01 said:
3. Lastly, you say you want to update the combo box value to the last selected product code instead of the default value, and you have suggested that you are hoping the above statement will do that. But your statement only modifies cell in col B on sheet SCODE so I don't follow how you expect to modify the combo box.
Click to expand...
I think I know what's wrong.
MASTER worksheet: the data entered and saved by the userform is stored on this worksheet
SCODE worksheet: table listing product (column A) and corresponding codes (column B)

I do not need to track products, only codes. I created the combo box as a kind of cheat sheet, to help the user select the correct code based on the product. Hence, the MASTER worksheet has a column for codes, but not one for products.

Therefore, there is nothing in the code to 'keep' the product selected because I've not 'stored' it on the MASTER worksheet.

So, I assume the only way to get what I want, is to add another column to the MASTER worksheet and update the code accordingly?
 
About riv01's #2 comment: You say you "removed the default text value from properties", and I think you meant you blanked out the Value in the user form's textbox. But I think what he meant is that it's more work, but better programming, to use statement #2 than statement #1:
Code: 
If txtSCode <>"" Then Sheets("SCODE").Range("B" & me.cboSCode.ListIndex+2).Value = me.txtSCode '#1
If txtSCode.Value <>"" Then Sheets("SCODE").Range("B" & me.cboSCode.ListIndex+2).Value = me.txtSCode.Value '#2
I feel the same way as riv01; it's true that I can do something like this:
Code: 
If ocs(1, 1) = "" Then... 'where ocs is a cell reference
...and Excel will know I mean not the cell itself but the cell's Value property (because Value is the "default property" of a Range object). But I prefer to spell it out and not depend on default properties; someday I'll be mistaken about a default property and the program will do something I didn't intend, possibly without telling me so.
 
Ok, now about your 14:52 post. I may have misunderstood what you need. What I think I heard is that when an operator opens the form, your program initializes the values in the drop-down list, but when he closes and re-opens the form those values are lost. So I suggested that you could save the values in the drop-down list in a worksheet (it could be a hidden worksheet so as not to distract the users), and reload the drop-down box each time.

But the fact is I don't yet understand what's going on. I think I should ask for some details. Remember the step-by-step description we went through in the other thread? What exactly happens when you open the user form, and when you re-open it? And what do you mean by "open" and "re-open", exactly?
 
Yodelayheewho said:
The issue: If the userform is saved, closed then reopened, the combo box value reverts back to the default value instead of showing the product type. However, the value of the textbox (product type code) is correct. How can I get the combo box value to appear correctly if the textbox is not blank
Click to expand...

A UserForm is not saved. It is either hidden or unloaded. If there is anything on the UserForm (like text box contents) that you want to save, you have to write code to save it somewhere (like in worksheet cells) before you unload the UserForm. I assume you already have this code, though you have not posted it. If you want a ComboBox to reopen to the last pick, you have to make sure to save the ComboBox's ListIndex property to somewhere then reload it the next time you load the UserForm.

Code: 
'Save Form
Private Sub CommandButton1_Click()
    Worksheets("Sheet1").Range("B2").Value = Me.ComboBox1.ListIndex
    Worksheets("Sheet1").Range("B3").Value = Me.TextBox1.Value
    Unload Me
End Sub
Code: 
Private Sub UserForm_Initialize()
    'Restore TextBox
    Me.TextBox1.Value = Trim(Worksheets("Sheet1").Range("B3").Value)

    If Me.TextBox1.Value = "" Then
        Me.ComboBox1.Value = "Select product type to get S-Code"
    Else
        'Restore ComboBox pick
        Me.ComboBox1.ListIndex = Worksheets("Sheet1").Range("B2").Value
    End If

    'Restore TextBox
    Me.TextBox1.Value = Trim(Worksheets("Sheet1").Range("B3").Value)
End Sub
Code: 
Private Sub ComboBox1_change()
    Me.TextBox1 = Me.ComboBox1.Value
End Sub
 
rlv01 said:
A UserForm is not saved. It is either hidden or unloaded. If there is anything on the UserForm (like text box contents) that you want to save, you have to write code to save it somewhere (like in worksheet cells) before you unload the UserForm. I assume you already have this code, though you have not posted it. If you want a ComboBox to reopen to the last pick, you have to make sure to save the ComboBox's ListIndex property to somewhere then reload it the next time you load the UserForm.
Click to expand...
I think my newness is showing in the vernacular I'm using. The main data worksheet is titled: MASTER and a command button titled: DATA ENTRY FORM. I click the command button to show the userform and fill in the data. The userform contains several command buttons: SEARCH (to find existing orders on the MASTER), ADD NEW (to add new orders to the MASTER), SAVE (updates and saves the data to the MASTER.

I plan to test the code rvl01 has shared above, in the meantime, I've provided my current code with redactions to keep it short.

Thank you both for taking the time to share your knowledge and insight. I love coding and appreciate those that are more experienced.

Code: 
Private Sub UserForm_Initialize()
'redacted code'
    '***ComboBox lists***
'redacted 1-6 ComboBox Lists
cboTE.List = [LISTS!O2:O3].Value
cboTerms.List = [LISTS!Q2:Q54].Value
cboProcess.List = [LISTS!AB2:AB14].Value
cboRecRev.List = [LISTS!AD2:AD13].Value
cboSCode.List = [SCODE!A2:A114].Value 'ComboBox per this topic'

'***Populate txtSCode based on cboSCode selection***1 of 3 codes***
With Sheets("SCODE")
      Me.cboSCode.List = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row).Value
   End With
'redacted code'
End Sub

Code: 
'***Populate txtSCode based on cboSCode selection***2 of 3 codes***
Private Sub cboSCode_Click()
Me.txtSCode.Value = Sheets("SCODE").Range("B" & Me.cboSCode.ListIndex + 2).Value
End Sub

Code: 
'***Populate txtSCode populates based on cboSCode typing search***3 of 3 codes***
Private Sub cboSCode_Change()
Me.txtSCode.Value = Sheets("SCODE").Range("B" & Me.cboSCode.ListIndex + 2).Value
End Sub

Code: 
'Add New Button
Private Sub cmbNew_Click()
On Error Resume Next
'redacted code'
   
    '***ComboBox lists***
'redacted 1-6 ComboBox Lists
cboTE.List = [LISTS!O2:O3].Value
cboTerms.List = [LISTS!Q2:Q54].Value
cboProcess.List = [LISTS!AB2:AB14].Value
cboRecRev.List = [LISTS!AD2:AD13].Value
cboSCode.List = [SCODE!A2:A114].Value 'ComboBox per this topic'
'redacted code'

Dim A As Long
A = Application.WorksheetFunction.CountIf(Range("D:D"), Me.txtShopOrdNum.Text)
If A >= 1 Then
MsgBox "The Shop Order Number Already Exists. Please Enter a Unique Shop Order Number"
Else
lastrow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Master").Cells(lastrow + 1, 1).Value = txtPrefix
'redacted column codes 2-20
Worksheets("Master").Cells(lastrow + 1, 21).Value = txtPO
Worksheets("Master").Cells(lastrow + 1, 22).Value = CDate(Me.txtPODate.Value)
Worksheets("Master").Cells(lastrow + 1, 23).Value = CDate(Me.txtPORecDate.Value)
Worksheets("Master").Cells(lastrow + 1, 24).Value = CDbl(Me.txtPOAmt.Value)
Worksheets("Master").Cells(lastrow + 1, 25).Value = cboTerms
Worksheets("Master").Cells(lastrow + 1, 26).Value = cboShipVia
Worksheets("Master").Cells(lastrow + 1, 27).Value = cboShipType
Worksheets("Master").Cells(lastrow + 1, 28).Value = cboShipChrgs
Worksheets("Master").Cells(lastrow + 1, 29).Value = txtShipInst
Worksheets("Master").Cells(lastrow + 1, 30).Value = txtSO
Worksheets("Master").Cells(lastrow + 1, 31).Value = txtQuote
Worksheets("Master").Cells(lastrow + 1, 32).Value = txtPM
Worksheets("Master").Cells(lastrow + 1, 33).Value = txtEE
Worksheets("Master").Cells(lastrow + 1, 34).Value = txtSys
Worksheets("Master").Cells(lastrow + 1, 35).Value = txtSCode 'TextBox per this topic'
'redacted column codes 36-100
End If
End Sub

Code: 
'Update and Save Button'
Private Sub cmbUpdate_Click()
On Error Resume Next
'redacted code'

    '***ComboBox lists***
'redacted 1-6 ComboBox Lists
cboTE.List = [LISTS!O2:O3].Value
cboTerms.List = [LISTS!Q2:Q54].Value
cboProcess.List = [LISTS!AB2:AB14].Value
cboRecRev.List = [LISTS!AD2:AD13].Value
cboSCode.List = [SCODE!A2:A114].Value 'ComboBox per this topic'
'redacted code'

Dim Shop_Order_Number As String
Dim n As String
n = txtNotes 'Overrides the default limit of characters in a textbox; allows unlimited characters***
Shop_Order_Number = Trim(txtShopOrdNum)
lastrow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Worksheets("Master").Cells(i, 4).Value = Shop_Order_Number Then
Worksheets("Master").Cells(i, 1).Value = txtPrefix
'redacted column codes 2-20
Worksheets("Master").Cells(i, 21).Value = txtPO
Worksheets("Master").Cells(i, 22).Value = Me.txtPODate.Value
Worksheets("Master").Cells(i, 23).Value = Me.txtPORecDate.Value
Worksheets("Master").Cells(i, 24).Value = Me.txtPOAmt.Value
Worksheets("Master").Cells(i, 25).Value = cboTerms
Worksheets("Master").Cells(i, 26).Value = cboShipVia
Worksheets("Master").Cells(i, 27).Value = cboShipType
Worksheets("Master").Cells(i, 28).Value = cboShipChrgs
Worksheets("Master").Cells(i, 29).Value = txtShipInst
Worksheets("Master").Cells(i, 30).Value = txtSO
Worksheets("Master").Cells(i, 31).Value = txtQuote
Worksheets("Master").Cells(i, 32).Value = txtPM
Worksheets("Master").Cells(i, 33).Value = txtEE
Worksheets("Master").Cells(i, 34).Value = txtSys
Worksheets("Master").Cells(i, 35).Value = txtSCode
'redacted column codes 36-100'
End If
Next
ActiveWorkbook.Save
MsgBox ("Your work is saved")
End Sub

Code: 
'Search and Display Button'
Private Sub cmbSearchDisplay_Click()
'redacted code'

Dim Shop_Order_Number As String
Shop_Order_Number = Trim(txtShopOrdNum)
lastrow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Worksheets("Master").Cells(i, 4).Value = Shop_Order_Number Then
txtPrefix = Worksheets("Master").Cells(i, 1).Value
'redacted column codes 2-20
txtPO = Worksheets("Master").Cells(i, 21).Value
txtPODate = Worksheets("Master").Cells(i, 22).Value
txtPORecDate = Worksheets("Master").Cells(i, 23).Value
txtPOAmt = Format(Worksheets("Master").Cells(i, 24).Value, "$#,##0.00")
cboTerms = Worksheets("Master").Cells(i, 25).Value
cboShipVia = Worksheets("Master").Cells(i, 26).Value
cboShipType = Worksheets("Master").Cells(i, 27).Value
cboShipChrgs = Worksheets("Master").Cells(i, 28).Value
txtShipInst = Worksheets("Master").Cells(i, 29).Value
txtSO = Worksheets("Master").Cells(i, 30).Value
txtQuote = Worksheets("Master").Cells(i, 31).Value
txtPM = Worksheets("Master").Cells(i, 32).Value
txtEE = Worksheets("Master").Cells(i, 33).Value
txtSys = Worksheets("Master").Cells(i, 34).Value
txtSCode = Worksheets("Master").Cells(i, 35).Value 'TextBox per this topic
'redacted column codes 36-100'
End If
Next
End Sub
 
I tried the following code and get a runtime error '-2147352571 (80020005)': Could not set the ListIndex property. Type mismatch.
The range for the Products (cboSCode) is on worksheet "SCODE", A2:A114
The range for the Codes (txtSCode) is on worksheet "SCODE", B2:B114
You had "B3", "B2" and "B3". I thought it may have to do with ListIndex starts at '0', but no luck.
When I hover over the error, it says: "Me.cboSCode.ListIndex= -1
I tried to find a solution, but ended back here.

Code: 
Private Sub UserForm_Initialize()
    '***ComboBox lists***
'redacted lists 1-7'
cboTerms.List = [LISTS!Q2:Q54].Value
cboProcess.List = [LISTS!AB2:AB14].Value
cboRecRev.List = [LISTS!AD2:AD13].Value
cboSCode.List = [SCODE!A2:A114].Value

'CHANDOO CODE FOR SCODE
'Restore txtSCode value
Me.txtSCode.Value = Trim(Worksheets("SCODE").Range("B2").Value)
If Me.txtSCode.Value = "" Then
    Me.cboSCode.Value = "Select product to get S-Code"
Else
    'Restore ComboBox selection
    Me.cboSCode.ListIndex = Worksheets("SCODE").Range("A2").Value 'Error line
End If
'Restore txtSCode value
Me.txtSCode.Value = Trim(Worksheets("SCODE").Range("B2").Value)
End Sub
 
Likely the wrong data type.
Code: 
Me.cboSCode.ListIndex = Worksheets("SCODE").Range("A2").Value 'Error line
ListIndex is a 'Long' data type and you are attemping to load a string value, or so I presume since cell A2 is the first value of your list of picks:
Code: 
cboSCode.List = [SCODE!A2:A114].Value

Did you explicitly save the ListIndex value to a cell as I do in my example code above at this line:
Code: 
Worksheets("Sheet1").Range("B2").Value = Me.ComboBox1.ListIndex

You might be better off creating a simplified version of your workbook w/userform using the code you have posted and uploading it here so we can inspect it.
 
You must log in or register to reply here.
Back
Top