asked on

Excel Userform Initialize

The attached spreadsheet has a Userform that allows the User to scroll through the rows of data in the spreadsheet.

Trying to figure out how to have the user double click in a "Sub-System" cell (Column C) and have the Userform open to that row data. Currently the Userform opens to the first record, and the user has to scroll through rows to reach the row in question.
Tracker-Workbook.xlsm

Wilder1626

An example could be like below:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim Lastrow As Integer
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Set Rng = Range("C6:C" & Lastrow)

If Not Intersect(Target, Rng) Is Nothing Then SubSystemDetail.Show

End Sub

Open in new window

If you want to be able to type in the cell with the Form open also, you use VbModeless:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, 

Dim Lastrow As Integer
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Set Rng = Range("C1:C" & Lastrow)

If Not Intersect(Target, Rng) Is Nothing Then SubSystemDetail.Show VbModeless
End Sub

Open in new window

DougDodge

ASKER

Double click opens the Userform, but it does not open it to the item that was double clicked on, it just opens the Userform.

ASKER CERTIFIED SOLUTION

Wilder1626

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DougDodge

ASKER

Works like a charm.....

Thanks.....

Wilder1626

i'm glad i was able to help.