Embedding Excel Cells On a Userform

I have a project where various items from many different 'sub-classes' are very widely separated/distributed on a msXL worksheet. :p

To make this project user-friendly, using 'additional controls' I have embedded cells (from another worksheet program - viz: "easyoffice") onto a userform. The idea being that the user can view and input new data for any particular sub-class in a very simple summarized form without having to search through the entire worksheet for the correct column etc. :)

This raw data on the embedded cells then has some initial calculations performed on it, the results are then double-checked by the person inputting the data, and if there are no corrections required, the data can then be written onto the XL worksheet in the relevant columns.

Not everyone can be expected to have these two different spreadsheet programs on their computer so this solution is not going to be much use to friends who want to use my project for themselves. I really need to embed some EXCEL cells, but cant find any reference to such a procedure. ;)

My question is this: How do you embed some XL worksheet cells onto a userform that is to be displayed over the top of the main XL worksheet? :bink:

Quote:

---

Not everyone can be expected to have these two different spreadsheet programs on their computer so this solution is not going to be much use to friends who want to use my project for themselves. I really need to embed some EXCEL cells, but cant find any reference to such a procedure. http://vbaexpress.com/forum/images/smilies/wink2.gif

---

Consider to use the spreadsheet-component which is part of the Office Web-components. You can download the latest version here:
http://www.microsoft.com/downloads/d...displaylang=en

Let us know what You think of this approach,

Kind regards,
Dennis

OK thanx very much Dennis.



Unfortunately this wont work as it requires Office 2003 to use - That's my mistake, I should have been more specific about O/S there. :blush



The problem is that the project is intended as freeware for use by small to medium size amateur sporting clubs that - collectively - will own a wide variety of O/S and would not be able to afford to upgrade simply so they can use it. I had thus arbitrarily decided to set Win98 and Office97 as the minimum O/S required for its' use. :rolleyes:

I COULD perhaps extract the relevant OCX file from easyoffice and include it as part of the project, unfortunately I may then run into all sorts of legal problems :argue as easyoffice is "free for personal use only" and the OCX file is a "non-free" developers tool from Tidestone (It serves the same purpose as the free microsoft download you gave above but can be used on a wider variety of O/S) :bink:

Hi, not an approach I would recommend but you can have an Excel spreadsheet embedded in a form. Not sure which versions have these objects but I have XL2002 which has this. With the form is design view open up the Control toolbox and then right click a blank area of the toolbox and select Additional Controls. Scroll down until you see Microsoft Office Spreadsheet 10.0 and put an x in the box and this control will be added to the control toolbox.

Ive never used this control but like all controls on a Userform it will not 'store' the information so when the User has finished entering data you will need to save to a sheet.

As a suggestion you could simply have an entry sheet with drop-downs etc that the user inputs info into and then clicks a button which populates it to the correct sheet/range. Like a front-end if you like. Call me old fashioned but an embedded worksheet on a form doesnt sit right with me.
:bore

Quote:

---

Originally Posted by parry

open up the Control toolbox and then right click a blank area of the toolbox and select Additional Controls. Scroll down until you see Microsoft Office Spreadsheet 10.0 and put an x in the box and this control will be added to the control toolbox........(etc)

.......As a suggestion you could simply have an entry sheet with drop-downs etc that the user inputs info into and then clicks a button which populates it to the correct sheet/range. Like a front-end if you like. Call me old fashioned but an embedded worksheet on a form doesnt sit right with me.
:bore

---

NOPE...Doesn't work for me on Office97, all I get is 'Tidestone Formula One 6.1 Workbook' which I must admit DOES do the job but leaves unanswered the unauthorised use Vs. licensing/payment problems (mentioned in my last post) for what I intended to be used as a freebie. But thanx for the input anyway. :rolleyes:

I know what you mean by the last bit. I did try several other options first and got various ppl to 'test-run' them. For speed, ease and simplicity for the end-user, this type of solution really does appear to be the best option for the front-end. :)
(N.B. If they know what they're doing they can (as I do) simply use the spread-sheet to make their entries, but I know that most of the ppl using it - being part-time volunteers - will not know very much about what they're doing and need to be presented with the option of a much smaller range of columns and cells to search through/choose from to make their entries...Yet still presented in a s/s form to teach them so that they can eventually learn to bypass this function altogether and read and use the main s/s for entries) :bink:

If you are just looking at data entry you could also look into using other controls such as the listview or grid controls.

:)

Hi,

AFAIK:
OWC does not require You to have Office 2003 installed.
Using the OWC-spreadsheet seems to work for me with XL 97.

Q: Have You really tested it?

parry,
You refer to the same control as I'm discussing.

Q: Why do You not recommend using a OWC-control in a userform?

Yes, it's not a storage-place but it is not required as there exist several options for storing data, on a sheet, in a textfile and in the windows-registry.

Kind regards,
Dennis

Hi Dennis,

Just wondering if you tested on a separate PC that only has Excel 97 installed or whether you have them all installed on one machine?

:)

Quote:

---

Originally Posted by mark007

Hi Dennis,

Just wondering if you tested on a separate PC that only has Excel 97 installed or whether you have them all installed on one machine?

