Data Validation for a Range

[LordDragon]

Greetings,


I have tried all of the following codes.

The one that is not currently commented out works, sort of, but it gives me the message box, even if I enter a number in the cell.

Private Sub Worksheet_Change(ByVal Target As Range)


    With Range("D2:D39")
        If Not IsNumberic Then
            MsgBox "Enter a number in the quantity field."
        End If
    End With
    


'    If Target.Range = "D2:D39" Then
'        If Not IsNumeric(Target) Then
'            MsgBox "Enter a number in the quantity field."
'        End If
'    End If


'    With Range("D2:D39")
'        .Validation.Add
'        .IgnoreBlank = True
'        .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertWarning
'        .ErrorTitle = "Numeric"
'        .ErrorMessage = "You must enter a number in the quantity field."
'    End With


End Sub

Can I get a little help please?

[SamT]

Private Sub Worksheet_Change(ByVal Target As Range)
 'This structure leaves the Sub open for use with other ranges and other conditions

If Not Intersect(Target, Range("D2:D39")) Is Nothing Then VerifyNumeric Target
     
End Sub

Public Sub VerifyNumeric(Target As Range)
'This sub can be in ThisWorkbook or any Module and called by many subs
  If Not IsNumeric(Target) Then
    MsgBox "Enter a number in the quantity field."
    Target.Value = ""
    Target.Select
  End If
     
End Sub

[LordDragon]

Sam,

Thanks, that almost works.

I have the following code set up for each sheet in my workbook. It is called from a reset button for the given sheets. After testing your code, it worked fine, until I used this button, which then caused a message for each cell in the range that was checked.

Function GeneralUseItemsClear()
'Resets the General Use Items Page to the original unused state.


    Application.ScreenUpdating = False
   
    'Reset the Yes/No field to No on the General Use Items Sheet and clear the Quantity Needed Column
    With ActiveWorkbook.Worksheets("General Use Items")
        .Range("A2:A39").Value = strNo
        .Range("D2:D39").ClearContents
    End With


    Application.ScreenUpdating = True
    
    Application.GoTo Sheets("General Use Items").Range("A1"), True


End Function

Luckily I tested it on a page with only 38 items. Other pages items number in the hundreds. And that same Function is called for each page in the entire workbook when the Reset Workbook button is used. Had I applied this to all the pages, then that numbers in the thousands.

Anyway, any suggestions on how to fix it?

I am using this code to make sure that on all the pages that need it the "Yes" and "No" fields are correct. Maybe this can be modified to cover both the numeric fields (column D on every page in the array) and the Yes/No fields. Or something similar to do the number thing.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Allows the Yes/No fields to accept partial entries and lower case.


    'Declare the variables
    Dim lngRow As Long
    Dim RngToCheck As Range


    'Set the range to check for change.
    Set RngToCheck = Intersect(Sh.Columns(1), Target)


    'No need to do anything if nothing in Column A is changed
    If Not RngToCheck Is Nothing Then
        
        'Determine the sheets to check
        For Each sht In Array(Sheet4, Sheet5, Sheet6, Sheet7, Sheet8, Sheet9, Sheet3, Sheet12, Sheet10, Sheet12, Sheet13, Sheet17, Sheet18)
            If sht Is Sh Then
                Application.ScreenUpdating = False
                Application.EnableEvents = False
      
      'Determine how many rows there are
      lngRow = Application.Max(2, Sh.Range("A" & Sh.Rows.Count).End(xlUp).Row) 'if sheet only has headers then the last row number may have been less than 3.
      
      'Set the acceptance to include lower case and single letters.
      For Each cll In Intersect(Target, Sh.Range("A2:A" & lngRow)).Cells
        Select Case cll.Value
          Case "y", "Y", "yes": cll.Value = strYes
          Case "n", "N", "no": cll.Value = strNo
        End Select
      Next cll
      
      Application.ScreenUpdating = True
        Exit For  'no need to check the rest if one sheet has been found.
        End If
  Next sht
End If


Application.EnableEvents = True


End Sub

[SamT]

