New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

RePopulating text box from found searched data value

Macros, VBA, Excel Automation, etc.

RePopulating text box from found searched data value

Postby Lazmail » Fri Apr 29, 2016 8:14 am

Hi All,
I am trying to populate a Userform with data from a spreadsheet "Welfare Listing" with up to 200 names in a range "F2:F201"
I wish to copy the found data in the searched row using the find system and using the current row of the worksheet("Welfare Listing" vba code TextBox18.Value = (Sheets("Welfare Listing").Cells(CurrentRow, "f").Value)

If I replace the (current row) for the numerical value of the row that the cursor is located then I am able to get the desired result, but when I run my macro it always seems to give me the result that is determined by the current row of the Worksheet("Data Entry") which is the name of the sheet that the Userform is displayed in.

I am trying to find a name on Worksheet("Welfare Listing") and use this name to search for the CurrentRow.value of the name and by using this value I am then able to populate the data to the UserForm which is activated by selecting the Worksheet("Data Entry")

Could someone please help. :?: :?: :?: :?:

The Vba code that have written is shown below.

Private Sub CommandButton8_Click()
Dim Today
Today = Now ' Assign current system date and time.
'MsgBox "current row" & ActiveCell.Row
Dim CurrentRow
Dim MyDate
CurrentRow = ActiveCell.Row
MyDate = Date ' MyDate contains the current system date.
Dim WL As Long, MS As Long, MR As Long, lrWL As Long
lrWL = Sheets("Welfare Listing").Range("S" & Rows.Count).End(xlUp).Row
TextBox23.Value = Left(Sheets("Welfare Listing").Cells(CurrentRow, "S").Value, 4)
TextBox34.Value = Right(Sheets("Welfare Listing").Cells(CurrentRow, "S").Value, 4)
TextBox24.Value = Left(Sheets("Welfare Listing").Cells(CurrentRow, "U").Value, 4)
TextBox35.Value = Mid(Sheets("Welfare Listing").Cells(CurrentRow, "U").Value, 6, 3)
TextBox36.Value = Right(Sheets("Welfare Listing").Cells(CurrentRow, "U").Value, 3)
TextBox18.Value = (Sheets("Welfare Listing").Cells(CurrentRow, "f").Value)
End Sub


Thank you in advance
  • 0

Lazmail
Rookie
 
Posts: 14
Joined: Feb 3, 2015
Reputation: 0
Excel Version: 2007

Re: RePopulating text box from found searched data value

Postby NoSparks » Fri Apr 29, 2016 9:12 am

No doubt what's posted is only a portion of the actual procedure as there is nothing stating the name to find nor where it is found, although there is an indication that you might be looking in column "S" of Welfare Listing.

I don't see any variable being declared as Range for the find location of the name being searched for but none the less your CurrentRow would be ThatVariable.Row rather than ActiveCell.Row
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: RePopulating text box from found searched data value

Postby Sisyphus » Sun May 01, 2016 7:52 am

As far as I know there is only one ActiveCell in a workbook at any one time, and it can't be on a sheet which is not the ActiveSheet. I don't have absolute knowledge because I abandoned the use of ActiveAnything for most purposes long, long ago. It always leads to troubles like you are facing.
If your ActiveCell is a reliable indicator you should assign CurrentRow = ActveCell.Row perhaps at the 'Welfare Listing' Deactivate event. This would require CurrentRow As Long to be a publicly declared variable. I wouldn't do that. The alternative might be to call the form from the Welfare sheet, but you must have your reasons for not wanting that, and even if you could change your procedure I would try to avoid the structure.
CurrentRow can be determined either as the Row property of a Find range or, perhaps, simply as the next empty row in the 'Welfare Listing' worksheet. Either method seems much more reliable. I would look for a way to determine the row without relying on what the user clicks.
Other than that, I feel you use too much code to reference the worksheet in a very inefficient way. Excel identifies column U as column 22. Every time you mention"U" VB must convert the string "U" to the Long 22. To reduce your volume of code and speed it up try this:-
Code: Select all
With Sheets("Welfare Listing").Rows(CurrentRow)
    TextBox18.Value = .Cells(6).Value         ' 6 = F
End With

If you are so inclined you can declare variables like,
Dim UColumn As Long
UColumn = Asc("U") - 64
The difference between ASC and column number is always 64. Personally, I would use an enum for this purpose, and I would replace the ananymous "UColumn" with a meaningful name.

Every reference to the worksheet takes about 20 times longer than a reference to a variable stored in memory. Consider this:-
Code: Select all
Dim Tmp As Strng
With Sheets("Welfare Listing").Rows(CurrentRow)
    Tmp = Trim(.Cells(22).Value)           ' 22 = U
    TextBox24.Value = Left(Tmp, 4)
    TextBox25.Value = Mid(Tmp, 6, 3)
    TextBox26.Value = Right(Tmp, 3)
    TextBox18.Value = .Cells(6).Value      ' 6 = F
End With

The use of Tmp allows out to check whether the sbstrings you are extracting really contain the formation you want. You can re-use Tmp to handle the value in column S.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: RePopulating text box from found searched data value

Postby Lazmail » Mon May 09, 2016 3:16 am

Hi Guys,

Thank you all for your responses, sorry I have been so long in replying but I have been busy digesting and utilising your info.

I have been able to successfully use your VBA codes and now that Sisyphus has added his response I have to possibly adjust my point of attack and consider the operational times and maybe I will have to rewrite some or all of my codes, but I hopefully will be able to do this.

If not I will be back again requesting more assistance and I hope that I have not exhausted your patience.

Thank you all once again.

Cheers :D :D :D :D
  • 0

Lazmail
Rookie
 
Posts: 14
Joined: Feb 3, 2015
Reputation: 0
Excel Version: 2007


Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 55 guests

cron