Excel Form and message box

[Pam Field]

Hi there,

I'm running Excel 2000. I am just learning so please excuse my questions if
they sound simple.

To put this as simply as possible I have a spreadsheet of children's names.
I've created a form that is activated by a control button. What I want to
do is choose a child's name from the combo box and eventually delete that
child from the spreadsheet. I have an "are you sure" type msgbox but cannot
get the yes/no side of it functioning correctly. I've worked on this for
hours and have just given up.

This is my code so far:

Private Sub cboRemChild_Click()
Dim myCell As Range
Dim ChosenName As String
Dim NameFound As Boolean
Dim Ans As Integer

ChosenName = cboChildName.Text
Sheets("Child Records").Select

NameFound = False
For Each myCell In Range("Name_of_Child")
If myCell.Value = ChosenName Then
myCell.Select
NameFound = True
Unload Me
Exit For
End If
Next myCell

If NameFound = False Then
MsgBox "Name not entered or not Found!"
cboChildName.SetFocus
End If

MsgBox "Are you sure you want to remove this child?", vbYesNo

If Ans = vbYes Then
Selection.Delete Shift:=xlUp
Range("A6").Select
Else
frmRemChild.Show
End If
End Sub
-------------------
If either yes or no are chosen it performs the Else command. What have I
done wrong? It would also be great to have the Exclamation Mark icon appear
in this msgbox. Oh yes, if you've got this far thank you very much for
taking the time to read my ramblings

Thanks for any assistance in advance and I'm really sorry if my code looks
extremely amateur but that's me

Regards
Pam

 

[Maistrye]

Hi there,

I'm running Excel 2000. I am just learning so please excuse m
questions if
they sound simple.

To put this as simply as possible I have a spreadsheet of children'
names.
I've created a form that is activated by a control button. What I wan
to
do is choose a child's name from the combo box and eventually delet
that
child from the spreadsheet. I have an "are you sure" type msgbox bu
cannot
get the yes/no side of it functioning correctly. I've worked on thi
for
hours and have just given up.

This is my code so far:
...
If NameFound = False Then
MsgBox "Name not entered or not Found!"
cboChildName.SetFocus
End If

MsgBox "Are you sure you want to remove this child?", vbYesNo

If Ans = vbYes Then
Selection.Delete Shift:=xlUp
Range("A6").Select
Else
frmRemChild.Show
End If
End Sub
-------------------
If either yes or no are chosen it performs the Else command. What hav
I
done wrong? It would also be great to have the Exclamation Mark ico
appear
in this msgbox. Oh yes, if you've got this far thank you very muc
for
taking the time to read my ramblings

Thanks for any assistance in advance and I'm really sorry if my cod
looks
extremely amateur but that's me

Regards
Pam

Hi Pam,

Try changing the following line:
MsgBox "Are you sure you want to remove this child?", vbYesNo

to this:
ans = MsgBox "Are you sure you want to remove this child?", vbYesNo

I think that should fix your problems. (You just need to assign th
return value to your variable in order to be able to use it)

Scot

 

[Pam Field]

Thanks Scott,

Unfortunately that give me a 'syntax error' but your response has reminded
me that I don't have an actual ans= statement and I know I must have but
what and where? I'm getting way to old to learn this stuff!

Any other assistance would be greatly appreciated.

cheers
Pam

 

[Pam Field]

Ok I've changed the variable to YesNo (found it on some website) and that
works now I just have some tidying up to do. Hope nobody minds if I come
back with more questions on this one

YesNo = MsgBox("Are you sure you want to remove this child?",

vbYesNo + vbExclamation, "Caution")

Select Case YesNo
Case vbYes
Selection.Delete Shift:=xlUp
Range("A6").Select
Case vbNo
frmRemChild.Show
End Select

 

[JON JON]

Hello,

Try this code

ans = MsgBox ("Are you sure you want to remove this child?",
vbYesNo+vbExclamation)