Controls (Control Toolbox): Get values from Spreadsheet 9.0

Relman · Sep 15th 2003

Hi All

I'd like to have a table in a UserForm which the User can fill with values, which are then used in various calculations when a "Calculate" button is clicked.

The finished form is going to be part of an Add-In, so I don't want to go the "Put data in a textbox, press OK, copy to a background sheet, clear textbox, repeat" route.

The MSO Spreadsheet 9.0 object looks the biz, but I can't find out how to use it this way, only for putting spreadsheet data on a web page.
I’ve put one on the UserForm, formatted just nicely, but I don’t seem to be able to return the values from the cells.

I’ve got this code on the Button_Click() event:

txtTotup.Value = WorksheetFunction.Sum(Spreadsheet1.Columns(1))

If I’ve got 12, 12, 12 in cells A1 to A3, the textbox shows “12”, so I’m getting something at least!

BTW the .Range Property doesn’t work, so

txtTotup.Value = WorksheetFunction.Sum(Spreadsheet1.Range(“A1:A10”))

gives an error.

Any suggestions gratefully accepted

Chris

**Andy Pope** · Sep 16th 2003

Hi Chris,

As you quite rightly point out the range object does not appear to function.

You can use the .Cells() instead.

And instead of using a .Worksheetfunction how about using the spreadsheet control to do the calculation. See example;

Code

With Me.Spreadsheet1
        .Cells(1, 2).Formula = "=Sum(A1:A10)"
        TextBox1.Text = .Cells(1, 2)
    End With

Hope this helps

Cheers
Andy

Relman · Sep 16th 2003

Hi Andy, thanks for the reply

Yeah, that's about what I'd figured I'm still playing/fiddling/tweaking in an effort to make that A1:A10 range dynamic, just like a 'real' spreadsheet, but without success so far!

BTW: Funny thing that you should be the one to reply, 'cos I bet you can guess where I'm going with this, and what my next post is likely to be :biggrin:;;)

"If you want to confuse your audience, use statistics. If you REALLY want to baffle 'em, use charts"

Cheers, Ace

Chris

Controls (Control Toolbox): Get values from Spreadsheet 9.0

Participate now!

Share