Select a range of cells in a Userform spreadsheet

[Snailspace]

Greetings yet again,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I’ve a spreadsheet control on a userform, laid out as below;<o></o>
I’d like the user to be able to highlight the row buttons (not always a continuous range), the selected rows can then have further work done on them once a command button is pressed.


Excel 2003
<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Doc Date</TD><TD>Doc No</TD><TD>Value</TD><TD>Due Date</TD><TD>Order No</TD><TD>Status</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">07/12/2010</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">10.00</TD><TD style="TEXT-ALIGN: right">31/01/2011</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">08/12/2010</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">20.00</TD><TD style="TEXT-ALIGN: right">31/01/2011</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">13/12/2010</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">30.00</TD><TD style="TEXT-ALIGN: right">31/01/2011</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">13/12/2010</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">40.00</TD><TD style="TEXT-ALIGN: right">31/01/2011</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">21/02/2011</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">50.00</TD><TD style="TEXT-ALIGN: right">31/03/2011</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet3​

NB – I’ve had to copy an example as I could not use the HTML addin to genereate the pic.The name of the spreadsheet is ssOpenItems<o></o>


I’ve tried using an input box to allow the selection, and referring to the selected items directly, both give the nothing selected message.<o></o>

Private Sub cmdAddDisputed_Click()
 
'check if a range is selected
On Error Resume Next
''Set rngNewDisputes = Application.InputBox(Prompt:="blah blah", Title:="New Dispute", Type:=8)
Set rngNewDisputes = Me.ssOpenItems.Selection
'
If rngNewDisputes Is Nothing Then
    MsgBox ("Nothing Selected")
Else
    MsgBox (rngNewDisputes.Address)
End If
 
End Sub

Any pointers in the right direction gratefully received.
SS

 

[Snailspace]

Just started a new job and the system produces lots of text reports which I'm slowly managing to sift out the info I need from, but it’s a tad frustrating.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I’m hoping to be able to set up more useful tables in the near future, unfortunately I cannot access the drives at home so I’m using the csv files as temporary tables with the aim of later on just querying via ODBC unless the files prove to be faster.<o></o>
<o> </o>
The listbox certainly looks as if it’s the way to go, now I just need to restructure all my queries, as I’d thought it best to load as much info as possible and then play with it in excel. At least it will keep me busy for a while. <o></o>
<o> </o>
p45cal, thanks for confirming that I wasn’t going mad when it would let me select the active sheet behind but not the one on the form.<o></o>
<o> </o>
Norie, It was interesting to note how many times your name popped up while I was trying find out more about recordsets and listboxes. Thanks again for the pointer. <o></o>
SP