auto answer messagebox?

rutger

Board Regular
Joined
Apr 5, 2005
Messages
74
Hey there,

I have the following problem:
I have a peace of code that copies a cells value to each second next column. (this copies that value for one week). I can also enter a value in every 8th column to copie the value for one day. When copying for one week, a message box shows up every 8th column asking if i want to copy the value for that whole day.
Is it possible to give those messageboxes a default answer ("NO")?
I tried setting EnableEvents to false but then other pieces of my code stop functioning.

Thanks in advance,
Greetz,
Rutger
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
One of the MsgBox properties is: "vbDefaultButton" there is one for each button position 1 to 4. Just chain the one you want as the default to your Button property!


If you use the Windows Scripting Object in place of the Excel VBA Msgbox you will have greater control over the MsgBox like this:


Sub myTimeBox()
'Sheet module code, like: Sheet1.
'Close message after time if no action!
Dim myTimedBox As Object
Dim boxTime%, myExpired%, myOK%, myQuestBox%

'Access timed message box.
Set myTimedBox = CreateObject("WScript.Shell")
boxTime = 3

'Get user responce!
myQuestBox = myTimedBox.Popup("Click OK!" & vbCr & vbCr & "Or," & vbCr & _
vbCr & "Do nothing and this message will close in 3 seconds.", _
boxTime, "Select ""OK"" to Continue!", vbOKCancel + vbvbDefaultButton2)

'User Selected "OK."
If myQuestBox = 1 Then
'Add any code in place of code below for this condition!
myOK = myTimedBox.Popup("You Clicked OK!" & vbCr & vbCr & "Or," & vbCr & _
vbCr & "Do nothing and this message will close in 3 seconds.", _
boxTime, "You Took Action!", vbOKOnly)

Else

'User took no Action!
myExpired = myTimedBox.Popup("You Did Not Click OK!" & vbCr & vbCr & "Or," & vbCr & _
vbCr & "Do nothing and this message will close in 3 seconds.", _
boxTime, "No Action Taken!", vbOKOnly)
End If

End Sub
 
Upvote 0
Rutger

How is this message box getting generated?

It doesn't sound to me like a standard Excel message.
 
Upvote 0
msgbox is a vba function

experiment with this

in vba editor (click at+f11) you get vbeditor click control R you get project. click your file and click insert module and copy this programme

Public Sub test()
MsgBox "you have opened a message box"
End Sub

keep cursor at the end of the first line and click function F5 you will get a mesage box.click ok and get out of it

now you know what it is.
 
Upvote 0
If the problem is the message boxes bugging you and you would rather not see them, try setting a variable that turns MsgBox display on/off and then check this before showing any message boxes.

eg:

Code:
Dim msgsOK As Boolean
Dim ans As Integer

msgsOK = False       'change to True if you want to see messages, False to turn them off
ans = 7

If msgsOK Then
    ans = MsgBox("Do you want to copy the value for that whole day ?", vbYesNo, "Copy data")
End If

If ans = 6 Then
    'Do this if the ans was YES
Else
    'Do this if the ans was NO
End If
 
Upvote 0
venkat1926

I don't know if your post is a response to mine but what I'm trying to establish is how exactly this message box is being created.
 
Upvote 0
there are too many replies likely to be confusing. however your pointed question "how is message box generated" (hope you know what is meant by vba)
the answer is
it is generated through vba

in vb editor you copy these three lines

Public Sub test()
MsgBox "you have opened a message box"
End Sub

keeping the cursor somewhere within the above three lines you click function key F5 see what happens.
is this clear now.
 
Upvote 0
venkat1926

I know what a message box is.:)

That is not the only way a message could be displayed when using VBA.

The 'message box' described could also be one generated by Excel itself, for example a message asking if you want to overwrite data in a cell.
 
Upvote 0
venkat1926

I know what a message box is.:)

That is not the only way a message could be displayed when using VBA.

The 'message box' described could also be one generated by Excel itself, for example a message asking if you want to overwrite data in a cell.
 
Upvote 0

Forum statistics

Threads
1,203,491
Messages
6,055,727
Members
444,814
Latest member
AutomateDifficulty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top