Question
Is is possible to do the following upon highlighting a row on a worksheet:
Automatically display a userform populated with data from the highlighted row and from specific columns?
Background
I have a a worksheet with data populating several hundred rows, with data spanning 20 columns across. Some of the columns contains a basic yes/no or dates whilst other columns contain a relatively large amount of text (which makes it difficult for a user to scroll through).
I want the userform to show the contents of certain defined columns and display it within textboxes (with scroll bars enabling the user to view the large amounts of text more easily).
Requirements
1. Upon selecting a row in the worksheet, the userform appears
2. Userform is populated with elements of the data on the worksheet
For example something like:
____________________________________________________________
Detailed Background
Code already being used:
This is used to capture data and populate the worksheets using a seperate userform etc, so some of the elements will already be defined (although most of it will be irrelevant to this question):
Is is possible to do the following upon highlighting a row on a worksheet:
Automatically display a userform populated with data from the highlighted row and from specific columns?
Background
I have a a worksheet with data populating several hundred rows, with data spanning 20 columns across. Some of the columns contains a basic yes/no or dates whilst other columns contain a relatively large amount of text (which makes it difficult for a user to scroll through).
I want the userform to show the contents of certain defined columns and display it within textboxes (with scroll bars enabling the user to view the large amounts of text more easily).
Requirements
1. Upon selecting a row in the worksheet, the userform appears
2. Userform is populated with elements of the data on the worksheet
For example something like:
Code:
frmClarStatus.show
txtClar_no.Value = Cells(selected row, column number)
txtbox2.Value = Cells(selected row, column number)
Detailed Background
Code already being used:
This is used to capture data and populate the worksheets using a seperate userform etc, so some of the elements will already be defined (although most of it will be irrelevant to this question):
Code:
Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
Dim UniqueList() As String
Dim x As Long
Dim Rng1 As Range
Dim c As Range
Dim Unique As Boolean
Dim y As Long
Set Rng1 = Sheets("Summary").Range("E5:E29")
y = 1
ReDim UniqueList(1 To Rng1.Rows.Count)
For Each c In Rng1
If Not c.Value = vbNullString Then
Unique = True
For x = 1 To y
If UniqueList(x) = c.Text Then
Unique = False
End If
Next
If Unique Then
y = y + 1
Me.cboResponsible1.AddItem (c.Text)
Me.cboResponsible2.AddItem (c.Text)
UniqueList(y) = c.Text
End If
End If
Next
txtClar_no.Value = ""
'.Value = "" Ensures that the user form segment is blank when form is activated
With cboType
'cboType relates to the drop down menu on the user form describing the
'type of clarification
.AddItem "Commercial"
.AddItem "Contractual"
.AddItem "Technical"
.AddItem "Other"
End With
cboType.Value = ""
txtDoc_ref.Value = ""
txtLoc_ref.Value = ""
txtDeadline.Value = "dd.mm.yyyy hh:mm"
txtIssued.Value = Format$(Date, "dd.MM.yyyy")
End Sub
Private Sub cmdOk_Click()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim PM As String
PM = Sheets("Summary").Range("G5").Value
Dim TCM As String
TCM = Sheets("Summary").Range("G7").Value
Dim SnM As String
SnM = Sheets("Summary").Range("G9").Value
Dim TM As String
TM = Sheets("Summary").Range("G11").Value
Dim SEng As String
SEng = Sheets("Summary").Range("G13").Value
Dim Eng As String
Eng = Sheets("Summary").Range("G15").Value
Dim Tax As String
Tax = Sheets("Summary").Range("G17").Value
Dim Legal As String
Legal = Sheets("Summary").Range("G19").Value
Dim SCM As String
SCM = Sheets("Summary").Range("G21").Value
Dim HSE As String
HSE = Sheets("Summary").Range("G23").Value
Dim EST As String
EST = Sheets("Summary").Range("G25").Value
Dim Plan As String
Plan = Sheets("Summary").Range("G27").Value
Dim Risk As String
Risk = Sheets("Summary").Range("G29").Value
Dim Description As String
Description = txtDescription.Value
Dim DKK As String
DKK = Sheets("Summary").Range("B38").Value
Dim EUR As String
EUR = Sheets("Summary").Range("B40").Value
Dim GBP As String
GBP = Sheets("Summary").Range("B42").Value
Dim NOK As String
NOK = Sheets("Summary").Range("B44").Value
Dim USD As String
USD = Sheets("Summary").Range("B46").Value
Dim CURR6 As String
CURR6 = Sheets("Summary").Range("B48").Value
Dim CURR7 As String
CURR7 = Sheets("Summary").Range("B50").Value
Dim lrow As Long
Worksheets("Clarification Log").Activate
With Worksheets("Clarification Log")
lrow = Range("B65336").End(xlUp).Row + 1 '' Gets the last row to populate
End With
With frmClar
Cells(lrow, 2).Value = .txtClar_no
Cells(lrow, 3).Value = .txtKey
Cells(lrow, 4).Value = "Ongoing"
Cells(lrow, 5).Value = .txtDeadline '(Note : ''ckeck on this later)
Select Case cboResponsible1.ListIndex
Case 0
Cells(lrow, 6).Value = PM
Case 1
Cells(lrow, 6).Value = TCM
Case 2
Cells(lrow, 6).Value = SnM
Case 3
Cells(lrow, 6).Value = TM
Case 4
Cells(lrow, 6).Value = SEng
Case 5
Cells(lrow, 6).Value = Eng
Case 6
Cells(lrow, 6).Value = Tax
Case 7
Cells(lrow, 6).Value = Legal
Case 8
Cells(lrow, 6).Value = SCM
Case 9
Cells(lrow, 6).Value = HSE
Case 10
Cells(lrow, 6).Value = EST
Case 11
Cells(lrow, 6).Value = Plan
Case 12
Cells(lrow, 6).Value = Risk
End Select
Select Case cboResponsible2.ListIndex
Case 0
Cells(lrow, 7).Value = PM
Case 1
Cells(lrow, 7).Value = TCM
Case 2
Cells(lrow, 7).Value = SnM
Case 3
Cells(lrow, 7).Value = TM
Case 4
Cells(lrow, 7).Value = SEng
Case 5
Cells(lrow, 7).Value = Eng
Case 6
Cells(lrow, 7).Value = Tax
Case 7
Cells(lrow, 7).Value = Legal
Case 8
Cells(lrow, 7).Value = SCM
Case 9
Cells(lrow, 7).Value = HSE
Case 10
Cells(lrow, 7).Value = EST
Case 11
Cells(lrow, 7).Value = Plan
Case 12
Cells(lrow, 7).Value = Risk
End Select
Cells(lrow, 8).Value = txtIssued
Cells(lrow, 9).Value = txtDoc_ref
Cells(lrow, 10).Value = txtLoc_ref
Cells(lrow, 15).Value = Description
If chkImpact = True Then
Cells(lrow, 11).Value = "Yes"
Else
Cells(lrow, 11).Value = "No"
End If
ActiveWorkbook.Save
If .chkImpact = True Then
Sheets("Template").Copy
'Section of code above creates a new workbook from the Template sheet if the
'cost impact check box is checked
Range("C5") = .txtClar_no
Range("C6") = .cboType
Range("C7") = .txtDoc_ref
Range("C8") = .txtLoc_ref
Range("C9") = .txtKey
Range("G5") = .txtDeadline ''to look at later
'Should ensure that the correct date and time format is applied (dd.mm.yyyy hh:mm)
Range("G6") = .txtIssued
Range("G7") = .cboResponsible1.Value
Range("G8") = .cboResponsible2.Value
Range("B12") = .txtDescription
Range("AC3") = DKK
Range("AD3") = EUR
Range("AE3") = GBP
Range("AF3") = NOK
Range("AG3") = USD
Range("AH3") = CURR6
Range("AI3") = CURR7
'Section of code above populates the new workbook sheet with the required input
'data from the user form (the same data as input in the Clarification Log sheet)
ActiveWorkbook.SaveAs wb.Path & "\" & .txtClar_no
End If
End With
Unload Me
Call UserForm_Initialize
End Sub
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub cmdClear_Click()
chkImpact = False
Call UserForm_Initialize
'Section of code above Clears the User Form
End Sub