Using Excel to give score/ratings to ideas

[soxgirl87]

Hey everyone!

I'm using Excel 2007 and trying to create a survey to send out to my co-workers about rating different ideas. The rating system works perfectly -- the problem is, making it work for more than one idea! What it does is have 3 radio buttons (1, 2, 3) for a bunch of different criteria, and then tallies it up at the end in a "Total Score" box.

What I need is a way to press a button that applies the Total Score to the current Idea being rated, so they can move on to rating the next idea

I've attached what I have so far here:
http://www.filesavr.com/ZBSIYKXUHIZF3GK

As you can see, I've tried to create a listbox, with 3 columns. That way, the user can select one item in the listbox, fill out the criteria ratings, and press submit.

The problem is, my submit button doesn't do anything I can't figure out how to make it modify the 3rd column (score) of the currently selected item.

Is there a way to basically say: ListBox1.SelectedItem.3rdColumn.Value = F10 (where F10 is the Total Score cell)

I know SelectedItem and 3rdColumn don't exist But I hope it helps explain what I'm trying to do

Any thoughts or ideas on this?? I'm also open to trying something totally without the listbox.

Thanks so much!

 

[AlphaFrog]

I didn't download your file but the syntax would be something like this...

Private Sub CommandButton1_Click()
    ListBox1.List(ListBox1.ListIndex, 2) = Range("F10").Value
End Sub

ListBox1.ListIndex is the selected row in the list box
2 is the 3rd column in the listbox (indexed 0 to 2)

 

[soxgirl87]

I didn't download your file but the syntax would be something like this...

Private Sub CommandButton1_Click()
    ListBox1.List(ListBox1.ListIndex, 2) = Range("F10").Value
End Sub

ListBox1.ListIndex is the selected row in the list box
2 is the 3rd column in the listbox (indexed 0 to 2)

Perfect!! Thank you so much! One last question -- after this is all done, I'd like it to export the data from the ListBox into an excel spreadsheet (so we can compile all the data).

Is there a way I could add a button that says "Click Here When Finished" and then have it export all the data into a new Sheet? Or even in the same sheet, below the data?

 

[soxgirl87]

This code doesn't seem to work when using the ListFillRange property -- I get permission denied. Is there a way to modify the code so it's more like:

ListBox1.List(ListBox1.ListIndex, 2).RANGEVALUE = Range("F10").Value

So it essentially changes the range value of the selected item/column?

 

[AlphaFrog]

Private Sub CommandButton1_Click()
   Range([COLOR="Red"]"A1:C10"[/COLOR])(ListBox1.ListIndex + 1, 3).Value = Range("F10").Value
End Sub

Change A1:C10 to your list fill range.


"Click Here When Finished"

Private Sub CommandButton2_Click()
    Range([COLOR="Red"]"A1:C10[/COLOR]").Copy Destination:=Sheets("Sheet2").Range("D2")
End Sub