Worksheet focus change issues with userform(s)

[oldman]

This has perplexed me for the past year. I have a search userform with a textbox designated for searching a worksheet "combined" for a specific value. This search userform is called from the primary worksheet "sheet2" via another userform.

Once the value is found, the other textboxes in the search userform populate with data adjacent to the found value. This executes with no issue.

I need to maintain focus on "sheet2" but worksheet "combined" takes the focus.

Private Sub CmdSearch_Click()
Set ws = Worksheets("Combined")
Dim Nullstring
Application.ScreenUpdating = False

    If mytext.Value = "" Or Nullstring Then
        MsgBox "Please enter a ticket number"
    mytext.SetFocus
    Exit Sub
    
    End If
    
    Dim strFind
    Dim rSearch As Range  'range to search
    Set rSearch = ws.Range("D:D")
    Dim c
    strFind = mytext.Value
    If strFind = Nullstring Then GoTo error1
    
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues, MatchCase:=True)
        If Not c Is Nothing Then    'found it
        MsgBox strFind & " FOUND", vbOKOnly
 
            c.Select
           
    With Me
    
        .mytext.Value = ActiveCell.Value
        .txtsdm.Value = ActiveCell.Offset(0, -3).Value
        .txtsdd.Value = ActiveCell.Offset(0, -2).Value
        .txtsdy.Value = ActiveCell.Offset(0, -1).Value
        .mytext.Value = ActiveCell.Offset(0, 0).Value
        .txtdpm.Value = ActiveCell.Offset(0, 1).Value
        .txtdpd.Value = ActiveCell.Offset(0, 2).Value
        .txtdpy.Value = ActiveCell.Offset(0, 3).Value
        .txtnet.Value = ActiveCell.Offset(0, 4).Value
        .txtmar.Value = ActiveCell.Offset(0, 5).Value
        .txtgross.Value = ActiveCell.Offset(0, 6).Value
        .txtpab.Value = ActiveCell.Offset(0, 7).Value
        .txtadj.Value = ActiveCell.Offset(0, 8).Value
        .txtdel.Value = ActiveCell.Offset(0, 9).Value
        .txtint.Value = ActiveCell.Offset(0, 10).Value
        .txtfin.Value = ActiveCell.Offset(0, 11).Value
        
    End With
'cmdedit.Visible = True
'cmdupdate.Visible = True
'cmddelete.Visible = True
 Else
        
            MsgBox "NO EXACT MATCH WAS FOUND! PLEASE TRY AGAIN"
End If
End With
error1: mytext.Value = ""

End Sub

[Tommy]

Hi oldman,

I think the below modified code will help you out. The variable c does not need to be selected before use.

Private Sub CmdSearch_Click()    Set Ws = Worksheets("Combined")
    Dim Nullstring
    Application.ScreenUpdating = False
     
    If mytext.Value = "" Or Nullstring Then
        MsgBox "Please enter a ticket number"
        mytext.SetFocus
        Exit Sub
         
    End If
     
    Dim strFind
    Dim rSearch As Range 'range to search
    Set rSearch = Ws.Range("D:D")
    Dim c
    strFind = mytext.Value
    If strFind = Nullstring Then GoTo error1
     
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues, MatchCase:=True)
        If Not c Is Nothing Then 'found it
            MsgBox strFind & " FOUND", vbOKOnly
             
            'c.Select
             
            With Me
                 
                .mytext.Value = c.Value
                .txtsdm.Value = c.Offset(0, -3).Value
                .txtsdd.Value = c.Offset(0, -2).Value
                .txtsdy.Value = c.Offset(0, -1).Value
                .mytext.Value = c.Offset(0, 0).Value
                .txtdpm.Value = c.Offset(0, 1).Value
                .txtdpd.Value = c.Offset(0, 2).Value
                .txtdpy.Value = c.Offset(0, 3).Value
                .txtnet.Value = c.Offset(0, 4).Value
                .txtmar.Value = c.Offset(0, 5).Value
                .txtgross.Value = c.Offset(0, 6).Value
                .txtpab.Value = c.Offset(0, 7).Value
                .txtadj.Value = c.Offset(0, 8).Value
                .txtdel.Value = c.Offset(0, 9).Value
                .txtint.Value = c.Offset(0, 10).Value
                .txtfin.Value = c.Offset(0, 11).Value
                 
            End With
             'cmdedit.Visible = True
             'cmdupdate.Visible = True
             'cmddelete.Visible = True
        Else
             
            MsgBox "NO EXACT MATCH WAS FOUND! PLEASE TRY AGAIN"
        End If
    End With
