Thread: Select Listindex from column A on selected row

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