Display Range Value in Userform on Active Row

[reberryjr]

I've encountered a few challenges and am in need of some assistance. I have a worksheet that houses 42 data elements. Some of these are names that are split into First, Middle, Last and Suffix. I have a user form that will be used for data entry and each of these fields has their own text box. I have another user form (frm_MediaReview) where I want the data in the active cell to display the concatenated name.

1st Issue - frm_MediaReview is displaying whenever a cell in column A is active. That's happening through the code I've posted below. Instead, I'd like the form to only show if someone double clicks a cell in column A.

2nd Issue - How can I either concatenate the 4 name cells into 1 OR use a range? I would think the range would be cleaner, as I will want to use ranges a search function that I'm going to build out.

Private Sub UserForm_Initialize()


Dim Director1Name As Range


With Me
.txt_Title.Value = Cells(ActiveCell.Row, "A").Value
.txt_Status.Value = Cells(ActiveCell.Row, "B").Value
.txt_LoanedTo.Value = Cells(ActiveCell.Row, "C").Value
.txt_BluRay.Value = Cells(ActiveCell.Row, "D").Value
.txt_BluRay3D.Value = Cells(ActiveCell.Row, "E").Value
.txt_DVD.Value = Cells(ActiveCell.Row, "F").Value
.txt_Digital.Value = Cells(ActiveCell.Row, "G").Value
.txt_4KUHD.Value = Cells(ActiveCell.Row, "H").Value
.txt_Platform.Value = Cells(ActiveCell.Row, "I").Value
.txt_Media.Value = Cells(ActiveCell.Row, "J").Value
.txt_Collection.Value = Cells(ActiveCell.Row, "K").Value
.lnk_WebLink.Value = Cells(ActiveCell.Row, "L").Value
.txt_Rating.Value = Cells(ActiveCell.Row, "M").Value
.txt_Length.Value = Cells(ActiveCell.Row, "N").Value
.txt_Released.Value = Cells(ActiveCell.Row, "O").Value
.txt_Director1F.Value = Cells(ActiveCell.Row, "P").Value
.txt_Director1M.Value = Cells(ActiveCell.Row, "Q").Value
.txt_Director1L.Value = Cells(ActiveCell.Row, "R").Value
.txt_Director1S.Value = Cells(ActiveCell.Row, "S").Value
.txt_Director2F.Value = Cells(ActiveCell.Row, "T").Value
.txt_Director2M.Value = Cells(ActiveCell.Row, "U").Value
.txt_Director2L.Value = Cells(ActiveCell.Row, "V").Value
.txt_Director2S.Value = Cells(ActiveCell.Row, "W").Value
.txt_Genre.Value = Cells(ActiveCell.Row, "X").Value
.txt_Genre2.Value = Cells(ActiveCell.Row, "Y").Value
.txt_Star1F.Value = Cells(ActiveCell.Row, "Z").Value
.txt_Star1M.Value = Cells(ActiveCell.Row, "AA").Value
.txt_Star1L.Value = Cells(ActiveCell.Row, "AB").Value
.txt_Star1S.Value = Cells(ActiveCell.Row, "AC").Value
.txt_Star2F.Value = Cells(ActiveCell.Row, "AD").Value
.txt_Star2M.Value = Cells(ActiveCell.Row, "AE").Value
.txt_Star2L.Value = Cells(ActiveCell.Row, "AF").Value
.txt_Star2S.Value = Cells(ActiveCell.Row, "AG").Value
.txt_Star3F.Value = Cells(ActiveCell.Row, "AH").Value
.txt_Star3M.Value = Cells(ActiveCell.Row, "AI").Value
.txt_Star3L.Value = Cells(ActiveCell.Row, "AJ").Value
.txt_Star3S.Value = Cells(ActiveCell.Row, "AK").Value
.txt_Star4F.Value = Cells(ActiveCell.Row, "AL").Value
.txt_Star4M.Value = Cells(ActiveCell.Row, "AM").Value
.txt_Star4L.Value = Cells(ActiveCell.Row, "AN").Value
.txt_Star4S.Value = Cells(ActiveCell.Row, "AO").Value
.txt_Synopsis.Value = Cells(ActiveCell.Row, "AP").Value

.txt_Director1Name = Range(ActiveCell.Range, "Director1Name").Value

End With


End Sub

 

[Kenneth Hobson]

Please paste code between code tags. Click # icon on toolbar to insert the tags.

(1) Right click the sheet's tab, View Code, and paste:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column = 2 Then
    Cancel = True
      frm_MediaReview.Show
  End If
End Sub

Are you talking about Named Range cells or people Names which can be a big pain. What if you put it into one cell and then needed to sort? What if there is no middle name? What if there is not suffix. You will need several If()'s or such to build a proper name.
e.g.

Sub tName()
  Dim n$, ar As Long, a, s
  ar = ActiveCell.Row
  Select Case Cells(ar, "X").Value
    Case "Male"
      'a = WorksheetFunction.Transpose(WorksheetFunction.Transpose(Range(Cells(ar, "P"), Cells(ar, "S"))))
      a = WorksheetFunction.Index(Range(Cells(ar, "P"), Cells(ar, "S")).Value, 1, 0)
      s = Join(a, " ")
      If UBound(a) = 4 Then s = Replace(s, a(3) & " ", a(3) & ", ")
      msgbox s
    Case "Female"
      ''''
    Case Else
      '''
  End Select
End Sub

You could make your code easier to maintain if you put say the Column number or Column letter into the control's Tag property. Then a loop of control's with say a prefix name of "txt_" would fill the control's value property or vice-versa.

 

[reberryjr]

Thank you for the guidance. As for the 1st snag I was having, I used your code but changed the column indicator from 2 to 1. Works great and it makes sense! As far as the 2nd snag...well, I'm not entirely certain what your code is suggesting, as I'm new to VBA in Excel. I know that I can concatenate the name in the worksheet itself through nested IF statements, but I'm trying to learn VBA.

 

[Kenneth Hobson]

(2) I was not sure what the 1 and 2 in the names meant. I guessed that 1 was gender = Male. So, the names for name1's was in P:S columns, 4 cells in other words. First Middle Last Suffix. Putting those into an array, we can create a string delimited by space characters. Howsoever, one normally puts a comma after the last name if a suffix exists. So, if ubound(a)=4 in this case, we know there were 4 elements or parts to the name. We then use Replace() to change the 3rd " " to ", ".

Basically, Index(), Join()

As I said, people names can get tricky. Of course you can use the concatenation operator "&" to concatenate the name parts with space characters and commas. Some people will put two words for their first, middle, or last name. Still, it is best to get the 3 or 4 name parts and build the person's name making changes to suit the data and account for divergence from assumptions.

 

[reberryjr]

Between Google and my books, I'm not seeing anything that tells me to how to concatenate cells from a worksheet, into a textbox on a userform. I'll have to look around some more. Thank you again for the guidance!

 

[Kenneth Hobson]

TextBox1.Value = Range("A1").Value & " " Range("B1").Value & " that's all folks!"

 

[reberryjr]

Thank you! That snippet helped me form what I needed!