Excel Validation Pt II

I have code to change  the  value of certain columns to upper case. I might even name the ranges involved to make it simpler.

Is there any way for the macro or code to execute when the particular cell within that range looses focus? here's the code:

Private Sub UCASE (ByVal Target As Range)
 Dim MyCols As Variant
    Dim i As Integer
    Dim LastRow As Long
    Dim MyRow As Long

    MyCols = Array("I", "L", "O")

Application.ScreenUpdating = False
    For i = LBound(MyCols) To UBound(MyCols)
        LastRow = Cells(Rows.Count, MyCols(i)).End(xlUp).Row
        For MyRow = 1 To LastRow
            Cells(MyRow, MyCols(i)) = UCASE(Cells(MyRow, MyCols(i)))
        Next MyRow
    Next i
Application.ScreenUpdating = True
End Sub
dawber39Database Analyst / Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FarWestCommented:
check this code

Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print "this is the range R" + CStr(Target.Row) + " C" + CStr(Target.Column)

End Sub

Open in new window

0
FarWestCommented:
and this is a complete sample with range checking
Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Range("A1:A30"), Target).Address = Range("A1:A30").Address Then 'assuming A1:A30 is your test range
UCASE Target 
End If
End Sub

Open in new window

0
dawber39Database Analyst / Application DeveloperAuthor Commented:
Will that work with a named range?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

FarWestCommented:
here is a line with named range
If Union(Range("_Tar"), Target).Address = Range("_Tar").Address Then 

Open in new window


please note that I think  UCASE is reserved work and you should  make it a function and rename it
so you can user
Target.Value = myUcase(Target)
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
This is what I have set up so far within some other functions where I think appropriate, that are part of the entire process. I keep getting a Variable not defined error with "Target" highlighted. Do I have to declare that as a variable?


    strCriteria1 = nz(Trim(Range("E3").Value), "")
    strCriteria2 = nz(Trim(Range("E4").Value), "")
   
    If Union(Range("I17:I30"), Target).Address = Range("I17:I30").Address Then
    UCase Target

    ElseIf strCriteria1 = "" Or strCriteria2 = "" Then
        MsgBox "You must select a Sales Org and Doc Type"
        Sheets("Template").Select
        If strCriteria1 = "" Then
            Range("E3").Select
            Exit Function
        ElseIf strCriteria2 = "" Then
            Range("E4").Select
            Exit Function
        End If
0
FarWestCommented:
Target is coming as a parameter from the event, if you use it inside any other sub you should it as parameter

Private Sub Worksheet_Change(ByVal Target As Range)

if you wish send the complete code and  please use Code tag in comment editor
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
Here is the complete function - as I see it, where this is definitely required- I put it in here. I could just create another module and call to it I guess. Programming in Excel is not my forte - I am more familiar with Access and programming around the objects and controls. This is not my creation, it involved 6 different coders, and to tell the truth - it looks like a mess:

Public Function fnCallRequired()
Dim strCriteria1 As String
Dim strCriteria2 As String

    strCriteria1 = nz(Trim(Range("E3").Value), "")
    strCriteria2 = nz(Trim(Range("E4").Value), "")
    
    If Union(Range("I17:I30"), Target).Address = Range("I17:I30").Address Then
    UCase Target

    Else
    
    If strCriteria1 = "" Or strCriteria2 = "" Then
        MsgBox "You must select a Sales Org and Doc Type"
        Sheets("Template").Select
        If strCriteria1 = "" Then
            Range("E3").Select
            Exit Function
        ElseIf strCriteria2 = "" Then
            Range("E4").Select
            Exit Function
        End If
      
    End If
    End If

    fnRequiredFields eReq, 4
    ActiveWorkbook.Save
    
End Function

Open in new window

