Userform Combobox unable to display data

[FaridWahidi]

Hi everyone,

I have created data entry form using UserForm multiple page consists of several pages. All functions working fine except unable to view data for Combobox. It has value starting from 1.0, 1.5, 2.0, 2.5,.... until 5.0.

By entering AA number (e.g. 2015777889 ) and click on "view" button, other comboboxes working fine except the one highlighted in red font.

here is my attachment.
https://app.box.com/s/0rfxfw62bdpc9ubbs005n8zfffag7r90

Private Sub CommandButton_View_Click()  'view button, click it after AA number entered
    Dim answer       As Integer
    Dim s            As String
    Dim firstAddress As String


      ' Do Not accept one or more spaces as a valid input.
        s = Trim(Me.TextBox_AAnumber.Value)
        
        If s = "" Then MsgBox "Please enter AA number", 48, "Borrower name is blank": Exit Sub


        With wsScore.Rows(9).Cells
            Set r = .find(s, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByColumns)
    
            If Not r Is Nothing Then
                firstAddress = r.Address
                
                Do
                    If Len(Trim(s)) <> 10 Then
                        MsgBox "AA number must be in 10 characters", vbExclamation, "Standard Characters Requirement"
                        Exit Sub
                    End If
                    
                    answer = MsgBox("Is it AA number you are looking for?" & vbLf & vbLf & _
                            "AA Number:" & vbTab & vbTab & r.Value & vbLf & vbLf & _
                            "Credit Personnel Name:" & vbTab & r.Offset(-5, 0).Value & vbLf & vbLf & _
                            "YES:  To Confirm" & vbTab & "NO:  To Find Next", _
                            vbYesNo + vbQuestion, "Searching AA record")
                            
                    If answer = vbYes Then
                        Call FillForm: Exit Sub
                    End If
        
                    Set r = .FindNext(r)
                Loop While r.Address <> firstAddress
            End If
        End With
     
        MsgBox """" & s & """" & vbLf & vbLf & "AA number you have entered not found", 64, "Please verify AA number"
End Sub

Sub FillForm()

On Error Resume Next
    Me.ComboBox_ScorecardName.Value = wsScore.Cells(4, r.Column).Value
    Me.TextBox_BorrowerName.Value = wsScore.Cells(5, r.Column).Value
    Me.ComboBox_Nob.Value = wsScore.Cells(6, r.Column).Value
[COLOR=#ff0000]    Me.ComboBox_Brr.Value = wsScore.Cells(7, r.Column).Value[/COLOR]
    Me.ComboBox_Frr.Value = wsScore.Cells(8, r.Column).Value
    Me.TextBox_AAnumber.Value = wsScore.Cells(9, r.Column).Value
    Me.ComboBox_NoApplication.Value = wsScore.Cells(10, r.Column).Value
    Me.ComboBox_AACategory.Value = wsScore.Cells(11, r.Column).Value
    Me.ComboBox_AppLevel.Value = wsScore.Cells(12, r.Column).Value
    Me.ComboBox_RiskAssessment.Value = wsScore.Cells(13, r.Column).Value
    Me.ComboBox_Month.Value = wsScore.Cells(14, r.Column).Value




    Me.ComboBox_YorN_1.Value = wsScore.Cells(18, r.Column).Value
    Me.ComboBox_YorN_2.Value = wsScore.Cells(19, r.Column).Value
    Me.ComboBox_YorN_3.Value = wsScore.Cells(20, r.Column).Value
    Me.ComboBox_YorN_4.Value = wsScore.Cells(21, r.Column).Value
    Me.ComboBox_YorN_5.Value = wsScore.Cells(25, r.Column).Value
    Me.ComboBox_YorN_6.Value = wsScore.Cells(49, r.Column).Value
    Me.ComboBox_YorN_7.Value = wsScore.Cells(144, r.Column).Value
    Me.ComboBox_YorN_8.Value = wsScore.Cells(145, r.Column).Value


    If wsScore.Cells(145, r.Column).Value = "NA" Then CheckBox8.Value = True
    Me.ComboBox_YorN_9.Value = wsScore.Cells(146, r.Column).Value


[COLOR=#ff0000]    Me.ComboBox1.Value = wsScore.Cells(22, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox2.Value = wsScore.Cells(23, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox3.Value = wsScore.Cells(24, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox4.Value = wsScore.Cells(26, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox5.Value = wsScore.Cells(27, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox6.Value = wsScore.Cells(28, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox7.Value = wsScore.Cells(29, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox8.Value = wsScore.Cells(30, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox9.Value = wsScore.Cells(31, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox10.Value = wsScore.Cells(32, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox11.Value = wsScore.Cells(33, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox12.Value = wsScore.Cells(34, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox13.Value = wsScore.Cells(35, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox14.Value = wsScore.Cells(37, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox15.Value = wsScore.Cells(38, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox16.Value = wsScore.Cells(39, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox17.Value = wsScore.Cells(40, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox18.Value = wsScore.Cells(42, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox19.Value = wsScore.Cells(43, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox20.Value = wsScore.Cells(44, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox21.Value = wsScore.Cells(45, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox22.Value = wsScore.Cells(46, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox23.Value = wsScore.Cells(47, r.Column).Value[/COLOR]
[COLOR=#ff0000]    Me.ComboBox24.Value = wsScore.Cells(48, r.Column).Value   [/COLOR]
On Error GoTo 0
End Sub

 

[My Aswer Is This]

Wow.

Normally a combobox would be loaded with a range of values and then you choose the value you want from the combobox.

Why would you want to have 25 or more comboxes loaded with only one value?

 

[FaridWahidi]

Hi,

This is a apart of my entry form function for user to view data then make amendment if required.

please view my attachment for clear understanding

https://app.box.com/s/exvm92tnjwsvj4iqgq3d5f78zuvpe93a
https://app.box.com/files/0/f/0/1/f_73829299125

 

[Worf]

- I removed the on error resume next statement to find out what lines were causing errors.
- You should format all cells on the scorecard and data sheets as text, even the ones with numbers in them.
- It may be necessary to retype some numbers, so that you get a green marker on the cell’s top left corner, stating that the number is formatted as text.
- If needed, I can post a link to my test workbook.

 

[FaridWahidi]

Hi Worf,




Thanks a lot. I have changed formatting as a text then retype the drop drown list value. It working fine finally.

I'm not thinking that it is very simple problem. over the year I have improved my skills a lot. Thank you again.

 

[Worf]

You are welcome.