Combobox2 Does not populate data

H

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
204
Office Version
  1. 2010
Platform
  1. Windows
Hello everyone

Happy B Holiday. I have an data sheet whereby it has a lot information and i have created an userform1 whereby below code i have placed from another sheet that i had however when I tried to play around combobox2 wont shows any data into any the boxes or even nothing on Combobox2 and wondering if someone could kindly help me please.

I Have First Name in Column "C" and Surname in Column "D" which the combobox2 needs to look for and then when select the Name+Surname it will show all the data in to each text boxes which i have

VBA Code: 
Private Sub ComboBox2_Change()

Dim RecordRow As Long

Dim i As Integer

Dim ControlsArr As Variant


Application.EnableCancelKey = xlDisabled




On Error GoTo errHandler:





ControlsArr = Array(Me.TextBox1, Me.ComboBox2, Me.TextBox3, Me.TextBox4, Me.TextBox5, Me.TextBox6, _

Me.ComboBox1, Me.TextBox7, Me.TextBox9, Me.TextBox10, Me.TextBox11, Me.TextBox8)

'get record row

RecordRow = Val(Me.ComboBox2.ListIndex) + 2



If RecordRow > 3 Then Set rng = sh.Cells(RecordRow, 1) Else Set rng = Nothing



For i = 1 To UBound(ControlsArr)

With ControlsArr(i)

If Not rng Is Nothing Then

'populate controls from range

.Text = sh.Cells(rng.Row, i).Text

.Enabled = False

Else

'clear controls

.Text = ""

.Enabled = True

End If

End With

Next i



'enable Update RTW button

With Me.CommandButton1

.Enabled = Not rng Is Nothing And Len(Me.TextBox19.Text) = 0

Me.TextBox8.Enabled = .Enabled

Me.TextBox8.BackColor = RGB(255, 255, 295)

End With



'your name textbox

With Me.TextBox8

If .Enabled Then .SetFocus

End With



'update attendance log button

With Me.CommandButton1

.Enabled = Me.ComboBox2.ListIndex = -1

'refresh button

Me.CommandButton2.Enabled = .Enabled

End With



On Error GoTo 0

Exit Sub

errHandler:

MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _

& Err.Number & vbCrLf & Err.Description & vbCrLf & _

"Please Contact Admin"

End Sub


Private Sub CommandButton1_Click()

Application.EnableCancelKey = xlDisabled

Dim YourName As String



YourName = Me.TextBox8.Text

If YourName = "" Then

MsgBox "Please confirm RTW is completed", vbCritical, "RTW Confirmation"

Exit Sub

End If



On Error GoTo errHandler

sh.Unprotect shPassword



'update column I

With rng.Offset(, 8).Interior

.Pattern = xlNone

.TintAndShade = 0

.PatternTintAndShade = 0

End With



rng.Offset(, 8).Value = TextBox8.Value

rng.Offset(, 9).Value = TextBox9.Value

rng.Offset(, 10).Value = TextBox10.Value

rng.Offset(, 10).Value = TextBox11.Value

rng.Offset(, 10).Value = TextBox12.Value

Me.CommandButton2.Enabled = False

'inform user

MsgBox "Updated Successfully!", vbInformation, "Updated"





sh.Protect shPassword



Unload Me



Worksheets("Full_IDs").Activate

Worksheets("Full_IDs").Cells(1, 3).Select



Application.EnableEvents = True

Application.ScreenUpdating = True



On Error GoTo 0



Exit Sub

errHandler:

MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _

& Err.Number & vbCrLf & Err.Description & vbCrLf & _

"Please Contact Admin"



End Sub



Private Sub CommandButton2_Click()

Application.EnableCancelKey = xlDisabled



On Error GoTo errHandler:



If Me.ComboBox2.ListIndex = -1 Then

MsgBox "There is no data to reset", vbExclamation, "Reset Form"

Exit Sub

End If



Unload Me

UserForm1.Show



On Error GoTo 0

Exit Sub

errHandler:

MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _

& Err.Number & vbCrLf & Err.Description & vbCrLf & _

"Please Contact Admin"

End Sub


Private Sub UserForm1_Activate()



Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Full_IDs")



On Error GoTo errHandler:



Application.ScreenUpdating = False

Application.EnableEvents = False



'staff name

With Me.ComboBox2

.ColumnCount = 2

.List = sh.Range(sh.Range("D3"), sh.Range("E" & sh.Rows.Count).End(xlUp)).Value

.Font.Size = 11

.Height = 26

.Width = 160

.Left = 400

.Top = 216

.TextColumn = 2

.Style = fmStyleDropDownList

End With



'your name

With Me.TextBox8

.Height = Me.ComboBox2.Height

.Width = 114

.Left = 550

.Top = Me.ComboBox2.Top

End With

End Sub

Userform1.jpg
 
Sort by date Sort by votes
Hi Dave Sorry i only have very basic knowledge of code and not an expert. i have also tried above now i am getting an same error as previously

www.mrexcel.com

