Solved

Excel Userform Initialize

Posted on 2015-01-20
5
174 Views
Last Modified: 2015-01-20
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
0
Comment
Question by:DougDodge
  • 3
  • 2
5 Comments
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40560705
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

0
 

Author Comment

by:DougDodge
ID: 40560766
Double click opens the Userform, but it does not open it to the item that was double clicked on, it just opens the Userform.
0
 
LVL 11

Accepted Solution

by:
Wilder1626 earned 500 total points
ID: 40560851
If you want to see a value from a cell in the userform, you can do this. example
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

SubSystemDetail.txtSubSystem = Cells(Application.ActiveCell.Row, 3).Value
    SubSystemDetail.Show

End Sub

Open in new window


That would enter the value from the C column Cell into the txtSubSystem  textbox

You can put has many textbox as you needs
Ex:
SubSystemDetail.TexBox1= Cells(Application.ActiveCell.Row, 3).Value

SubSystemDetail.TexBox2= Cells(Application.ActiveCell.Row, 4).Value

Open in new window

0
 

Author Closing Comment

by:DougDodge
ID: 40560963
Works like a charm.....

Thanks.....
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40560973
i'm glad i was able to help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel Formula to Pull Data from Text String 7 72
Excel 2016 crashes with "Closer" add-in 3 105
Word 2010 mail merge 3 60
Simple Google Sheets Script question. 2 13
To stay competitive, modern businesses must adapt and stay innovative, and this is increasingly only possible by working with outside talent. Managers and executives have understood the power of outsourcing for quite some time, but traditional clien…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
The viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now