PDA

View Full Version : [SOLVED] Worksheet focus change issues with userform(s)



oldman
09-24-2013, 09:41 AM
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
09-24-2013, 10:17 AM
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
09-24-2013, 10:43 AM
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
09-24-2013, 10:49 AM
Outstanding!!!!!!!!!!!!!!

Tommy
09-24-2013, 11:20 AM
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
09-24-2013, 11:34 AM
Thank you

SamT
09-25-2013, 09:43 AM
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
09-27-2013, 07:15 AM
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
09-27-2013, 08:32 AM
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
09-27-2013, 08:52 AM
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
09-27-2013, 03:19 PM
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
09-28-2013, 08:46 AM
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
09-28-2013, 10:57 AM
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. :beerchug:

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

oldman
09-28-2013, 12:25 PM
Excellent attitude!