error1:     mytext.Value = ""
     
End Sub

[oldman]

One more question please?Is there a method to reduce the reptitious recital of:.mytext.Value = c.Value .txtsdm.Value = c.Offset(0, -3).Value .txtsdd.Value = c.Offset(0, -2).Value Etc. . . .

[oldman]

Outstanding!!!!!!!!!!!!!!

[Tommy]

In reference to the other question:

You can leave out the .value as this is the default, as in

                .mytext = c                 .txtsdm = c.Offset(0, -3) 
                .txtsdd = c.Offset(0, -2) 
                .txtsdy = c.Offset(0, -1) 
                .mytext = c.Offset(0, 0) 
                .txtdpm = c.Offset(0, 1) 
                .txtdpd = c.Offset(0, 2) 
                .txtdpy = c.Offset(0, 3) 
                .txtnet = c.Offset(0, 4) 
                .txtmar = c.Offset(0, 5) 
                .txtgross = c.Offset(0, 6) 
                .txtpab = c.Offset(0, 7) 
                .txtadj = c.Offset(0, 8) 
                .txtdel = c.Offset(0, 9) 
                .txtint = c.Offset(0, 10) 
                .txtfin = c.Offset(0, 11)

But this is not any quicker or easier.

[oldman]

Thank you

[SamT]

Sub Form_Initialize()
With Inputs
.Add Controls(mytext)
.Add Controls(txtsdm)
.Add Controls(txtsdd)
.Add Controls(txtsdy)
.Add Controls(mytext)
.Add Controls(txtdpm)
.Add Controls(txtdpd)
.Add Controls(txtdpy)
.Add Controls(txtnet)
.Add Controls(txtmar)
.Add Controls(txtgro)
.Add Controls(txtpab)
.Add Controls(txtadj)
.Add Controls(txtdel)
.Add Controls(txtint)
.Add Controls(txtfin)
End With
End Sub


Sub Modifications_and_Additions()
Dim Inputs As New Collection
Dim II As Long 'Inputs Index number

 With rSearch 
        Set c = .Find(strFind, LookIn:=xlValues, MatchCase:=True).Row 
        If Not c Is Nothing Then 'found it
            MsgBox strFind & " FOUND", vbOKOnly 
    

For II = 1 to Inputs.Count
   Inputs(II) = WS.Cells(c, II)
Next
'
'
'
End Sub

[oldman]

Sam:

What you suggest replaces what I originally sumbitted? If so, can you give me an explanation?

Lastly, I appreciate the advise to read the Forum FAQ but how does it apply to my post?

[SamT]

read the Forum FAQ is part of my Sig, everybody sees it.

I totally blew that code. See if you understand this version. It loops thru the Inputs Collection to retrieve all corresponding columns' values.

Option Explicit

''''' UserForm Declarations:

' A collection for input controls
Dim Inputs As New Collection
    
''''' Add sub or Edit original
Sub UserForm_Initialize()

    'Fill the input control collection in the order of the columns the recieve their data
    'should go into.
    With Inputs
        .Add Controls(mytext)
        .Add Controls(txtsdm)
        .Add Controls(txtsdd)
        .Add Controls(txtsdy)
        .Add Controls(mytext)
        .Add Controls(txtdpm)
        .Add Controls(txtdpd)
        .Add Controls(txtdpy)
        .Add Controls(txtnet)
        .Add Controls(txtmar)
        .Add Controls(txtgro)
        .Add Controls(txtpab)
        .Add Controls(txtadj)
        .Add Controls(txtdel)
        .Add Controls(txtint)
        .Add Controls(txtfin)
    End With
End Sub
 
