inputbox validate against list in worksheet

SCOTTWHITTAKER2333

New Member
Joined
Jun 1, 2010
Messages
32
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:
Code:
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

MisterCrash

Active Member
Joined
Jan 27, 2003
Messages
279
Try starting your procedure like this:

Code:
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

New Member
Joined
Jun 1, 2010
Messages
32
I tried putting that in but it doesn't seem to be working correctly.
The code looks as follows:
Code:
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?
 

MisterCrash

Active Member
Joined
Jan 27, 2003
Messages
279
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

New Member
Joined
Jun 1, 2010
Messages
32
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,213
Messages
5,509,871
Members
408,757
Latest member
Jamarr123

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top