Script or VBA Code to Read Thru Excel rows and... 2

[JohnBates]

Hi VBA experts,

I'll try to explain what I want to do:

I have 36 Excel worksheets. Lets name these WS2.xls thru WS37.

I want to create another Excel worksheet that contains information about each employee - 1 row per employee. The row will have checkboxes for various employee categories.
Lets call this WS1.


QUESTION:
How can I read each row in WS1 and ... based on which columns have checkboxes, print one of the other worksheets (WS2 or WS3 or ...) ?

Thanks for any ideas.
John

 

[Zathras]

Assuming you're using the Control Toolbox to get the checkbox, all you need to do is have two columns on WS1. Link each checkbox to a distinct cell in the first column and put the worksheet name in the second column on the same row. Then write a macro to loop thru the first column and wherever you find "TRUE", use the corresponding sheet name and call the printing subroutine to print that sheet.

 

[SkipVought]

Hi,

Checkboxes are not directly column or row related, although you can use the TopLeftCell or BottomRightCell properties. The procedure will either loop thru a range of cells or a collection of controls.

So is the data in cells or is the data in check boxes -- and if checkboxes, are these boxes Form controls or Toolbox controls?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Skip: Not sure what you mean by ...Checkboxes are not directly column or row related...

By setting the LinkedCell property of the check box, you can directly relate the checkbox to a specific cell (i.e. column and row).

 

[JohnBates]

Thanks SkipVought and Zathras,

Actually Skip, the ws that will contain the data hasn't been created yet - so we can design it either way. If using cells makes it easier, we can forget the checkboxes.
So, if I have a value of 0 or 1 in each of the 6 cells, will that make the project a little easier?

BTW, I want to bypass Access if at all possible, just using VBA and Excel 2000.


Thanks for your time. John

 

[Zathras]

...So, if I have a value of 0 or 1 in each of the 6 cells, will that make the project a little easier?...

I assume you meant each of the 36 cells, and no it would not be any easier, just different. Whether you use check boxes to set the value (to TRUE or FALSE) in the first column of the two columns I described, or whether you manually enter a 1 or zero, the effect is the same: Loop thru the column and if it is TRUE (or 1), then print the sheet named in the second column.

 

[SkipVought]

Z,
Naturally, you are correct. We did not know how the checkboxes were being used. As it turns out, they are not yet being used.

John,
A better question to ask at this point of design is, "What is your objective for this project?" Don't be concerned about cells or checkboxes -- what business objective are you seeking to accomplish?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[JohnBates]

Z...
"I assume you meant each of the 36 cells"

No there will be 6 "input" cells for each row.