Combobox2 Does not populate data

Hello everyone Happy B Holiday. I have an data sheet whereby it has a lot information and i have created an userform1 whereby below code i have placed from another sheet that i had however when I tried to play around combobox2 wont shows any data into any the boxes or even nothing on Combobox2...
www.mrexcel.com www.mrexcel.com
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
I suggest you place your workbook with dummy data in a file sharing site like dropbox & provide a link to it here.
My time this week is somewhat limited but maybe another here can have a look at what you are trying to do.

Dave
 
Upvote 0
Hi,

Found a moment to make a vein attempt to update your project – as said earlier, it looks like you had taken most of the codes from another project & tried to adapt it for this new requirement. Drawback of doing this is working out what it is you want it to do verses what the code was doing.

I concluded that you were simply trying to return a record to the form based on combobox2 selection, edit it & return it to the database? Hopefully, the corrected codes which also now include ability to add new record will now do what you want. I have included some commentary in various codes which should guide you.

You can download the updated file here: Sample ID Pass v4.xlsm



Hope what I have done enables you to progress with your project as I now must get on building my granddaughters rabbit hutch.



Good luck



Dave
 
Upvote 0
Hi Dave

after playing around with the code below I have managed to get it sort of working and all text boxes populates with the text. however I am stuck now as I need all the text boxes to be enabled to be edit the each text boxes and update the row accordingly of text boxes of each row names as per the selected the Names which is in column D and E. (but I would like to disable the permanent 3 text boxes Not be able to Edit and only visible the information on text boxes 3, 4 & 7 and rest are to be editable)

i hope i am making a sense in here

would be great full if you could kindly help me with the code

VBA Code: 
Dim sh As Worksheet
Private Sub ComboBox2_Change()
Dim RecordRow   As Long
    Dim i           As Integer
    Dim ControlsArr As Variant
   
   Application.EnableCancelKey = xlDisabled
   

   On Error GoTo errHandler:
   
   'Me.Label72.Visible = False
    ControlsArr = Array(Me.ComboBox1, Me.TextBox15, Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.TextBox4, Me.TextBox5, _
                        Me.TextBox6, Me.TextBox14, Me.TextBox7, Me.TextBox8, Me.TextBox9, Me.TextBox17, Me.TextBox16, Me.TextBox10, Me.TextBox11, _
                        Me.TextBox12, Me.TextBox13)


    
    'get record row
    RecordRow = Val(Me.ComboBox2.ListIndex) + 3
   
    If RecordRow > 3 Then Set rng = sh.Cells(RecordRow, 3) Else Set rng = Nothing
   
    For i = 1 To UBound(ControlsArr)
        With ControlsArr(i)
            If Not rng Is Nothing Then
            'populate controls from range
                .Text = sh.Cells(rng.Row, i).Text
                .Enabled = False
            Else
            'clear controls
                .Text = ""
                .Enabled = True
            End If
         End With
    Next i

    'enable Update RTW button
    With Me.CommandButton1
        .Enabled = Not rng Is Nothing And Len(Me.TextBox18.Text) = 0
        Me.TextBox18.Enabled = .Enabled
        Me.TextBox18.BackColor = RGB(255, 255, 295)
    End With
   
    'your name textbox
    With Me.TextBox18
        If .Enabled Then .SetFocus
    End With
   
    'update attendance log button
    With Me.CommandButton1
        .Enabled = Me.ComboBox2.ListIndex = -1
        'refresh button
        Me.CommandButton2.Enabled = .Enabled
    End With
    
        On Error GoTo 0
    Exit Sub
errHandler:
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please Contact Admin"
End Sub






Private Sub CommandButton1_Click()
Application.EnableCancelKey = xlDisabled
    Dim YourName As String
   
    YourName = Me.TextBox18.Text
    If YourName = "" Then
        MsgBox "Please confirm RTW is completed", vbCritical, "RTW Confirmation"
        Exit Sub
    End If
   
    On Error GoTo errHandler
    sh.Unprotect shPassword
  
    'update column I
     With rng.Offset(, 8).Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
        End With

    rng.Offset(, 8).Value = TextBox18.Value
    rng.Offset(, 9).Value = TextBox9.Value
    rng.Offset(, 10).Value = TextBox10.Value
    rng.Offset(, 10).Value = TextBox11.Value
    rng.Offset(, 10).Value = TextBox12.Value
    Me.CommandButton2.Enabled = False
    'inform user
   MsgBox "Updated Successfully!", vbInformation, "Updated"
   

    sh.Protect shPassword
    
    Unload Me

Worksheets("Full_IDs").Activate
Worksheets("Full_IDs").Cells(1, 3).Select

Application.EnableEvents = True
Application.ScreenUpdating = True

    On Error GoTo 0
    
    Exit Sub
errHandler:
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please Contact Admin"

End Sub

Private Sub CommandButton2_Click()
Application.EnableCancelKey = xlDisabled

