Message Box - Yes/No

[mathel]

Hi, I am running Excel 2003 and have created a Mssg Box for a workbook that
requires a Yes / No response. For some reason, the 'Yes' response needs to
be 'double clicked' to activiate it. Can someone tell me how, or if it can
be changed to 'single click'. The code I have is:

Sub ClearDoc()

Dim response As Long
answer = MsgBox("Do you have another Agency Billing to complete ?",
vbYesNo + vbQuestion)

If answer = vbYes Then

Sheets("Input").Select
Range("A6:h35").Select
Selection.ClearContents
Range("A6").Select

Else

Sheets("Input").Select
Range("A6").Select

MsgBox "Costs have been recorded. This file will now close", vbInformation

Application.ScreenUpdating = True
ActiveWorkbook.Close SaveChanges:=False

End If
End Sub

Thanks

 

[Dave Peterson]

If you slow down your mouse actions, does it work ok?

I'm guessing that you're just too quick with the first click.

 

[Mike H]

Hi,

There is nothing in the code that would require a double click but you could
try this simplified version. Note the code still selects A6 but unless you
have a particular reason for doing so i doubt it is necessary.

Sub ClearDoc()
answer = MsgBox("Do you have another Agency Billing to complete ?", vbYesNo
+ vbQuestion)
If answer = vbYes Then
Application.EnableEvents = False
Sheets("Input").Range("A6:h35").ClearContents
Application.EnableEvents = True
Sheets("Input").Range("A6").Select
Else
Sheets("Input").Range("A6").Select
MsgBox "Costs have been recorded. This file will now close", vbInformation
ActiveWorkbook.Close SaveChanges:=False
End If
End Sub

Mike

 

[john]

another approach you may want to consider:

Sub ClearDoc()
Dim ClearRange As Range

With Worksheets("Input")

.Activate

Set ClearRange = .Range("A6:H35")

.Range("A6").Select

End With

answer = MsgBox("Do you have another Agency Billing to complete ?", _
36, "Agency Billing")

If answer = 6 Then

Application.EnableEvents = False

ClearRange.ClearContents

Application.EnableEvents = True

Else

msg = MsgBox("Costs have been recorded. This file will now close", _
64, "Agency Billing")

ThisWorkbook.Close SaveChanges:=False

End If

End Sub

 

[mathel]

I tried slowing mouse action, double click on 'Yes' is still required. The
'No' needs single click only. I had actually tried UserForm before going
with the Message Box and had the same problem with the double click on the
'Yes'.

 

[mathel]

I tried what you suggested, the double click is still needed on the Yes
button. No is working fine (single click). Before using the Message Box, I
tried UserForm and had the same problem with the 'Yes' button.

 

[mathel]

I tried what you wrote in the wb I am using, and still had the same problem
of having to double click the Yes button. I then opened a 'blank' workbook
and ran the same code and have no problems.

Initially, I had set up a UserForm for the code, this was when I first ran
into the 'double click' problem. I tend to suspect the wb is 'contaminated'
and I should recreate it from scratch to.

 

[john]

I have not experienced the problem you have encountered before and without
seeing all you code could not offer any guesses what may be causing your
problem.

Unless others here can offer any further guidance, if transferring to
another workbook solves your problem that probably would be simplest solution
for you.

 

[RB Smissaert]

Works all OK here, apart from not declaring the answer variable.
Try this:

Sub ClearDoc()

Dim answer As VbMsgBoxResult

DoEvents
answer = MsgBox("Do you have another Agency Billing to complete ?", _
vbYesNo + vbQuestion)


RBS