Consulting

Results 1 to 12 of 12

Thread: Solved: Validation

  1. #1
    VBAX Regular
    Joined
    May 2004
    Posts
    42
    Location

    Solved: Validation

    I have an Excel spreadsheet that keeps track of notes throughout the day. It is not a complicated form, the users types into one cell for each record that they want to enter. Once the user is finished with entering their notes for the day they hit a button that saves it into a different location with all of the other notes from the previous days. I would like to add a couple of columns that will have to be entered before the notes are tranferred. Once the users is done entering the note in the cell it will ask him "Why" (vbExclamation) and set the focus on the next cell. I know how to do this in a DB with the Event Procedures but do not know how to in Excel. Thank you in advance.

  2. #2
    BoardCoder
    Licensed Coder
    VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    619
    Location
    You want to use the change event of the worksheet I think. Open the IDE (alt F11) and double click on the sheet object in the project explorer on the left hand side. Now select the worksheet object in the top left dropdown of the resulting code window and then select the change event in the top right dropdown. This will give you the following:

    [vba]
    Private Sub Worksheet_Change(ByVal Target As Range)

    End Sub
    [/vba]

    You can then check the cell that was changed by looking at either the address, row or column property of the target to see if it is the cell you are looking for. If so then you can run your code asking why and selecting the next cell.

    Hope that helps.

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net | Professional Office Developers Association

  3. #3
    VBAX Regular
    Joined
    May 2004
    Posts
    42
    Location
    I think I am doing something wrong here because it is not working. This is what I have in there.

    [vba]Private Sub Worksheet_Change(ByVal Target As Range)
    If Not IsNull(a2) And IsNull(b2) Then
    strmessage = "You must enter why this happened in B2"
    intoptions = vbExclamation
    bytchoice = MsgBox(strmessage, intoptions)
    Cancel = True
    a2.SetFocus
    End If
    End Sub[/vba]

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    hi!

    this should get you where (i think) you're trying to go:

    [vba]Private Sub Worksheet_Change(ByVal Target As Range)
    If Not IsEmpty(Range("A2")) And IsEmpty(Range("B2")) Then
    MsgBox ("You must enter why this happened in B2")
    End If
    Range("B2").Activate
    End Sub
    [/vba]

    see if that helps.

  5. #5
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    Hi,

    I'm assuming you want to do this for a range of entries rather than a hard-coded reference to just one cell. Amend the rngData variable in the code below to suit your requirements. Also, if you are having trouble getting the code to run - make sure you have it as worksheet code rather than in a general module (right click on the sheet tab concerned and select View Code - that's where it belongs).

    [vba]Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngData As Range
    Const strmessage As String = "You must enter why this happened " & _
    "in the next cell"

    Set rngData = Me.Range("A1:A10")
    'the range where the notes are added

    If Not Intersect(rngData, Target) Is Nothing Then
    MsgBox prompt:=strmessage, Buttons:=vbExclamation
    Target.Offset(0, 1).Select
    End If
    'a change in our range, so give the prompt

    End Sub[/vba]HTH

  6. #6
    VBAX Regular
    Joined
    May 2004
    Posts
    42
    Location
    It works exactly how I would like it to. Thank you very much for your help. I usually use VBA in Access which seems to be different in Excel. Can you suggest a book that can help me with VBA and Macros in Excel?

  7. #7
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    619
    Location
    I would reccomend:

    Excel 2000 (or 2002) VBA Programmers Reference by Bullen & Green I believe. It's Wrox press.

    It is an excellent resource especially if you are used to VBA but just not excel.

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net | Professional Office Developers Association

  8. #8
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Yeah, but those guys spell funny. They say things like programme and colour, dont' they?


    :rofl
    ~Anne Troy

  9. #9
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    619
    Location
    Quote Originally Posted by Dreamboat
    Yeah, but those guys spell funny. They say things like programme and colour, dont' they?


    :rofl
    Hmm, what langauge do you americans speak again...?

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net | Professional Office Developers Association

  10. #10
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    Quote Originally Posted by mark007
    Hmm, what langauge do you americans speak again...?

    well, americanese of course.

  11. #11
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Is this solved?
    ~Anne Troy

  12. #12
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    619
    Location
    It works exactly how I would like it to. Thank you very much for your help.
    I guess so
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net | Professional Office Developers Association

Posting Permissions

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