example:
C1 C2 C3 C4 C5 C6 Empl#
Row 1 X X 111
Row 2 X X 112
....and so on
Within each row (i.e. employee #)I think I will have 36 possible combinations of the 6 "variables". That's where I get the 36 worksheets.

Skip,
This is a manufacturing plant. Each assembly worker must wear certain safety equipment/clothing, based on their job duties. So, if Col 1 is "Gloves", Empl# 111 is required to wear gloves. Each of the 36 worksheets will reflect whether Gloves, Safety Tape etc are required for that employee. The appropriate box for Gloves, Tape etc is colored if it is a required piece of clothing.

Not a well-educated workforce and many do not speak english very well... so we are going for the visual representations on the colored forms/worksheets.

John

 

[SkipVought]

So an employee is required to have a certain set of equpment. What is the purpose of this workbook with respect to this business case? You are storing this information. What do you need to do with the information?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[JohnBates]

well Skip, the appropriate worksheet (1 of the 36) will be printed and given to each employee.

My plan is to run a VBA process that will read the WS1 rows and print the appropriate worksheet based on that rows values.

Probably not the best design but - as always- they want it ASAP.
Thanks, John

 

[SkipVought]

OK. I understand.

Zathras' last suggestion would probably be the way to go with a quick and dirty approch.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

John: It looks like Skip is going to take care of you on this thread, so I'll bow out. But just one more thing confusues me: With 6 columns (or check boxes), you have the theoretical possiblity of 64 combinations (2^6 = 64). Even if the first check box is only for print/noprint that still is a theoretical possibility of 32 combinations of the remaining 5 columns (2^5 = 32). So where does the number 36 come from?

Also, in terms of over-all design, you should be able to use a single worksheet for the report, just hide rows for those sections where the column is not checked. In other words, the report would have a common section at the top which is always printed and then 5 (or 6?) sections which consist of rows that are hidden or exposed according to whether they should be included in the report. Then, maybe some common section at the bottom to indicate that the entire report has been printed. This way would make maintenance of the report a good deal simpler. No need to make the same change in half a dozen worksheets every time the wording for a section is revised.

For the record, I think using check boxes for the interface is an excellent idea.

 

[SkipVought]

John,
Procedurally, will you make selections for all employees and then print "packets" for all the selections.

Or are you going in an employee at a time?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[JohnBates]

Thanks for the correction Zanthras, yes it would be 64 possible combinations.

Skip, we will not use a one-employee-at-a-time approach. After we get all employees entered into the WS1, we will
then want to print a worksheet for all rows in WS1 (which will be 1600, 1 for ea. employee).

I haven't coded much VBA, but I do use VB 6 alot. Can you help me with the syntax for reading thru all the rows and cells and comparing the checkbox to TRUE/YES ?

Thanks, John

 

[SkipVought]

Sure,

Here's how to set up your sheet

1. using the control toolbox, insert ONE checkbox on your sheet.
2. in the properties window, delete the caption

Here's the code to control the checkbox

Private Sub CheckBox1_Click()
    With CheckBox1
        .TopLeftCell.Value = .Value
        .Visible = False
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Set rng = Application.Intersect(Target, Range(Cells(1, iStartCol), Cells(1, iStartCol + 35)))
    If Not rng Is Nothing Then
        With Target
            CheckBox1.Top = .Top
            CheckBox1.Left = .Left
            CheckBox1.Width = .Width
            CheckBox1.Height = .Height
            CheckBox1.Value = .Value
            CheckBox1.Visible = True
        End With
    End If
End Sub

iStartCol is the starting column for the checkboxes -- change it according to your design.


1. set a range to the cells you want to interrogate. If we assume that your employee range starts in column A row 1 then...

Dim r As Range, rng As Range, lLastRow As Long
lLastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(lLastRow, 1))
For Each r In rng
   'evaluate this row for which forms to print
   For Each c In Range(Cells(r.Row, iStartCol), Cells(r.Row, iStartCol + 35))
      If c.Value Then
         'go print this form

      End If
   Next
Next

That will kind of get you started.

Call back

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Ok. So Skip gets a star for the dancing check box....

With 1600 employees, check boxes are kind of out of the question. Here is a prototype that I think is what you are looking for:

Set up a workbook with two sheets, names "EmployeeList" and "ReportPage"

On the "ReportPage" sheet, put the word "Gloves" in A6 thru A10 and give the rows 6 thru 10 a range name of "GLOVES"

Also on the "ReportPage" sheet, put the word "SafetyTape" in A12 thru A18 and give the rows 12 thru 18 a range name of "SAFETYTAPE"

(Eventually you would set up the remaining 4 ranges in a similar fashion)

On the "EmployeeList" sheet, set up some sample data this way:
[blue]

A1: 'Gloves
B1: 'Safety Tape
C1: 'Thing3
D1: 'Thing4
E1: 'Thing5
F1: 'Thing6
G1: 'Employee #
H1: 'Employee Name
A2: 1
G2: 101
H2: 'John Jones
A3: 1
B3: 1
G3: 102
H3: 'Sam Snead

[/color]
And put the following in a code module:
[blue]

Option Explicit

Sub MainProcess()
Dim r As Range
Dim c As Range
Dim sht As Worksheet
  Sheets("EmployeeList").Activate
  Set sht = ActiveSheet
  Set r = Intersect(sht.UsedRange, Range("G2:G65536"))
  For Each c In r
    Call DoPrintJob(c.Value, c.Offset(0, 1).Value, _
             c.Offset(0, -6).Value, c.Offset(0, -5).Value, _
             c.Offset(0, -4).Value, c.Offset(0, -3).Value, _
             c.Offset(0, -2).Value, c.Offset(0, -1).Value)
    sht.Activate
  Next c
  Set r = Nothing
  Set sht = Nothing
End Sub

Sub DoPrintJob(EmployeeNum As Integer, EmployeeName As String, _
              Gloves As String, SafetyTape As String, Thing3 As String, _
              Thing4 As String, Thing5 As String, Thing6 As String)
  Worksheets("ReportPage").Activate
  [A1] = EmployeeNum
  [B1] = EmployeeName
  Range("GLOVES").EntireRow.Hidden = (Gloves = "")
  Range("SAFETYTAPE").EntireRow.Hidden = (SafetyTape = "")

[green]

  ' Etc. with the other 4 ranges

[/color]

  ActiveSheet.PrintPreview
End Sub

[/color]
When you are ready to roll, change the .PrintPreview to .Print