if statement for populating data

**idonaldson** · 01-24-2012, 05:20 PM

Hello,

First post, long time lurker. I've found many answers to numerous questions over the years on this great site, but I finally have one I can't seem to find a solution for.

I have 2 sheets in this work book. Sheet 1 is a form for receiving in material, and sheet 2 is layed out to print Avery 5978 (2X4) shipping labels with info from sheet 1. I need to be able to determine which shipping labels on the page to print. My first thoughts, coming from java programming, would be some kind of If..then statement like the following:

If(Sheet1!O5 != Blank) {
Sheet2!A1:B6 = visible
}

Which would check all 10 cells for some sort of mark, probably just an X.

On sheet 1 I just have 5x2 cells marked out, and if there is an X in the box, that should allow that corresponding label to get printed off of sheet 2. I was trying to tie this all to a button and grid on sheet 1. I have the button that will print out sheet 2, but it currently prints all the data.

Any thoughts?

**Leith Ross** · 01-24-2012, 07:54 PM

Hello idonaldson,

Welcome to the Forum!

It would help to see the worksheet layout. If you have a workbook you can post that would be best. If not, post a picture of the worksheet.

**idonaldson** · 01-25-2012, 09:07 AM

Here is the workbook. There is some code in the vba editor to print the label page from the button.

Been thinking about this for the last hour or so, and I think I've simplified the problem/solution down to this:

On sheet 1 i have a block of 10 cells 2 x 5. If there is an x in one of the cells the corresponding range of cells on sheet 2 need to have their text in black. if there isn't an x, text color should be white.

The only issue I have is I am very limited on my VBA coding skills, and not sure how to go about getting conditional statements to work in vba.

Thanks again for any help

**idonaldson** · 01-25-2012, 05:17 PM

Let me know if I'm going about this the wrong way, but the easiest way I can think of getting this down would be something like:

IF (Sheet1!K5 == 'x') THEN
Labels!:A1:B6.Color = "BLACK"

It will have 10 if statements for the block of 10.
My problem is converting this to acceptable and runable VBA.

**Leith Ross** · 01-25-2012, 06:01 PM

Hello idonaldson,

I am working on several projects concurrently. I just downloaded the workbook and am looking it over now.

**idonaldson** · 01-25-2012, 06:17 PM

What I have right now is

If UCase(Range("K5").Value) = "X" Then
    MsgBox Sheets("Labels").Range("B2").Value
End If

Which works, and I was just using MsgBox to see that my logic was working, now I'm trying to get it to change the font color on the Labels sheet if X is present.
I was trying to get

Sheets.("Labels").Range("A1:B6").Font.Color = RGB(0,0,0)

to work, but the syntax isn't correct.

Thanks again

**idonaldson** · 01-25-2012, 06:50 PM

I have came up with a solution, definitely doesn't get the elegant ribbon, but it gets the job done.


Sub Button3_Click()
    Worksheets("Labels").Range("A1:E34").Font.Color = RGB(255, 255, 255)
    If UCase(Range("k5").Value) = "X" Then
        Worksheets("Labels").Range("A1:B6").Font.Color = RGB(0, 0, 0)
    End If
    If UCase(Range("k7").Value) = "X" Then
        Worksheets("Labels").Range("A8:B13").Font.Color = RGB(0, 0, 0)
    End If
    If UCase(Range("k9").Value) = "X" Then
        Worksheets("Labels").Range("A15:B20").Font.Color = RGB(0, 0, 0)
    End If
    If UCase(Range("k11").Value) = "X" Then
        Worksheets("Labels").Range("A22:B27").Font.Color = RGB(0, 0, 0)
    End If
    If UCase(Range("k13").Value) = "X" Then
        Worksheets("Labels").Range("A29:B34").Font.Color = RGB(0, 0, 0)
    End If
    If UCase(Range("M5").Value) = "X" Then
        Worksheets("Labels").Range("D1:E6").Font.Color = RGB(0, 0, 0)
    End If
    If UCase(Range("M7").Value) = "X" Then
        Worksheets("Labels").Range("D8:E13").Font.Color = RGB(0, 0, 0)
    End If
    If UCase(Range("M9").Value) = "X" Then
        Worksheets("Labels").Range("D15:E20").Font.Color = RGB(0, 0, 0)
    End If
    If UCase(Range("M11").Value) = "X" Then
        Worksheets("Labels").Range("D22:E27").Font.Color = RGB(0, 0, 0)
    End If
    If UCase(Range("M13").Value) = "X" Then
        Worksheets("Labels").Range("D29:E34").Font.Color = RGB(0, 0, 0)
    End If
    
    Dim dtWaitingtime As Date
    Dim dtHr As Date, dtMnt As Date, dtSec As Date
   
    Application.ScreenUpdating = False
    With Worksheets("Labels")
     .Visible = -1                ' <- make your hidden worksheet 'visible'
     .PageSetup.PrintArea = "$A$1:$E$34"
     .PrintOut Copies:=1, Collate:=True
    End With
   
    ' Wait 1 second
    dtHr = Hour(Now())
    dtMnt = Minute(Now())
    dtSec = Second(Now()) + 1
    dtWaitingtime = TimeSerial(dtHr, dtMnt, dtSec)
    Application.Wait dtWaitingtime
   
    Worksheets("Labels").Visible = 0   '<- Hide your worksheet again
    Application.ScreenUpdating = True  ' <- and turn the screenupdating back on
    
    Worksheets("Labels").Range("A1:E34").Font.Color = RGB(255, 255, 255)
End Sub

**Leith Ross** · 01-25-2012, 07:55 PM

Hello idonaldson,

I made a few changes to the workbook. I created 10 named ranges for each label. Labels 1 to 5 are in columns "A:B" and 6 to 10 in columns "D:E". I changed the formatting of the cells in the Labels to Print table. The font is Arial 20 point and centered in the cell. The user must type an "X" in the cell. Case is ignored. Here is the macro that has been added to the "Print Labels" button.


Sub PrintLabels()

    Dim I As Long, J As Long
    Dim LabelInfo As Variant
    Dim N As Long
    Dim Wks As Worksheet
    
        Set Wks = Worksheets("Sheet1")
        
        LabelInfo = Wks.Range("D4:D8").Value
        
        Worksheets("Labels").Range("B:B,E:E").ClearContents
        
        For I = 11 To 13 Step 2
            For J = 5 To 13 Step 2
                N = (((I - 11) / 2) * 5) + ((J - 5) / 2) + 1
                If UCase(Wks.Cells(J, I)) = "X" Then
                   Range("Label" & N).Columns(2).Value = LabelInfo
                End If
            Next J
        Next I
    
End Sub