+ Reply to Thread
Results 1 to 2 of 2

If then statement to control when the script runs

Hybrid View

realniceguy5000 If then statement to control... 04-29-2009, 01:20 PM
foxguy Re: If then statement to... 04-29-2009, 06:42 PM
  1. #1
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    If then statement to control when the script runs

    Hi,

    I have a small problem, I have a script in worksheet change sub, However I added a piece to sort the columns. Is there a way I can have the sort not take place till after the data is entered into range "B"

    As it is now the script runs as soon as the users enters anything in range "A"

    Thank You, Mike

    Private Sub Worksheet_Change(ByVal Target As Range)
        
        
        ActiveSheet.Unprotect "Password"
       
        If Target.Cells.Count > 1 Then
            Exit Sub
        End If
        On Error GoTo ErrHandler:
        If Not Application.Intersect(Me.Range("A5:B100"), Target) Is Nothing Then
            If IsNumeric(Target.Value) = False Then
                Application.EnableEvents = False
                'Target.Value = StrConv(Target.Text, vbLowerCase)
                'Target.Value = StrConv(Target.Text, vbUpperCase)
                
                Target.Value = StrConv(Target.Text, vbProperCase)
                Application.EnableEvents = True
            End If
        End If
    ErrHandler:
        Application.EnableEvents = True
        
    'Part 2 
            Application.ScreenUpdating = False
                      
            Range("A3:B100").Select
            Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
     
            Application.ScreenUpdating = True
                
               
        
        ActiveSheet.Protect "Password"
        
    End Sub

  2. #2
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: If then statement to control when the script runs

    'This line returns TRUE whenever anyone enters anything in Column A or B
    If Not Application.Intersect(Me.Range("A5:B100"), Target) Is Nothing Then
    'This line return TRUE whenever anyone enters anything in Column B Only
    If Not Application.Intersect(Me.Range("B5:B100"), Target) Is Nothing Then

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1