How to populate a userform by highlighting a row? (Excel 97)

[chappy]

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:

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):

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

 

[sunnyland]

Hello there,

I didn't read all your code as I think always a really long code put me off: I prefer to receive a sample of the spreadsheet with really long code instead.

I think something like this should work:
This code on the worksheet ,will when full row selected open the userform:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Columns.Count = 256 And Target.Rows.Count = 1 Then
UserForm1.Show
End If
End Sub

This code will be on the userform. I don't know what kind of control you have on your userform so this code just loop through each cell in the row selected and then place value in the control using the index of the control.
I put on error resume next, which is not a good practise just so you can try out the code without having an error.
Meaning if you have less than 256 columns it will still display. Now it is not obvious to write the code when you don't know which columns you want to have displayed.

Post back if you need further help or if this code doesn't work for you.


Private Sub UserForm_Initialize()
On Error Resume Next
If Selection.Columns.Count = 256 And Selection.Rows.Count = 1 Then
For Each rangi In Selection
Me.Controls(x) = rangi
x = x + 1
Next
End If
End Sub


Always use a copy of your workbook to try out....

 

[chappy]

Re

Thanks for responding to my question Sunnyland.

I have copied in your code but unfortunately can't get it to work in the worksheet. I may not have put the code in exactly the right place (unfortunately I am not experienced with VB).

In answer to your comment, the userform mainly contains textboxes (12 of them).
I need to populate them as per the following sample:

txtKey.Value = "contents of column F"
txtDescription.Value = "contents of column G"
txtCoResp.Value = "contents of column H"
txtNotes.Value = "contents of column L"

Also I only need the form to appear when highlighting rows on sheet31(Circular Log).

Any further help you can give would be much appreciated.

 

[sunnyland]

Hello again,

If you go to the sheet31 in excel then go to the bottom of Excel window, on the tab at the bottom[where you can rename the worksheet] once you are on the tab of the sheet31 right click and select view code. That will bring you to where the first code should be put.

In VBA, there is 2 ways to access a worksheet. Suppose sheet31 is the name you see when you are in excel and look at the tab at the bottom sheet:

worksheets("sheet31").name
or
sheet31.name

It is better to use the second approach as if you decide to rename the worksheet ("hello") using sheet31.name will still work when the former worksheets("sheet31") will raise an error.

The code to go on the sheet31 is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Columns.Count = 256 And Target.Rows.Count = 1 Then
UserForm1.Show
End If
End Sub

Now I am not sure if in Excel 97, the userform can be modal or non modal.
By modal meaning : you have to close the userform before you can select something in Excel. If this is the case then on the userform page code paste this code [press F7 when your mouse is on the userform and it should bring up the code or the blank page] :
''''code for modal

Private Sub UserForm_Initialize()
refreshform
End Sub

Sub refreshform()
Application.StatusBar = Selection.Row
On Error GoTo erreurs
If Selection.Columns.Count = 256 And Selection.Rows.Count = 1 Then
txtKey = Sheet31.Cells(Selection.Row, 6) 'contents of column F
txtDescription = Sheet31.Cells(Selection.Row, 7) 'contents of column G
txtCoResp = Sheet31.Cells(Selection.Row, 8) 'contents of column H
txtNotes = Sheet31.Cells(Selection.Row, 12) 'contents of column L
End If
Exit Sub
erreurs:
MsgBox Err.Description
End Sub

If your form is not modal[meaning you can go back and forth between the form and the spreadsheet then the same code as to be in not the initialize sub but rather in the activate sub] the code on sheet31 will be slightly different as follow:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Columns.Count = 256 And Target.Rows.Count = 1 Then
UserForm1.Show
UserForm1.refreshform
End If
End Sub

and the one on the userform will be the same.
For the rest of your 12 textboxes just in the sub refreshform add :
txtname = Sheet31.Cells(Selection.Row, columnnumber)
replacing txtname by the name of the txtbox and columnnumber by the columnnumber

 

[chappy]

RE

Thanks again Sunnyland. Due to your extremely helpful and easy to understand explanations my file now works exactly as I had hoped.

In answer to your question / comment - it is modal.

Many thanks for your assistance!