Populate a Userform at open

[stirlingmw]

Morning all
I have an excel workbook I am using as a database with 2 worksheets "Project Master" with 72 Columns of data and "Project Summary" with 8 Columns of selected data from "Project Master". I also have a Userform "frmViewData" which when open is populated by selecting a Project from a Combobox which finds the corresponding row from the "Project Master" worksheet and then populates the textboxes etc with data from the rest of that row or I can Add new projects through this Userform.
Worksheet "Project Summary" uses cell references to populate columns B to G from "Project Master" (i.e. B2='Project Master'!A2) and Column H shows a Hyperlink if the corresponding cell in "Project Master" shows TRUE.

I am trying to do 3 things to tidy my workbook up so it is more user friendly for my team.

1. Rather than Cell references I would like VBA to populate "Project Summary" from data in "Project Master" if data is available in the corresponding row (there are no blank rows until last row).
2. When a row of data is selected in "Project Summary" frmViewData is opened populated with data from the same row in "Project Master".
3. If No.2 isn't easy I have left Column A empty in "Project Summary", so that when new data is entered into "Project Master" the text "View Data" is added in the corresponding row in "Project Summary" and when this text is selected FrmViewData is opened populated with the right data.

Here are shortened examples of the code I am using to populate the Userform and also save new and updated data to Master Worksheet

Private Sub CmbFindProject_Change()
 
blnNew = False
totRows = Worksheets("Project Master").Range("A1").CurrentRegion.Rows.count
For i = 2 To totRows
    If Trim(Worksheets("Project Master").Cells(i, 1)) = Trim(CmbFindProject.text) Then
TxtProject.text = Worksheets("Project Master").Cells(i, 1).Value
CmbTeam.text = Worksheets("Project Master").Cells(i, 2).Value
'Carries on until column 72
 
Exit For
End If
Next i
End Sub
 
Private Sub cmdSave_Click()
If TxtProject.text = "" Then
MsgBox "Enter new project data or select a project", vbCritical, "Save"
TxtProject.SetFocus
Exit Sub
End If
Call pSave
End Sub
 
Private Sub pSave()
Dim a As Long
 
If blnNew = True Then
totRows = Worksheets("Project Master").Range("A1").CurrentRegion.Rows.count
With Worksheets("Project Master").Range("A1")
.Offset(totRows, 0) = TxtProject.text
.Offset(totRows, 1) = CmbTeam.text
'Carries on until column 72
 
End With
Call comboboxFill
Else
totRows = Worksheets("Project Master").Range("A1").CurrentRegion.Rows.count
For i = 2 To totRows
    If Trim(Worksheets("Project Master").Cells(i, 1)) = Trim(CmbFindProject.text) Then
Worksheets("Project Master").Cells(i, 1) = TxtProject.text 'Project Title
Worksheets("Project Master").Cells(i, 2) = CmbTeam.text 'CDT Team
'Carries on until column 72
 
Exit For
End If
Next i
End If
blnNew = False
End Sub
 
Private Sub comboboxFill()
CmbFindProject.Clear
totRows = Worksheets("Project Master").Range("A1").CurrentRegion.Rows.count
For i = 2 To totRows
CmbFindProject.AddItem Worksheets("Project Master").Cells(i, 1).Value
Next i
End Sub

I hope that isnt too long winded

Thanks in advance

Steve

 

[Norie]

Steve

What are you going to use instead of cell references to populate the userform?

 

[stirlingmw]

Norie

The cell reference I mentioned was to populate the worksheet "Project Summary". The Userform is currently populated by the VBA code within Private Sub CmbFindProject_Change(). I was thinking something like this to populate the userform based on which row is selected from the "Project Summary" worksheet, But as I wasnt selecting the desired data from a Combobox, but instead either selecting a cell or something like that from a worksheet, I didnt know which parts of the code to modify.

 

[Norie]

Steve

Do you mean you want the userform to pop up when a row is selected on the 'Project Summary' sheet and to be populated with data from the selected row?

The code for populating the userform from the selected row might look something like this.

TxtProject.text = Worksheets("Project Summary").Cells(ActiveCell.Row, 1).Value
CmbTeam.text = Worksheets("Project Summary").Cells(ActiveCell.Row, 2).Value