Solved

Embed selected cells into a user form

Posted on 2013-11-14
10
262 Views
Last Modified: 2013-11-15
Folks,
I have attached a workbook with a worksheet that I would like to embed into a form but only the cells with data. Any ideas?
Book1.xlsm
0
Comment
Question by:Frank Freese
  • 7
  • 3
10 Comments
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39649996
You have to install Office Web Component 11 to do that. OWC11 was supposed to be used with Office 2003 but it works fine in 32-bit Office 2007. I have not try it with Office 2010 yet.

Once OWC11 is installed, in the VBA Editor, go to Tools -> References. Make sure Microsoft Office Web Components is enabled.

On the user form, right-click on the Controls Toolbox, and select Additional Controls. Scroll down to enable Microsoft Office Spreadsheet.

You should now be able to embed Spreadsheet in user forms.
0
 

Author Comment

by:Frank Freese
ID: 39650070
This might be too much of Excel for my users. It's a great control though.
Here's what I was looking for. A control or some VBA that would allow me to embed into a user form the contents (Range XX:YY) ,without the gridlines, from a worksheet.  This does not mean the Web Control won't do this because I've not worked with it before.
Let's see if others have another option for me.
0
 

Author Comment

by:Frank Freese
ID: 39651467
Folks,
My research showed that Office Web Component 11 has to be installed on each machine to work. If that's the case then I will need a different option(s).
I tried capturing what I wanted from Excel as a jpg file and embedding it into a text control but it really looks "bad".
What I'm asking may not be possible. If that's the case then I could simply hyperlink to another worksheet in the workbook where the information resides.
I thank all for you're consideration and input, as always.
0
 

Author Comment

by:Frank Freese
ID: 39651569
Joop.
The multicolumn listbox is worth investigating, however, the formatting is very important part to make the user better understand. Loosing that would confuse them. You can look at the embeeded file I included in this thread to see what I mean.
If there was just a control that allowed one to copy and paste into the control on the user form without loosing formatting with scroll bars would be great.  
There may not be such a control - I'm looking for one though, or VBA.
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39651773
fh_freese,

I have created an image of the info you want to show on a user form, then, I have created a userform with a frame inserted. Then put the picture into the frame. Add scroll bar to the frame.

Can you check if it is good enough for you?
Book1-with-UserForm.xlsm
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Frank Freese
ID: 39651817
HarryHYLee,
Stupid me!!!!!! I forgot about the frame.....this will work.
I really appreciate you hanging in there with me!
Thanks - points well earned.
0
 

Author Closing Comment

by:Frank Freese
ID: 39651818
perfect - great job!
0
 

Author Comment

by:Frank Freese
ID: 39651853
HarryHYLee,
By the way, what did you use to capture the image?
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39651974
1) What I did was change the column width, and row height of the columns and rows around the area you want to show.

2) Set border for better looking, Then, select the area that surround the info you want to show. (Make sure you select one row above and one column left of the area you want to capture the borders.

3) Copy using Ctrl-C. Open up a photo editor (I used Paint). Enlarge the picture so that the whole copied area can show properly on the picture. Paste the clipboard into onto the image.

4) Save it as JPEG.

5) Go back to Excel and use VBA Editor to create a UserForm. Again, make sure the UserForm is big enough. Insert a Frame. Enlarge the frame to at least the width of the image + space for a vertical scroll bar.

6) Insert Picture inside the frame, and insert a scroll bar inside the frame.

7) Finally, setup code of the scroll bar to scroll the image inside the frame.
0
 

Author Comment

by:Frank Freese
ID: 39651993
thanks for the follow-up
have a safe weekend
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now