''''' Edit Sub
Sub Modifications_and_Additions()
    Dim i As Long 'Inputs Index number
    Dim C As Variant 'A range and/or a row number as needed
     
    With rSearch
    'C will be used as row number when saving data from Inputs collection Controls.
    Set C = .Find(strFind, LookIn:=xlValues, MatchCase:=True) 'C is a Range
        If Not C Is Nothing Then 'found it
        'Add line
            MsgBox strFind & " FOUND", vbOKOnly
        C = C.Row 'C is now a number
             
            'Replace original lines from "With Me" to "End With" inclusive
            
            'Assumes that the first data column is "A"
            For i = 1 To Inputs.Count
                Inputs(i).Value = WS.Cells(C, i).Value  'Adjust Cells' "i" with  "i + n" as needed to conform to Form Controls' sheet columns
                'where "n" is difference between col"A" and first datacolumn
            Next 'Old "End With" was here
            
             '
             '
             '
        End Sub

[oldman]

Now I understand. After the userform populated with the found data and I make a change to the data, what is the best method to save it to the original range of the worksheet? This is what I am using but it saves it to the activesheet (sheet2). I need it saved to sheet "combined")

'Edit Feature
Private Sub cmdedit_Click()
Dim ws As Worksheet
Set ws = Worksheets("Combined")
    
With ws
    ActiveCell = Me.mytext.Value
    ActiveCell.Offset(0, -3).Value = Me.txtsdm.Value
    ActiveCell.Offset(0, -2).Value = Me.txtsdd.Value
    ActiveCell.Offset(0, -1).Value = Me.txtsdy.Value
    ActiveCell.Offset(0, 0).Value = Me.mytext.Value
    ActiveCell.Offset(0, 1).Value = Me.txtdpm.Value
    ActiveCell.Offset(0, 2).Value = Me.txtdpd.Value
    ActiveCell.Offset(0, 3).Value = Me.txtdpy.Value
    ActiveCell.Offset(0, 4).Value = Me.txtnet.Value
    ActiveCell.Offset(0, 5).Value = Me.txtmar.Value
    ActiveCell.Offset(0, 6).Value = Me.txtgross.Value
    ActiveCell.Offset(0, 7).Value = Me.txtpab.Value
    ActiveCell.Offset(0, 8).Value = Me.txtadj.Value
    ActiveCell.Offset(0, 9).Value = Me.txtdel.Value
    ActiveCell.Offset(0, 10).Value = Me.txtint.Value
    ActiveCell.Offset(0, 11).Value = Me.txtfin.Value
    
    
    
      
       
    Me.txtsdm.SetFocus
    
    
    
    
  
    'Clear the data
    
    Call clrctrl
    Me.txtsdm.SetFocus
   
    
    MsgBox "Record Changed!"
    
 
    
End With
End Sub

[SamT]

A) I didn't catch it earlier, because I didn't have to look at the code to edit it. but, you are reading and writing the Control "MyText" twice and I put it in the Inputs Collection twice. Remove the first instance of .Add MyText from the collection.

B) In your latest example, you forgot to put a dot in front of Active Cell. I'm not even sure there was an ActiveCell on WS.

C) Declare a new variable RecordRow in the Form Declarations area. You might as well declare WS there too. Leave WS's assignment where it is. In my last example change "C=C.Row" to "RecordRow = C.Row" and replace "C" in the Inputs Loop with "RecordRow."



Now since Inputs, RecordRow, and WS are in the Form's Public Declarations area, they will stay in memory until the Form is closed. Merely reverse the code that set the Controls' values.

            'Assumes that the first data column is "A"
With WS
            For i = 1 To Inputs.Count
                .Cells(RecordRow, i).Value = Inputs(i).Value  'Adjust first "i" with  "i + n" As needed to conform to data columns
                'where "n" is difference between col"A" and first datacolumn
            Next
End With

Putting the Inputs loop inside "With WS...End With" is more efficient. Note the dot in front of "Cells"

Now you can get rid of that entire With WS ActiveCell.Offset blah, blah, blah section.

[oldman]

Sam:

I attempted to apply your recommendations but with no success. Would it be a problem if we were to use my exisiting code with some modifications? I understand what I already written and I have a userform already in place that is designed to help the user.

Please do not take offense to my request.

[SamT]

Would it be a problem if we were to use my exisiting code with some modifications?

Heck no! Use any code you want. I'm only here to teach, not mandate.

In fact it is much preferred that you always use code that you understand.

[oldman]

Excellent attitude!