PDA

View Full Version : Solved: Forms 101... showing range of cells on a user form



GreenTree
03-19-2007, 08:18 AM
In a project I'm working on, I have some flight schedules displayed on one spreadsheet, with several columns of information (departure city, departure time, arrival city, arrival time, flight duration, etc) for each leg of the trip, and as many rows as are required for the trip at hand. Somewhere between two and twenty rows, generally. That info is on one worksheet (lets say in A1:E2 up to A1:E20 depending on the # of legs), and has formatting set to quickly show various relevant things, like destination city vs connection city. What I'd like to do is, while the user is looking at another worksheet, give him a button to click that brings up a userform with exactly what is the schedule at the moment. So, if the schedule has 3 legs, the userform would show A1:E3 (but not all 20 rows), complete with formats. All that the user would then do after looking at it is click OK and close down the form, no interaction required.

I'm totally new to "Visual" programming, so while I can grind the data and get the stuff onto the spreadsheet the way I want it and manipulate the variables involved, getting it into a userform is still a mystery to me. Any help will be much appreciated! (And if I need to just lose the formatting, I could live with that, although keeping the colored text, backgrounds, etc would be optimal if it's possible.)

Thanks,

GT

lucas
03-19-2007, 08:43 AM
Hi GT,
You don't say how you want to retrieve the info but here is an example using a listbox. Look at the listbox properties in the vbe to see how the data is returned to the listbox.....rowsourse and column count are the specific properties you should look at. The sheet1 can be hidden too.

If this is not what your looking for you will have to be more specific.

GreenTree
03-19-2007, 11:14 AM
Thanks! That is doing exactly what I wanted. I'll need to spend some time digging into it all, but the general idea looks spot-on. Is there a way for all of some of the source formatting to show up on the form? Right now, I use several colors for the text and several colors in the backgrounds to make certain information more obvious. Can colors show up on the userform (driven by the source cells), or am I going to be better off just doing everything in plain text?

Thanks again for getting me on the right track here!

Charlize
03-19-2007, 02:07 PM
A little variation.

Charlize

Tommy
03-19-2007, 04:26 PM
A little variation.

Charlize

and another :rotlaugh:

GreenTree
03-19-2007, 05:18 PM
Guys, thanks for the help, although I couldn't seem to get either spreadsheet to run. Probably (almost certainly!) some problem on my end, although I'm at a bit of a loss to know what.

Charlize, I get an error when I run that spreadsheet, "lvwReport" is unknown. Is there an add-in that I should have enabled for that to work?

Tommy, at "UserForm1.Show" I get "Run-time error '438': Object doesn't support this property of method". ??

I appreciate you both taking time to help me with this question!

GT

Charlize
03-20-2007, 01:00 AM
What excel version do you have ? The variation of Tommy adapted to work with a calling function. You must use ALT+F8 and select the macro that needs to be run. For some reason it won't work with the button on sheet2.

Charlize

Charlize
03-20-2007, 01:12 AM
Changed my file a bit (remove a reference of web browser control cause maybe you don't have that).

Charlize

GreenTree
03-20-2007, 02:08 PM
Had a chance to look at Tommy's and Charlize's spreadsheets, and many thanks to you both for taking the time to help out!

I probably wasn't as clear as I could have been about what I have and what I'm trying to get to. The "source" worksheet has all the data and, of particular interest, all the formatting, that I need. The flights already have the colors & so forth on them, and the code that does that is complete & works as I need it to. What I'm hoping to do is display in the userform not just the same text (which the examples Steve & you guys have given me do well -- thanks!), but that same text with the same formatting applied as it has on the source worksheet. The user doesn't need to DO any selecting; the code I already have figures out all the colors etc on its own.

Is there a way to just show a "window" of the one worksheet within the userform? It seems like the commented-out parts of Tommy's code would set the text & background colors of each textbox on the form to the same colors of the cells that it's reading from, which seems like a promising approach (although for some reason I can't seem to get Tommy's code to run -- error message as in my previous post). The # of rows would be pretty fixed (I'm assuming) on the userform, so a 3-line display would have as many boxes as a 12-line display, just 9 rows of them blank... not the worst result, and would be livable if no better option presents itself. I'll need to play with the forms & boxes a bit to find what will work best on my project.

Thanks again for everyone's help. If nobody has thoughts on how to create the "window" to the existing range of cells that shows formatting & all, I'll mark this thread solved in a day or so.

GT

Charlize
03-20-2007, 03:31 PM
What excel version do you have ? The variation of Tommy adapted to work with a calling function. You must use ALT+F8 and select the macro that needs to be run. For some reason it won't work with the button on sheet2.

Charlize
Read my post no 7 and try the attached file ... Perhaps an answer to load the cellformat on a form.

Tommy
03-20-2007, 04:26 PM
I didn't have a property properly qualified. I tried a trick and tripped and busted my nose. :mkay

In this code I am just picking up the formatting in the cell and putting it in the textbox. It can be adjusted for length as well as depth.

Now for the "window" can this be a range? If so No problem, other than fitting it all on the form. Otherwise I think I would be guessing at what there is.:yes

Sub Letssee()
Dim mI As Long, TxtBx As Control
mI = 1
For Each TxtBx In Me.Controls
If InStr(1, TxtBx.Name, "TextBox") > 0 Then
mI = Val(Replace(TxtBx.Name, "TextBox", ""))
TxtBx.Text = ActiveCell.Offset(, mI - 1).Value
TxtBx.BackColor = ActiveCell.Offset(, mI - 1).Interior.Color
TxtBx.ForeColor = ActiveCell.Offset(, mI - 1).Font.Color
' there are more font formatting options
TxtBx.Font.Name = ActiveCell.Offset(, mI - 1).Font.Name
End If
Next
End Sub

GreenTree
03-20-2007, 08:13 PM
Tommy,

That looks very promising, and (when I'm not bleery-eye tired) I'll certainly play with that. I'll also take another look at the file from Charlize; I thought I'd tried the alt-F8 trick with no luck, but I'll give it another go tomorrow. Running Office 2003, fwiw.

Thanks again for the help, all!

GT

Charlize
03-21-2007, 12:59 AM
Tommy,

That looks very promising, and (when I'm not bleery-eye tired) I'll certainly play with that. I'll also take another look at the file from Charlize; I thought I'd tried the alt-F8 trick with no luck, but I'll give it another go tomorrow. Running Office 2003, fwiw.

Thanks again for the help, all!

GT
You'll have to use the file from post no7 to have colors. Tested this on 2002 and 2003. I'll rest my case.

Charlize

GreenTree
03-21-2007, 06:43 PM
Guys,

Thanks to all for the help -- much appreciated!

"Solved"

GT