Function GeneralUseItemsClear()
'Resets the General Use Items Page to the original unused state.
  Application.ScreenUpdating = False
  Application.EnableEvents = False
     
     'Reset the Yes/No field to No on the General Use Items Sheet and clear the Quantity Needed Column
    With ActiveWorkbook.Worksheets("General Use Items")
        .Range("A2:A39").Value = strNo
        .Range("D2:D39").ClearContents
        .Range("A1").Activate
    End With
     
  Application.ScreenUpdating = True
  Application.EnableEvents = True
End Function

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
 'the Change Event occurs for every cell that is changed on any worksheet
 'This structure leaves the Sub open for use with other ranges and other conditions
      
 'Absolutely prevent this sub if EnableEvents is False. Probably Redundant
 If Not Application.EnableEvents Then Exit Sub
     
 If Not Intersect(Target, sh.Columns(1)) Is Nothing Then VerifyProperCaseYesNo sh, Target

End Sub

Public Function VerifyProperCaseYesNo(sh As Object, Target As Range)
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  
  With sh
    Select Case LCase(Left(Trim(Target.Value), 1))
      Case "y": Target.Value = strYes
      Case "n": Target.Value = strNo
      Case Is <> "": Target.Value = "N/A" 'Throws an error on sheet if anything besides "y/n" is first letter
    End Select
  End With
  
  Application.ScreenUpdating = True
  Application.EnableEvents = True
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
  'This structure leaves the Sub open for use with other ranges and other conditions
  'Absolutely prevent this sub if EnableEvents is False. Probably Redundant
  If Not Application.EnableEvents Then Exit Sub
     
    If Not Intersect(Target, Range("D2:D39")) Is Nothing Then VerifyNumeric Target
     
End Sub

Public Sub VerifyNumeric(Target As Range)
     'This sub can be in ThisWorkbook or any Module and called by many subs
  Application.ScreenUpdating = False
  Application.EnableEvents = False
    
    If Not IsNumeric(Target) Then
        MsgBox "Enter a number in the quantity field."
        Target.Value = ""
        Target.Select
    End If     
End Sub

[LordDragon]

Sam,

Almost everything works. The VerifyProperCaseYesNo function is the part that isn't working.

Let me rephrase that. It works as written and does what the code tells it to. However, I need it to not work on every sheet as there are some that will use Column A for something other than the Yes/No thing. It is also putting "N/A" in the cell if something other than "y" or "n" is entered. I need it to instead put a message up explaining to the user what to do.

I'm going to keep playing with it and see if I can get it working. But I would not turn down some help.

[SamT]

The Select Case checks if the first letter in the Cell is "y," "Y," "n," or "N," or some other character. It will match "Nope" " Y" "Yassuh", "y " and "N"

BTW, This really should be a Sub and not a Function. Changing it won't affect any other code.

Public Function VerifyProperCaseYesNo(sh As Object, Target As Range)

Dim Found As Boolean
Dim Sht As Variant
    Application.ScreenUpdating = False
    Application.EnableEvents = False
     
    For Each Sht In Array(Sheet3, Sheet4, Sheet5, Sheet6, Sheet7, Sheet8, Sheet9, Sheet10, Sheet12, Sheet13, Sheet17, Sheet18)
        If sh Is Sht Then
        Found = True
        Exit For
    Next Sht
    
    If Not Found Then Exit Function

    With sh
        Select Case LCase(Left(Trim(Target.Value), 1))
        Case "y": Target.Value = strYes
        Case "n": Target.Value = strNo
        Case Is <> ""
          MsgBox "Enter " & strYes & " or " & strNo & " in the cell"
          Target.Value = ""
          Target.Select
        End Select
    End With
     
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Function

If it were me, I would lose the WorkBook_SheetChange Sub and just put the operative line from it into the already existing WorkSheet_Change subs in the Worksheets. Then I would lose that atrocious looking "For Each Sht in Array" Structure. Note the "Target" parameters in the two Change Event Subs and the way I handled (not) referencing the Sheet in VerifyNumeric

[LordDragon]

SamT,


Thank you for the help thus far. I seem to be having more problems though.

I tried doing as you suggested and moved the Select Case code to the respective sheet WorkSheet_Change sub. However, it wasn't working at all there.

