check off, look up forms - VERY confused

A

Abi

If I was doing this all in Access, I prmoise I wouldn't be bugging you so
much! (unfortunately I'm the only one who has access, and this has to be
shared with others)

Okay, I have a sheet with two columns. One lists the product name and the
other lists the product price. (That's easy)

On another sheet, I'd like to be able to create a lookup for this list. This
is how I'd like it to happen. Someone tell me if I'm off my nut and it can't
be done this way!

Sheet 1: Contains the product list described above.

Sheet 2: Contains a generic form that, say, a salesperson is filling out. It
contains a checkbox selection so that the person can just check off all the
products that a customer would be buying.

Sheet 3: Here's the tricky part. I want all of the selections to list on
this sheet, along with their prices. Now, I know how to use Vlookup to get
the price showing up once the product is there. My two problems are:

(A) How do I get the products that are checked off to list on Sheet 3.
(B) Is it possible to confine X amount of products per sheet? I mean, if a
salesperson checks off 52 products, and I know only 20 will print per page,
can I ensure that only 20 checked off products will go to Sheet 3, 20 to
Sheet 4, and 12 to Sheet 5?
 
D

Dave Peterson

This may not work for you.

First, I'd try to let excel figure out the paging. If you have descriptions
that vary in length (even wrapped text in a single cell), it could be pretty
ugly (and for sure not much fun).

If you have to put headers on a separate worksheet and print that separately,
that would be ok in my book.

I think I'd put checkboxes from the Forms toolbar in column A.
Link each text box to the cell that it's in.
Give that cell a custom format of ;;; (3 semicolons) to hide the true/false

Then give the user a little macro that would delete/hide all the rows that have
a false in column A.

This hides those rows.
Option Explicit
Sub HideRows()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

With ActiveSheet
FirstRow = 3 ' a few header rows
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "A").Value = False Then
'choose one of the following 2 lines
.Rows(iRow).Hidden = True
'.Rows(iRow).Delete
End If
Next iRow
End With
End Sub

'this shows them so that they could make changes, too:
Sub ShowRows()
ActiveSheet.Rows.AutoFit
End Sub


In fact, I think I'd keep a master worksheet (hidden) and give the user a macro
that would copy that sheet -- just so they could start another project:

Option Explicit
Sub MakeNewSheet()
Application.ScreenUpdating = False
With Worksheets("Master")
.Visible = xlSheetVisible
.Copy _
before:=Worksheets(1)
.Visible = xlSheetVeryHidden
End With

With Worksheets(1)
.Name = Format(Now, "yyyymmdd_hhmmss")
End With
Application.ScreenUpdating = True
End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top