Search code examples
excelvbaif-statementlookup-tables

If statement skipping internal code

First off this code is under an NDA, and I cannot show much more. I am trying to get a star to show up on a sheet either as black or yellow. when the code gets past the sheet selection it skips back to the main code. I have tried another if then and the current Case formats. The code skips no matter what is in the cell it is suppose to look at. I put numbers, true, and false. The entire program is based on look up tables, and I am just trying to add code for the stars. Any help will be great.

  Sub Star()

  Range(Cells(nxtRow, "B"), Cells(nxtRow + 1, "B")).Select
  With Selection
    .MergeCells = True
  End With

   '8.3x11 sheet
    If (p_size = 1) Then
      y = 171.25 + 43.5 * Mtimes
        ActiveSheet.Shapes.AddShape(msoShape5pointStar, 23.25, y, 22, 
        _22).Select
     Select Case Cells(i, "s").Value

    'chooses yellow

        Case True
            Selection.ShapeRange.Fill.Visible = msoTrue
            Selection.ShapeRange.Fill.Solid
            Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13
            Selection.ShapeRange.Fill.Transparency = 0#
            Selection.ShapeRange.Line.Weight = 0.75
            Selection.ShapeRange.Line.DashStyle = msoLineSolid
            Selection.ShapeRange.Line.Style = msoLineSingle
            Selection.ShapeRange.Line.Transparency = 0#
            Selection.ShapeRange.Line.Visible = msoTrue
            Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
            Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)

    'chooses black

        Case False
            Selection.ShapeRange.Fill.Visible = msoTrue
            Selection.ShapeRange.Fill.Solid
            Selection.ShapeRange.Fill.ForeColor.SchemeColor = 1
            Selection.ShapeRange.Fill.Transparency = 0#
            Selection.ShapeRange.Line.Weight = 0.75
            Selection.ShapeRange.Line.DashStyle = msoLineSolid
            Selection.ShapeRange.Line.Style = msoLineSingle
            Selection.ShapeRange.Line.Transparency = 0#
            Selection.ShapeRange.Line.Visible = msoTrue
            Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
            Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)

        Case Else: MsgBox ("star color indeterminate")
        End Select


'11x17 sheet
    ElseIf (p_size = 2) Then
     y = 160 + 33 * Mtimes
    ActiveSheet.Shapes.AddShape(msoShape5pointStar, 48#, y, 22, 22).Select

    Select Case Cells(i, "s").Value

    'chooses yellow

        Case True
            Selection.ShapeRange.Fill.Visible = msoTrue
            Selection.ShapeRange.Fill.Solid
            Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13
            Selection.ShapeRange.Fill.Transparency = 0#
            Selection.ShapeRange.Line.Weight = 0.75
            Selection.ShapeRange.Line.DashStyle = msoLineSolid
            Selection.ShapeRange.Line.Style = msoLineSingle
            Selection.ShapeRange.Line.Transparency = 0#
            Selection.ShapeRange.Line.Visible = msoTrue
            Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
            Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)

    'chooses black

        Case False
            Selection.ShapeRange.Fill.Visible = msoTrue
            Selection.ShapeRange.Fill.Solid
            Selection.ShapeRange.Fill.ForeColor.SchemeColor = 1
            Selection.ShapeRange.Fill.Transparency = 0#
            Selection.ShapeRange.Line.Weight = 0.75
            Selection.ShapeRange.Line.DashStyle = msoLineSolid
            Selection.ShapeRange.Line.Style = msoLineSingle
            Selection.ShapeRange.Line.Transparency = 0#
            Selection.ShapeRange.Line.Visible = msoTrue
            Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
            Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)

        Case Else: MsgBox ("star color indeterminate")
        End Select

    End If

End Sub

Final fixed code. With Wookiee's help I got it to color correctly. So, I made 'i' a variable that is collected, and that fixed the skipping. I added worksheet ('d") to actually color the stars correctly by looking at the right spot. 

  Sub Star(i)

  Range(Cells(nxtRow, "B"), Cells(nxtRow + 1, "B")).Select
With Selection
    .MergeCells = True
  End With

'8.3x11 sheet
  If (p_size = 1) Then
    y = 171.25 + 43.5 * Mtimes
    ActiveSheet.Shapes.AddShape(msoShape5pointStar, 23.25, y, 
22, 22).Select
    Select Case Sheets("d").Cells(i, "s").Value

    'chooses yellow

        Case True
            Selection.ShapeRange.Fill.Visible = msoTrue
            Selection.ShapeRange.Fill.Solid
            Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13
            Selection.ShapeRange.Fill.Transparency = 0#
            Selection.ShapeRange.Line.Weight = 0.75
            Selection.ShapeRange.Line.DashStyle = msoLineSolid
            Selection.ShapeRange.Line.Style = msoLineSingle
            Selection.ShapeRange.Line.Transparency = 0#
            Selection.ShapeRange.Line.Visible = msoTrue
            Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
            Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 
 255, 255)

    'chooses black

        Case False
            Selection.ShapeRange.Fill.Visible = msoTrue
            Selection.ShapeRange.Fill.Solid
            Selection.ShapeRange.Fill.ForeColor.SchemeColor = 0
            Selection.ShapeRange.Fill.Transparency = 0#
            Selection.ShapeRange.Line.Weight = 0.75
            Selection.ShapeRange.Line.DashStyle = msoLineSolid
            Selection.ShapeRange.Line.Style = msoLineSingle
            Selection.ShapeRange.Line.Transparency = 0#
            Selection.ShapeRange.Line.Visible = msoTrue
            Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
            Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)

        Case Else: MsgBox ("star color not found")
        End Select


'11x17 sheet
ElseIf (p_size = 2) Then
    y = 160 + 33 * Mtimes
    ActiveSheet.Shapes.AddShape(msoShape5pointStar, 48#, y, 
  22, 22).Select

    Select Case Sheets("d").Cells(i, "s").Value

    'chooses yellow

        Case True
            Selection.ShapeRange.Fill.Visible = msoTrue
            Selection.ShapeRange.Fill.Solid
            Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13
            Selection.ShapeRange.Fill.Transparency = 0#
            Selection.ShapeRange.Line.Weight = 0.75
            Selection.ShapeRange.Line.DashStyle = msoLineSolid
            Selection.ShapeRange.Line.Style = msoLineSingle
            Selection.ShapeRange.Line.Transparency = 0#
            Selection.ShapeRange.Line.Visible = msoTrue
            Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
            Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 
  255, 255)

    'chooses black

        Case False
            Selection.ShapeRange.Fill.Visible = msoTrue
            Selection.ShapeRange.Fill.Solid
            Selection.ShapeRange.Fill.ForeColor.SchemeColor = 0
            Selection.ShapeRange.Fill.Transparency = 0#
            Selection.ShapeRange.Line.Weight = 0.75
            Selection.ShapeRange.Line.DashStyle = msoLineSolid
            Selection.ShapeRange.Line.Style = msoLineSingle
            Selection.ShapeRange.Line.Transparency = 0#
            Selection.ShapeRange.Line.Visible = msoTrue
            Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
            Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)

        Case Else: MsgBox ("star color not found")
        End Select
     Else: MsgBox ("paper size not found")
    End If

End Sub
Solution

  • Check the value of your "i" variable in your two lines:

    Select Case Cells(i, "s").Value

    It's possible that it's not in the range you are expecting.