inputbox validate against list in worksheet

[SCOTTWHITTAKER2333]

I have a workbook open macro that I have been using for a while and tweaking every now and then but I am having truble with trying to check if data entered into an input box is in a range of cells on the sheet and if it is not then add it to the next blank spot in that range (the last 3 or 4 are empty right now)
Currecntly I am stuck and getting a Method Range of object_Global Failed error
I get the feeling that I am missing something simple but can't seem to put my finger on it.
Here is what the code looks like right now:

Private Sub Workbook_Open()
' saveasauto Macro
' Macro recorded 5/28/2010 by SCOTT.WHITTAKER
'      (tweaked by HalfAce sometime later...)
 
Dim SKUa$, Shift$, MeaName$, fName$, NewSKU$, Newwt$, Newwttype$
GiveMeAName:
If Worksheets("Bowls").Range("R14") = "" Then
  SKUa = InputBox("Enter the product number")
  If SKUa <> Range("A133") Or Range("134") Or Range("135") Or Range("136") Or Range("137") Or Range("138") Or Range("139") Or Range("140") Or Range("141") Or Range("142") Then
  If MsgBox("This product Number is not currently on the net weight form" & vbCr & vbCr & _
      "do you want to temporarly add a new product?, Note: Please contact QA mgmt to have a new product added perminately.", vbYesNo) = vbNo Then
      MsgBox "You must now go back to the start!!!"
      GoTo GiveMeAName
      End If
  Else
  MsgBox "Please make sure you answer the following questions accurately!!!"
  NewSKU = InputBox("What is the product ID number?")
  Newwt = InputBox("What is the weight? examples, 8.00 or 7.50")
  Newwttype = InputBox("What is the measurement type? examples, oz or lbs")
 End If
  Shift = InputBox("Enter your shift:")
   If Len(Shift) > 1 Then
         MsgBox "you may enter only the number for the shift, DO NOT add (st), (nd) or (rd) after the number. YOU MUST NOW START OVER!!!"
         GoTo GiveMeAName
    End If
   If Len(SKUa) = 0 Or Len(Shift) = 0 Then
    If MsgBox("Can't save this file without an SKU and a Shift entered." & vbCr & vbCr & _
      "Want to try again?", vbYesNo) = vbYes Then
      GoTo GiveMeAName
    Else
       ThisWorkbook.Close False
    End If
  End If
  MeaName = Shift & "-" & SKUa & "-" & "-" & "Net wts" & Format(Now(), "mm-dd-yy") & ".xls"
  fName = ThisWorkbook.Path & "\" & MeaName
  If Dir(fName, vbDirectory) <> "" Then
    MsgBox "A file named '" & MeaName & " already exists." & vbCr & vbCr & _
    MeaName & " will now open."
    Workbooks.Open fName
    ThisWorkbook.Close False
    Exit Sub
  End If
  ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & MeaName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
 
    Worksheets("Bowls").Range("R14") = SKUa
    Worksheets("Bowls").Range("F1") = Shift
  ActiveSheet.Unprotect Password:="qalead"
  Worksheets("Bowls").Range("A143") = NewSKU
  Worksheets("Bowls").Range("B143") = Newwt
  Worksheets("Bowls").Range("C143") = Newwttype
  Worksheets("Bowls").Range("D143") = 1
  ActiveSheet.protect Password:="qalead", DrawingObjects:=False, Contents:=True, Scenarios:= _
        False
 End If
 End Sub

Any help would be great
Oh ya the problem seems to be this part:
If SKUa <> Range("A133") Or Range("134") Or Range("135") Or Range("136") Or Range("137") Or Range("138") Or Range("139") Or Range("140") Or Range("141") Or Range("142") Then

 

[MisterCrash]

Try starting your procedure like this:

Private Sub Workbook_Open()
' saveasauto Macro
' Macro recorded 5/28/2010 by SCOTT.WHITTAKER
'      (tweaked by HalfAce sometime later...)
 