0
dawber39Database Analyst / Application DeveloperAuthor Commented:
But I have to maintain it
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
Instead of the Else after that code- perhaps it should be an End If - so it can contine
0
FarWestCommented:
when do you want to run fnCallRequired
is it when cell changed?
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
Yes - that would be ideal. What I find confusing is there are buttons to retrieve material lists, Validate and save, and search Customers... but none of these buttons appear to be executing a macro or specific code - (or at least I cannot find one attached to it). I have tried breaking through, and stepping through the code- and I'll be damned if I can see it. I know the validate or Materials buttons do contact a mule service connected to an SAP for the information retrieved, you can see the site for the mule service but there is nothing distinguishing the buttons attached to that call. There is so much code in there, and even some that is "Commented Out" so it doesn't execute. Its ridiculous, never seen anything like it. But to answer your question - yes I would like to haver that happen as soon as the cell loses focus
0
FarWestCommented:
LOL, you think it is a complete mess,
never mind, please note that code can be distributed 1) workbook level 2) sheet Level 3) user module Level 4) from Level
and on to of that Excel can execute code in other workbooks and on templates like PERSONAL.XLSB
for buttons in sheets go to design mode right click on Button and then select Assign Macro, then you will find the macro assigned to the control and in what file,
for forms button just double click on control then you will go to associated code
back to our problem
because you did not send the whole code functions like nz , fnRequiredFields are not known for me also variables like eReq and some others
in general I understood  what you want to do is to capitalize values that user enter in I17:I30
and validate that values in E3,E4 are not null (although it depends on what nz function do)
first as i seams from fnCallRequired logic it should be called before saving the workbook (something like before post validation)  , the UCASE function for I17:130 can be done on change event as proposed
although it can be called as will in fnCallRequired

so:
1) Modify UCASE sub to have another name like MyUcase because UCASE is reserved
2) In  MyUcase  there is a parameter (Target) that is never used if you need it to Capitalize  only changed cell then the code should be changed totally or even just replaced with Target.Value = UCase(Target.Value)
3) put this in the worksheet who has the cells that needs to be capitalized code section
Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Range("I17:I30"), Target).Address = Range("I17:I30").Address Then
MyUCase Target
End If
fnCallRequired
End Sub

Open in new window

2) put this in ThisWorkBook code section so it will be called before any saving to the workbook
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'call conver to upper case for all ranges
MyUCase Range("I17:I30")
Cancel = fnCallRequired
End Sub

Open in new window


and put this in a Module

Public Function fnCallRequired() As Boolean
Dim strCriteria1 As String
Dim strCriteria2 As String

    strCriteria1 = nz(Trim(Range("E3").Value), "")
    strCriteria2 = nz(Trim(Range("E4").Value), "")
  
    If strCriteria1 = "" Or strCriteria2 = "" Then
        MsgBox "You must select a Sales Org and Doc Type"
        Sheets("Template").Select
        If strCriteria1 = "" Then
            Range("E3").Select
            fnCallRequired = True
            Exit Function
        ElseIf strCriteria2 = "" Then
            Range("E4").Select
            fnCallRequired = True
            Exit Function
        End If
    
    End If
    End If
    fnCallRequired = False

    fnRequiredFields eReq, 4
    'ActiveWorkbook.Save
    
End Function
                                          
                  
Public Sub MyUCase(ByVal Target As Range)
    Dim rCol As Range
 For Each rCol In Target.Cells
    rCol.Value = UCase(rCol.Value)
 Next
End Sub
                        

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dawber39Database Analyst / Application DeveloperAuthor Commented:
Thanks man..... I really appreciate your efforts. Love this site
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
I found the problem within that huge abomination of code. The code actually checks each iteration of the material number as it progresses, and I thought it was just stopping t the first one. When each number is checked, it returns with either the number and appropriate info for that number, and if it is valid it returns a [ ] symbol. This is the key - because there is an if statement that says if it is returned like that, then the cell = " " then gives the testing function a PASSED - which in turn directs the code progression to skip over the MsgBox  "9988898897 is an invalid Material number". So - I removed the line giving it a pass - created another variable with an if statement and placed it into the existing if statement with the MsgBox, and added and end function thereafter. - Works perfectly - thank you for all your help. I will have to post this comment in several other places. Thanks again guys - you got me looking in the right places
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.