On Error GoTo errHandler:

 If Me.ComboBox2.ListIndex = -1 Then
        MsgBox "There is no data to reset", vbExclamation, "Reset Form"
        Exit Sub
    End If
    
    Unload Me
    UserForm1.Show

 On Error GoTo 0
    Exit Sub
errHandler:
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please Contact Admin"

End Sub


Private Sub UserForm_Initialize()
Set sh = ThisWorkbook.Sheets("Full_Airside_IDs")

 
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    'staff name
     With Me.ComboBox2
        .ColumnCount = 3
        .List = sh.Range(sh.Range("D3"), sh.Range("E" & sh.Rows.Count).End(xlUp)).Value
        .Font.Size = 11
        .Height = 26
        .Width = 160
        .Left = 400
        .Top = 216
        .TextColumn = 1
        '.Style = fmStyleDropDownCombo
        .Style = fmStyleDropDownList
     End With
    
     'your name
     With Me.TextBox18
        .Height = Me.ComboBox2.Height
        .Width = 114
        .Left = 550
        .Top = Me.ComboBox2.Top
    End With
End Sub
 
Upvote 0
dmt32 said:
Hi,

Found a moment to make a vein attempt to update your project – as said earlier, it looks like you had taken most of the codes from another project & tried to adapt it for this new requirement. Drawback of doing this is working out what it is you want it to do verses what the code was doing.

I concluded that you were simply trying to return a record to the form based on combobox2 selection, edit it & return it to the database? Hopefully, the corrected codes which also now include ability to add new record will now do what you want. I have included some commentary in various codes which should guide you.

You can download the updated file here: Sample ID Pass v4.xlsm



Hope what I have done enables you to progress with your project as I now must get on building my granddaughters rabbit hutch.



Good luck



Dave
Click to expand...
Hi Dave Sorry i didn't see the new post. this is really good and what i was looking for just a small question how can i disable the Textboxes 3, 4 & 7 permanent disable from when Updating the data from editing as these 3 boxes information will never change. really sorry.

oh that great and good luck with the rabit Hutch :)

Thank you Once again :)
 
Last edited:
Upvote 0
hsolanki said:
Hi Dave Sorry i didn't see the new post. this is really good and what i was looking for just a small question how can i disable the Textboxes 3, 4 & 7 permanent disable from editing as these 3 boxes information will never change. really sorry.

oh that great and good luck with the rabit Hutch :)
Click to expand...

replace this section of code in the UserForm_Intialize event

VBA Code: 
'--------------------------------------------------------------------------------------
'                  set tab order for textbox & combobo controls
'--------------------------------------------------------------------------------------
    arr = ControlsArr(Me)
    For i = 1 To UBound(arr)
        arr(i).TabIndex = i - 1
      Select Case i
        Case 3, 4, 7
         arr(i).Locked = True
      End Select
    Next i

Dave
 
Upvote 0
Sorry, ignore last post as did that in haste

Replace ComboBox2 code with updated version below

VBA Code: 
Private Sub ComboBox2_Change()
    Dim RecordRow       As Long
    Dim i               As Integer
    Dim Celltext        As String
    Dim arr             As Variant
   
    On Error GoTo errHandler:
    If EventsOff Then Exit Sub
   
    'get record row
    RecordRow = Val(Me.ComboBox2.ListIndex + 3)
   
    If RecordRow > 2 Then Set Rng = sh.Cells(RecordRow, 1) Else Set Rng = Nothing
   
    EventsOff = Rng Is Nothing
    UpdateRecord = Not Rng Is Nothing
    arr = ControlsArr(Me)
   
    For i = 1 To UBound(arr)
        With arr(i)
            If Not Rng Is Nothing Then
                Celltext = sh.Cells(Rng.Row, i).Text
                'populate controls from range
                .Text = Celltext
                Select Case i
                    Case 3, 4, 8
                        .Locked = True
                End Select
            Else
                'clear controls
                .Value = ""
                .Locked = False
            End If
        End With
    Next i
   
errHandler:
    EventsOff = False
    If Err <> 0 Then
        MsgBox "An Error has Occurred  " & vbCrLf & "The Error number is:  " _
             & Err.Number & vbCrLf & Err.Description & vbCrLf & _
               "Please Contact Admin"
    End If
End Sub

It slipped my mind that there is a combobox in the array & if want to submit new record, controls will need to be unlocked

Dave
 
Upvote 0
Solution
Hi Dave

Thank you so much once again, I really appreciate for your time and kindness for putting code together. you are a ⭐:):giggle:
 
Upvote 0
You must log in or register to reply here.

Similar threads

KlausW
  • Solved
Take fil name from cell
Replies
4
Views
246
KlausW
KlausW
G
  • Solved
VBA to export .xlsb to .xlsx, values only (no formulas!)
Replies
8
Views
110
GVR16
G
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
191
krunal123
K
O
  • Question Question
Frustrating intermittent VBA error dialog telling me that "User-define Type Not Defined"
Replies
1
Views
241
Micron
M
S
  • Question Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
539
sspatriots
S

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top