Dim SKUa$, Shift$, MeaName$, fName$, NewSKU$, Newwt$, Newwttype$
Dim rngCheckRange as Range
GiveMeAName:
If Worksheets("Bowls").Range("R14") = "" Then
  Set rngCheckRange = Range("A133:A142")
  SKUa = InputBox("Enter the product number")
  If Application.WorksheetFunction.CountIf(rngCheckRange, SKUa) = 0 Then

 

[SCOTTWHITTAKER2333]

I tried putting that in but it doesn't seem to be working correctly.
The code looks as follows:

Private Sub Workbook_Open()
' saveasauto Macro
' Macro recorded 5/28/2010 by SCOTT.WHITTAKER
'      (tweaked by HalfAce sometime later...)
      
Dim SKUa$, Shift$, MeaName$, fName$, NewSKU$, Newwt$, Newwttype$
Dim rngCheckRange As Range
GiveMeAName:
If Worksheets("Bowls").Range("R14") = "" Then
  Set rngCheckRange = Worksheets("Bowls").Range("A133:A142")
  SKUa = InputBox("Enter the product number")
  If Application.WorksheetFunction.CountIf(rngCheckRange, SKUa) = 0 Then
   If MsgBox("This product Number is not currently on the net weight form. Do you want to temporarly add a new product?, Note: Please contact QA mgmt to have a new product added perminately.", vbYesNo) = vbNo Then
      MsgBox "You must now go back to the start!!!"
      GoTo GiveMeAName
      End If
  Else
  MsgBox "Please make sure you answer the following questions accurately!!!"
  NewSKU = InputBox("What is the product ID number?")
  Newwt = InputBox("What is the weight? examples, 8.00 or 7.50")
  Newwttype = InputBox("What is the measurement type? examples, oz or lbs")
 End If
  Shift = InputBox("Enter your shift:")
   If Len(Shift) > 1 Then
         MsgBox "you may enter only the number for the shift, DO NOT add (st), (nd) or (rd) after the number. YOU MUST NOW START OVER!!!"
         GoTo GiveMeAName
    End If
   If Len(SKUa) = 0 Or Len(Shift) = 0 Then
    If MsgBox("Can't save this file without an SKU and a Shift entered." & vbCr & vbCr & _
      "Want to try again?", vbYesNo) = vbYes Then
      GoTo GiveMeAName
    Else
       ThisWorkbook.Close False
    End If
  End If
  MeaName = Shift & "-" & SKUa & "-" & "-" & "Net wts" & Format(Now(), "mm-dd-yy") & ".xls"
  fName = ThisWorkbook.Path & "\" & MeaName
  If Dir(fName, vbDirectory) <> "" Then
    MsgBox "A file named '" & MeaName & " already exists." & vbCr & vbCr & _
    MeaName & " will now open."
    Workbooks.Open fName
    ThisWorkbook.Close False
    Exit Sub
  End If
  ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & MeaName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
 
    Worksheets("Bowls").Range("R14") = SKUa
    Worksheets("Bowls").Range("F1") = Shift
  ActiveSheet.Unprotect Password:="*****"
  Worksheets("Bowls").Range("A143") = NewSKU
  Worksheets("Bowls").Range("B143") = Newwt
  Worksheets("Bowls").Range("C143") = Newwttype
  Worksheets("Bowls").Range("D143") = 1
  ActiveSheet.protect Password:="*****", DrawingObjects:=False, Contents:=True, Scenarios:= _
        False
 End If
 End Sub

The currect Issue is that it is not recognizing product #'s that are in the range. (it is skipping straight to the section for entering a new product id.I thought that maybe the cells were not formated correctly but that wasn't it.
Any Ideas?

 

[SCOTTWHITTAKER2333]

Anybody?

 

[MisterCrash]

Honestly, given what you are trying to do, it would probably be more worthwhile to set up a UserForm for entry rather than a series of InputBoxes. You'll be able to control the flow of information much better that way.

Send me a PM and I can walk you through it.

 

[SCOTTWHITTAKER2333]

You were right. The userform was exactly what I needed to do. I had never actually used one before however there was no need to walk me through it. I have figured it out. Thanks though. The Userforms combo box was exact;y what I needed for the "sku#".
Thanks again.