I managed to get it working on a few pages but was getting errors on others. When it was working, I had the VerifyProperCaseYesNo function, without the Array called from the respective pages using the same code as the VerifyNumeric call code, just slightly modified to that function.

The propblems started when I tried testing some of the hidden pages. These pages are made visible by selecting them from the "System Selection" page. When these pages are called, there are several things that happen with them. Based on some of the other items on the "System Selection" page several cells in Columns A and D are changed by the code. My theory is these changes are what's causing the errors, but I haven't been able to figure out why yet.

I will continue to try to figure it out. In case you have time and would like to continue helping I have attached a copy of my workbook for you to review. Maybe seeing the errors I'm getting and having a chance to review the other code will help.

To help you understand my logic here. There is a module for every page (other than the page itself), they are paired up by number. So Sheet7 uses the code that is in Module7. If there is a generic code that all (or most) of the pages use, they will be in Modules 1 or 2 most likely as these pages don't actually require code. Yes, there are pages that don't have any code and even though the module number for that page exists, it too might be empty.

As far as your suggestion to make the VerifyProperCaseYesNo a Sub instead of a Function. My understanding is that Subs are called by the user (either through buttons, or hot keys) and Functions are called by other code. It is also my understanding that Functions should do one thing and then send the result back to the calling Sub. So if a Sub needs more than one thing done, they should be calling more than one Function to accomplish this.

I am basing this understanding on my research and reading, I have no formal training on VBA coding. Plus what little training I have had never covered Subs vs. Functions.

Thanks again for the help, I will post here again if I solve my problem.

[SamT]

As far as your suggestion to make the VerifyProperCaseYesNo a Sub instead of a Function. My understanding is that Subs are called by the user (either through buttons, or hot keys) and Functions are called by other code. It is also my understanding that Functions should do one thing and then send the result back to the calling Sub. So if a Sub needs more than one thing done, they should be calling more than one Function to accomplish this.

A Function is designed to be used on the right hand side of an "=" sign. It cannot be "called" by a User except in Formulas.

A Sub is designed to be "called" and not to return a value. For this reason, many improperly name it a macro. A Macro is a transcript of a recording of actions taken by the User. A Sub is code written by a coder. it may be based on a Macro.

Generically, a Function Calculates and a Sub Acts. Although a function is often used to perform Actions, that's just a poor programming practice. Both can be "called" in code.

*Call is a VBA Keyword with a very specific purpose.

[LordDragon]

