PDA

View Full Version : Solved: Validation



bkudulis
05-24-2004, 06:52 AM
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.

mark007
05-24-2004, 07:04 AM
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:


Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


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.

:)

bkudulis
05-24-2004, 11:07 AM
I think I am doing something wrong here because it is not working. This is what I have in there.

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

Zack Barresse
05-24-2004, 11:16 AM
hi!

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

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


see if that helps.

Richie(UK)
05-24-2004, 11:43 AM
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).

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 SubHTH

bkudulis
05-24-2004, 11:45 AM
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?

mark007
05-24-2004, 12:15 PM
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.

:)

Anne Troy
05-24-2004, 12:21 PM
Yeah, but those guys spell funny. They say things like programme and colour, dont' they?

:hi:
:rofl

mark007
05-24-2004, 12:26 PM
Yeah, but those guys spell funny. They say things like programme and colour, dont' they?

:hi:
:rofl

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

;)

Zack Barresse
05-24-2004, 02:11 PM
Hmm, what langauge do you americans speak again...?

;)
well, americanese of course. :giggle

Anne Troy
06-04-2004, 01:47 PM
Is this solved?

mark007
06-04-2004, 02:38 PM
It works exactly how I would like it to. Thank you very much for your help.

I guess so ;)