:)

---

Mark007,

I have the priviligies to have several computers whereof 1 x P2 350 Mhz which runs some old goodies like XL 97 and Quattro Pro for DOS. The only problem in this context is that I also has installed VB.NET on this one for explicit working with XL 97.

In addition I also got a Linux-computer that runs with Debian and most of the available spreadsheets for Linux. For several reasons this is my favourite-computer :rofl :rofl

Kind regards,
Dennis

Quote:

---

Originally Posted by Dennis W

Hi,

AFAIK:
OWC does not require You to have Office 2003 installed.
Using the OWC-spreadsheet seems to work for me with XL 97.

Q: Have You really tested it?

Kind regards,
Dennis

---

Yes, sorry, I should have said that I did download and try it anyway. The actual message I got when trying to install was that it required a later version of WINDOWS (not Office) - I am using Win98SE. But will try again anyway. :bink:

As mentioned, because of the wide-spread use of Win98 & Office97 I set Win98 as the benchmark for the minimum O/S required.

It's looking more and more like I might have to put a bandanna on my head, a parrot on my shoulder and wear an eye-patch (if you get the drift of what I mean?)... :D

Muchly thanking all for your efforts anyway

Dennis, just curious...

I've not tried to download the OWC 2003 from that link you provided, but I'm on Office 2002, and there are (of course) OWC 10.0 controls already on my machine. Have they changed the OWC Spreadsheet control for 2003 to your knowledge, or are they basically the same thing.

I'm guessing that they are basically the same, but I wondered if you knew the difference(s) if there are any?

Hi,

I?m on Windows 2000 on the old machine so this might explain it.

I will see if I can test in with Win98SE as well.

Mike,
AFAIK, no particular differences.

Kind regards,
Dennis

Putting an OWC-spreadsheet an a form work fine on WIN98SE, Office 2000, and OWC 9.0
Unfortuanatly I'm not able to try with XL97.

BR
Tommy Bak

Quote:

---

Originally Posted by tommy bak

Putting an OWC-spreadsheet an a form work fine on WIN98SE, Office 2000, and OWC 9.0
Unfortuanatly I'm not able to try with XL97.

BR
Tommy Bak

---

The download I got from the microsoft site was OWC 11.0, if there was an earlier version (e.g. OWC 9.0) it could very possibly work for me also!! :)

Cant find an earlier version on the ms site - any ideas where I can download it? :bink:

Quote:

---

Originally Posted by Dennis W

parry,
You refer to the same control as I'm discussing.

Q: Why do You not recommend using a OWC-control in a userform?

Yes, it's not a storage-place but it is not required as there exist several options for storing data, on a sheet, in a textfile and in the windows-registry.

Kind regards,
Dennis

---

Hi Dennis, Im questioning the practical application in using this type of control as Im not clear on what the subclasses is all about. No biggie.

Quote:

---

Originally Posted by parry

Hi Dennis, Im questioning the practical application in using this type of control as Im not clear on what the subclasses is all about. No biggie.

---

Sigh...OK Parry, don't say you didn't ask for it! :p :yes

I'm sure there would be many other applications that would be best served in a similar manner, but in this project it's to do with a club recorder inputting weekly archery scores and checking all these for any possible records. :help

There are many different types of archery "rounds" (SUB-SUB-CLASSES) and, as an example, a PART of a round (a SUB-SUB-SUB-CLASS) may include i) 30 shots @ 50m on either a "big" or "small" face, ii) 36 shots @ 50m on either a "big" or "small" face. :)

The recorder is thus faced with four separate columns for what appears (at a quick glance) to be the same thing - in addition to this, a round may consist of shooting the SAME combination of distance and target face 2, 3, or 4 times - these 2, 3, 4 scores have to be recorded separately and we actually end up having the recorder (in this example alone) faced with SIXTEEN almost identical column headings to choose from to enter their data and check for records - a nightmare scenario for any novice. :wot :eek: :bawl

This can be alleviated by having two spread-sheets - one for 30 and one for 36 shots (as these are entirely different CLASSES of records), and fully resolved by grouping various types of rounds on each s/s into a smaller number of SUB-CLASSES to choose from e.g. 1) National/International rounds 2) "Clout", and to make it acceptable for all clubs, 3) Junior and 4) various "Customized" rounds. :bink:

In addition to all this...the recorder is actually working from score-sheets that have the 'Name' of the round, a date, 4 sub-totals and a total. But for record purposes this has to be analyzed a little differently. Unfortunately, there are too many rounds to just 'click a named button' for a round - this method doesn't permit customization either - and we also need to compare the sub-totals against any identical sub-sub-sub-classes that are also shot in a differently named round. :help :bore

So....for this project, the recorder only have to enter the date & the 4 scores for the distances on the small 'front-end' s/s embedded on the form (a much smaller selection of column headings), a sub then reads this and presents it to them in the same 'form' they are working from (Name of the round, a date, 4 sub-totals and a total) and if this tallies correctly with the score-sheet in front of them, ALL this data is then used to populate the s/s and search for records. :bink: :bink:

I hope this satisfies your curiousity Parry?
Regards, John :hi: