Combobox2 Does not populate data

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,790
Office Version
  1. 2019
Platform
  1. Windows
Quick glance at your code & looks like you have made the common error of renaming an event code to that of your userform

Rich (BB code):
Private Sub UserForm1_Activate()

Regardless of your forms name it MUST always be UserForm

Rich (BB code):
Private Sub UserForm_Activate()

Dave
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,790
Office Version
  1. 2019
Platform
  1. Windows
Hi,
suggest you post code & highlight the line that errors.

Dave
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi Dave Thank you for quick reply i get an error on the
VBA Code:
If RecordRow > 3 Then Set rng = sh.Cells(RecordRow, 3) Else Set rng = Nothing
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,790
Office Version
  1. 2019
Platform
  1. Windows
Hi Dave Thank you for quick reply i get an error on the
VBA Code:
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

Rich (BB code):
Private Sub UserForm_Activate()
Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Full_IDs")

Dave
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi Dave it comes with an error outside procedures
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,790
Office Version
  1. 2019
Platform
  1. Windows
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

Rich (BB code):
Dim sh As Worksheet

Dave
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,790
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
.Style = fmStyleDropDownList

to this

VBA Code:
.Style = fmStyleDropDownCombo


and see if resolves

Dave
 

Forum statistics

Threads
1,141,204
Messages
5,704,944
Members
421,372
Latest member
Jamie11

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
Top