Results 1 to 4 of 4

Thread: What this Private Sub does?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jan 2011
    Posts
    110

    Post What this Private Sub does?

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cellContents As String
        Dim valLength As Integer
        cellContents = Trim(Str(Val(Target.Value)))
        valLength = Len(cellContents)
        If valLength <> 3 Then
            MsgBox ("Please enter a 3 digit area code.")
            Cells(9, "C").Select
        Else
            Cells(9, "C").Value = cellContents
            Cells(9, "D").Select
        End If
    End Sub

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: What this Private Sub does?

    It's an event handler. When the worksheet changes, it runs. It checks a cell and makes sure that the contents has a length of three. No more, no less. Thou shalt not count one, nor shall thou count two unless proceeding directly to three. Five is right out!

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Fanatic Member dmaruca's Avatar
    Join Date
    May 2006
    Location
    Jacksonville, FL
    Posts
    577

    Re: What this Private Sub does?

    bravo! bravo! lol i love monty python

  4. #4
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: What this Private Sub does?

    I would also suggest adding the .EnableEvents Line

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cellContents As String
        Dim valLength As Integer
        cellContents = Trim(Str(Val(Target.Value)))
        valLength = Len(cellContents)
        
        Application.EnableEvents = False
        
        If valLength <> 3 Then
            MsgBox ("Please enter a 3 digit area code.")
            Cells(9, "C").Select
        Else
            Cells(9, "C").Value = cellContents
            Cells(9, "D").Select
        End If
        
        Application.EnableEvents = True
    End Sub
    Sid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width