Combobox2 Does not populate data

[hsolanki]

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

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

 

[dmt32]

Quick glance at your code & looks like you have made the common error of renaming an event code to that of your userform

Private Sub UserForm1_Activate()

Regardless of your forms name it MUST always be UserForm

Private Sub UserForm_Activate()

Dave

 

[hsolanki]

Hi Dave many thanks for coming back to me.
i have tried changing it to as advised and it works the combobox2 populate the names however when i tried selecting name comes up with an error 424 Object required.

wonder if you could help me with the code

Thank you

 

[dmt32]

Hi,
suggest you post code & highlight the line that errors.

Dave

 

[hsolanki]

Hi Dave Thank you for quick reply i get an error on the

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

 

[dmt32]

Hi Dave Thank you for quick reply i get an error on the

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

In the UserForm_Activate event, MOVE the declared variable shown in BOLD to the VERY TOP of your forms code page OUTSIDE any procedure & see if this solves your problem

Private Sub UserForm_Activate()
Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Full_IDs")

Dave

 

[hsolanki]

Hi Dave it comes with an error outside procedures

 

[dmt32]

Hi Dave it comes with an error outside procedures

You should have only moved this line from the UserForm_Activate event to the top of the code page

Dim sh As Worksheet

Dave

 

[hsolanki]

Hi Dave I have tried as above and now it comes with an error code 380 Could not set the text properly, invalid property value

 

[dmt32]

Hi Dave I have tried as above and now it comes with an error code 380 Could not set the text properly, invalid property value

That error is unrelated to your last issue as looking at all your code, I suspect you have taken most of it from another project & are trying to adapt it to a new requirement?
If this is so, I suspect that you will keep finding such errors occurring & you would, in my view, do better to start afresh developing code to meet your requirement.

I suspect the latest error is caused by line In the UserForm_Activate event that changes the control style from dropdown combobox to dropdown list

change this line

.Style = fmStyleDropDownList

to this

.Style = fmStyleDropDownCombo

and see if resolves

Dave