Hi All:

I am putting an edit data procedure together. User enters data on
userform (UserNewEntry), calculates some risk metrics, and an output
spreadsheet is populated with inputs and calculated outputs.

The project unique ID (from the output data) populates the first
userform combobox dropdown. The user can select a pre-existing project
(populating all userform controls from the output sheet) and user can
edit inputs from the userform. The user can also enter in a new
project in cboProjectTitle.

So that the user can edit the project directly from the output sheet, I
would like to give them the capability to select any value on a
particular project row and call the userform populated with that data
so that the risk calculations can be redone.

Here's what I have done: The output sheet has a commandbar with an
edit button and a new project button. I would like the edit button to
select the value in column A on the selected row, populate the
cboProjectTitle with that value thereby populating all controls with
the input data on the selected row (seperate module). Clearly, the new
entry button would call the userform deaulted to blank (the user can
still select a pre-existing project from cboProjectTitle.

My code is pretty lengthy. But the relevant portion should, in basic
form, look something like this:
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''
'Commandbar procedure
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''
Sub CreateToolbar()
Dim RRTools As CommandBar
Dim bSave As CommandBarButton
Dim bEdit As CommandBarButton

On Error Resume Next
CommandBars("RR Field Tools").Delete
On Error GoTo 0

Set RRTools = CommandBars.Add

With RRTools
.Name = "RR Field Tools"
.Position = msoBarFloating
' .Protection = msoBarNoCustomize+msoBarNoResize
.Visible = True
End With

Set bSave = CommandBars("RR Field
Tools").Controls.Add(Type:=msoControlButton)
With bSave
.FaceId = 3
.OnAction = "SaveButton"
.Caption = "Save Locally"
End With

Set bEdit = CommandBars("RR Field
Tools").Controls.Add(Type:=msoControlButton)
With bEdit
.FaceId = 2946
.OnAction = "EditButton"
.Caption = "Edit Current Selection"
End With


End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''
'Button procedures
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''
Sub SaveButton()
On Error Resume Next
ActiveWorkbook.SaveAs Filename:="S:\LDC-LI\Risk\Risk Registry Field
Model\" & "RiskField." & Environ("username") & "." & Month(Now), _
FileFormat:=xlWorkbookNormal, CreateBackup:=True,
accessmode:=xlExclusive
On Error GoTo 0
End Sub

Sub EditButton()
Dim selectrow as range
On Error Resume Next
Set PTitleRow = ActiveCell.End(xlToLeft).Select
Load UserNewEntry
UserNewEntry.cboProjectTitle.Listindex=PTitleRow
Show UserNewEntry
On Error GoTo 0
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''

The userform comes up, but unpopulated. Any help/feedback is much
appreciated.

Cheers,
James KD

First, I don't have a guess.

But I think you're masking any error by including "On Error Resume Next" in that
"EditButton" routine.

I'd comment out that line (or remove it and make sure bad things can't happen)
and do some testing.



KD wrote:

Hi All:

I am putting an edit data procedure together. User enters data on
userform (UserNewEntry), calculates some risk metrics, and an output
spreadsheet is populated with inputs and calculated outputs.

The project unique ID (from the output data) populates the first
userform combobox dropdown. The user can select a pre-existing project
(populating all userform controls from the output sheet) and user can
edit inputs from the userform. The user can also enter in a new
project in cboProjectTitle.

So that the user can edit the project directly from the output sheet, I
would like to give them the capability to select any value on a
particular project row and call the userform populated with that data
so that the risk calculations can be redone.

Here's what I have done: The output sheet has a commandbar with an
edit button and a new project button. I would like the edit button to
select the value in column A on the selected row, populate the
cboProjectTitle with that value thereby populating all controls with
the input data on the selected row (seperate module). Clearly, the new
entry button would call the userform deaulted to blank (the user can
still select a pre-existing project from cboProjectTitle.

My code is pretty lengthy. But the relevant portion should, in basic
form, look something like this:
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''
'Commandbar procedure
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''
Sub CreateToolbar()
Dim RRTools As CommandBar
Dim bSave As CommandBarButton
Dim bEdit As CommandBarButton

On Error Resume Next
CommandBars("RR Field Tools").Delete
On Error GoTo 0

Set RRTools = CommandBars.Add

With RRTools
.Name = "RR Field Tools"
.Position = msoBarFloating
' .Protection = msoBarNoCustomize+msoBarNoResize
.Visible = True
End With

Set bSave = CommandBars("RR Field
Tools").Controls.Add(Type:=msoControlButton)
With bSave
.FaceId = 3
.OnAction = "SaveButton"
.Caption = "Save Locally"
End With

Set bEdit = CommandBars("RR Field
Tools").Controls.Add(Type:=msoControlButton)
With bEdit
.FaceId = 2946
.OnAction = "EditButton"
.Caption = "Edit Current Selection"
End With

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''
'Button procedures
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''
Sub SaveButton()
On Error Resume Next
ActiveWorkbook.SaveAs Filename:="S:\LDC-LI\Risk\Risk Registry Field
Model\" & "RiskField." & Environ("username") & "." & Month(Now), _
FileFormat:=xlWorkbookNormal, CreateBackup:=True,
accessmode:=xlExclusive
On Error GoTo 0
End Sub

Sub EditButton()
Dim selectrow as range
On Error Resume Next
Set PTitleRow = ActiveCell.End(xlToLeft).Select
Load UserNewEntry
UserNewEntry.cboProjectTitle.Listindex=PTitleRow
Show UserNewEntry
On Error GoTo 0
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''

The userform comes up, but unpopulated. Any help/feedback is much
appreciated.

Cheers,
James KD

--

Dave Peterson