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.