Spreadsheet Control 11.0 - data in

[vegasfoster]

Hi, so I just want populate some cells in a spreadsheet control box on my userform from data in the associated worksheet. I found sample code to get the data out,

Private Sub PasteButton_Click()

ActiveCell.Value = Spreadsheet1.Range("B5")

Unload Me

End Sub

But can't find an example to get data in. I've tried numerous things like

Private Sub Spreadsheet1_Initialize()

Spreadsheet1.Range("A1") = Worksheets("Input").Range("Date")

End Sub

but no joy. I don't get any errors, but nothing happens.

I'm clearly not searching for the right thing.

Thanks for any help.

 

[Logit]

What do you mean by "spreadsheet control box" ?

Combobox or Listbox ?

 

[Norie]

Try putting the 2nd code in the userform's Initialize event.

Private Sub UserForm_Initialize()

    Spreadsheet1.Range("A1").Value = Worksheets("Input").Range("Date").Value

End Sub

 

[vegasfoster]

Try putting the 2nd code in the userform's Initialize event.

Private Sub UserForm_Initialize()

    Spreadsheet1.Range("A1").Value = Worksheets("Input").Range("Date").Value

End Sub

Good idea, but didn't work

I've tried something simple as Spreadsheet1.Range("A1").Value = 5, but it is blank. I'm missing something.

 

[vegasfoster]

What do you mean by "spreadsheet control box" ?

Combobox or Listbox ?

No, it's an additional control. I want to display a bunch of data for people who don't know how to use excel, i.e. I don't want them to have to switch back and forth between the userform and excel. A multi-column listbox doesn't offer the formatting options I need and I really, really don't want to use text boxes as they will take forever to set up and be difficult to manage down the road. This would be perfect if I could get it to populate.

 

[Norie]

Have you tried adding a sheet reference.

Private Sub UserForm_Initialize()

    Spreadsheet1.Sheets("Sheet1").Range("A1").Value = Worksheets("Input").Range("Date").Value

End Sub

PS I can't access the Spreadsheet control anymore - they decided to remove it from the latest versions of Excel apparently so I'm kind of going from memory.

 

[Logit]

Found this resource .. not certain if it will assist you.

https://support.microsoft.com/en-us...e-spreadsheet-web-component-with-visual-basic

https://bytes.com/topic/access/answers/889533-microsoft-office-spreadsheet-11-0-activex-control

 

[vegasfoster]

Have you tried adding a sheet reference.

Private Sub UserForm_Initialize()

    Spreadsheet1.Sheets("Sheet1").Range("A1").Value = Worksheets("Input").Range("Date").Value

End Sub

PS I can't access the Spreadsheet control anymore - they decided to remove it from the latest versions of Excel apparently so I'm kind of going from memory.

Hmmm, that's interesting, then I have to figure out if all the versions can run it. That's very good to know before rolling this thing out, thank you.

 

[vegasfoster]

Found this resource .. not certain if it will assist you.

https://support.microsoft.com/en-us...e-spreadsheet-web-component-with-visual-basic

https://bytes.com/topic/access/answers/889533-microsoft-office-spreadsheet-11-0-activex-control

That was very helpful, make me realize the basic code was correct, so started playing with it and figured out that Initialize() isn't sufficient to populate the cells, have to do something like Click(). I think I come up with workable solution. Haha!

Thank you everyone for your help, I appreciate it, save me huge headache.

 

[Logit]

I was not aware of the Spreadsheet Control 11.0 until you mentioned it. Did a little research and apparently it is something discontinued from ver. 2003 ?

Would you be willing to share your final product so I can see what it is and how it functions in real life ? The few articles I found on the Net made it sound
very interesting.

Thanks.