Controls (Control Toolbox): Get values from Spreadsheet 9.0

  • 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



    "Varium et mutabile semper Excel"

  • 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;

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

    Hope this helps



  • 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



    "Varium et mutabile semper Excel"

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!