Excel VBA Row by Row Link to Launch Userform with data from given Row

[Jaye Cavallo]

Hello,

I have developed a tracker which tracks customer inquiries. The excel file launches a userform from a button on a custom ribbon to enter information and then adds that information to a row on a worksheet. I am hoping to find a way to add a column to the worksheet through the userform which would create link to launch the userform with the information from a given row. So, each row of data would have a link, say "View," which would launch the userform with that row's data populating the userform. I am unable to attach the outreach tracker because i cannot install anything on my work PC. I do not have admin priv.

If anyone is interested in helping me, I am guessing I could email the file directly to you.

Please let me know if you can help.

Thank you in advance

 

[DanteAmor]

I don't think a link is necessary.
You can double click on a cell; the userform will open and take the data from the row of the cell where you double clicked.

Put the following code in the sheet events:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True
  With UserForm1
    .nRow = ActiveCell.Row
    .Show
  End With
End Sub

Note Sheet Event:
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.


Put the following code in the userform:

Public nRow As Long       'At the beginning of all the code

Private Sub UserForm_Activate()
  If nRow <> 0 Then
    TextBox1.Value = Range("A" & nRow).Value
    TextBox2.Value = Range("B" & nRow).Value
    TextBox3.Value = Range("C" & nRow).Value
  End If
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Jaye Cavallo]

Thank you, Dante. This seems to work just fine.

 

[Jaye Cavallo]

So, one question. How can I set the value of nRow so as to not launch the form on the header row? I tried <=1, but that did not work.

 

[DanteAmor]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Row < 2 Then Exit Sub
  Cancel = True
  With UserForm1
    .nRow = ActiveCell.Row
    .Show
  End With
End Sub

 

[Jaye Cavallo]

Also, now that I have had some time to work with your solution, your code definitely does what I asked for it to do. However, the other functions on the userform do not perform as expected now. I have an update button on the form with which I can update the entry on the form, which matches the row in worksheet. I also have a counter on the form which displays which row is selected based on the userform data. When your code is included in my vba code, whatever row I double click, that data appears in the userform, but cell selected is the first row of data on the excel worksheet and the counter says the userform is on row 1. So if I select update on the userform, row 1 is updated as opposed the actual row I had double clicked.

So I guess I still have more work to do. This is probably too much to explain to you when you can't see the file and how it works.

 

[DanteAmor]

Create a new thread and put your code there to verify it, maybe someone else can help you with what you have.