Pulling Values from Multiple Sheets Based on ComboBox Changes

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
714
Office Version
  1. 365
Platform
  1. Windows
I'm thoroughly confused as to why this code works sometimes, but not others.

What I'm trying to do:
The first code has the User select a value from cobo_Nickname (this is working). The code then searches a particular worksheet for that value, and populates the txt_ClientID value, that is in that row. Sometimes this works and sometimes it doesn't and I'm not sure why. The second code reads the value of the txt_ClientID and accesses the worksheet with the same name as that value. It then finds the first instance of "Late" in column CE, and returns various values from that row. This part isn't working at all.

This is particularly concerning because the vast majority of the application I'm building, uses this type of practice. What I don't understand is why the first code works some of the time and not others.


Code:
Private Sub cobo_Nickname_Change()
Dim ws3 As Worksheet
Dim FindRow As Range
Dim Nick As Long
Set ws3 = ThisWorkbook.Sheets("Bios")
With ws3
'Finds the row where the value of the cobo_RefNickame exists, and populates the RefID combobox with the value of the Client ID, from the same row.
Set FindRow = Range("J:J").Find(What:=Me.cobo_Nickname, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If Not FindRow Is Nothing Then
    Nick = FindRow.Row
Else
    Exit Sub
End If
End With
Me.txt_ClientID = ws3.Range("E" & Nick).Value
End Sub

Private Sub txt_ClientID_Change()
Dim Sht As String
Dim LastRow As Long
Dim UpdateRow As Long
Dim FindRow As Range
Sht = Me.txt_ClientID
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "CE").End(xlUp).Row
    Set FindRow = Range("CE:CE").Find(What:="Late", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
    If Not FindRow Is Nothing Then
        UpdateRow = FindRow.Row
    End If
End With
On Error Resume Next
Me.txt_Name = Sheets(Sht).Range("E" & UpdateRow).Value
'Me.cobo_Nickname = Sheets(Sht).Range("F" & UpdateRow).Value
Me.txt_DPStatus = Sheets(Sht).Range("G" & UpdateRow).Value
Me.txt_DPDelq = Sheets(Sht).Range("L" & UpdateRow).Value
Me.txt_TPStatus = Sheets(Sht).Range("M" & UpdateRow).Value
Me.txt_TPDelq = Sheets(Sht).Range("R" & UpdateRow).Value
Me.txt_TRStatus = Sheets(Sht).Range("S" & UpdateRow).Value
Me.txt_TRNextDue = Sheets(Sht).Range("Y" & UpdateRow).Value
Me.txt_TRDelq = Sheets(Sht).Range("AB" & UpdateRow).Value
Me.txt_DCNextDue = Sheets(Sht).Range("AJ" & UpdateRow).Value
Me.txt_DCPymtStatus = Sheets(Sht).Range("AK" & UpdateRow).Value
Me.txt_DCDelq = Sheets(Sht).Range("AM" & UpdateRow).Value
Me.txt_OCNextDue = Sheets(Sht).Range("AU" & UpdateRow).Value
Me.txt_OCPymtStatus = Sheets(Sht).Range("AV" & UpdateRow).Value
Me.txt_OCDelq = Sheets(Sht).Range("AX" & UpdateRow).Value
Me.txt_CTINextDue = Sheets(Sht).Range("BF" & UpdateRow).Value
Me.txt_CTIPymtStatus = Sheets(Sht).Range("BG" & UpdateRow).Value
Me.txt_CTIDelq = Sheets(Sht).Range("BI" & UpdateRow).Value
Me.txt_CTONextDue = Sheets(Sht).Range("BQ" & UpdateRow).Value
Me.txt_CTOPymtStatus = Sheets(Sht).Range("BR" & UpdateRow).Value
Me.txt_CTODelq = Sheets(Sht).Range("BT" & UpdateRow).Value
Me.txt_TotalDue = Format(Sheets(Sht).Range("BV" & UpdateRow).Value, "#0.00")
Me.txt_Wk1 = Format(Sheets(Sht).Range("BW" & UpdateRow).Value, "#0.00")
Me.txt_Wk2 = Format(Sheets(Sht).Range("BX" & UpdateRow).Value, "#0.00")
Me.txt_Wk3 = Format(Sheets(Sht).Range("BY" & UpdateRow).Value, "#0.00")
Me.txt_Wk4 = Format(Sheets(Sht).Range("BZ" & UpdateRow).Value, "#0.00")
Me.txt_Wk5 = Format(Sheets(Sht).Range("CA" & UpdateRow).Value, "#0.00")
Me.txt_FundsRcvdToDate = Format(Sheets(Sht).Range("CB" & UpdateRow).Value, "#0.00")
Me.txt_InReserve = Format(Sheets(Sht).Range("CC" & UpdateRow).Value, "#0.00")
Me.txt_NetDue = Format(Sheets(Sht).Range("CD" & UpdateRow).Value, "#0.00")
End Sub
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
I think that I may have figured out the issue. I wasn't actually activating the worksheet, but was saying "With ActiveSheet". Hopefully the fix sticks, as I add more data to test it further.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

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