Updating Multiple Sheets, Where Control Value Exists

[reberryjr]

I'm trying to update two different sheets, where a value in each sheet matches the value of a textbox. The code below, works fine until I try to incorporate the ws2 stuff highlighted in red. Can someone advise how I need to change the code to accomplish the goal?

Private Sub cmd_Submit_Click()
Application.ScreenUpdating = False
[COLOR=#ff0000]Dim ws2 As Worksheet[/COLOR]
Dim ws3 As Worksheet
[COLOR=#ff0000]Dim FindRow2 As Range[/COLOR]
Dim FindRow3 As Range
[COLOR=#ff0000]Dim UpdateRow2 As Long[/COLOR]
Dim UpdateRow3 As Long
Dim Msg1 As Variant
Dim Response As VbMsgBoxResult
Dim isok As Boolean
    isok = True
[COLOR=#ff0000]Set ws2 = ThisWorkbook.Sheets("Summaries")[/COLOR]
Set ws3 = ThisWorkbook.Sheets("Bios")
[COLOR=#ff0000]With ws2
Set FindRow2 = Range("C:C").Find(What:=Me.txt_ClientID, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If Not FindRow2 Is Nothing Then
    UpdateRow2 = FindRow2.Row
Else
    Exit Sub
End If[/COLOR]
[COLOR=#FF0000]End With[/COLOR]
With ws3
Set FindRow3 = Range("E:E").Find(What:=Me.txt_ClientID, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If Not FindRow3 Is Nothing Then
    UpdateRow3 = FindRow3.Row
Else
    Exit Sub
End If
End With
 
Msg1 = MsgBox("Were there any data entry errors found?", vbYesNo, "Data Entry Validation")
If Msg1 = vbYes Then
    Me.cobo_Nickname.SetFocus
Else
ws3.Range("B" & UpdateRow3).Value = Now()
ws3.Range("C" & UpdateRow3).Value = (Me.cobo_Status)
ws3.Range("J" & UpdateRow3).Value = (Me.cobo_Nickname)
ws3.Range("K" & UpdateRow3).Value = (Me.cobo_Gender)
ws3.Range("L" & UpdateRow3).Value = CDate(Me.txt_DoB)
ws3.Range("M" & UpdateRow3).Value = (Me.txt_SignupAge)
ws3.Range("O" & UpdateRow3).Value = (Me.txt_Phone)
ws3.Range("P" & UpdateRow3).Value = (Me.txt_Email)
ws3.Range("Q" & UpdateRow3).Value = (Me.txt_Street1)
ws3.Range("R" & UpdateRow3).Value = (Me.txt_Street2)
ws3.Range("S" & UpdateRow3).Value = (Me.txt_City)
ws3.Range("T" & UpdateRow3).Value = (Me.cobo_ST)
ws3.Range("U" & UpdateRow3).Value = (Me.txt_Zip)
ws3.Range("V" & UpdateRow3).Value = (Me.cobo_RefCat)
ws3.Range("W" & UpdateRow3).Value = (Me.txt_RefID)
ws3.Range("X" & UpdateRow3).Value = (Me.cobo_RefNickname)
ws3.Range("Z" & UpdateRow3).Value = (Me.txt_Notes)
[COLOR=#ff0000]ws2.Range("B" & UpdateRow2).Value = (Me.cobo_Status)[/COLOR]
End If
Application.ScreenUpdating = True

End Sub

 

[cerfani]

step through the code and explain what is wrong specifically... is there an error? Unexpected result?

 

[reberryjr]

No errors get thrown when I step through the code. I've verified that the ws2 name is spelled correctly, it references the correct column on ws2, and that the value of the control exists in that column. The code seems to be hitting the Exit Sub in the ws2 part, and exiting, but not finding the control value, even though it is present.

 

[cerfani]

you probably are not referencing the ranges properly. For example, you aren't even using the worksheets in the With statements.

Set FindRow2 = .Range("C:C").Find(What:=Me.txt_ClientID, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)

.Range will then use the range on ws2... Range without the '.' is referencing whatever sheet is active

 

[reberryjr]

I have "with ws2" and "with ws3" coded, so I don't think that's it. But, I added the ws2.range and ws3.range, just in case, and the code still isn't functioning as expected.

With ws2
Set FindRow2 = ws2.Range("C:C").Find(What:=Me.txt_ClientID, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If Not FindRow2 Is Nothing Then
    UpdateRow2 = FindRow2.Row
Else
    Exit Sub
End If
End With
With ws3
Set FindRow3 = ws3.Range("E:E").Find(What:=Me.txt_ClientID, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If Not FindRow3 Is Nothing Then
    UpdateRow3 = FindRow3.Row
Else
    Exit Sub
End If
End With

 

[cerfani]

I meant you weren't using the sheet reference in the With function.

You did this...

With ws2
    Range(...)
End With

that may or may not be using ws2 depending on what sheet is active, this would use ws2, regardless what sheet is active...

With ws2
    .Range(...)
End With

if you don't plan to use ws2 in the With function, then you can just omit the With statement. That's what I meant before. I don't know if that was your problem but it seemed like an issue since you weren't using ws2 even though you tried to use With. In your last post with the updated code, you still aren't using the With statement so you may as well delete it, since you are now referencing ws2 and ws3 directly.

 

[reberryjr]

Ahhhhh....now that make sense. I added the Activate coding, and it works.

Thanks for the response!

 

[cerfani]

No prob!