Cool. Thanks for the explanation. I'm learning and developing my understanding of it all from reading and talking to people like you. There isn't much out there (that I've found anyway) about Subs and Functions and the differences or similarities of them. Especially about when to use which.

[SamT]

I managed to get it working on a few pages but was getting errors on others. When it was working, I had the VerifyProperCaseYesNo function, without the Array called from the respective pages using the same code as the VerifyNumeric call code, just slightly modified to that function.

Use this version

Function VerifyProperCaseYesNo(Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
     
' "Target" is a unique Object that only exists in one place in the project.
        'With sh <---remove
            Select Case LCase(Left(Trim(Target.Value), 1))
            Case "y": Target.Value = strYes
            Case "n": Target.Value = strNo
            Case Is <> ""
                MsgBox "Enter " & strYes & " or " & strNo & " in the cell"
                Target.Value = ""
                Target.Select
            End Select
        'End With <---remove
          
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Function

[SamT]

For a beginner, your PasonBOM Project is very impressive, but your programming and design inexperience is showing very badly.

[Paul_Hossler]

There is an error in the CustomUI. It should be TabHome in all the places

<tab id="tabPasonMain" label="Pason Main" insertBeforeMso="TabHome">

I have 'Show User Interface Errors turned on


Also, there's a lot of tabs with only one button. If that's all you might create a Group called Resets and just have a single new Tab

[LordDragon]

Paul,

I was considering moving all the reset buttons, I just haven't been too concerned with it yet. I originally had them all on the Pason Main tab, but moved them because I was thinking about adding the ability to add, remove, and/or reorganize the system pages through buttons on those other tabs. However, since most of my workbook code uses the cell locations (i.e. "A34") instead of cell content to work, I haven't added that, yet.

I think I will follow your advice though and go ahead and move them all to a single tab now. I can always move them again later if I do add the extra features.

[LordDragon]

SamT,

I changed the VerifyProperCaseYesNo function to the latest one you suggested. It works, but now I'm getting an error on the VerifyNumeric code. The Debug button takes me to the function and highlights the Target.Select line. I'm assuming this is because the newly opened page is not the active page at the time the code runs.

I have attempted forcing it to make that page active before calling the function, but that didn't work either. I was also getting the message box as the page was opening, so I commented out the Target.Select line and it ran, but I still got the message box. It occurred once for each cell that was made blank by the code.

This told me that I needed to allow for the cells to be blank, but then also make sure they were numeric if no blank.

So I attempted to put this Select Case code in it's place:

Function VerifyNumeric(Target As Range)
     
     Application.ScreenUpdating = False
     Application.EnableEvents = False
     
     Select Case IsEmpty(Target.Value)
        Case True
            Exit Function
        Case Else
            Select Case IsNumeric(Target.Value)
                Case True
                    Exit Function
                Case Else
                    MsgBox "Enter a number in the quantity field."
            End Select
    End Select
     
     
'    If Not IsNumeric(Target) Then
'        MsgBox "Enter a number in the quantity field."
'        Target.Value = ""
'        Target.Select
'    End If


    Application.ScreenUpdating = True
    Application.EnableEvents = True


End Function

Honestly, I don't have a good grasp on Select Case structures. But it looks to me like this one should work.

[Paul_Hossler]

I think I will follow your advice though and go ahead and move them all to a single tab now. I can always move them again later if I do add the extra features.

I'd just add another group ('Resets') to the main 'Pason' tab

Too many tabs start to fill the ribbon and I find it gets a little unwieldy

Maybe just adding a 'Pason' tab with groups ('Main' 'Reset' 'Order' etc.)

[SamT]

It occurred once for each cell that was made blank by the code.

That sounds like the "cell blanking" code didn't disable Events.

Use this

Function VerifyNumeric(Target As Range)
' Checks if Target is empty, otherwise checks if target is numeric. Also removes 
' any spaces. (an added feature)

    If Trim(Target)  = vbNullString Then Exit Function

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
   If Not IsNumeric(Target.Value) Then
        MsgBox "Enter a number in the quantity field."
        Target.Value = ""
        Application.Goto Target
   
ElseIf Target <> Replace(Target, " ", "") Then Target = Replace(Target, " ", "")
End If
     
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Function

[SamT]

With a master list of Part names and number, correct the numbers in other sheets then highlight the names in other sheets that don't match.

Sub CompareParts()
Dim Sht As Worksheet
Dim Found As Range
Dim Lr As Long
Dim rw As Long

Application.EnableEvents = False
Lr = Cells(Rows.Count, 2).End(xlUp).Row

For Each Sht In Worksheets
  If Sht.Name <> "Master DataList" Then
    With Sheets("Master DataList")
      For rw = 2 To Lr
        Set Found = Sht.UsedRange.Find(.Cells(rw, 2))
          If Not Found Is Nothing Then
          'Correct the Part number
            If Found.Offset(0, 1) <> .Cells(rw, 3) Then _
              Found.Offset(0, 1) = Cells(rw, 3)
          End If
        Set Found = Nothing
        Set Found = Sht.UsedRange.Find(.Cells(rw, 3))
          If Not Found Is Nothing Then
          'Highlight the Part Name
            If Found.Offset(0, -1) <> .Cells(rw, 2) Then _
              Found.Offset(0, -1).Interior.ColorIndex = 3
          End If
        Set Found = Nothing
      Next rw
    End With
  End If
Next Sht
Application.EnableEvents = True
End Sub

[LordDragon]

That sounds like the "cell blanking" code didn't disable Events.

SamT,

I had some issues with my computer so I wasn't able to test that VerifyNumeric code until just now.

It keeps wanting to debug and highlighting the "If Trim(Target) = vbNullString Then Exit Function" line.

I'm not sure what it means, but I've tried moving it to after the EnableEvents is False and commenting it out completely and it doesn't work.

I'll keep researching, but I thought you should know.