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