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

 

[p45cal]

No problem here (bar not being able to select non-contiguous rows or cells in the spreadsheet control).
I was using this version of the spreadsheet control:
Microsoft Ofice Spreadsheet 11.0
while using Excel 2003.

 

[Snailspace]

Thanks for the quick reply, was it the input box that works on your side?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I didn’t realise that you had to type the range in here rather than select with a mouse.<o></o>
If I do this it does pass the correct range. <o></o>
<o></o>
<o></o>
I'd rather have the user select by mouse click.
I’ve had a quick look but I can’t see where I change the type of input box to allow mouse selection rather than having to type the ranges in.<o></o>

 

[Snailspace]

I may have found a work around, although I don’t fully understand it.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
If I declare a variable as a string I can use the code below to get the row addresses.
This does not allow non-contiguous rows, but I can live with that.
<o></o><o></o>

[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana]temp = Me.ssOpenItems.Selection.Address<o:p></o:p>[/FONT][/COLOR]
[/COLOR][/FONT]

<o> </o>
<o></o>
If anyone could explain why I can’t declare a range variable for the selection I’d appreciate it.<o></o>
<o></o>

[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana]rngtemp = Me.ssOpenItems.Selection[/FONT][/COLOR]
[/COLOR][/FONT]

 

[Norie]

This question is kind of off topic, but is that all the data you are working with?

The reason I'm asking is because it could be easily displayed on a userform using a listbox.

A listbox would allow the user to select multiple rows of data, contiguous or not.

Of course you would lose any functionality that was only available using the spreadsheet control.

Sorrry for butting in, just kind of wondering.

 

[p45cal]

It was the selection that worked.
I've now tried the input box line instead. It does allow me to select cells, but on the active sheet behind the userform, not on the spreadsheet control on the userform.
When I typed in a range, it appeared to work, but I checked the resultant range's parentage and it turns out to be that of the sheet underlying the userform - so be careful.
Why have a spreadsheet control on the userform? Why not use the active sheet?

 

[Snailspace]

Thanks for the interest, to put it in perspective I’m using ADO to pull in a list of invoices from a text file based on a customer’s account number. The aim being to make it an addin I (and others) can use at work<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I chose a spreadsheet on the userform as I do a few calculations on different fields and show them on a different page.<o></o>
It’s also useful for the user to copy and paste the list into their spreadsheet.<o></o>
<o> </o>
I’ll look into the listbox as it looks neater for this section. Just means a rethink of the other pages.<o></o>

 

[Norie]

You could probably do quite a bit of that with listbox (or two?)

Actually when you mentioned ADO it got me wondering how you would populate a listbox with the multiple records with multiple fields.

I've done it when there's only one field just using AddItem but not that.

Anyway I tried this and it worked fine.

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim strConn As String
Dim sql As String
Dim varData

    'connect to MySQL server using MySQL ODBC 3.51 Driver
    Set conn = New ADODB.Connection
    strConn = "DRIVER={MySQL ODBC 5.1 Driver};"
    strConn = strConn & "SERVER=*********;"
    strConn = strConn & "DATABASE=**************************;"
    strConn = strConn & "UID=root;PWD=*"
    conn.ConnectionString = strConn
 
    conn.Open
 
    sql = "SELECT * FROM tblstates;"

    Set rs = New ADODB.Recordset

    rs.Open sql, conn
 
    varData = rs.GetRows
 
    varData = Application.Transpose(varData)
 
    With UserForm1.ListBox1
        .ColumnCount = rs.Fields.Count
        .List = varData
    End With

    Set rs = Nothing

    Set conn = Nothing

Sorry for digressing again, actually just meant to say that if the spreadsheet control is working that's the important thing.

 

[Snailspace]

Norie, taking a quick look at what you’ve provided it might well be a neater solution.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I’ll have a play around and post back the results, unfortunately I’ve been summoned to help with dinner.<o></o>

 

[Norie]

I'd be interested to know how it works with text files.